Forem: Austin

NYC Collision Data Analysis

Austin — Mon, 28 Aug 2023 02:29:26 +0000

aibistin.github.io

Crash City

Austin — Fri, 07 Apr 2023 17:00:27 +0000

Analyzing NYC Traffic Collision Data on the Linux Command Line with SoQL and Curl

New York City and the NYPD publish a dataset of traffic collisions, related fatalities, injuries and other details here.
This was first published in July 2012, and is updated regularly to this date.

NYC Commuters, especially pedestrians and cyclists, have to endure many hazards just to get to and from work. Crime is one issue, but it's not as treacherous as crossing Queens Boulevard during rush hour, or cycling in downtown Manhattan when some clown driving an SUV the size of an Sherman tank is taking up half the road.

Previously I did some analysis using their downloadable CSV dataset.

Here I’m going to use the curl utility along with the SODA or SoQL Query Language, to try and make some sense out of this published data.

Using the Socrata Query Language, SoQL

Dataset App Token
Socrata functions

How many collisions since July 2012

curl --get --data-urlencode "\$\$app_token=uvwxyz" --data-urlencode "\$select=count(*)" https://data.cityofnewyork.us/resource/h9gi-nx95.json 
[{"count":"1977803"}]

Explanation

curl --get or -G
Use the GET verb as we are ‘getting’ data
v
Lots of verbose output as you can see from the above output.
d
Request data to pass to the API in ASCII format
--data-urlencode
URL-Encode the data. Safer than just using -d
$$app_token
Users personal authorization. Not really necessary for ad-hoc requests
Socrata open data API [App-Token]](https://dev.socrata.com/docs/app-tokens.html)
I replaced my actual token with ‘uvwxyz’ for fairly obvious reasons
"$select=count(*)"
Similar to the SQL SELECT and SQL count aggregate function
SoQL $select
SoQL count

Observation

1,977,803 Collisions from July 2012 to March 2023 seems like a lot to me. You’d wonder what’s the point of driving tests if we still end up with this many collisions.

Getting all the collision records between two arbitrary dates, June 30th 2022 to December 31 2022.

This time I’ll use the -v switch for curl to get a much more verbose output.

> curl --get -v  --data-urlencode "\$\$app_token=xyz" --data-urlencode "\$select=*" --data-urlencode "\$where=crash_date between '2022-06-30T00:00:00.000' and '2022-12-31T00:00:00.000'"  https://data.cityofnewyork.us/resource/h9gi-nx95.json

 % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0*   Trying 52.206.68.26:443...
* Connected to data.cityofnewyork.us (52.206.68.26) port 443 (#0)
...
> GET /resource/h9gi-nx95.json?$$app_token=xyz&$select=%2A&$where=crash_date+between+%272022-06-30T00%3A00%3A00.000%27+and+%272022-12-31T00%3A00%3A00.000%27 HTTP/1.1
> Host: data.cityofnewyork.us
> User-Agent: curl/7.81.0
...
< HTTP/1.1 200 OK
....
< X-SODA2-Fields: ["crash_date","crash_time","borough","zip_code","latitude","longitude","location","on_street_name","off_street_name","cross_street_name","number_of_persons_injured","number_of_persons_killed","number_of_pedestrians_injured","number_of_pedestrians_killed","number_of_cyclist_injured","number_of_cyclist_killed","number_of_motorist_injured","number_of_motorist_killed","contributing_factor_vehicle_1","contributing_factor_vehicle_2","contributing_factor_vehicle_3","contributing_factor_vehicle_4","contributing_factor_vehicle_5","collision_id","vehicle_type_code1","vehicle_type_code2","vehicle_type_code_3","vehicle_type_code_4","vehicle_type_code_5"]
< X-SODA2-Types: ["floating_timestamp","text","text","text","number","number","location","text","text","text","number","number","number","number","number","number","number","number","text","text","text","text","text","number","text","text","text","text","text"]
< X-SODA2-Data-Out-Of-Date: false
...
{ [14733 bytes data]
...
[{"crash_date":"2022-06-30T00:00:00.000","crash_time":"14:01","cross_street_name":"101       EAST DRIVE","number_of_persons_injured":"1","number_of_p
ersons_killed":"0","number_of_pedestrians_injured":"0","number_of_pedestrians_killed":"0","number_of_cyclist_injured":"1","number_of_cyclist_killed":
"0","number_of_motorist_injured":"0","number_of_motorist_killed":"0","contributing_factor_vehicle_1":"Pedestrian/Bicyclist/Other Pedestrian Error/Con
fusion","collision_id":"4542318","vehicle_type_code1":"Bike"}
...
,{"crash_date":"2022-07-03T00:00:00.000","crash_time":"22:30","borough":"BRONX","zip_code":"10458","latitude":"40.866802","longitude":"-73.88444","location":{"latitude":"40.866802","longitude":"-73.88444","human_address":"{\"address\": \"\", \"city\": \"\", \"state\": \"\", \"zip\": \"\"}"},"on_street_name":"WEBSTER AVENUE","off_street_name":"EAST 199 STREET","number_of_persons_injured":"0","number_of_persons_killed":"0","number_of_pedestrians_injured":"0","number_of_pedestrians_killed":"0","number_of_cyclist_injured":"0","number_of_cyclist_killed":"0","number_of_motorist_injured":"0","number_of_motorist_killed":"0","contributing_factor_vehicle_1":"Driver Inattention/Distraction","contributing_factor_vehicle_2":"Unspecified","collision_id":"4543075","vehicle_type_code1":"Station Wagon/Sport Utility Vehicle","vehicle_type_code2":"Station Wagon/Sport Utility Vehicle"}]

Explanation

1000 records
When no $limit is set, this is the default maximum rows returned
curl --get or -G
Use the GET verb as we are ‘getting’ data
-v
Lots of verbose output as you can see
-d
Request data to pass to the API in ASCII format
--data-urlencode
URL-Encode the data. Safer than just using -d

$$app_token
Users personal authorization. Not really necessary for ad-hoc requests
"$select=*"
Similar to an SQL SELECT
Selecting all columns. This is the default and can be omitted
SoQL $select
$where
Similar to SQL WHERE to filter down data.
SoQL $where
between … and …
SoQL between
Narrow our results down to collisions between the two *inclusive ‘crash_date’ values

Observation

It turns out after piping this request to a wc command, that the API only returns 1000 rows, which is the default maximum amount if the $limit clause isn’t specified. With the $limit clause, the maximum amount that can be returned with one call is 50,000 rows. To get more, you will need to order and page through the data.
One other thing to note here is that when using the -v, verbose switch, you get to see the column names and their data types.

The NYC dataset column names

"crash_date","crash_time","borough","zip_code","latitude","longitude","location","on_street_name","off_street_name","cross_street_name","number_of_persons_injured","number_of_persons_killed","number_of_pedestrians_injured","number_of_pedestrians_killed","number_of_cyclist_injured","number_of_cyclist_killed","number_of_motorist_injured","number_of_motorist_killed","contributing_factor_vehicle_1","contributing_factor_vehicle_2","contributing_factor_vehicle_3","contributing_factor_vehicle_4","contributing_factor_vehicle_5","collision_id","vehicle_type_code1","vehicle_type_code2","vehicle_type_code_3","vehicle_type_code_4","vehicle_type_code_5"

Corresponding Field Data Types

"floating_timestamp","text","text","text","number","number","location","text","text","text","number","number","number","number","number","number","number","number","text","text","text","text","text","number","text","text","text","text","text"

SoQL Query Clauses from the Docs

Parameter	Description	Default	In $query
$select	The set of columns to be returned, similar to a SELECT in SQL	All columns, equivalent to $select=*	SELECT
$where	Filters the rows to be returned, similar to WHERE	No filter	WHERE
$order	Column to order results on, similar to ORDER BY in SQL	Unspecified order	ORDER BY
$group	Column to group results on, similar to GROUP BY in SQL	No grouping	GROUP BY
$having	Filters the rows that result from an aggregation, similar to HAVING	No filter	HAVING
$limit	Maximum number of results to return	1000 (2.0 endpoints: maximum of 50,000; 2.1: unlimited »)	LIMIT
$offset	Offset count into the results to start at, used for paging	0	OFFSET
$q	Performs a full text search for a value.	No search	N/A
$query	A full SoQL query string, all as one parameter	N/A	N/A
$$bom	Prepends a UTF-8 Byte Order Mark to the beginning of CSV output	false	N/A

Get all the collisions for zip code 10036, Times Square NYC, for Feb 2023

Save it into file times_square_july_2022.json

curl --get --data-urlencode "\$\$app_token=uvwxyz"  --data-urlencode "\$select=*" / --data-urlencode "\$where=crash_date between '2023-02-01T00:00:00.000' and '2023-02-28T00:00:00.000'" --data-urlencode "zip_code=10036"  https://data.cityofnewyork.us/resource/h9gi-nx95.json >    collisions_z10036_feb_2023.json

Explanation

"\$where=crash_date between '2023-02-01T00:00:00.000' and '2023-02-28T00:00:00.000'" --data-urlencode "zip_code=10036"

Specify dates between and including February 1st to the 28th.
zip_code=10036 to narrow down our results.

Count how many collisions using the Linux wc command with our newly created file, times_square_july_2022.json.

wc -l collisions_z10036_feb_2023.json 
25 collisions_z10036_feb_2023.json

Double check that count of 25 collisions, using the SoQl count(*) function.

> curl --get --data-urlencode "\$\$app_token=uvwxyz"  --data-urlencode "\$select=count(*)"  --data-urlencode "\$where=crash_date between '2023-02-01T00:00:00.000' and '2023-02-28T00:00:00.000'" --data-urlencode "zip_code=10036"  https://data.cityofnewyork.us/resource/h9gi-nx95.json 
[{"count":"25"}]

Explanation

\$select=count(*)
Similar to the SQL count function, this uses the SoQL count function to count the number of rows that match our search criteria.
[{"count":"25"}], which matches the number of records in the collisions_z10036_feb_2023.json file

Observation

25 collisions in one midtown zip code for February is almost 1 collision a day. I’m sure that's lower than many other zip codes.

Get the 10 worst zip codes for collisions in February 2023

> curl --get --silent  ‘$$app_token=uvwxyz’  --data-urlencode "\$select=count(*), zip_code"   --data-urlencode "\$where=crash_date between '2023-02-01T00:00:00.000' and '2023-02-28T00:00:00.000'"  --data-urlencode '$group=zip_code'   https://data.cityofnewyork.us/resource/h9gi-nx95.json | jq -r '.[] | .zip_code + " " + .count' | sort  -k 2,2nr -k 1n | head -n10
11207 105
11212 85
11208 79
11226 75
11234 72
11236 72
11101 71
11203 67
11368 67
11211 62

Explanation

OK, I threw in a lot of commands here.
"\$select=count(*), zip_code"
Selecting the count and zip_code
SoQL count function to count the number of rows that match our search criteria.
$group=zip_code
Similar to the SQL GROUP BY
Returns aggregate rows grouped by the zip_code
jq -r '.[] | .zip_code + " " + .count'
Using the very useful jq to do additional filtering
jq bills itself as, “a lightweight and flexible command-line JSON processor”
I extract the zip_code and collision count for each zip code and concatenate them using the bash +, concatenation operator
sort -k 2,2nr -k 1n
Using the bash sort command, we do a reverse numerical sort by the second field, which is the count. We also do a numerical sort on the zip_code for zip_codes with identical collision counts
head -n10
This gets the first 10, which are the 10 zip codes with the most collisions, starting with the very worst.

Observation

I could have used SoQL $sort and $limit to do some of this work, but I chose the bash sort, just because ...
Zip code 11207, East New York, Brooklyn, emerges as the zip with the most collisions in February.
This zip has a lot of issues with traffic safety, as you could also check here .
105 collisions in one month. 3.75 a day? There’s something seriously wrong there. You’d probably need some kind of armor suit just to cross the street there.

As the queries get more complex, these one line commands start to get long and hard to manage.

Curl has an option to create a config file. On a Linux system the default config is usually ~/.curlrc. You can specify a config file with the -K or --config switch.

I created the below config file for these requests
The config file sets the NYC API URL, the $$app_token parameter, a GET request, as well as asking for verbose output

##### The ./.nyc_curlrc file contents
# --- NYC Collision Data ---
get
url = "https://data.cityofnewyork.us/resource/h9gi-nx95.json"
data-urlencode  =  "\$\$app_token=uvwxyz"
verbose

The previous example can now be rewritten to use the .nyc_curlrc config file. I also broke up the commands into separate lines using the bash continuation ‘\’ . Enclosing some of the commands in single quotes also means that the ‘$’ doesn’t need to be escaped.

> curl -K ./.nyc_curlrc \
 --data-urlencode '$select=count(*), zip_code' \
 --data-urlencode '$where=crash_date between "2023-02-01T00:00:00.000" and "2023-02-28T00:00:00.000"' \
 --data-urlencode '$group=zip_code'  \
  | jq -r '.[] | .zip_code + " " + .count' | sort  -k 2,2nr -k 1n | head -n10

This is a little more concise than the previous version, and yields the same result.

### Now to find how many cyclists and pedestrians were killed over the duration of this dataset

 curl -K ./.nyc_curlrc \
  --data-urlencode "\$select=date_extract_y(crash_date) AS year, SUM(number_of_pedestrians_killed) AS tot_pedestrians_killed, SUM(number_of_cyclist_killed) AS tot_cyclist_killed"  \
  --data-urlencode "\$group=year" \
  --data-urlencode "\$order=tot_pedestrians_killed DESC"  | jq .

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   885    0   885    0     0   1616      0 --:--:-- --:--:-- --:--:--  1614
[
  {
    "year": "2013",
    "tot_pedestrians_killed": "176",
    "tot_cyclist_killed": "11"
  },
  {
    "year": "2016",
    "tot_pedestrians_killed": "149",
    "tot_cyclist_killed": "18"
  },
  {
    "year": "2014",
    "tot_pedestrians_killed": "133",
    "tot_cyclist_killed": "20"
  },
  {
    "year": "2015",
    "tot_pedestrians_killed": "133",
    "tot_cyclist_killed": "15"
  },
  {
    "year": "2022",
    "tot_pedestrians_killed": "132",
    "tot_cyclist_killed": "18"
  },
  {
    "year": "2019",
    "tot_pedestrians_killed": "131",
    "tot_cyclist_killed": "31"
  },
  {
    "year": "2021",
    "tot_pedestrians_killed": "129",
    "tot_cyclist_killed": "19"
  },
  {
    "year": "2017",
    "tot_pedestrians_killed": "127",
    "tot_cyclist_killed": "27"
  },
  {
    "year": "2018",
    "tot_pedestrians_killed": "123",
    "tot_cyclist_killed": "10"
  },
  {
    "year": "2020",
    "tot_pedestrians_killed": "101",
    "tot_cyclist_killed": "29"
  },
  {
    "year": "2012",
    "tot_pedestrians_killed": "72",
    "tot_cyclist_killed": "6"
  },
  {
    "year": "2023",
    "tot_pedestrians_killed": "18",
    "tot_cyclist_killed": "8"
  }
]

Explanation

date_extract_y(crash_date) AS year
Will extract ‘2023’ from ‘2023-02-03T00:00:00.000’
SoQL date_extract_y
SUM(number_of_pedestrians_killed) AS tot_pedestrians_killed
SUM
Similar to SQL SUM aggregate function.
AS
Give these aggregate results a meaningful label
$group=year and $order=tot_pedestrians_killed
Similar to the SQL GROUP BY and ORDER BY
Returns aggregate rows grouped by the year they occurred.
Sorted having the year with most pedestrian fatalities first
jq .
This is the most basic jq command
It just prints the JSON output in it’s default “pretty” format
We could have added --silent to the curl command or config file, to not print the curl download statistics.

Observation

2012 and the current year, 2023,  can be omitted as both years have incomplete data.

Run the previous query minus years 2012 and 2023

curl -K ./.nyc_curlrc   --data-urlencode '$select=date_extract_y(crash_date) AS year, SUM(number_of_pedestrians_killed) AS tot_pedestrians_killed, SUM(number_of_cyclist_killed) AS tot_cyclists_killed' \
  --data-urlencode '$where=year not in ("2012", "2023")' \
  --data-urlencode '$group=year' \
  --data-urlencode '$order=tot_pedestrians_killed DESC, tot_cyclists_killed'

[{"year":"2013","tot_pedestrians_killed":"176","tot_cyclists_killed":"11"}
,{"year":"2016","tot_pedestrians_killed":"149","tot_cyclists_killed":"18"}
,{"year":"2015","tot_pedestrians_killed":"133","tot_cyclists_killed":"15"}
,{"year":"2014","tot_pedestrians_killed":"133","tot_cyclists_killed":"20"}
,{"year":"2022","tot_pedestrians_killed":"132","tot_cyclists_killed":"18"}
,{"year":"2019","tot_pedestrians_killed":"131","tot_cyclists_killed":"31"}
,{"year":"2021","tot_pedestrians_killed":"129","tot_cyclists_killed":"19"}
,{"year":"2017","tot_pedestrians_killed":"127","tot_cyclists_killed":"27"}
,{"year":"2018","tot_pedestrians_killed":"123","tot_cyclists_killed":"10"}
,{"year":"2020","tot_pedestrians_killed":"101","tot_cyclists_killed":"29"}]

Explanation

$where=year not in ("2012", "2023")
Added a WHERE clause to omit years 2012 and 2023 from the query
SoQL not in (...)

Observation

Well, it’s not that safe being a pedestrian or cyclist in New York City. Checking the injury count would yield much higher numbers.

Run a query to get a yearly total of injured pedestrians and cyclists

Our query string was getting a little bit out of hand and difficult to manage.
I created a dedicated config file, .nyc_ped_cyc_injured_yearly_curlrc for our next request.

The Config

> cat .nyc_ped_cyc_injured_yearly_curlrc 
# --- NYC Collision Data - Injured List  ---
get
url = "https://data.cityofnewyork.us/resource/h9gi-nx95.json"
data-urlencode  = "\$\$app_token=uvwxyz"
data-urlencode  = "\$select=date_extract_y(crash_date) AS year, SUM(number_of_pedestrians_injured) AS tot_pedestrians_injured, SUM(number_of_cyclist_injured) AS tot_cyclists_injured"
data-urlencode  = "\$where=year not in ('2012','2023')"
data-urlencode  = "\$group=year" 
data-urlencode  = "\$order=tot_pedestrians_injured DESC, tot_cyclists_injured DESC"
silent

The Query using the config file

>  curl --config ./.nyc_ped_cyc_injured_yearly_curlrc 
[{"year":"2013","tot_pedestrians_injured":"11988","tot_cyclists_injured":"4075"}
,{"year":"2017","tot_pedestrians_injured":"11151","tot_cyclists_injured":"4889"}
,{"year":"2018","tot_pedestrians_injured":"11123","tot_cyclists_injured":"4725"}
,{"year":"2016","tot_pedestrians_injured":"11090","tot_cyclists_injured":"4975"}
,{"year":"2014","tot_pedestrians_injured":"11036","tot_cyclists_injured":"4000"}
,{"year":"2019","tot_pedestrians_injured":"10568","tot_cyclists_injured":"4986"}
,{"year":"2015","tot_pedestrians_injured":"10084","tot_cyclists_injured":"4281"}
,{"year":"2022","tot_pedestrians_injured":"8963","tot_cyclists_injured":"5025"}
,{"year":"2021","tot_pedestrians_injured":"7503","tot_cyclists_injured":"4961"}
,{"year":"2020","tot_pedestrians_injured":"6691","tot_cyclists_injured":"5576"}]

Observation

Looks like the config file worked as expected. While the number of pedestrians injured is declining a little, the number of cyclists injured is going in the opposite direction.

Using jq to do additional filtering

Similar to the previous query, extract the yearly totals of injured cyclists. This time we’ll use jq to filter the output.

> curl --config ./.nyc_ped_cyc_injured_yearly_curlrc \
  | jq -r '.[] | .year + "," + .tot_cyclists_injured' | sort -k 1n \
  | column -t -s, --table-columns=Year,CyclistsInjured 
Year  CyclistsInjured
2013  4075
2014  4000
2015  4281
2016  4975
2017  4889
2018  4725
2019  4986
2020  5576
2021  4961
2022  5025

Explanation

This is similar to the previous query except I used jq to extract the injured cyclist data only from the returned results.

sort -k 1n
- Sort the Year numerically

sort -k 1n
Sort the Year, numerically
column -t -s, --table-columns=Year,CyclistsInjured
Add column headers for readability
The jq command already created comma separated results

Observation

2020 and 2022 were the worst years for bicyclist injuries. 2020 was a year where cycling became more popular. The injuries dropped a little in 2021, maybe because cyclists got a little scared after the slaughter in 2020. The upward trend may be returning, based on the 2022 results.

Get the 10 worst zip codes for collisions in January 2023

Previously I got the [10 worst Zip codes for collisions in February][Get the 10 worst zip codes for collisions in February 2023]. I used some bash commands to fine tune results. Here I will use SoQL to do most of the heavy lifting.

Config file `.nyc_jan_coll_curlrc`

> cat .nyc_jan_coll_curlrc 
# --- NYC Collision Data - January Collisions  ---
get
url = "https://data.cityofnewyork.us/resource/h9gi-nx95.json"
data-urlencode  = "\$\$app_token=uvwxyz"
data-urlencode = "\$select=zip_code,count(zip_code) AS collision_count"
data-urlencode = "\$where=crash_date between '2023-01-01' AND '2023-01-31' "
data-urlencode = "\$group=zip_code"
data-urlencode = "\$order=collision_count DESC, zip_code"
data-urlencode = "\$limit=10"
silent

> curl --config ./.nyc_jan_coll_curlrc \
  | jq -r '.[] | .zip_code + ", " + .collision_count' \
  | column -t -s, --table-columns=ZipCode,CollisionCount
ZipCode  CollisionCount
11207     124
11236     83
11208     82
11212     77
11203     69
11385     67
11234     66
11206     64
10002     63
11101     61

Explanation

Most of this is similar to our earlier request for February stats. This time we are using a new config file ..

Instead of sorting the results using the bash sort, we sort using the SoQL [$order](https://dev.socrata.com/docs/queries/order.html). We get the 10 worst using the $limit clause.
\$order=collision_count DESC, zip_code
Sort the collision count from worst to “least worst”.
zip_code ascending sort
$limit=10
Get the first 10 after the sort using $limit

Observation

Zip Code 11207, again emerges as a collision prone area with 124 collisions in January. That’s 4 collisions a day. Every day is a regular demolition derby day in that part of Brooklyn.

Analyzing New York City Traffic Collisions with Perl One-Liners and Linux

Austin — Tue, 21 Mar 2023 17:12:33 +0000

Just when you think it’s safe outside. Here are some statistics relating to traffic collisions in New York City.

Background

NYC publishes vehicle collision data which anyone can access using their API. You can also download this information in standard CSV (Comma Separated Values) file format.
The file is fairly large, 420 MB, with almost 2 Million lines.

-rw-rw-r-- 1 austin austin 402M Mar  4 20:38 all_motor_vehicle_collision_data.csv
…
bash > wc -l all_motor_vehicle_collision_data.csv 
1972886 all_motor_vehicle_collision_data.csv

Display the first five records of the dataset using head

bash > head -n5 all_motor_vehicle_collision_data.csv 
CRASH DATE,CRASH TIME,BOROUGH,ZIP CODE,LATITUDE,LONGITUDE,LOCATION,ON STREET NAME,CROSS STREET NAME,OFF STREET NAME,NUMBER OF PERSONS INJURED,NUMBER OF PERSONS KILLED,NUMBER OF PEDESTRIANS INJURED,NUMBER OF PEDESTRIANS KILLED,NUMBER OF CYCLIST INJURED,NUMBER OF CYCLIST KILLED,NUMBER OF MOTORIST INJURED,NUMBER OF MOTORIST KILLED,CONTRIBUTING FACTOR VEHICLE 1,CONTRIBUTING FACTOR VEHICLE 2,CONTRIBUTING FACTOR VEHICLE 3,CONTRIBUTING FACTOR VEHICLE 4,CONTRIBUTING FACTOR VEHICLE 5,COLLISION_ID,VEHICLE TYPE CODE 1,VEHICLE TYPE CODE 2,VEHICLE TYPE CODE 3,VEHICLE TYPE CODE 4,VEHICLE TYPE CODE 5
09/11/2021,2:39,,,,,,WHITESTONE EXPRESSWAY,20 AVENUE,,2,0,0,0,0,0,2,0,Aggressive Driving/Road Rage,Unspecified,,,,4455765,Sedan,Sedan,,,
03/26/2022,11:45,,,,,,QUEENSBORO BRIDGE UPPER,,,1,0,0,0,0,0,1,0,Pavement Slippery,,,,,4513547,Sedan,,,,
06/29/2022,6:55,,,,,,THROGS NECK BRIDGE,,,0,0,0,0,0,0,0,0,Following Too Closely,Unspecified,,,,4541903,Sedan,Pick-up Truck,,,
09/11/2021,9:35,BROOKLYN,11208,40.667202,-73.8665,"(40.667202, -73.8665)",,,1211      LORING AVENUE,0,0,0,0,0,0,0,0,Unspecified,,,,,4456314,Sedan,,,,

Using head to display the first record only

bash > head -n1 all_motor_vehicle_collision_data.csv 
CRASH DATE,CRASH TIME,BOROUGH,ZIP CODE,LATITUDE,LONGITUDE,LOCATION,ON STREET NAME,CROSS STREET NAME,OFF STREET NAME,NUMBER OF PERSONS INJURED,NUMBER OF PERSONS KILLED,NUMBER OF PEDESTRIANS INJURED,NUMBER OF PEDESTRIANS KILLED,NUMBER OF CYCLIST INJURED,NUMBER OF CYCLIST KILLED,NUMBER OF MOTORIST INJURED,NUMBER OF MOTORIST KILLED,CONTRIBUTING FACTOR VEHICLE 1,CONTRIBUTING FACTOR VEHICLE 2,CONTRIBUTING FACTOR VEHICLE 3,CONTRIBUTING FACTOR VEHICLE 4,CONTRIBUTING FACTOR VEHICLE 5,COLLISION_ID,VEHICLE TYPE CODE 1,VEHICLE TYPE CODE 2,VEHICLE TYPE CODE 3,VEHICLE TYPE CODE 4,VEHICLE TYPE CODE 5

Use Perl to list the column names in numerical order

bash > perl -F, -an -E '$. == 1 && say $i++ . "\t$_" for @F'  all_motor_vehicle_collision_data.csv
0   CRASH DATE
1   CRASH TIME
2   BOROUGH
3   ZIP CODE
4   LATITUDE
5   LONGITUDE
6   LOCATION
7   ON STREET NAME
8   CROSS STREET NAME
9   OFF STREET NAME
10  NUMBER OF PERSONS INJURED
11  NUMBER OF PERSONS KILLED
12  NUMBER OF PEDESTRIANS INJURED
13  NUMBER OF PEDESTRIANS KILLED
14  NUMBER OF CYCLIST INJURED
15  NUMBER OF CYCLIST KILLED
16  NUMBER OF MOTORIST INJURED
17  NUMBER OF MOTORIST KILLED
18  CONTRIBUTING FACTOR VEHICLE 1
19  CONTRIBUTING FACTOR VEHICLE 2
20  CONTRIBUTING FACTOR VEHICLE 3
21  CONTRIBUTING FACTOR VEHICLE 4
22  CONTRIBUTING FACTOR VEHICLE 5
23  COLLISION_ID
24  VEHICLE TYPE CODE 1
25  VEHICLE TYPE CODE 2
26  VEHICLE TYPE CODE 3
27  VEHICLE TYPE CODE 4
28  VEHICLE TYPE CODE 5

Explanation

'perl -an -E'
- Split up the column values into array '@F'
'-F,'
- Specifies a comma field separator.
'$. == 1'
- The Perl special variable '$.' contains the current line number.
- Display the first line only.
'say $i++ . "\t$_" for @F'
- Prints a tab separated counter variable '$i', and the corresponding column name, stored in the Perl default variable '$_'.

Create a work-file containing the zip-code, injury count, and fatality count. Only get records that include a zip-code and at least one injury or fatality.

3   ZIP CODE
10  NUMBER OF PERSONS INJURED
11  NUMBER OF PERSONS KILLED

Method

Unfortunately the previous method for splitting a comma delimited file has limitations. It cannot handle fields with embedded commas. The Street Name fields often have embedded commas which will throw off our column numbering.
To get around this we can use Text::CSV, which has both functional and OO interfaces. For one-liners, it exports a handy csv function. From the Text::CSV documentation 'my $aoa = csv (in => "test.csv") or die Text::CSV_XS->error_diag;'', it’ll convert the CSV file into an array of arrays.
I'll modify this example slightly to 'csv( in => $ARGV[0], headers => qq/skip/ )'. The @ARGV array contains any input arguments. The first element $ARGV[0] will contain the input CSV file. We don’t need the header row, so it’ll be skipped.

perl -MText::CSV=csv  -E '$aofa = csv( in => $ARGV[0], headers => qq/skip/ ); ( $_->[3] =~ /^\S+$/ ) && say qq/$_->[3],$_->[10],$_->[11]/ for @{$aofa}'  all_motor_vehicle_collision_data.csv | sort -t, -k 1 -r > sorted_injured_killed_by_zip.csv

Explanation

Input file 'all_motor_vehicle_collision_data.csv'
'perl -MText::CSV=csv'
- Run the perl command with '-M' switch to load a Perl module.
'Text::CSV=csv'
- Export the ‘csv’ function from the 'Text::CSV' module.
'( $_->[3] =~ /^\S+$/ )'
- Use a Regular expression to only process rows that have non-blank data in the ZIP CODE field.
'say qq/$->[3],$->[10],$_->[11]/ for @{$aofa}'
- Loop through the Array of Arrays '$aofa'
- Print the contents of columns 3,10,11 followed by a line break.
The output is piped '|' into the Linux sort command.
- Sorting on the first field, ZIP CODE and redirecting, '>' into a new file, 'sorted_injured_killed_by_zip.csv'.
- See the ss64.com site for more details on the Linux sort command.
The new file has about 1.36 Million lines.

Get a line count with wc. Display the first 10 records using head

bash > wc -l sorted_injured_killed_by_zip.csv 
1359291 sorted_injured_killed_by_zip.csv
bash > head -n10 sorted_injured_killed_by_zip.csv | column -t -s, --table-columns=ZipCode,#Injured,#Killed
ZipCode  #Injured  #Killed
11697    4         0
11697    3         0
11697    2         0
11697    2         0
11697    2         0
11697    1         0
11697    1         0
11697    1         0
11697    1         0
11697    1         0

Explanation

'wc -l'
- Counts the number of lines in our new file
'head -n 10'
- Prints out the first 10 lines of the file
'column -t -s, --table-columns=ZipCode,#Injured,#Killed'
- column
- '-t' switch will tell 'column' to print in table format.
- '-s' switch specifies an input delimiter of ','.
- The output is tabbed.

List the 10 worst zip codes for injuries

We can use the output file, sorted_injured_killed_by_zip.csv, from the previous example

perl -n -E '@a=split(q/,/,$_);$h{$a[0]} += $a[1]; END{say qq/$_,$h{$_}/ for keys %h}' sorted_injured_killed_by_zip.csv  | sort -nr -t, -k 2  | head -n10 | column -t -s, --table-columns=ZipCode,#Injured
ZipCode  #Injured
11207    10089
11236    7472
11203    7426
11212    6676
11226    6103
11208    6027
11234    5505
11434    5403
11233    5159
11385    4440

Explanation

'@a=split(q/,/,$_);'
- As there are no embedded commas in this file we use the Perl ‘split’ function to break up the 3 CSV fields in each row into array '@a'.
'$h{$a[0]} += $a[1];'
- The first element of each row, ZIP CODE is used as a key for Hash'%h'.
- The value is the accumulated number of injuries for that ZIP CODE.
'$h{$a[0]} += $a[1]'
- We accumulate the second element, $[1], which contains 'NUMBER OF PERSONS INJURED'
- We can set a value for a Hash key without checking if it exists already.
- This is called Autovivification which is explained nicely by The Perl Maven.
'END{say qq/$,$h{$}/ for keys %h}'
- The 'END{}'block runs after all the rows are processed.
- The keys(Zip Codes) are read and printed along with their corresponding values.
- We could have used Perl to sort the output by the keys, or values.
- I used the Linux sort.
'sort -nr -t, -k 2'
- Will perform a numeric sort, descending on the # of people injured.
'head -n10'
- Will get the first 10 records printed.
'column -t -s, --table-columns=ZipCode,#Injured'
The 'columns' command will produce a prettier output.
- '-t' for table format.
- '-s' to specify that the fields are comma separated
- '--table-columns'' to add column header names.

Observation

Zip code 11207, which encompasses East New York, Brooklyn, as well as a small portion of Southern Queens, has a lot of issues with traffic safety.

Display the 10 worst zip codes for traffic fatalities

perl -n -E '@a=split(q/,/,$_);$h{$a[0]} += $a[2]; END{say qq/$_,$h{$_}/ for keys %h}' sorted_injured_killed_by_zip.csv  | sort -nr -t, -k 2  | head -n10 | column -t -s, --table-columns=ZipCode,#Killed
ZipCode  #Killed
11236    44
11207    34
11234    29
11434    25
11354    25
11229    24
11208    24
11206    23
11233    22
11235    21

Explanation

With a few minor adjustments, we got the worst zip codes for traffic collision fatalities
'$h{$a[0]} += $a[2]'
- Accumulate the third element, $[2], which contains 'NUMBER OF PERSONS KILLED'

Observation

Zip code 11236, which includes Canarsie Brooklyn is the worst for traffic fatalities according to this data.
- Zip code 11207 is also very bad for traffic fatalities, as well as being the worst for collision injuries
These stats are not 100 percent correct, as out of 1,972,886 collision records, 1,359,291 contained Zip codes.
- We have 613,595 records with no zip code, which were not included in the calculations.

Some NYC Borough Stats

Similar to how we created the 'sorted_injured_killed_by_zip.csv', we can run the following command sequence to create a new file 'sorted_injured_killed_by_borough.csv'

perl -MText::CSV=csv  -E '$aofa = csv( in => $ARGV[0], headers => qq/skip/ ) ; ( $_->[2] =~ /^\S+/ ) && say qq/$_->[2],$_->[10],$_->[11]/ for @{$aofa}'  all_motor_vehicle_collision_data.csv | sort  -t, -k 3rn -k 2rn -k 1  >|  sorted_injured_killed_by_borough.csv

Explanation

The Borough field is the third column, '2 BOROUGH', starting from 0, in the 'all_motor_vehicle_collision_data.csv' file.
'( $_->[2] =~ /^\S+/ )'
- Only get rows which have non blank data in the BOROUGH field.
'sort -t, -k 3rn -k 2rn -k 1'
- I added some more precise sorting, which is unnecessary except to satisfy my curiosity.
- sort
- '-k 3rn''
- Sort by column 3(starting @ 1), which is the fatality count field.
- This is sorted numerically in descending order.
- '-k 2rn''
  - When equal, the injury count is also sorted numerically, descending.
- '-k 1'
- The Borough is sorted in ascending order as a tiebreaker.

Display the first 10 rows of this file.

bash > head -n10 sorted_injured_killed_by_borough.csv | column -t -s, --table-columns=Borough,#Injured,#Killed
Borough        #Injured  #Killed
MANHATTAN      12        8
QUEENS         3         5
QUEENS         15        4
QUEENS         1         4
STATEN ISLAND  6         3
BROOKLYN       4         3
BROOKLYN       3         3
QUEENS         3         3
BROOKLYN       1         3
QUEENS         1         3

Sanity check if we got all five boroughs

cut -d, -f 1  sorted_injured_killed_by_borough.csv | sort -u 
BRONX
BROOKLYN
MANHATTAN
QUEENS
STATEN ISLAND

Explanation

'cut -d, -f 1'
- cut to split the comma delimited file records.
- '-d,'
- Specifies that the cut will comma delimited
- '-f 1'
- Get the first field from the cut, which is the Borough Name.
'sort -u'
- Sorts and prints only the unique values to STDOUT
We got all 5 New York City boroughs in this file.

Display collision injuries for each borough

bash > perl -n -E '@a=split(q/,/,$_);$h{$a[0]} += $a[1]; END{say qq/$_,$h{$_}/ for keys %h}' sorted_injured_killed_by_borough.csv   | sort -nr -t, -k 2 | column -t -s,
BROOKLYN       137042
QUEENS         105045
BRONX          62880
MANHATTAN      61400
STATEN ISLAND  15659

Observation

Brooklyn emerges as the Borough with the most traffic injuries.

Display collision fatalities by Borough

bash > perl -n -E '@a=split(q/,/,$_);$h{$a[0]} += $a[2]; END{say qq/$_,$h{$_}/ for keys %h}' sorted_injured_killed_by_borough.csv   | sort -nr -t, -k 2 | column  -J -s, --table-columns Borough,#Killed
{
   "table": [
      {
         "borough": "BROOKLYN",
         "#killed": "564"
      },{
         "borough": "QUEENS",
         "#killed": "482"
      },{
         "borough": "MANHATTAN",
         "#killed": "300"
      },{
         "borough": "BRONX",
         "#killed": "241"
      },{
         "borough": "STATEN ISLAND",
         "#killed": "88"
      }
   ]
}

Explanation

Similar to the Injury count by Borough, this counts all fatalities by borough and prints the output in JSON format.
'column -J -s, --table-columns Borough,#Killed' Use the 'column' command with the '-J' switch, for JSON, instead of '-t' for Table.

I forgot to mention what date range is involved with this dataset. We can check this with the cut command.

cut -d, -f1 all_motor_vehicle_collision_data.csv | cut -d/ -f3 | sort -u
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023

Explanation

Get the date field, '0 CRASH DATE' which is in 'mm/dd/yyyy' format.
'cut -d, -f ' 'all_motor_vehicle_collision_data.csv'
- Get the first column/field of data for every row of this CSV file.
- '-d,' specifies that we are cutting on the comma delimiters.
- '-f 1' specifies that we want the first column/field only
- This is the date in 'mm/dd/yyyy' format.
'cut -d/ -f3'
- Will cut the date using '/’ as the delimiter.
- Grab the third field from this, which is the four digit year.
'sort -u'
- The years are then sorted with duplicates removed.

Observation

The dataset started sometime in 2012 and continues until now, March 2023.

Display the 20 worst days for collisions in NYC

bash > cut -d, -f1 all_motor_vehicle_collision_data.csv | awk -F '/' '{print $3 "-" $1 "-" $2}' | sort | uniq -c | sort -k 1nr | head -n20 | column -t --table-columns=#Collisions,Date
#Collisions  Date
1161        2014-01-21
1065        2018-11-15
999         2017-12-15
974         2017-05-19
961         2015-01-18
960         2014-02-03
939         2015-03-06
911         2017-05-18
896         2017-01-07
884         2018-03-02
883         2017-12-14
872         2016-09-30
867         2013-11-26
867         2018-11-09
857         2017-04-28
851         2013-03-08
851         2016-10-21
845         2017-06-22
845         2018-06-29
841         2018-12-14

Explanation

Get a count for all collisions for each date on record
Display the first 20 with the highest collision count
cut
- Get the first column from the dataset.
- Pipe this date into the awk command.
- AWK is a very useful one-liner tool as well as being a full scripting language.
'awk -F '/' '{print $3 "-" $1 "-" $2}'
- '-F '/' '
- Split the date into separate fields using the ’/’ as a delimiter.
- $1 contains the month value, $2 contains the day of month and $3 contains the four digit year value.
- These will be printed in the format '”yyyy-mm-dd”.
Dates are then sorted and piped into the uniq command.
'uniq -c'
- Will create a unique output.
- '-c' switch gets a count of all the occurrences for each value.
The output is piped into another sort command, which sorts by the number of occurrences descending.

Observation

I’m not sure if there is any explanation for why some days have a lot more collisions than others. Weatherwise, January 21 2014 was a cold day, but otherwise uneventful. November 15 2018 had some snow, but not a horrific snowfall. The clocks went back on November 4, so that wouldn’t be a factor.
2014-01-21 weather
2018-11-15 weather

Display the twenty worst times during the day for collisions

bash > cut -d, -f2 all_motor_vehicle_collision_data.csv | sort | uniq -c | sort -k 1nr | head -n20 |column -t  --table-columns=#Collisions,Time
#Collisions  Time
27506       16:00
26940       17:00
26879       15:00
24928       18:00
24667       14:00
22914       13:00
20687       9:00
20641       12:00
20636       19:00
19865       16:30
19264       8:00
19107       10:00
19106       14:30
19010       0:00
18691       11:00
18688       17:30
16646       18:30
16602       20:00
16144       8:30
16008       13:30

Explanation

We use the time field, '1 CRASH TIME', which is in 24 hour format, 'HH:MM'

Observation

Using the 'actual time' may be a bit too specific. I’ll use the 'hour of day' instead to give a 60 minute time frame.

 bash > cut -d, -f2 all_motor_vehicle_collision_data.csv | cut -d : -f1 | sort | uniq -c | sort -k 1nr | head -n10 | column -t --table-columns=#Collisions,Hour
#Collisions  Hour
143012      16
139818      17
132443      14
123761      15
122971      18
114555      13
108925      12
108593      8
105206      9
102541      11

Explanation

Similar to the previous example, except this time the cut command is used to split the time HH:MM, delimited by ':'
'cut -d : -f 1'
- '-d'
- The 'cut' delimiter is ':'
- '-f 1'
- Grab the first field, 'HH' of the 'HH:MM'.
Use something like the printf command to append ':00' to those hours.

Observation

As you would expect, most collisions happen during rush hour.

Display the worst years for collisions

bash > cut -d, -f1 all_motor_vehicle_collision_data.csv | cut -d '/' -f3 | sort | uniq -c | sort -k 1nr | head -n10 | column -t --table-columns=#Collisions,Year
#Collisions  Year
231564       2018
231007       2017
229831       2016
217694       2015
211486       2019
206033       2014
203734       2013
112915       2020
110546       2021
103745       2022

Explanation

We use the first column, '0 CRASH DATE' again
'cut -d '/' -f3'
- Extracts the 'yyyy' from the 'mm/dd/yyyy'

Observation

Some improvement seen in 2020, 2021 and 2022, if you can believe the data.
One unscientific observation here is that the NYPD may have been much less vigilant in the past few years than they were prior to Commissioner Raymond Kelly and his successors.
Also, by only printing out the worst 10 years, partial years 2012 and 2023 were excluded.

For a comparison. See how many people were injured or killed in traffic collisions.

First create a work file, `'sorted_injured_killed_by_year.csv'`, with three columns, Year, Injured count and Fatality count

We need the Text::CSV Perl module here due to those embedded commas in earlier fields. Below are the three fields needed.

0   CRASH DATE
10  NUMBER OF PERSONS INJURED
11  NUMBER OF PERSONS KILLED

bash > perl -MText::CSV=csv -E '$aofa = csv( in => $ARGV[0], headers => qq/skip/ ); ($_->[10] || $_->[11]) && say substr($_->[0],6,4) . qq/,$_->[10],$_->[11]/ for @{$aofa}' all_motor_vehicle_collision_data.csv | sort > sorted_injured_killed_by_year.csv

Check out the work file we just created

bash > wc -l sorted_injured_killed_by_year.csv 
433081 sorted_injured_killed_by_year.csv

bash > head -n10 sorted_injured_killed_by_year.csv | column -t -s, --table-columns=Year,#Injured,#Killed
Year  #Injured  #Killed
2012  0         1
2012  0         1
2012  0         1
2012  0         1
2012  0         1
2012  0         1
2012  0         1
2012  0         1
2012  0         1
2012  0         1

Worst years for collision injuries

perl -n -E '@a=split(q/,/,$_);$h{$a[0]} += $a[1]; END{say qq/$_,  $h{$_}/ for sort {$h{$b} <=> $h{$a} } keys %h}' sorted_injured_killed_by_year.csv | head -n10 |  column  -t -s, --table-columns=Year,#Injured 
Year  #Injured
2018    61941
2019    61389
2017    60656
2016    60317
2013    55124
2022    51883
2021    51780
2015    51358
2014    51223
2020    44615

Explanation

This is similar to how we got the Zip Code and Borough data previously.
This time the Perl sort is used instead of the Linux sort.
'END{say qq/$_, $h{$_}/ for sort {$h{$b} <=> $h{$a} } keys %h}'
- 'for' statement loops through the '%h' hash keys(years).
- The corresponding Hash values(Injured count), are sorted in descending order.
'sort {$h{$b} <=> $h{$a} }'.
- $a and $b are default Perl sort variables.
- Rearranged it to 'sort {$h{$a} <=> $h{$b} }', to sort the injury count in ascending order.

Observation

While the collision count may have gone down, there isn't any real corresponding downward trend in injuries.

The worst years for collision fatalities.

bash > perl -n -E '@a=split(q/,/,$_);$h{$a[0]} += $a[2]; END{say qq/$_,  $h{$_}/ for sort {$h{$b} <=> $h{$a} } keys %h}' sorted_injured_killed_by_year.csv | head -n10 |  column  -t -s, --table-columns=Year,#Killed
Year  #Killed
2013    297
2021    294
2022    285
2020    268
2014    262
2017    256
2016    246
2019    244
2015    243
2018    231

Explanation

Slightly modified version of the injury by year count.

Observation

Same as with the injuries count. There isn't any real corresponding downward trend in traffic collision fatalities.

Conclusion

There’s lots more work that can be done to extract meaningful information from this dataset.

What’s clear to me, is that all the political rhetoric and money poured into Vision Zero has yielded little in terms of results.
Most of the solutions are obvious from a logical point of view, but not a political point of view. I walk and cycle these streets and know how dangerous it is to cross at the “designated” crosswalks when cars and trucks are turning in on top of you. Cycling in NYC is even worse.

Some sugggested solutions

Delayed green lights co cars cars don't turn in on pedestrians at crosswalks.
Much higher tax and registraton fees for giant SUV's and pickup trucks. The don’t belong in the city.
Better bike lanes, instead of meaningless lines painted on the road.
- Many bike lanes are used as convenient parking for NYPD and delivery vehicles.
Basic enforcement of traffic laws, which isn't being done now.
- Drivers ignore red lights, speed limits, noise restrictions etc. when they know they aren't being enforced.
- Driving while texting or yapping on the phone is the norm, not the exception.
Drastically improve public transit, especially in areas not served by the subway system.

Some Perl CLI Resources

Perldocs - perlrun

Peteris Krumins has some great e-books

Dave Cross - From one of his older posts on perl.com

Some NYC Street Resources

StreetsBlog NYC

Hellgate NYC - Local NYC News

Liam Quigley - Local Reporter

More Liam Quigley - Twitter

These Stupid Trucks are Literally Killing Us - YouTube

Me

Linkedin
My Blog

Forem: Austin

NYC Collision Data Analysis

Crash City

Analyzing NYC Traffic Collision Data on the Linux Command Line with SoQL and Curl

Using the Socrata Query Language, SoQL

How many collisions since July 2012

Explanation

Observation

Getting all the collision records between two arbitrary dates, June 30th 2022 to December 31 2022.

Explanation

Observation

The NYC dataset column names

Corresponding Field Data Types

SoQL Query Clauses from the Docs

Get all the collisions for zip code 10036, Times Square NYC, for Feb 2023

Explanation

Explanation

Observation

Get the 10 worst zip codes for collisions in February 2023

Explanation

Observation

As the queries get more complex, these one line commands start to get long and hard to manage.

Explanation

Observation

Run the previous query minus years 2012 and 2023

Explanation

Observation

Run a query to get a yearly total of injured pedestrians and cyclists

The Config

The Query using the config file

Observation

Using jq to do additional filtering

Explanation

Observation

Get the 10 worst zip codes for collisions in January 2023

Config file .nyc_jan_coll_curlrc

Explanation

Observation

Some Perl CLI Resources

Some NYC Street Resources

Me

Analyzing New York City Traffic Collisions with Perl One-Liners and Linux

Just when you think it’s safe outside. Here are some statistics relating to traffic collisions in New York City.

Background

Display the first five records of the dataset using head

Using head to display the first record only

Use Perl to list the column names in numerical order

Explanation

Create a work-file containing the zip-code, injury count, and fatality count. Only get records that include a zip-code and at least one injury or fatality.

Method

Explanation

Get a line count with wc. Display the first 10 records using head

Explanation

List the 10 worst zip codes for injuries

We can use the output file, sorted_injured_killed_by_zip.csv, from the previous example

Explanation

Observation

Display the 10 worst zip codes for traffic fatalities

Explanation

Observation

Some NYC Borough Stats

Explanation

Display the first 10 rows of this file.

Sanity check if we got all five boroughs

Explanation

Display collision injuries for each borough

Observation

Display collision fatalities by Borough

Explanation

I forgot to mention what date range is involved with this dataset. We can check this with the cut command.

Explanation

Observation

Display the 20 worst days for collisions in NYC

Explanation

Observation

Display the twenty worst times during the day for collisions

Explanation

Observation

Explanation

Observation

Config file `.nyc_jan_coll_curlrc`

First create a work file, `'sorted_injured_killed_by_year.csv'`, with three columns, Year, Injured count and Fatality count