Sloppy STATS19 data reanalysis (see https://x.com/XplodingCabbage/status/2059720248652169558)

gistfile1.txt

import csv
collisions = list(csv.DictReader(open('dft-road-casualty-statistics-collision-1979-latest-published-year.csv')))

print("Row count, 1979-present:", len(collisions))

fatal_collisions = [row for row in collisions if row['collision_severity'] == '1']

print("Of those,", len(fatal_collisions), "were fatal")

correct_years_collisions = [row for row in fatal_collisions if 2005 <= int(row['collision_year']) <= 2018]

print("Of those,", len(correct_years_collisions), "were in 2005-2018")

potentially_relevant_collision_indexes = {row['collision_index'] for row in correct_years_collisions}

casualty_rows_iter = csv.DictReader(open('dft-road-casualty-statistics-casualty-1979-latest-published-year.csv'))
casualty_rows_by_index = {}
for row in casualty_rows_iter:
    if row['collision_index'] in potentially_relevant_collision_indexes:
        casualty_rows_by_index.setdefault(row['collision_index'], []).append(row)

vehicle_rows_iter = csv.DictReader(open('dft-road-casualty-statistics-vehicle-1979-latest-published-year.csv'))
vehicle_rows_by_index = {}
for row in vehicle_rows_iter:
    if row['collision_index'] in potentially_relevant_collision_indexes:
        vehicle_rows_by_index.setdefault(row['collision_index'], []).append(row)

def dead_ped(collision):
    for casualty in casualty_rows_by_index[collision['collision_index']]:
        if casualty['casualty_type'] == '0' and casualty['casualty_severity'] == '1':
            return True

dead_ped_collisions = [row for row in correct_years_collisions if dead_ped(row)]

print("Of those,", len(dead_ped_collisions), "involved a PEDESTRIAN fatality")

def dead_ped_on_pavement(collision):
    for casualty in casualty_rows_by_index[collision['collision_index']]:
        if casualty['casualty_type'] == '0' and casualty['casualty_severity'] == '1' and casualty['pedestrian_location'] == '6':
            return True

dead_ped_on_pavement_collisions = [row for row in dead_ped_collisions if dead_ped_on_pavement(row)]

print("Of those,", len(dead_ped_on_pavement_collisions), "involved a pedestrian ON A PAVEMENT being killed")

def involved_car(collision):
    for vehicle in vehicle_rows_by_index[collision['collision_index']]:
        if vehicle['vehicle_type'] in ('8', '9'):
            return True

def involved_bicycle(collision):
    for vehicle in vehicle_rows_by_index[collision['collision_index']]:
        if vehicle['vehicle_type'] == '1':
            return True

def involved_car_on_pavement(collision):
    for vehicle in vehicle_rows_by_index[collision['collision_index']]:
        if vehicle['vehicle_type'] in ('8', '9') and vehicle['vehicle_location_restricted_lane_historic'] == '9':
            return True

def involved_bicycle_on_pavement(collision):
    for vehicle in vehicle_rows_by_index[collision['collision_index']]:
        if vehicle['vehicle_type'] == '1' and vehicle['vehicle_location_restricted_lane_historic'] == '9':
            return True

involving_car_count = sum(1 for row in dead_ped_on_pavement_collisions if involved_car(row))
involving_bicycle_count = sum(1 for row in dead_ped_on_pavement_collisions if involved_bicycle(row))
involving_car_on_pavement_count = sum(1 for row in dead_ped_on_pavement_collisions if involved_car_on_pavement(row))
involving_bicycle_on_pavement_count = sum(1 for row in dead_ped_on_pavement_collisions if involved_bicycle_on_pavement(row))

print("Of the collisions involving a pedestrian on a pavement being killed...")
print("-", involving_car_count, "involved a car")
print("-", involving_bicycle_count, "involved a bicycle")
print("-", involving_car_on_pavement_count, "involved a car that the data characterise as on the pavement at the time of the collision")
print("-", involving_bicycle_on_pavement_count, "involved a bicycle that the data characterise as on the pavement at the time of the collision")