Spaces:
Sleeping
Sleeping
File size: 7,456 Bytes
79d2b4d | 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 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 | 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()
|