2. Crash data sources and formats

Crash reporting pipelines rarely receive data in a single, tidy shape. A state DOT may publish collision records as a flat CSV export, a telematics vendor may push JSON event payloads, and an internal safety team may maintain a manually curated Excel workbook with severity classifications. Before any aggregation, charting, or Power BI modeling can happen, all three must be parsed into a consistent tabular structure with trustworthy timestamps, normalized severity codes, and explicit handling of missing values. This chapter builds that ingestion layer with pandas, using realistic crash-record schemas as the working example.

Representative crash schemas across the three formats

Assume a crash dataset with the following core fields: a crash identifier, an occurrence timestamp, latitude and longitude, a severity code, the number of vehicles involved, and a free-text or coded weather condition. The three source formats express these fields differently, and the differences matter more than the file extension.

The KABCO severity scale (K = fatal, A = suspected serious injury, B = suspected minor injury, C = possible injury, O = property damage only) is the most common convention in U.S. crash records. Whatever the source calls it, the ingestion layer should map it to a single ordered categorical type so that downstream filters and Power BI slicers behave predictably.

Parsing CSV crash exports

CSV is the simplest format but the most fragile in practice. Read it with explicit dtypes and explicit missing-value tokens rather than letting pandas guess:

import pandas as pd

csv_crashes = pd.read_csv(
    "data/crashes_2024_q1.csv",
    dtype={"crash_id": "string", "severity": "string"},
    na_values=["", "NA", "N/A", "Unknown", "-999"],
    parse_dates=["crash_datetime"],
    keep_default_na=True,
)

Two details are worth calling out. First, forcing crash_id to string prevents pandas from stripping leading zeros or converting identifiers to integers. Second, listing -999 as a missing token is essential: many legacy exports use sentinel values for unknown coordinates, and leaving them in place will drag your map centroids toward the Gulf of Guinea. After loading, verify the parse with csv_crashes.dtypes and csv_crashes["crash_datetime"].isna().sum(); a nonzero count of unparsed timestamps usually means the source uses a non-ISO format such as 03/14/2024 07:42 AM, which requires an explicit format= argument or pd.to_datetime(..., format="mixed").

Parsing JSON event payloads

JSON crash feeds typically arrive as either a list of records or newline-delimited JSON (NDJSON). For a list of records, pd.read_json works, but nested objects need flattening. A robust pattern is to load with the standard library and normalize:

import json
import pandas as pd

with open("data/telematics_events.json", encoding="utf-8") as f:
    payload = json.load(f)

json_crashes = pd.json_normalize(
    payload,
    sep="_",
    record_path=None,
)

json_crashes = json_crashes.rename(columns={
    "location_lat": "latitude",
    "location_lon": "longitude",
    "event_time": "crash_datetime",
})

json_normalize with sep="_" flattens {"location": {"lat": ...}} into location_lat, which keeps the schema readable. If the feed uses epoch milliseconds, convert explicitly: pd.to_datetime(json_crashes["crash_datetime"], unit="ms", utc=True). Always parse epoch values as UTC first, then convert to the reporting timezone with .dt.tz_convert("America/Chicago"); skipping the UTC step is the single most common cause of crashes appearing in the wrong hour bucket in Power BI.

Parsing Excel crash workbooks

Excel introduces its own hazards: merged header rows, multiple sheets, and serial dates. Read with an explicit sheet and skip the title rows:

xlsx_crashes = pd.read_excel(
    "data/safety_review.xlsx",
    sheet_name="Crashes",
    skiprows=3,
    dtype={"crash_id": "string"},
    na_values=["", "#N/A", "Unknown"],
)

Excel datetime cells usually arrive as proper datetime64 values, but text-formatted dates do not. Check with pd.api.types.is_datetime64_any_dtype(xlsx_crashes["crash_datetime"]); if it returns False, coerce with pd.to_datetime(..., errors="coerce") and inspect the resulting NaT rows to see which source cells were malformed. Excel serial numbers (e.g., 45365) can be converted with pd.to_datetime(45365, unit="D", origin="1899-12-30"), though this is rarely needed when the column is typed correctly.

Normalizing severity codes and timestamps

Once all three frames share column names, apply a single normalization function. Severity should become an ordered categorical so that sorting and Power BI legend order follow the KABCO hierarchy rather than alphabetical order:

SEVERITY_MAP = {
    "K": "Fatal", "fatal": "Fatal", "1": "Fatal",
    "A": "Serious Injury", "serious": "Serious Injury", "2": "Serious Injury",
    "B": "Minor Injury", "minor": "Minor Injury", "3": "Minor Injury",
    "C": "Possible Injury", "possible": "Possible Injury", "4": "Possible Injury",
    "O": "Property Damage Only", "pdo": "Property Damage Only", "5": "Property Damage Only",
}

def normalize_severity(series: pd.Series) -> pd.Series:
    cleaned = series.astype("string").str.strip().str.lower()
    mapped = cleaned.map({k.lower(): v for k, v in SEVERITY_MAP.items()})
    return pd.Categorical(
        mapped,
        categories=["Fatal", "Serious Injury", "Minor Injury",
                    "Possible Injury", "Property Damage Only"],
        ordered=True,
    )

Unmapped codes become NaN in the categorical, which is exactly what you want: they surface in a data-quality report instead of silently polluting a severity chart. For timestamps, standardize to a single timezone-aware column and derive analysis fields such as crash_date, crash_hour, and day_of_week once, at ingestion, so every downstream consumer agrees.

Handling missing values deliberately

Missing data in crash records is rarely random. Coordinates may be missing for rural crashes, severity may be pending investigation, and weather may be uncoded. Treat missingness as a first-class field: add boolean indicator columns such as has_coordinates and severity_pending, then decide per column whether to drop, impute, or retain. Dropping rows with missing coordinates is appropriate for mapping but wrong for severity counts; a single global dropna() silently biases every downstream metric. A practical rule is to keep the full frame, filter at the point of each analysis, and log the row counts removed at each filter so the Power BI report can display a data-completeness note.

Verifying the ingestion before it reaches Power BI

Run a short validation block after concatenating the three normalized frames. Check that crash_datetime is timezone-aware and non-null for at least 99% of rows, that every severity value falls inside the five ordered categories, and that coordinate pairs are either both present or both missing. A quick assertion set catches the majority of silent ingestion bugs:

assert crashes["crash_datetime"].dt.tz is not None
assert crashes["severity"].notna().mean() > 0.99
assert (crashes["latitude"].isna() == crashes["longitude"].isna()).all()
print(crashes.groupby("severity", observed=False).size())

If the severity groupby shows an unexpected NaN bucket, inspect the raw codes with crashes.loc[crashes["severity"].isna(), "severity_raw"].value_counts() and extend the mapping table rather than dropping the rows. If timestamps fail the timezone assertion, the culprit is almost always a JSON feed parsed without utc=True or an Excel column that arrived as text. Fixing these at the ingestion boundary keeps every downstream chapter — aggregation, dashboards, and DAX measures — built on a frame whose types and semantics are already correct.