Learning by Doing:
Python + SQL Server + California CrashesLearning by Doing:
Python + SQL Server + California Crashes

0. Cybersecurity & Production Warning

1. La idea del ejercicio

El punto de partida fue simple: aprender Python con un caso real, no con ejercicios artificiales. Primero pensamos en PDFs, pero luego apareció una oportunidad mejor: trabajar con datos abiertos oficiales de accidentes de tránsito de California.

Fuente abierta: California Crash Reporting System (CCRS), publicado en el California Open Data Portal.

https://data.ca.gov/dataset/ccrs

2. Archivos usados

Se descargaron los archivos Crashes del CCRS desde 2019 hasta 2026 y se guardaron en un folder local.

crashes_2019.csv
crashes_2020.csv
crashes_2021.csv
crashes_2022.csv
crashes_2023.csv
crashes_2024.csv
crashes_2025.csv
crashes_2026.csv

4. Profiling inicial de los CSV

Antes de cargar datos a SQL, se hizo un inventario. El objetivo era entender qué había realmente en el folder: archivos, tamaño, filas, columnas y nombres de campos.

from pathlib import Path
import pandas as pd

folder = Path.cwd()
csv_files = sorted(folder.glob("*.csv"))

print(f"Folder: {folder}")
print(f"CSV files found: {len(csv_files)}")
print("-" * 80)

inventory = []

for file in csv_files:
    print(f"Processing: {file.name}")

    sample = pd.read_csv(file, nrows=1000, low_memory=False)

    with open(file, "r", encoding="utf-8", errors="ignore") as f:
        row_count = sum(1 for _ in f) - 1

    inventory.append({
        "file_name": file.name,
        "size_mb": round(file.stat().st_size / (1024 * 1024), 2),
        "rows": row_count,
        "columns": len(sample.columns),
        "column_names": " | ".join(sample.columns)
    })

report = pd.DataFrame(inventory)
print(report[["file_name", "size_mb", "rows", "columns"]])
report.to_excel("csv_inventory_report.xlsx", index=False)
print("DONE - Report created: csv_inventory_report.xlsx")

Insight técnico: los 8 archivos tienen 74 columnas. Eso abrió la posibilidad de una tabla única en SQL Server.

5. Validación del esquema

No bastaba con saber que todos tenían 74 columnas. Había que confirmar que los nombres y el orden fueran exactamente iguales.

from pathlib import Path
import pandas as pd

folder = Path.cwd()
csv_files = sorted(folder.glob("*.csv"))

reference_file = csv_files[0]
reference_columns = list(pd.read_csv(reference_file, nrows=0).columns)

print(f"Reference file: {reference_file.name}")
print(f"Reference columns: {len(reference_columns)}")
print("-" * 80)

all_ok = True

for file in csv_files:
    columns = list(pd.read_csv(file, nrows=0).columns)
    same_names = columns == reference_columns
    print(f"{file.name}: {'OK' if same_names else 'DIFFERENT'}")

    if not same_names:
        all_ok = False
        missing = set(reference_columns) - set(columns)
        extra = set(columns) - set(reference_columns)
        print(f"  Missing columns: {missing}")
        print(f"  Extra columns: {extra}")

print("-" * 80)

if all_ok:
    print("SUCCESS - All CSV files have the same columns in the same order.")
else:
    print("WARNING - Some CSV files have different columns.")

Resultado: todos los archivos tenían las mismas columnas en el mismo orden.

6. Limpieza de nombres de columnas

Se detectó que algunas columnas tenían espacios al inicio o nombres poco cómodos para SQL. Por eso se normalizaron a nombres SQL-friendly.

import pandas as pd
import re

df = pd.read_csv("crashes_2019.csv", nrows=0)

def clean_column_name(col):
    col = col.strip()
    col = re.sub(r"[^A-Za-z0-9]+", "_", col)
    col = col.strip("_")
    return col

print("Original Column -> Clean Column")
print("-" * 80)

for col in df.columns:
    clean_col = clean_column_name(col)
    print(f"{col}  ->  {clean_col}")

7. Perfil de tipos y nulos

Se tomó una muestra de 10,000 filas para ver tipos de datos y valores nulos.

import pandas as pd

df = pd.read_csv(
    "crashes_2026.csv",
    nrows=10000,
    low_memory=False
)

profile = pd.DataFrame({
    "Column": df.columns,
    "DataType": df.dtypes.astype(str),
    "Nulls": df.isnull().sum().values,
    "NullPercent": round((df.isnull().sum() / len(df)) * 100, 2).values
})

print(profile)
profile.to_excel("column_profile.xlsx", index=False)
print("DONE - column_profile.xlsx created")

Observación: se detectaron columnas operacionales con alto porcentaje de nulos, algo normal en datasets administrativos: campos opcionales, evidencia, media, narrativas o datos que aplican solo a ciertos casos.

8. Validación de la llave: Collision Id

Antes de usar Collision Id como llave candidata, se verificó si era único en 2026.

import pandas as pd

df = pd.read_csv(
    "crashes_2026.csv",
    usecols=["Collision Id"]
)

print("Rows:", len(df))
print("Unique:", df["Collision Id"].nunique())
print("Duplicates:", len(df) - df["Collision Id"].nunique())

Conclusión: Collision_Id es excelente candidato para llave primaria, al menos en el archivo 2026 validado.

9. Carga piloto: 10,000 filas a SQL Server

Primero se cargó una muestra controlada a dbo.Crashes_Test. Esto permitió validar conexión, tabla, nombres de columnas y velocidad sin arriesgar la carga completa.

import pandas as pd
import re
from sqlalchemy import create_engine

def clean_column_name(col):
    col = col.strip()
    col = re.sub(r"[^A-Za-z0-9]+", "_", col)
    col = col.strip("_")
    return col

server = "JCDCOMPUTER"
database = "CaliforniaCrashes"
driver = "ODBC Driver 17 for SQL Server"

connection_string = (
    f"mssql+pyodbc://@{server}/{database}"
    f"?driver={driver.replace(' ', '+')}"
    f"&trusted_connection=yes"
)

engine = create_engine(connection_string, fast_executemany=True)

print("Reading sample data...")

df = pd.read_csv(
    "crashes_2026.csv",
    nrows=10000,
    low_memory=False
)

df.columns = [clean_column_name(col) for col in df.columns]

print("Rows to load:", len(df))
print("Columns:", len(df.columns))
print("Loading data to SQL Server...")

df.to_sql(
    name="Crashes_Test",
    con=engine,
    schema="dbo",
    if_exists="replace",
    index=False,
    chunksize=1000
)

print("DONE - 10,000 rows loaded into dbo.Crashes_Test")

USE CaliforniaCrashes;
GO

SELECT COUNT(*) AS TotalRows
FROM dbo.Crashes_Test;
GO

SELECT TOP 10 *
FROM dbo.Crashes_Test;
GO

10. Carga completa de 2026 por chunks

Después de validar la muestra, se cargó completo el archivo 2026 usando chunks. Este fue el primer ETL completo del laboratorio.

import pandas as pd
import re
from sqlalchemy import create_engine

def clean_column_name(col):
    col = col.strip()
    col = re.sub(r"[^A-Za-z0-9]+", "_", col)
    return col.strip("_")

server = "JCDCOMPUTER"
database = "CaliforniaCrashes"
driver = "ODBC Driver 17 for SQL Server"

connection_string = (
    f"mssql+pyodbc://@{server}/{database}"
    f"?driver={driver.replace(' ', '+')}"
    f"&trusted_connection=yes"
)

engine = create_engine(
    connection_string,
    fast_executemany=True
)

csv_file = "crashes_2026.csv"
chunk_size = 25000

total_rows = 0
first_chunk = True

print("Starting load...")

for chunk in pd.read_csv(
    csv_file,
    chunksize=chunk_size,
    low_memory=False
):

    chunk.columns = [clean_column_name(c) for c in chunk.columns]

    chunk.to_sql(
        "Crashes_2026",
        con=engine,
        schema="dbo",
        if_exists="replace" if first_chunk else "append",
        index=False,
        chunksize=5000
    )

    total_rows += len(chunk)
    print(f"Loaded: {total_rows:,} rows")
    first_chunk = False

print()
print("DONE")
print(f"Total rows loaded: {total_rows:,}")

SELECT COUNT(*) AS TotalRows
FROM dbo.Crashes_2026;

11. Primeros KPIs en SQL

Una vez cargada la tabla, el objetivo dejó de ser mover datos. El objetivo pasó a ser entender la historia que los datos contaban.

SELECT
    COUNT(*) AS TotalCrashes,
    SUM(NumberKilled) AS TotalKilled,
    SUM(NumberInjured) AS TotalInjured
FROM dbo.Crashes_2026;

Valoración: ya no era una prueba técnica. La data empezó a producir indicadores de negocio.

12. Top ciudades por volumen

SELECT TOP 10
    City_Name,
    COUNT(*) AS TotalCrashes
FROM dbo.Crashes_2026
GROUP BY City_Name
ORDER BY COUNT(*) DESC;

Insight: Unincorporated concentra una proporción enorme de accidentes. Eso no representa una ciudad única, sino áreas no incorporadas, probablemente con carreteras, zonas rurales o jurisdicciones fuera de límites urbanos.

13. Fatalidades por ciudad

SELECT TOP 10
    City_Name,
    SUM(NumberKilled) AS TotalKilled
FROM dbo.Crashes_2026
GROUP BY City_Name
ORDER BY SUM(NumberKilled) DESC;

Insight: el ranking por volumen y el ranking por fatalidades no cuentan la misma historia. Una ciudad puede tener muchos accidentes, pero otra puede tener menos accidentes y mayor severidad.

Ese fue el primer salto analítico importante: pasar de contar eventos a medir severidad.

14. Tasa de fatalidad por ciudad

Para eliminar el sesgo del volumen, se calculó la tasa de fatalidad. Se usó un umbral mínimo para evitar distorsiones por muestras demasiado pequeñas.

SELECT TOP 20
    City_Name,
    COUNT(*) AS TotalCrashes,
    SUM(NumberKilled) AS TotalKilled,
    CAST(
        SUM(NumberKilled) * 100.0 /
        NULLIF(COUNT(*),0)
        AS DECIMAL(10,2)
    ) AS FatalityRatePct
FROM dbo.Crashes_2026
GROUP BY City_Name
HAVING COUNT(*) > 100
ORDER BY FatalityRatePct DESC;

Valoración: Los Angeles domina en volumen, pero algunas ciudades más pequeñas presentan tasas relativas más altas. Esto enseña una regla de negocio clave: volumen y riesgo no son lo mismo.

15. Tipo de colisión: volumen, fatalidad y severidad

SELECT TOP 20
    Collision_Type_Description,
    COUNT(*) AS TotalCrashes,
    SUM(NumberKilled) AS TotalKilled,
    SUM(NumberInjured) AS TotalInjured
FROM dbo.Crashes_2026
GROUP BY Collision_Type_Description
ORDER BY SUM(NumberKilled) DESC;

Hallazgo: REAR END es el tipo más frecuente, pero VEHICLE/PEDESTRAIN concentra más muertes con menos volumen. Esa diferencia es oro analítico.

Fatality Severity Index

SELECT
    Collision_Type_Description,
    COUNT(*) AS TotalCrashes,
    SUM(NumberKilled) AS TotalKilled,
    CAST(
        SUM(NumberKilled) * 100.0 /
        COUNT(*)
        AS DECIMAL(10,2)
    ) AS FatalityRatePct
FROM dbo.Crashes_2026
GROUP BY Collision_Type_Description
ORDER BY FatalityRatePct DESC;

Insight ejecutivo: un choque VEHICLE/PEDESTRAIN fue aproximadamente 60 veces más letal que un REAR END en este corte de 2026.

16. Problema real de calidad: PEDESTRAIN

Durante el análisis apareció una lección de data quality: el valor del campo no decía PEDESTRIAN, sino PEDESTRAIN. Por eso el filtro por texto no devolvía resultados. La solución profesional fue filtrar por código.

SELECT DISTINCT
    Collision_Type_Code,
    Collision_Type_Description,
    COUNT(*) AS TotalCrashes,
    SUM(NumberKilled) AS TotalKilled
FROM dbo.Crashes_2026
GROUP BY Collision_Type_Code, Collision_Type_Description
ORDER BY SUM(NumberKilled) DESC;

Regla de negocio: usar Collision_Type_Code = 'G' para representar VEHICLE/PEDESTRAIN y evitar depender del texto mal escrito.

17. Peatones por ciudad

SELECT TOP 20
    City_Name,
    COUNT(*) AS PedestrianCrashes,
    SUM(NumberKilled) AS PedestrianKilled
FROM dbo.Crashes_2026
WHERE Collision_Type_Code = 'G'
GROUP BY City_Name
ORDER BY SUM(NumberKilled) DESC;

Insight: Los Angeles tuvo menos accidentes peatón que Unincorporated, pero casi el doble de fallecidos. Eso sugiere que la severidad de los accidentes peatón no está determinada solo por el volumen.

18. Tasa de fatalidad en accidentes peatón

SELECT TOP 20
    City_Name,
    COUNT(*) AS PedestrianCrashes,
    SUM(NumberKilled) AS PedestrianKilled,
    CAST(SUM(NumberKilled) * 100.0 / COUNT(*) AS DECIMAL(10,2)) AS PedestrianFatalityRate
FROM dbo.Crashes_2026
WHERE Collision_Type_Code = 'G'
GROUP BY City_Name
HAVING COUNT(*) >= 10
ORDER BY PedestrianFatalityRate DESC;

Nota analítica: las ciudades con pocos casos pueden mostrar tasas muy altas. Hay que usar umbrales como HAVING COUNT(*) >= 50 o >= 100 para análisis más estables.

19. Iluminación en accidentes peatón

La pregunta evolucionó: si los accidentes con peatones son los más letales, ¿qué papel juega la iluminación?

SELECT
    LightingDescription,
    COUNT(*) AS TotalCrashes,
    SUM(NumberKilled) AS TotalKilled
FROM dbo.Crashes_2026
WHERE Collision_Type_Code = 'G'
GROUP BY LightingDescription
ORDER BY SUM(NumberKilled) DESC;

Tasa de fatalidad por iluminación

SELECT
    LightingDescription,
    COUNT(*) AS TotalCrashes,
    SUM(NumberKilled) AS TotalKilled,
    CAST(
        SUM(NumberKilled) * 100.0 /
        COUNT(*)
        AS DECIMAL(10,2)
    ) AS FatalityRatePct
FROM dbo.Crashes_2026
WHERE Collision_Type_Code = 'G'
GROUP BY LightingDescription
ORDER BY FatalityRatePct DESC;

Hallazgo principal: los accidentes peatón en oscuridad sin iluminación tienen una tasa de fatalidad mucho mayor que los accidentes en daylight. En este corte, DARK-NO STREET LIGHTS fue casi 9 veces más letal que DAYLIGHT.

20. Valoración ejecutiva del viaje analítico

El valor real de la sesión no fue instalar Python ni cargar un CSV. El valor fue recorrer un ciclo completo de pensamiento analítico:

Hallazgos generados

• Rear End es muy frecuente, pero de baja mortalidad relativa.
• Vehicle/Pedestrian es mucho menos frecuente, pero es el tipo más letal.
• Head-On y Overturned tienen alta severidad relativa.
• El ranking por volumen no es igual al ranking por riesgo.
• Los Angeles concentra muchas fatalidades peatón.
• La oscuridad, especialmente sin iluminación, aparece como factor crítico de severidad en accidentes peatón.
• El error textual PEDESTRAIN demostró la importancia de usar códigos cuando los textos no son confiables.

La regla maestra

No basta con contar accidentes. Hay que medir severidad, normalizar por volumen, separar frecuencia de riesgo y seguir preguntando hasta que el dato empiece a explicar el problema.

21. Lo que queda para la siguiente sesión

Próximo script propuesto: load_all_years.py

El próximo paso natural es cargar los 8 archivos en una sola tabla final dbo.Crashes, agregando una columna derivada Source_Year o usando la propia fecha del accidente para análisis temporal.

22. Conclusión

Este laboratorio demostró que Python puede funcionar como un ETL local real: lee archivos grandes, procesa por chunks, normaliza columnas, carga SQL Server y habilita análisis inmediato.

Pero la lección más importante fue otra: Python no fue el objetivo; Python fue el vehículo.

El verdadero objetivo fue transformar datos abiertos en conocimiento usando una combinación de experiencia de negocio, reglas analíticas, SQL, pensamiento crítico e IA como partner.

Learning by DoingPythonSQL ServerOpen DataETLBusiness IntelligenceTraffic Safety Analytics