preprocess.py

import pandas as pd
from pathlib import Path
import os

def process_data():
    print("Loading data...")
    # Read the large CSV
    # Use appropriate dtypes to save memory if needed
    df = pd.read_csv("final_combined_2020_2024_std10.csv", low_memory=False)
    
    print("Data loaded. Processing...")
    
    # Create output directory
    output_dir = Path("EDA/dashboard_data")
    output_dir.mkdir(parents=True, exist_ok=True)
    
    # Ensure Date is datetime
    df['Incident Date'] = pd.to_datetime(df['Incident Date'], errors='coerce')
    df['Year'] = df['Incident Date'].dt.year
    df['Month'] = df['Incident Date'].dt.month
    
    # 1. yearly_trends.csv
    print("Generating yearly_trends.csv...")
    yearly = df.groupby('Year').size().reset_index(name='count')
    yearly.to_csv(output_dir / "yearly_trends.csv", index=False)
    
    # 2. monthly_trends.csv
    print("Generating monthly_trends.csv...")
    monthly = df.groupby(['Year', 'Month']).size().reset_index(name='count')
    monthly.to_csv(output_dir / "monthly_trends.csv", index=False)
    
    # 3. hourly_distribution.csv
    print("Generating hourly_distribution.csv...")
    hourly = df.groupby('Incident Hour').size().reset_index(name='count')
    hourly.to_csv(output_dir / "hourly_distribution.csv", index=False)
    
    # 4. offense_categories.csv
    print("Generating offense_categories.csv...")
    cats = df.groupby('Offense Category').size().reset_index(name='count')
    cats.to_csv(output_dir / "offense_categories.csv", index=False)
    
    # 5. top_offense_names.csv
    print("Generating top_offense_names.csv...")
    offenses = df.groupby('Offense Name').size().reset_index(name='count')
    offenses.to_csv(output_dir / "top_offense_names.csv", index=False)
    
    # 6. top_locations.csv
    print("Generating top_locations.csv...")
    locs = df.groupby('Location Name').size().reset_index(name='count')
    locs.to_csv(output_dir / "top_locations.csv", index=False)
    
    # 7. victim_age_distribution.csv
    print("Generating victim_age_distribution.csv...")
    # Check column name for age
    age_col = 'Victim Age Group' if 'Victim Age Group' in df.columns else 'Victim Age'
    if age_col in df.columns:
        age = df.groupby(age_col).size().reset_index(name='count')
        age.to_csv(output_dir / "victim_age_distribution.csv", index=False)
    
    # 8. victim_race_by_offense.csv
    print("Generating victim_race_by_offense.csv...")
    race = df.groupby(['Offense Category', 'Victim Race']).size().reset_index(name='count')
    race.to_csv(output_dir / "victim_race_by_offense.csv", index=False)
    
    # 9. victim_sex_by_offense.csv
    print("Generating victim_sex_by_offense.csv...")
    sex = df.groupby(['Offense Category', 'Victim Sex']).size().reset_index(name='count')
    sex.to_csv(output_dir / "victim_sex_by_offense.csv", index=False)
    
    # 10. weapon_usage.csv
    print("Generating weapon_usage.csv...")
    weapon = df.groupby('Weapon Name').size().reset_index(name='count')
    weapon.to_csv(output_dir / "weapon_usage.csv", index=False)
    
    # 11. state_crime_rates.csv
    print("Generating state_crime_rates.csv...")
    # Approximate population by summing max population of each agency in the state
    # This is a heuristic
    if 'Population' in df.columns and 'Agency Name' in df.columns:
        agency_pop = df.groupby(['State', 'Agency Name'])['Population'].max().reset_index()
        state_pop = agency_pop.groupby('State')['Population'].sum().reset_index()
        state_counts = df.groupby('State').size().reset_index(name='count')
        state_rates = pd.merge(state_counts, state_pop, on='State')
        state_rates['CrimeRatePer100k'] = (state_rates['count'] / state_rates['Population']) * 100000
        state_rates.to_csv(output_dir / "state_crime_rates.csv", index=False)
    else:
        # Fallback if population not available
        state_counts = df.groupby('State').size().reset_index(name='count')
        state_counts['CrimeRatePer100k'] = state_counts['count'] # Dummy
        state_counts.to_csv(output_dir / "state_crime_rates.csv", index=False)

    # Optional: day_of_week_patterns.csv
    print("Generating day_of_week_patterns.csv...")
    df['DayOfWeek'] = df['Incident Date'].dt.day_name()
    dow = df.groupby('DayOfWeek').size().reset_index(name='count')
    # Sort by day order
    days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
    dow['DayOfWeek'] = pd.Categorical(dow['DayOfWeek'], categories=days, ordered=True)
    dow = dow.sort_values('DayOfWeek')
    dow.to_csv(output_dir / "day_of_week_patterns.csv", index=False)

    # Optional: victim_sex_by_offense_crosstab.csv
    print("Generating victim_sex_by_offense_crosstab.csv...")
    crosstab = pd.crosstab(df['Offense Category'], df['Victim Sex']).reset_index()
    crosstab.to_csv(output_dir / "victim_sex_by_offense_crosstab.csv", index=False)

    # 12. county_heatmap.csv (New)
    print("Generating county_heatmap.csv...")
    # Group by State, County, AND Offense Category for filtering
    county_counts = df.groupby(['State', 'County', 'Offense Category']).size().reset_index(name='count')
    
    # Try to map FIPS codes for better plotting
    # We will download a mapping file
    try:
        print("Downloading FIPS mapping...")
        fips_url = "https://raw.githubusercontent.com/kjhealy/fips-codes/master/county_fips_master.csv"
        fips_df = pd.read_csv(fips_url, encoding='ISO-8859-1')
        # fips_df has 'state_name', 'county_name', 'fips'
        # Normalize names for merging
        fips_df['state_name'] = fips_df['state_name'].str.upper()
        fips_df['county_name'] = fips_df['county_name'].str.upper().str.replace(' COUNTY', '').str.replace(' PARISH', '')
        
        # Map state abbreviations to full names if needed
        state_map = {
            'NY': 'NEW YORK',
            'WA': 'WASHINGTON',
            'NM': 'NEW MEXICO',
            'TX': 'TEXAS',
            'CO': 'COLORADO',
            'Colorado': 'COLORADO',
            'New Mexico': 'NEW MEXICO',
            'Texas': 'TEXAS',
            'Washington': 'WASHINGTON'
        }
        
        county_counts['State_Upper'] = county_counts['State'].map(state_map).fillna(county_counts['State'].str.upper())
        county_counts['County_Upper'] = county_counts['County'].str.upper().str.replace(' COUNTY', '').str.replace(' PARISH', '')
        
        merged = pd.merge(
            county_counts, 
            fips_df[['state_name', 'county_name', 'fips']], 
            left_on=['State_Upper', 'County_Upper'], 
            right_on=['state_name', 'county_name'], 
            how='left'
        )
        # Pad FIPS with leading zeros (5 digits)
        merged['fips'] = merged['fips'].fillna(0).astype(int).astype(str).str.zfill(5)
        merged = merged[merged['fips'] != '00000'] # Remove unmapped
        
        merged.to_csv(output_dir / "county_heatmap.csv", index=False)
    except Exception as e:
        print(f"Failed to generate FIPS mapping: {e}")
        # Fallback: just save the counts, maybe app can handle it or we skip
        county_counts.to_csv(output_dir / "county_heatmap.csv", index=False)

    print("Done!")

if __name__ == "__main__":
    process_data()