Skip to content

README.md

Latest commit

 

History

History
343 lines (262 loc) · 14.5 KB

README.md

File metadata and controls

343 lines (262 loc) · 14.5 KB
Raw

NJ DOT Car Crash Data

Analysis of NJ DOT car crash data.

Plots:

Methods:

Plots

I've only done a very quick first pass at cleaning and plotting the data here, so take these with a grain of salt.

There is a marked decrease in "injury" and "property damage" crashes since the onset of COVID (≈March 2020), but fatal crashes are roughly flat:

Crashes per Month (Statewide)

Injuries per Month (Statewide)

Property Damage Crashes per Month (Statewide)

Deaths per Month (Statewide)

Crashes per {County, Month}

Injuries per {County, Month}

Property Damage Crashes per {County, Month}

Deaths per {County, Month}

Crashes per Year (Statewide)

Injuries per Year (Statewide)

Property Damage Crashes per Year (Statewide)

Deaths per Year (Statewide)

Crashes per {County, Year}

Injuries per {County, Year}

Property Damage Crashes per {County, Year}

Deaths per {County, Year}

Crash-Type Percentages

Injuries, Property Damage, Deaths (as Percentage of All Crashes)

Deaths (as Percentage of All Crashes)

Methods

rawdata.py is a CLI for downloading+caching .zips, extracting .txts, cleaning+converting to .pqt (Parquet).

./rawdata.py --help
# Usage: rawdata.py [OPTIONS] COMMAND [ARGS]...
# 
# Options:
#   --help  Show this message and exit.
# 
# Commands:
#   check-nj-agg      For one or more years, verify the `NewJersey` file is a
#                     concatenation of the county-specific files
#   parse-fields-pdf  Parse fields+lengths from one of the `*CrashTable.pdf`s,
#                     using Tabula
#   pqt               Convert 1 or more unzipped {year, county} `.txt` files to
#                     `.pqt`s, with some dtypes and cleanup
#   txt               Convert 1 or more {year, county} .zip files (convert each
#                     .zip to a single .txt)
#   zip               Download 1 or more {year, county} .zip file(s)

Example: Download + Clean Data 

./rawdata.py zip -r NewJersey  # download statewide-aggregated `.zip`s for [2001,2020] x {Accidents,Drivers,Occupants,Pedestrians,Vehicles}
./rawdata.py txt -r NewJersey  # Extract each `.zip` (to a single `.txt`)
./rawdata.py pqt -r NewJersey  # Clean (parse dates, assign some dtypes) + convert to Parquet

Notebooks

SQLite DBs

njdot compute pqt -f
njdot compute db -f

cmymc.ipynb: generate cmymc.db containing several {county, muni, year, month} aggregation tables.

New Year Data Pipeline

When new crash data is released (typically annually), follow this pipeline to process and publish it:

1. Download Raw Data

# Download zip files for the new year (e.g., 2023)
rawdata zip -r NJ -y 2023

# Or use last 2 digits
rawdata zip -r NJ -y 23

# Force re-download if needed
rawdata zip -r NJ -y 2023 -ff

The rawdata zip command:

  • Downloads 5 record types: Accidents, Drivers, Occupants, Pedestrians, Vehicles
  • Caches HTTP headers (Date, Content-Length, Last-Modified, ETag) in njdot/data/.cache.pqt
  • Only re-downloads when headers change (除了 Date)
  • Stores zips in njdot/data/{year}/{region}{year}{type}.zip

2. Extract Text Files

# Extract zip files to .txt
rawdata txt -r NJ -y 2023

This extracts each zip to a single .txt file with fixed-width comma-delimited records.

3. Check Schema Changes

# Check field patterns to detect schema changes
rawdata fsck fields -r NJ -y 2023

# Check specific record type
rawdata fsck fields -r NJ -y 2023 -t Vehicles

Common schema changes:

  • Field width changes (e.g., 2023 Vehicles: Model field expanded from 20→30 chars)
  • New fields added or removed (e.g., 2023 Vehicles: HazMat Placard removed)
  • Value changes (e.g., 2023: Hit & Run blank→'N' instead of always blank)

If schema changes are detected:

  1. Update njdot/rawdata/fixes.py with appropriate fix functions
  2. Apply fixes in rawdata pqt step via get_fixes() in pqt.py

2023 Data Quality Issues

The 2023 NJDOT data had several data quality regressions not present in prior years (2001-2022):

  1. Empty municipality names (16 records):

    • Records had valid county/municipality codes but blank municipality names
    • Fix: Geocoded using lat/lon coordinates via spatial join with NJGIN municipality boundaries
    • Location: njdot/rawdata/pqt.py (applied during parquet generation)

  2. County name conflicts (5 records):

    • Records with correct county code but wrong county name (e.g., cc=3 Burlington with cn="Middlesex")
    • Fix: Majority voting - use most common county name for each county code
    • Location: njdot/harmonize-muni-codes.ipynb cell [6]

  3. Municipality name typos (2 records):

    • cc=3, mc=24: "Mount Laurel Twp" (1 record) vs. "Mount Holly Twp" (382 records)
    • Fix: Majority voting - use most common municipality name for each (cc, mc, year) tuple
    • Location: njdot/harmonize-muni-codes.ipynb cell [8]

  4. Type suffix variations:

    • Raw data contains both "Township"/"Twp" and "Borough"/"Boro" variations
    • Previously caused noisy "conflicts" (10 in 2023: Deptford Township/Twp, Washington Township/Twp, etc.)
    • Fix: Normalize "Township"→"Twp", "Borough"→"Boro" before conflict detection
    • Location: njdot/harmonize-muni-codes.ipynb cell [2]

  5. Decimal formatting in integer fields (69,536 Accident records):

    • "Distance To Cross Street" field has trailing decimals: '50.0', '0.00', '25.0', etc.
    • Pattern: Values 0-99 formatted with decimals (e.g., '50.0', '0.00'); larger values sometimes have trailing . (e.g., '100.', '135.')
    • 99.996% are whole numbers (69,533); only 3 have fractional parts (0.5 twice, 2.7 once)
    • This field had NO decimals in 2001-2022 data (always formatted as '50', '100', etc.)
    • Fix: Targeted decimal-stripping for this specific field only
      • Verifies fractional value histogram matches expected {0.5: 2, 2.7: 1} (warns if unexpected values found)
      • Strips trailing decimals (.d*) and truncates the 3 fractional values to integers
    • Location: njdot/load.py:load_year_df() (lines 99-130)

Architecture: Structural data issues (empty names) are fixed during parquet generation in pqt.py. Name conflicts and typos are resolved via majority voting in the harmonize-muni-codes.ipynb notebook, which previously asserted zero conflicts but now handles them gracefully. Decimal formatting issues are handled during integer type conversion in load.py.

4. Generate Per-Year Parquet Files

# Generate .pqt files for the new year
rawdata pqt -r NJ -y 2023

# Force regeneration if needed
rawdata pqt -r NJ -y 2023 -f

This creates:

  • njdot/data/{year}/{region}{year}{type}.pqt
  • Applies schema fixes, type conversions, and cleanup
  • Files are typically 10-50MB each

Verify the parquets:

python3 -c "import pandas as pd; df = pd.read_parquet('njdot/data/2023/NewJersey2023Accidents.pqt'); print(f'Rows: {len(df)}')"

5. Generate Combined Parquet Files

# Generate combined parquets across all years
env -u PYTHONPATH njdot compute pqt -f

Note: Run with env -u PYTHONPATH to avoid shadowing PyGithub package.

This performs geo-processing for crashes:

  1. Original location geocoding (olat/olon → occ/omc):

    • Creates GeoDataFrame from crash report lat/lon
    • Spatial join with NJ municipality geometries → original county/muni codes

  2. Interpolated location geocoding (sri/mp → ilat/ilon → icc/imc):

    • Uses SRI (Standard Route Identifier) + milepost data
    • Geocodes to lat/lon via nj_sri_mp.db (download from S3 if missing)
    • Spatial join with NJ municipality geometries → interpolated county/muni codes

  3. Deduplication: Resolves duplicate spatial join results using original cc/mc from crash record

Outputs combined parquets in njdot/data/:

  • crashes.parquet (~280MB, includes geo-processing)
  • drivers.parquet (~200MB)
  • occupants.parquet (~200MB)
  • pedestrians.parquet (~5MB)
  • vehicles.parquet (~240MB)

6. Generate SQLite Databases

# Generate SQLite databases for web app
env -u PYTHONPATH njdot compute db -f

This reads the combined parquets and creates indexed SQLite databases in www/public/njdot/:

  • crashes.db (~2.4GB)
  • drivers.db (~1GB)
  • occupants.db (~876MB)
  • pedestrians.db (~19MB)
  • vehicles.db (~1.1GB)

Indexes are created for common query patterns (e.g., (severity, dt, cc, mc) for crashes).

7. Track with DVC

# DVC automatically adds .dvc files during compute db
# Commit the changes
git add njdot/data/2023/*.pqt.dvc
git add njdot/data/*.parquet.dvc
git add www/public/njdot/*.db.dvc
git commit -m "Add 2023 NJDOT crash data"

8. Upload to S3

# Databases are automatically uploaded during compute db
# Manually upload if needed:
dvc push

9. Verify Web App

# Start dev server
cd www
npm run dev

Visit http://localhost:3000/#njdot and verify:

  • New year appears in year selector
  • Crash counts match expected values
  • Maps render correctly

Dependencies

SRI Database (nj_sri_mp.db): Required for milepost geocoding in step 5. Download from S3 if missing:

aws s3 cp s3://nj-crashes/nj_sri_mp.db nj_sri_mp.db

To regenerate or update SRI data:

# Fetch SRI data for all counties (slow, rate-limited)
nj-crashes sri county all fetch-sris --max-num -1

Municipality Geometries: Automatically pulled by DVC when needed. Located at nj_crashes/munis.geojson.

Troubleshooting

HTTP header caching issues: If rawdata zip isn't downloading updated files, check .cache.pqt for stale entries. Can force with -ff flag.

UTF-8 decode errors: Raw crash data contains invalid UTF-8 sequences. Use LC_ALL=C for text processing tools (trim, col, etc.).

Schema changes: Run rawdata fsck fields to detect. Add fixes to njdot/rawdata/fixes.py and update get_fixes() mapping.

Geo-processing errors: Ensure nj_sri_mp.db exists and municipality geometries are available. Check for geopandas version compatibility (spatial join column names changed in recent versions).

Missing 2023 data in DB: Check that combined parquet includes 2023 (step 5) before building DB (step 6). Delete cached parquets to force regeneration.

Caveats / TODOs

The fatal crash stats here also seem to differ from NJSP's data (see the root of this repository) by ≈10%.

Attributions:

TODO: add to www pages