4. Advanced Excel manipulation with pandas

Crash data rarely arrives as a single tidy table. A typical monthly extract from a state DOT or an insurance claims system may include separate files for crashes, vehicles, and occupants, each keyed by a crash identifier, each with its own column conventions, and each potentially containing thousands of rows. Before any metric can be computed, these fragments must be consolidated into a coherent analytical base. This chapter focuses on the three operations that carry the most weight in crash reporting workflows: multi-sheet consolidation with pandas, pivot table construction, and conditional formatting applied through xlsxwriter so that the final workbook communicates severity and trend at a glance.

Consolidating Multi-Sheet Crash Data

The consolidation pattern depends on whether the source sheets share a schema. When they do — for example, twelve monthly sheets named 2024-01 through 2024-12, each with identical columns — the correct approach is to read every sheet and concatenate them while preserving a provenance column. The provenance column matters because analysts frequently need to trace an anomalous record back to its source month.

import pandas as pd
from pathlib import Path

src = Path("data/crashes_2024.xlsx")
xl = pd.ExcelFile(src)

frames = []
for sheet in xl.sheet_names:
    df = xl.parse(sheet)
    df["source_sheet"] = sheet
    frames.append(df)

crashes = pd.concat(frames, ignore_index=True)
crashes["crash_date"] = pd.to_datetime(crashes["crash_date"], errors="coerce")
crashes = crashes.dropna(subset=["crash_id"]).drop_duplicates(subset=["crash_id"])

Two details in that snippet are load-bearing. First, errors="coerce" converts unparseable date strings into NaT rather than raising, which lets you count and inspect malformed dates instead of aborting the pipeline. Second, deduplication on crash_id is essential because monthly extracts often overlap at boundaries; without it, a crash occurring on the last day of a month may be counted twice, inflating every downstream severity rate.

When sheets do not share a schema — the common case when crashes, vehicles, and occupants live in separate sheets — concatenation is wrong. Use a relational join instead. Crash-level metrics such as fatal crash count must be computed on the crash table alone; joining vehicle or occupant rows first will multiply crash records and corrupt the denominator. A safe pattern is to aggregate the child table to one row per crash before merging:

vehicles = xl.parse("Vehicles")
veh_agg = (vehicles.groupby("crash_id")
           .agg(vehicle_count=("vehicle_id", "nunique"),
                max_severity=("severity", "max"))
           .reset_index())

crashes = crashes.merge(veh_agg, on="crash_id", how="left")
crashes["vehicle_count"] = crashes["vehicle_count"].fillna(0).astype(int)

After the merge, verify cardinality explicitly: len(crashes) must equal the deduplicated crash count from before the merge. If it grows, the child aggregation failed to produce a unique key and the join fanned out. This single assertion catches the most common consolidation defect in crash reporting.

Building Pivot Tables for Crash Metrics

Pivot tables answer the questions that drive safety decisions: how severity distributes across months, which collision types concentrate at which intersections, and whether a corridor's crash profile is changing. In pandas, pivot_table with an explicit aggfunc and margins=True produces a structure that maps cleanly onto an Excel pivot when written out.

crashes["month"] = crashes["crash_date"].dt.to_period("M").astype(str)
crashes["is_fatal"] = (crashes["severity"] == "Fatal").astype(int)
crashes["is_injury"] = crashes["severity"].isin(["Fatal", "Serious", "Minor"]).astype(int)

pivot = pd.pivot_table(
    crashes,
    index="month",
    columns="collision_type",
    values=["is_fatal", "is_injury"],
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="Total",
)

Note the deliberate choice to pivot on precomputed indicator columns rather than on severity directly with count. Counting rows gives crash counts; summing indicators gives the numerator for fatality and injury rates, which is what a safety engineer actually reports. The denominator — total crashes per month — should be kept in a parallel series so rates can be computed as is_fatal / total_crashes without re-deriving it inside the pivot.

For multi-level output, pass a list to index, for example ["month", "jurisdiction"]. Be aware that margins=True with multiple index levels produces subtotals that are easy to misread; when the pivot is destined for Excel, it is often cleaner to omit margins in pandas and let Excel's own pivot subtotal logic handle it, or to compute totals explicitly and append them as a labeled row.

Writing Formatted Workbooks with xlsxwriter

pandas' to_excel is convenient but limited: it cannot apply conditional formatting, freeze panes, or column widths in a single call. The xlsxwriter engine exposes those capabilities through a workbook object. The pattern is to create the writer, write each DataFrame to its own sheet, then apply formatting to the sheet's coordinate range.

with pd.ExcelWriter("crash_report.xlsx", engine="xlsxwriter") as writer:
    crashes.to_excel(writer, sheet_name="Crashes", index=False)
    pivot.to_excel(writer, sheet_name="Pivot")

    wb = writer.book
    ws = writer.sheets["Pivot"]

    ws.freeze_panes(1, 1)
    ws.set_column(0, 0, 14)
    ws.set_column(1, 12, 11)

    red = wb.add_format({"bg_color": "#F8D7DA", "font_color": "#842029"})
    amber = wb.add_format({"bg_color": "#FFF3CD", "font_color": "#664D03"})
    green = wb.add_format({"bg_color": "#D1E7DD", "font_color": "#0F5132"})

    last_row = len(pivot)
    last_col = len(pivot.columns)
    ws.conditional_format(1, 1, last_row, last_col, {
        "type": "cell", "criteria": ">=", "value": 5, "format": red})
    ws.conditional_format(1, 1, last_row, last_col, {
        "type": "cell", "criteria": "between",
        "minimum": 2, "maximum": 4, "format": amber})
    ws.conditional_format(1, 1, last_row, last_col, {
        "type": "cell", "criteria": "<=", "value": 1, "format": green})

The coordinate arguments to conditional_format are zero-indexed (first_row, first_col, last_row, last_col), and they refer to the worksheet grid, not the DataFrame. Because the pivot was written with its index, row 0 holds the header and column 0 holds the month labels; the data block therefore starts at row 1, column 1. Off-by-one errors here are the most frequent cause of formatting that appears shifted by one row or column.

For rate-based metrics, a three-color scale communicates magnitude more honestly than fixed thresholds, because crash counts vary widely by month and jurisdiction:

ws.conditional_format(1, 1, last_row, last_col, {
    "type": "3_color_scale",
    "min_color": "#63BE7B",
    "mid_color": "#FFEB84",
    "max_color": "#F8696B",
})

Choose the formatting strategy to match the metric's semantics. Absolute counts suit fixed thresholds when the reporting standard defines them — for example, flagging any intersection with five or more injury crashes in a quarter. Rates and indices suit color scales, since their interpretation is relative. Mixing the two on one sheet without labeling which columns use which rule is a common source of misreading in published reports.

End-to-End Verification

Before distributing the workbook, confirm three things. First, the consolidated crash count equals the sum of unique crash identifiers across all source sheets, minus any documented duplicates. Second, the pivot's grand total equals the consolidated count for count-based pivots, or equals the sum of the indicator column for rate-based pivots. Third, open the written file and inspect that the conditional formatting range covers exactly the data block — no header cells tinted, no trailing empty rows included. A quick programmatic check reads the file back with pd.read_excel and compares shapes against the in-memory frames; a mismatch usually indicates a sheet was written with an unexpected index.

The diagram above summarizes the flow and, more importantly, the verification gates that sit between stages. Each gate is a cheap assertion that prevents a class of silent error: a duplicated crash inflating a fatality count, a fan-out join multiplying records, or a formatting range that tints the header row and misleads a reader into treating a label as a value. In crash reporting, where a single miscounted fatality can trigger a corridor safety review, these checks are not optional polish — they are part of the deliverable.