Slyza13/Transport-Analysis---Week-2-Project

Transform raw South African statistics into actionable insights about transport equity, economic mobility, and policy implications through consistent weekly analysis. # Week 2: Transport Affordability Burden Analysis (South Africa) ## 📋 Overview This project is **Week 2 of a 90-Day Data Analytics Execution Plan**. It builds directly on **Week 1: Transport Expenditure Analysis**, extending the analysis from household spending to **labour market affordability**. **Core Question:** What percentage of a worker's income is consumed by transport costs in South Africa? --- ## 🎯 Core Finding **Transport sector workers spend 6.65% of their monthly salary on transport costs** (R2,223 out of R33,443 in average monthly earnings). ## 🔍 The Deeper Insight The analysis reveals a **regressive burden** where lower-income sectors bear a disproportionately higher cost: - **Trade sector:** 11.7% burden - **Mining sector:** 4.0% burden - **Trade workers experience nearly 3x the burden** of mining workers --- ## 📊 Data Sources - **Quarterly Employment Survey (QES), Q3 2025** – Statistics South Africa - Transport sector employment: 958,000 workers - Total gross earnings for the transport sector - **Household Expenditure Survey (HES)** – Statistics South Africa - Provincial transport expenditure (from Week 1 project) - National average: R21,930 annual per household --- ## 🔧 Methodology & Data Pipeline ### 1. Employment Data Processing (`week2_analysis.py`) - Loaded raw QES employment Excel tables - Programmatically detected header rows - Cleaned and standardized: Year, Quarter, Number of employees - Filtered to latest period: **2025 Q1 (March)** ### 2. Earnings Data Processing - Applied same cleaning logic to QES gross earnings tables - Verified earnings scale and converted values appropriately ### 3. Aggregation & Calculation - Aggregated employment and earnings across all transport subsectors - Calculated **average monthly earnings**: Average Monthly Earnings = (Total Gross Earnings × 1,000) ÷ Number of Employees ÷ 3 *Note: Division by 3 converts quarterly to monthly earnings* ### 4. Affordability Burden Metric - Imported national avera …