4. Data cleaning and transformation for crash safety

Crash safety datasets rarely arrive analysis-ready. A typical extract from a vehicle crash database, a police collision report feed, or an OEM test archive contains missing occupant fields, severity codes that differ between source systems, and injury scales that mix AIS, ISS, and free-text descriptions in the same column. Before any visual can be trusted — before a severity distribution chart or a body-region heat map means anything — these inconsistencies must be resolved in a deterministic, documented way. This chapter covers the three transformation tasks that dominate crash safety preparation: handling missing values, standardizing severity codes, and normalizing injury scales.

Why crash data is structurally messy

Crash records are collected under field conditions, not laboratory conditions. An occupant may be missing a restraint status because the vehicle was unoccupied at impact; a fatality may lack a hospital injury record because the victim died at the scene; a severity field may be blank because the reporting officer used a paper form that was later transcribed incompletely. These are not random gaps — they are informative missingness, and treating them as noise will bias every downstream visual. The first discipline is therefore to classify missingness before filling it.

A practical classification for crash data distinguishes three cases. Structurally missing values are impossible by definition: a pedestrian record has no "seat position" field, so a blank there is correct and should be preserved as a distinct category rather than imputed. Unknown-but-relevant values are missing because the information was not captured: restraint use, ejection status, airbag deployment. These should be flagged, not silently defaulted to "No." Recoverable values can be derived from other fields — for example, an occupant's injury severity can sometimes be reconstructed from a linked hospital record keyed on the crash ID.

Handling missing values without distorting the picture

The default instinct — fill nulls with zero or with the column average — is dangerous in crash safety. Filling a missing "Number of airbags deployed" with zero converts an unknown into a false negative, which will understate deployment rates in every visual. Filling a missing injury severity with the mean AIS produces a fractional value that no clinician would recognize. Instead, apply a hierarchy of treatments:

• Preserve as explicit "Unknown" for categorical fields where the absence itself is meaningful (restraint use, ejection, airbag status). Use a dedicated value such as "Unknown" rather than null so it appears as its own bar in charts.
• Derive from related fields where a deterministic rule exists. If Injury_Description contains "fractured femur" and AIS_Code is null, the AIS body region and severity can be assigned from a lookup table rather than imputed statistically.
• Exclude from the specific visual, not from the model when a field is too sparse to support a given chart. Filter the visual to rows where the field is populated, and state the denominator in the visual's subtitle.
• Impute only as a last resort, and only for numeric fields where a defensible model exists — and always keep the original null flag alongside the imputed value so the imputation can be audited.

For the worked example in this chapter, assume a crash extract of 12,480 occupant rows. Restraint status is null in 1,340 rows (10.7%), of which 980 are pedestrian records (structurally missing) and 360 are vehicle occupants with genuinely unknown status. The correct treatment is to set the 980 pedestrian rows to "Not Applicable" and the 360 occupant rows to "Unknown", then verify that the two categories sum to 1,340. A quick row-count check in Power Query — grouping by the new status column — confirms the transformation did not lose or duplicate records.

Standardizing severity codes

Severity is the backbone of nearly every crash safety visual, and it is the field most likely to arrive in incompatible encodings. One source may use the KABCO scale (Killed, A-injury, B-injury, C-injury, O-property damage only), another may use numeric codes 1–5, and a third may use free text such as "serious," "minor," "fatal." Standardizing means mapping every variant onto a single controlled vocabulary with a defined ordinal order, because severity is ordered and charts must respect that order.

A defensible target vocabulary for this course is a five-level scale aligned to KABCO, with an explicit numeric rank for sorting:

Implement the mapping in Power Query as a lookup table joined on the raw code, not as a nested if-chain. A lookup table is auditable, extensible when a new source appears, and keeps the transformation logic out of the query script. After the merge, validate that no row retains an unmapped code: filter the merged column for nulls and expect zero rows. If unmapped codes appear, add them to the lookup rather than coercing them to a default — a silent default to "Property Damage Only" would systematically understate injury burden.

Normalizing injury scales

Injury scales answer a different question than crash severity. Crash severity describes the event's outcome category; injury scales describe the anatomical harm to a specific body region. The two most common scales in crash data are AIS (Abbreviated Injury Scale, 1–6 per body region) and ISS (Injury Severity Score, 0–75, derived from the three most severe AIS regions). Mixing them in one column — some rows carrying an AIS code, others an ISS total — makes any aggregate meaningless.

Normalization here means three concrete operations. First, separate the scales into distinct columns: AIS_Severity (1–6), AIS_BodyRegion (a coded region such as head, chest, abdomen, extremities), and ISS_Total (0–75). Second, recompute ISS from AIS where the components exist rather than trusting a pre-aggregated total that may have been calculated under a different AIS version. The ISS rule is: take the highest AIS in each of the three most severely injured ISS body regions, square each, and sum. Third, flag version mismatches: AIS 2005 and AIS 2015 assign different severities to some injuries, so a dataset that mixes versions must carry an AIS_Version column, and any cross-version comparison must be labeled as such.

A reproducible check for the ISS recomputation: for a sample of 50 rows where both component AIS values and a reported ISS exist, compute ISS from components and compare to the reported value. Expect exact agreement where the version matches; where it does not, inspect the three highest regions manually before deciding whether the reported total or the recomputation is authoritative. Document the decision in the query's step names so the next analyst can follow it.

Putting the transformations in order

The three tasks are not independent, and their sequence matters. Handle missing values first, because a null severity code cannot be mapped and a null AIS cannot be recomputed. Standardize severity second, because the severity rank is often an input to injury-scale logic (for example, filtering to injury crashes before computing ISS distributions). Normalize injury scales last, because ISS recomputation depends on clean AIS components. Each step should end with a row-count assertion and a null-check on the transformed column; a transformation that changes the row count or leaves unexpected nulls is a defect, not a feature.

Finally, name every query step descriptively — Tagged Missingness Class, Merged Severity Lookup, Recomputed ISS from AIS — so the Applied Steps pane reads as a narrative of the cleaning logic. When a visual later shows an unexpected severity distribution, the analyst can walk the steps backward and locate exactly where the data diverged from expectation. Cleaning is not a prelude to analysis; in crash safety it is the analysis's foundation, and it must be as auditable as the visuals it supports.