Python Topic #5 — Learning by Doing
CCRS California Crashes
Business Intelligence Risk Discovery Report
A multidimensional Power BI report documenting hypotheses, findings, and evaluative analysis over California crash data from 2019–2025. This topic bridges SQL exploration and Machine Learning by identifying the variables that showed consistent risk signal before predictive modeling.
Informe multidimensional en Power BI que documenta hipótesis, hallazgos y análisis valorativo sobre datos de accidentes de California entre 2019 y 2025. Este tópico conecta la exploración SQL con Machine Learning al identificar las variables que mostraron señal consistente de riesgo antes del modelado predictivo.
2,906,173Total crashesAccidentes totales
1,626,464Injured personsPersonas lesionadas
28,522FatalitiesFallecidos
0.98%Overall fatality rateTasa fatal global
Report Logic
Lógica del Informe
1
Hypothesis: Fatality does not evolve proportionally to crash volume
Hipótesis: La mortalidad no evoluciona proporcionalmente al volumen de accidentes
Hypothesis. Years with the most crashes are not necessarily the years with the highest severity.
Hipótesis. Los años con más accidentes no necesariamente son los años con mayor severidad.
| Year | Crashes | % Crashes | Injured | % Injured | Killed | % Killed | Fatality Rate |
|---|
| 2022 | 405,249 | 13.94% | 226,304 | 13.91% | 4,661 | 16.34% | 1.15% |
| 2021 | 422,980 | 14.55% | 228,359 | 14.04% | 4,590 | 16.09% | 1.09% |
| 2023 | 409,815 | 14.10% | 230,844 | 14.19% | 4,269 | 14.97% | 1.04% |
| 2020 | 374,757 | 12.90% | 205,029 | 12.61% | 4,081 | 14.31% | 1.09% |
| 2024 | 416,917 | 14.35% | 236,847 | 14.56% | 4,030 | 14.13% | 0.97% |
| 2019 | 478,189 | 16.45% | 270,248 | 16.62% | 3,787 | 13.28% | 0.79% |
| 2025 | 398,266 | 13.70% | 228,833 | 14.07% | 3,104 | 10.88% | 0.78% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 100.00% | 28,522 | 100.00% | 0.98% |
Findings. 2019 has the highest crash volume, but 2022 has the highest fatalities and fatality rate. Severity increased strongly during 2020–2022 and then declined through 2025.
Hallazgos. 2019 tiene el mayor volumen de accidentes, pero 2022 presenta el mayor número de fallecidos y la mayor tasa de fatalidad. La severidad aumentó fuertemente entre 2020–2022 y luego descendió hacia 2025.
Evaluative analysis. Crash volume alone does not explain mortality. Temporal context carries risk signal and should be considered in downstream models.
Análisis valorativo. El volumen de accidentes por sí solo no explica la mortalidad. El contexto temporal contiene señal de riesgo y debe considerarse en modelos posteriores.
2
Hypothesis: Collision type separates frequency from severity
Hipótesis: El tipo de colisión separa frecuencia de severidad
| Code | Collision Type | Crashes | % Crashes | Injured | Killed | Fatality Rate |
|---|
| C | REAR END | 872,069 | 30.01% | 507,496 | 2,661 | 0.31% |
| B | SIDE SWIPE | 617,193 | 21.24% | 170,044 | 1,145 | 0.19% |
| E | HIT OBJECT | 517,563 | 17.81% | 181,841 | 6,769 | 1.31% |
| D | BROADSIDE | 517,182 | 17.80% | 471,452 | 4,577 | 0.88% |
| A | HEAD-ON | 134,202 | 4.62% | 121,109 | 3,510 | 2.62% |
| G | VEHICLE/PEDESTRIAN | 84,550 | 2.91% | 77,495 | 7,212 | 8.53% |
| H | OTHER | 79,346 | 2.73% | 32,145 | 703 | 0.89% |
| F | OVERTURNED | 69,943 | 2.41% | 55,486 | 1,865 | 2.67% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 28,522 | 0.98% |
Findings. Rear-end crashes dominate volume, but Vehicle/Pedestrian has the highest fatality rate and the highest death count among collision types.
Hallazgos. Rear End domina el volumen, pero Vehicle/Pedestrian tiene la mayor tasa de fatalidad y la mayor cantidad de fallecidos entre los tipos de colisión.
Evaluative analysis. Prioritizing by volume alone would mislead decision-makers. Collision type must be evaluated by both frequency and severity.
Análisis valorativo. Priorizar solo por volumen llevaría a conclusiones incorrectas. El tipo de colisión debe evaluarse por frecuencia y severidad.
3
Hypothesis: Administrative codes need semantic translation
Hipótesis: Los códigos administrativos necesitan traducción semántica
| Primary Factor Code | Crashes | % Crashes | Injured | Killed | Fatality Rate |
|---|
| A | 2,683,556 | 92.34% | 1,549,469 | 27,014 | 1.01% |
| B | 75,009 | 2.58% | 18,772 | 346 | 0.46% |
| D | 73,824 | 2.54% | 33,773 | 524 | 0.71% |
| C | 71,674 | 2.47% | 23,752 | 623 | 0.87% |
| Blank/Other | 2,110 | 0.07% | 698 | 15 | 0.71% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 28,522 | 0.98% |
Findings. Code A represents 92.34% of crashes, but the code alone has limited explanatory value without its operational meaning.
Hallazgos. El código A representa el 92.34% de los accidentes, pero el código por sí solo tiene valor explicativo limitado sin su significado operacional.
Evaluative analysis. This step led to a key modeling decision: use more meaningful violation groupings instead of raw administrative codes.
Análisis valorativo. Este paso condujo a una decisión clave de modelado: usar agrupaciones de infracciones más interpretables en lugar de códigos administrativos crudos.
4
Hypothesis: Pedestrian action codes contain hidden severity signal
Hipótesis: Los códigos de acción peatonal contienen señal oculta de severidad
| Code | Crashes | % Crashes | Injured | Killed | Fatality Rate |
|---|
| A | 2,777,774 | 95.58% | 1,511,774 | 20,475 | 0.74% |
| B | 40,669 | 1.40% | 39,226 | 1,256 | 3.09% |
| D | 24,904 | 0.86% | 21,720 | 2,835 | 11.38% |
| Blank/Other | 23,010 | 0.79% | 16,073 | 107 | 0.47% |
| E | 22,915 | 0.79% | 21,129 | 3,323 | 14.50% |
| F | 12,902 | 0.44% | 13,124 | 432 | 3.35% |
| C | 3,439 | 0.12% | 3,116 | 92 | 2.68% |
| G | 560 | 0.02% | 302 | 2 | 0.36% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 28,522 | 0.98% |
Findings. Codes D and E show extremely high fatality rates, but the codes required translation into pedestrian behaviors to become actionable.
Hallazgos. Los códigos D y E muestran tasas de fatalidad extremadamente altas, pero los códigos requirieron traducción a comportamientos peatonales para volverse accionables.
Evaluative analysis. Code-level BI revealed a signal; description-level BI explained it.
Análisis valorativo. El BI por código reveló la señal; el BI por descripción permitió explicarla.
5
Hypothesis: Some variables describe outcomes, not causes
Hipótesis: Algunas variables describen resultados, no causas
| Special Condition | Crashes | % Crashes | Injured | Killed | Fatality Rate |
|---|
| Blank / Normal | 2,755,572 | 94.82% | 1,577,381 | 9,176 | 0.33% |
| Private Property | 65,019 | 2.24% | 14,417 | 7 | 0.01% |
| On-Duty Emergency Vehicle | 20,221 | 0.70% | 7,100 | 1 | 0.00% |
| Fatal | 16,684 | 0.57% | 11,382 | 18,188 | 109.01% |
| Late-Reported | 14,416 | 0.50% | 5,509 | -1 | -0.01% |
| School Bus Collision | 7,902 | 0.27% | 1,544 | 0 | 0.00% |
| Courtesy Report | 6,468 | 0.22% | 2,683 | 0 | 0.00% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 28,522 | 0.98% |
Findings. “Fatal” produces an impossible 109.01% fatality rate because some fatal crashes include more than one death. This is not a cause; it is an outcome label.
Hallazgos. “Fatal” produce una tasa imposible de 109.01% porque algunos accidentes fatales incluyen más de una muerte. No es una causa; es una etiqueta de resultado.
Evaluative analysis. Special Condition was flagged as potential data leakage and excluded from predictive feature selection.
Análisis valorativo. Special Condition fue marcada como posible fuga de información y excluida de la selección de variables predictivas.
6
Hypothesis: Pedestrian behavior directly affects fatality risk
Hipótesis: El comportamiento peatonal afecta directamente el riesgo fatal
| Pedestrian Action Description | Crashes | % Crashes | Injured | Killed | Fatality Rate |
|---|
| NO PEDESTRIANS INVOLVED | 2,777,774 | 95.58% | 1,511,774 | 20,475 | 0.74% |
| CROSSING IN CROSS WALK AT INTERSECTION | 40,669 | 1.40% | 39,226 | 1,256 | 3.09% |
| CROSSING - NOT IN CROSSWALK | 24,904 | 0.86% | 21,720 | 2,835 | 11.38% |
| OTHER / UNKNOWN | 23,010 | 0.79% | 16,073 | 107 | 0.47% |
| IN ROAD - INCLUDES SHOULDER | 22,915 | 0.79% | 21,129 | 3,323 | 14.50% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 28,522 | 0.98% |
Findings. “In Road / Shoulder” reaches 14.50%, while “Crossing Not in Crosswalk” reaches 11.38%. Crosswalk at intersection is far lower at 3.09%.
Hallazgos. “In Road / Shoulder” alcanza 14.50%, mientras “Crossing Not in Crosswalk” alcanza 11.38%. Crosswalk en intersección es mucho menor con 3.09%.
Evaluative analysis. Protected pedestrian infrastructure appears associated with lower fatality risk. This variable became one of the strongest candidates for ML.
Análisis valorativo. La infraestructura peatonal protegida parece asociarse con menor riesgo fatal. Esta variable se convirtió en una de las candidatas más fuertes para ML.
7
Hypothesis: Visibility matters more than rain
Hipótesis: La visibilidad importa más que la lluvia
| Weather_1 | Crashes | % Crashes | Injured | Killed | Fatality Rate |
|---|
| CLEAR | 2,481,762 | 85.40% | 1,401,843 | 24,143 | 0.97% |
| CLOUDY | 298,140 | 10.26% | 159,973 | 3,316 | 1.11% |
| RAINING | 93,915 | 3.23% | 47,740 | 665 | 0.71% |
| FOG/VISIBILITY | 10,572 | 0.36% | 5,831 | 220 | 2.08% |
| SNOWING | 3,449 | 0.12% | 1,453 | 25 | 0.72% |
| OTHER | 2,558 | 0.09% | 902 | 27 | 1.06% |
| WIND | 2,102 | 0.07% | 1,120 | 30 | 1.43% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 28,522 | 0.98% |
Findings. Fog/Visibility has the highest fatality rate at 2.08%, while rain is lower than the global average at 0.71%.
Hallazgos. Fog/Visibility tiene la mayor tasa fatal con 2.08%, mientras lluvia queda por debajo del promedio global con 0.71%.
Evaluative analysis. Reduced visibility appears more severe than precipitation itself, supporting later cross-analysis with lighting and time.
Análisis valorativo. La visibilidad reducida parece más severa que la precipitación en sí, apoyando cruces posteriores con iluminación y hora.
8
Hypothesis: Secondary weather validates the visibility signal
Hipótesis: El clima secundario valida la señal de visibilidad
| Weather_2 | Crashes | % Crashes | Injured | Killed | Fatality Rate |
|---|
| Blank / None | 2,829,568 | 97.36% | 1,585,869 | 27,744 | 0.98% |
| RAINING | 58,084 | 2.00% | 29,737 | 486 | 0.84% |
| WIND | 7,138 | 0.25% | 4,107 | 157 | 2.20% |
| CLOUDY | 5,524 | 0.19% | 3,396 | 36 | 0.65% |
| SNOWING | 2,343 | 0.08% | 1,122 | 15 | 0.64% |
| FOG/VISIBILITY | 2,079 | 0.07% | 1,238 | 50 | 2.41% |
| OTHER | 744 | 0.03% | 502 | 10 | 1.34% |
| HAIL | 193 | 0.01% | 122 | 2 | 1.04% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 28,522 | 0.98% |
Findings. Fog/Visibility again leads severity at 2.41%, and Wind also rises to 2.20%.
Hallazgos. Fog/Visibility vuelve a liderar la severidad con 2.41%, y Wind también sube a 2.20%.
Evaluative analysis. The same signal appears across Weather_1 and Weather_2, strengthening confidence that visibility is a meaningful risk dimension.
Análisis valorativo. La misma señal aparece en Weather_1 y Weather_2, fortaleciendo la confianza en que la visibilidad es una dimensión de riesgo significativa.
9
Hypothesis: Violation groups reveal human risk behavior
Hipótesis: Los grupos de infracción revelan comportamiento humano de riesgo
| Violation Group | Crashes | % Crashes | Injured | Killed | Fatality Rate |
|---|
| Other / Unclassified | 866,476 | 29.82% | 586,335 | 12,081 | 1.39% |
| DUI / Alcohol | 245,344 | 8.44% | 133,559 | 6,090 | 2.48% |
| Unsafe Turn / Lane Change | 591,130 | 20.34% | 229,786 | 4,846 | 0.82% |
| Unsafe Speed | 839,425 | 28.88% | 489,330 | 4,357 | 0.52% |
| Stop Sign / Right of Way | 76,510 | 2.63% | 69,833 | 493 | 0.64% |
| Lane Violation | 215,948 | 7.43% | 62,256 | 345 | 0.16% |
| Failure To Yield | 71,340 | 2.45% | 55,365 | 310 | 0.43% |
| Total | 2,906,173 | 100.00% | 1,626,464 | 28,522 | 0.98% |
Findings. DUI/Alcohol has the highest fatality rate at 2.48%, about 2.5 times the global rate. Unsafe Speed dominates volume but not severity.
Hallazgos. DUI/Alcohol tiene la mayor tasa fatal con 2.48%, cerca de 2.5 veces la tasa global. Unsafe Speed domina el volumen, pero no la severidad.
Evaluative analysis. Human behavior became a central analytical theme. Violation Group was selected as a key predictive feature.
Análisis valorativo. El comportamiento humano se convirtió en un tema central del análisis. Violation Group fue seleccionada como variable predictiva clave.
10
Cross-Analysis: Testing whether the signals survive context
Cruces: Probar si las señales sobreviven al contexto
DUI / Alcohol Deep Dive
245,344DUI / Alcohol crashes
6,090DUI / Alcohol fatalities
2.48%DUI fatality rate
≈2.5xvs. global fatality rate
| DUI + Weather | Crashes | Killed | Fatality Rate |
|---|
| CLEAR | 212,518 | 5,186 | 2.44% |
| CLOUDY | 22,129 | 717 | 3.24% |
| RAINING | 7,410 | 97 | 1.31% |
| FOG/VISIBILITY | 1,278 | 53 | 4.15% |
| Total DUI | 245,344 | 6,090 | 2.48% |
| DUI + Time Bucket | Crashes | Killed | Fatality Rate |
|---|
| 22:00 - 00:00 | 39,086 | 886 | 2.27% |
| 00:00 - 02:00 | 37,941 | 1,049 | 2.76% |
| 20:00 - 22:00 | 36,234 | 718 | 1.98% |
| 18:00 - 20:00 | 29,800 | 661 | 2.22% |
| 02:00 - 04:00 | 29,037 | 752 | 2.59% |
| 04:00 - 06:00 | 10,950 | 398 | 3.63% |
| 06:00 - 08:00 | 5,924 | 238 | 4.02% |
| Total DUI | 245,344 | 6,090 | 2.48% |
Cross findings. DUI remains severe across contexts. Visibility problems and late-night / early-morning buckets amplify risk.
Hallazgos cruzados. DUI mantiene alta severidad en distintos contextos. Los problemas de visibilidad y los bloques de noche/madrugada amplifican el riesgo.
Evaluative analysis. These cross-filters demonstrate that fatality risk is not explained by one variable alone. Risk emerges through combinations: DUI + time + visibility + collision context.
Análisis valorativo. Estos filtros cruzados demuestran que el riesgo fatal no se explica por una sola variable. El riesgo emerge por combinaciones: DUI + hora + visibilidad + contexto de colisión.
11
Fatal Crashes Only: Profiling deaths instead of all crashes
Sólo accidentes fatales: Perfilar muertes en lugar de todos los accidentes
When filtering NumberKilled > 0, the analysis changes meaning. Fatality Rate is no longer useful because the dataset is already restricted to fatal crashes. The relevant questions become: Which categories contribute the most fatal crashes and fatalities?
Al filtrar NumberKilled > 0, el análisis cambia de significado. Fatality Rate deja de ser útil porque el conjunto ya está restringido a accidentes fatales. Las preguntas relevantes pasan a ser: ¿Qué categorías concentran más accidentes fatales y fallecidos?
26,370Fatal crashes
28,524Fatalities in fatal-crash profile
20.19%Fatal crashes from DUI
16.43%Fatal crashes in 2022
Findings. The fatal-crash-only view confirms recurring themes: DUI/Alcohol, late-day/night periods, visibility issues, and years 2021–2022 remain highly visible in the profile.
Hallazgos. La vista sólo de accidentes fatales confirma temas recurrentes: DUI/Alcohol, períodos tarde/noche, problemas de visibilidad y años 2021–2022 siguen apareciendo con fuerza.
Evaluative analysis. The same variables that appeared in overall severity analysis also appear when isolating fatal crashes. This consistency supports their selection for Machine Learning.
Análisis valorativo. Las mismas variables que aparecieron en el análisis general de severidad también aparecen al aislar accidentes fatales. Esta consistencia respalda su selección para Machine Learning.
12
Variables Selected for Machine Learning
Variables Seleccionadas para Machine Learning
Collision_Type_DescriptionPedestrianActionDescViolation_GroupCrash_Time_2H_BucketWeather_1Source_YearCity_Name / GIS later
Transition hypothesis. The variables discovered through Power BI should contain predictive signal for identifying fatal versus non-fatal crashes.
Hipótesis de transición. Las variables descubiertas mediante Power BI deben contener señal predictiva para identificar accidentes fatales vs no fatales.
Methodological bridge. Topic #5 documents how BI identified the candidate variables. Topic #6 will validate whether those variables generalize when training on 2019–2025 and testing against real 2026 data.
Puente metodológico. El Topic #5 documenta cómo BI identificó las variables candidatas. El Topic #6 validará si esas variables generalizan al entrenar con 2019–2025 y probar contra datos reales de 2026.
Executive Conclusion
Conclusión Ejecutiva
The Power BI risk discovery process demonstrated that crash severity is not driven by volume alone. The strongest signals emerged from the intersection of collision type, pedestrian behavior, violation group, time of day, and visibility-related conditions. Business Intelligence did more than summarize the data: it created the analytical foundation for predictive modeling.
El proceso de descubrimiento de riesgo en Power BI demostró que la severidad de los accidentes no depende únicamente del volumen. Las señales más fuertes emergieron de la intersección entre tipo de colisión, comportamiento peatonal, grupo de infracción, hora del día y condiciones relacionadas con visibilidad. Business Intelligence hizo más que resumir datos: creó la base analítica para el modelado predictivo.
Source workflow: CCRS open data → SQL Server → Power BI multidimensional analysis → selected variables for ML validation.