For my data assignment, my original idea was to focus on roads in Ottawa. Initially I wanted to see where the most speeding and dangerous driving citations were being issues by the Ottawa Police, and create a hotspot map to show where people were driving the most over the speed limit in the city. After taking hours to find data, then promptly losing it on my harddrive, I perused the Ottawa 311 service request dataset.
I then asked myself a question, being ‘Where are the worst/most frequently maintained roads in the city?” and lucky for me my query could be answered.
In the datasets found at http://data.ottawa.ca/en/dataset/2014-311-monthly-service-request-submissions and http://data.ottawa.ca/en/dataset/2015-311-monthly-service-request-submissions every single service record from Jan. 2014 to Sept. 2015 can be found, ranging from noise complaints to graffiti to ultimately what I was looking for, road maintenance.
The call type of ‘Roads Maintenance’ encompasses descriptions such as bus stops and curbs to private property and road travelled surfaces.
I sorted my data for each month individually by the ‘Road Maintenance’ call type. This narrowed down my search but putting my data into a pivot table to clearly see the results made things clearer to see. I did this for all 21 months of data I had been able to acquire, sorting and organizing each individually before combining each pivot table into one. I then took both pivot tables, containing all the road maintenance requests by ward for both 2014 and the nine months of 2015 and put them in their own workbook so it was easier to compare the data by eye.
I then used an online data visualization tool, onlinecharttool.com, to make three bar graphs. One graph for 2014, one for 2015, and one comparing both years (keeping in mind 2015 isn’t complete yet).
What I have found in my data is that Ward 12 has the most requests for road maintenance in the city, that ward being Rideau-Vanier. The ward had 15,790 requests in 2014 and as of Sept. of this year has 11,619.
In a close second, there was Ward 14, Somerset. A total of 14,603 requests were made in 2014 with 10,297 so far this year.
These two wards were in a class of their own. The closest ward in 2014 was Ward 15, Kitchissippi, with 9725, a difference of 4878. In 2015 the bronze medal went to the Capital ward with 7407, still 2,890 behind Somerset.
The rest of the table decreases naturally, with outlier wards like Stitsville and West-Carleton March being at the bottom of the table with 3768 and 3568 requests respectively in 2014 and currently 2848 and 2500 this year.
click the graphs below to enlarge
