car-sales-data-engineering-analytics

# Car Sales Data Engineering & Analytics > Skill by [ara.so](https://ara.so) — Data Skills collection. A comprehensive data engineering and analytics framework for processing ~24K car sales records with ETL pipelines, statistical modeling, and interactive Streamlit dashboards. Provides 15 pre-built analyses covering pricing trends, regional patterns, demographic insights, and feature correlations. ## Installation This project uses `uv` for package management: ```bash # Clone the repository git clone https://github.com/Abdumalik-ProDev/Car-Sales-Data-Engineering.git cd Car-Sales-Data-Engineering # Install dependencies uv sync ``` **Dependencies:** Python 3.10+, pandas, numpy, matplotlib, scipy, streamlit ## Quick Start ### Launch Interactive Dashboard ```bash # Start Streamlit web UI uv run streamlit run src/ui.py # Alternative via entry point uv run python -m src.main ``` ### Run Full Pipeline ```bash # Execute all 15 analyses and generate figures uv run python -m src.main --pipeline ``` This will: - Load and clean `data/Car sales.csv` - Generate `outputs/cleaned_data.csv` - Create 15 PNG charts in `outputs/figures/` ## Core Module: `src/analysis.py` The main analysis engine provides ETL, statistics, and modeling capabilities. ### Data Loading & Cleaning ```python from src.analysis import CarSalesAnalysis # Initialize analyzer analyzer = CarSalesAnalysis('data/Car sales.csv') # Access cleaned data df = analyzer.data print(f"Total records: {len(df)}") print(f"Columns: {df.columns.tolist()}") # Save cleaned dataset analyzer.save_cleaned_data('outputs/cleaned_data.csv') ``` **Key Columns:** - `car_id`, `date`, `customer_name`, `dealer_name`, `company`, `model` - `year`, `price`, `body_style`, `transmission`, `color` - `dealer_no`, `dealer_region`, `phone`, `gender`, `annual_income` ### Statistical Summaries ```python # Get descriptive statistics stats = analyzer.describe_data() print(stats) # Revenue metrics total_revenue = analyzer.data['price'].sum() avg_price = analyzer.data['price'].mean() median_price = analyzer.data['price'].median() print(f"Total Revenue: ${total_revenue:,.0f}") print(f"Avg Price: ${avg_price:,.0f}") print(f"Median Price: ${median_price:,.0f}") ``` ### Generate Individual Analyses ```python # Q1: Price distribution analyzer.plot_price_distribution(save_path='outputs/figures/q1_price_dist.png') # Q2: Monthly sales trend analyzer.plot_monthly_sales_trend(save_path='outputs/figures/q2_monthly_trend.png') # Q3: Sales by region analyzer.plot_sales_by_region(save_path='outputs/figures/q3_regional_sales.png') # Q6: Income vs Price regression analyzer.plot_income_vs_price(save_path='outputs/figures/q6_income_price.png') # Q9: Automatic vs Manual transmission comparison (t-test) analyzer.compare_transmission_prices(save_path='outputs/figures/q9_transmission.png') ``` ### Statistical Modeling ```python # Q12: Multiple linear regression # Predicts price from year, annual_income, transmission analyzer.multiple_regression_analysis(save_path='outputs/figures/q12_regression.png') # Q13: Detect outliers using Z-scores analyzer.detect_outliers_zscore(save_path='outputs/figures/q13_outliers.png') # Q15: Test price normality with Shapiro-Wilk analyzer.test_normality(save_path='outputs/figures/q15_normality.png') ``` ## Streamlit Dashboard (`src/ui.py`) ### Page Structure The dashboard provides 6 interactive sections: 1. **📊 Overview** - Data summary, sample rows, statistics 2. **💰 Sales & Revenue** - Price trends, regional analysis 3. **👥 Demographics** - Gender, income patterns 4. **🔧 Product Insights** - Brand, body style, transmission 5. **📈 Statistical Modeling** - Regression, outliers, normality 6. **🔍 Filter & Explore** - Custom filters with CSV export 7. **⚖️ Compare Segments** - Side-by-side comparison with t-tests ### Custom Filtering Example ```python # Users can filter via sidebar widgets # Example: Filter cars by price range and region # In ui.py, the filter logic: filtered = analyzer.data.copy() if price_range: filtered = filtered[ (filtered['price'] >= price_range[0]) & (filtered['price'] <= price_range[1]) ] if selected_regions: filtered = filtered[filtered['dealer_region'].isin(selected_regions)] if selected_companies: filtered = filtered[filtered['company'].isin(selected_companies)] # Display and export st.dataframe(filtered) st.download_button( "Download CSV", filtered.to_csv(index=False), "filtered_sales.csv" ) ``` ## Common Analysis Patterns ### Price Analysis by Category ```python # Average price by car company company_prices = analyzer.data.groupby('company')['price'].mean().sort_values(ascending=False) print(company_prices.head(10)) # Price by body style body_prices = analyzer.data.groupby('body_style')['price'].agg(['mean', 'median', 'count']) print(body_prices) # Price by transmission type trans_prices = analyzer.data.groupby('transmission')['price'].describe() print(trans_prices) ``` ### Regional & Temporal Analysis ```python # Sales volume by region regional_sales = analyzer.data['dealer_region'].value_counts() print(regional_sales) # Monthly revenue trend analyzer.data['month'] = pd.to_datetime(analyzer.data['date']).dt.to_period('M') monthly_revenue = analyzer.data.groupby('month')['price'].sum() print(monthly_revenue) # Year-over-year comparison yearly_sales = analyzer.data.groupby('year').agg({ 'price': ['sum', 'mean', 'count'] }) print(yearly_sales) ``` ### Statistical Tests ```python from scipy import stats # Compare prices: Automatic vs Manual transmission auto_prices = analyzer.data[analyzer.data['transmission'] == 'Automatic']['price'] manual_prices = analyzer.data[analyzer.data['transmission'] == 'Manual']['price'] t_stat, p_value = stats.ttest_ind(auto_prices, manual_prices) print(f"T-statistic: {t_stat:.4f}, P-value: {p_value:.4f}") # Correlation between income and price correlation = analyzer.data['annual_income'].corr(analyzer.data['price']) print(f"Income-Price Correlation: {correlation:.4f}") ``` ## Configuration ### File Paths Default paths are defined in `src/analysis.py`: ```python # Customize data paths analyzer = CarSalesAnalysis('custom_path/sales_data.csv') # Custom output directory analyzer.save_cleaned_data('custom_output/cleaned.csv') # Figures directory os.makedirs('custom_figures', exist_ok=True) analyzer.plot_price_distribution(save_path='custom_figures/prices.png') ``` ### Streamlit Configuration Create `.streamlit/config.toml` for dashboard customization: ```toml [theme] primaryColor = "#FF4B4B" backgroundColor = "#FFFFFF" secondaryBackgroundColor = "#F0F2F6" textColor = "#262730" [server] port = 8501 headless = true enableCORS = false ``` ## Running Full Pipeline Programmatically ```python from src.analysis import CarSalesAnalysis import os # Initialize analyzer = CarSalesAnalysis('data/Car sales.csv') # Create output directories os.makedirs('outputs/figures', exist_ok=True) # Save cleaned data analyzer.save_cleaned_data('outputs/cleaned_data.csv') # Generate all 15 analyses analyses = [ ('q1_price_dist.png', analyzer.plot_price_distribution), ('q2_monthly_trend.png', analyzer.plot_monthly_sales_trend), ('q3_regional_sales.png', analyzer.plot_sales_by_region), ('q4_gender_split.png', analyzer.plot_gender_distribution), ('q5_income_region.png', analyzer.plot_income_by_region), ('q6_income_price.png', analyzer.plot_income_vs_price), ('q7_company_prices.png', analyzer.plot_avg_price_by_company), ('q8_body_style.png', analyzer.plot_price_by_body_style), ('q9_transmission.png', analyzer.compare_transmission_prices), ('q10_colors.png', analyzer.plot_popular_colors), ('q11_heatmap.png', analyzer.plot_body_transmission_heatmap), ('q12_regression.png', analyzer.multiple_regression_analysis), ('q13_outliers.png', analyzer.detect_outliers_zscore), ('q14_dealer_prices.png', analyzer.plot_dealer_prices), ('q15_normality.png', analyzer.test_normality), ] for filename, func in analyses: func(save_path=f'outputs/figures/{filename}') print(f"✓ Generated {filename}") ``` ## Troubleshooting ### Missing Data Issues ```python # Check for missing values missing = analyzer.data.isnull().sum() print(missing[missing > 0]) # Handle missing values analyzer.data = analyzer.data.dropna(subset=['price', 'year']) analyzer.data['annual_income'].fillna(analyzer.data['annual_income'].median(), inplace=True) ``` ### Date Parsing Errors ```python # Ensure proper date format analyzer.data['date'] = pd.to_datetime(analyzer.data['date'], errors='coerce') analyzer.data = analyzer.data.dropna(subset=['date']) ``` ### Memory Issues with Large Datasets ```python # Load only required columns usecols = ['price', 'company', 'body_style', 'dealer_region', 'year'] df = pd.read_csv('data/Car sales.csv', usecols=usecols) # Use dtype optimization df['price'] = df['price'].astype('float32') df['year'] = df['year'].astype('int16') ``` ### Streamlit Port Conflicts ```bash # Specify custom port uv run streamlit run src/ui.py --server.port 8502 # Or in config echo "[server]\nport = 8502" > .streamlit/config.toml ``` ## Key Insights Reference - **Total Records:** 23,906 sales - **Revenue:** $655.6M total - **Pricing:** $27,426 avg, $23,000 median - **Top Body Style:** SUV (27%) - **Top Region:** Austin (17%) - **Premium Brand:** Cadillac ($37,557 avg) - **Demographics:** 79% Male, 21% Female - **Transmission:** 53% Automatic, 47% Manual