Spaces:
Runtime error
Runtime error
File size: 4,173 Bytes
0c76799 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | """
Simplified data loading module.
"""
import pandas as pd
import os
def load_and_process_data(data_dir="data"):
"""Load and process all data files."""
data = {
'sales': pd.DataFrame(),
'car_models': pd.DataFrame(),
'recalls': pd.DataFrame(),
'dealers': pd.DataFrame()
}
# Define file paths
files = {
'sales_by_model': 'AU_Sales_By_Model.xlsx',
'car_models': 'car_models.xlsx',
'recalls': 'Car_Recalls.xlsx',
'dealers': 'dealers.xlsx',
'additional_sales': 'sales_by_model.xlsx'
}
sales_dfs = []
for key, filename in files.items():
filepath = os.path.join(data_dir, filename)
if os.path.exists(filepath):
try:
df = pd.read_excel(filepath)
print(f"✅ Loaded {filename}: {len(df)} rows")
if key in ['sales_by_model', 'additional_sales']:
# Check if it has the expected columns
expected_cols = ['Year', 'Month', 'Date', 'Model', 'Dealer ID', 'Quantity Sold', 'Profit']
if all(col in df.columns for col in expected_cols):
sales_dfs.append(df[expected_cols])
else:
print(f" Skipping - unexpected columns: {df.columns.tolist()}")
elif key == 'car_models':
data['car_models'] = df
elif key == 'recalls':
data['recalls'] = df
elif key == 'dealers':
data['dealers'] = df
except Exception as e:
print(f"❌ Error loading {filename}: {e}")
# Combine sales data
if sales_dfs:
combined_sales = pd.concat(sales_dfs, ignore_index=True)
# Clean data
combined_sales['Year'] = combined_sales['Year'].astype(int)
combined_sales['Date'] = pd.to_datetime(combined_sales['Date'])
combined_sales['Quantity Sold'] = pd.to_numeric(combined_sales['Quantity Sold'], errors='coerce')
combined_sales['Profit'] = pd.to_numeric(combined_sales['Profit'], errors='coerce')
# Remove NaN rows
combined_sales = combined_sales.dropna(subset=['Quantity Sold', 'Profit'])
# Add derived columns
combined_sales['Avg_Profit_Per_Unit'] = combined_sales['Profit'] / combined_sales['Quantity Sold']
combined_sales['Month_Num'] = combined_sales['Date'].dt.month
combined_sales['Quarter'] = combined_sales['Date'].dt.quarter
combined_sales['Year_Month'] = combined_sales['Date'].dt.strftime('%Y-%m')
data['sales'] = combined_sales
print(f"✅ Total sales records: {len(combined_sales)}")
else:
print("❌ No sales data files found")
return data
def merge_with_dealers(sales_df, dealers_df):
"""Merge sales with dealer info."""
if sales_df.empty or dealers_df.empty:
return sales_df
try:
merged = sales_df.merge(
dealers_df[['Dealer ID', 'Country', 'State', 'City', 'Dealer Name']],
on='Dealer ID',
how='left'
)
return merged
except Exception as e:
print(f"Error merging dealers: {e}")
return sales_df
def prepare_recalls_data(recalls_df):
"""Prepare recalls data."""
if recalls_df.empty:
return recalls_df
try:
df = recalls_df.copy()
df['Date'] = pd.to_datetime(df['Date'])
df['Year'] = df['Date'].dt.year
df['Units'] = pd.to_numeric(df['Units'], errors='coerce')
return df
except Exception as e:
print(f"Error preparing recalls: {e}")
return recalls_df
def get_data_summary(data):
"""Get data summary."""
sales = data.get('sales', pd.DataFrame())
if sales.empty:
return {}
return {
'total_revenue': sales['Profit'].sum(),
'total_units': sales['Quantity Sold'].sum(),
'unique_models': sales['Model'].nunique(),
'unique_dealers': sales['Dealer ID'].nunique()
}
|