For my story I chose to look at noise complaints. I wanted to see where most complaints were lodged (which riding?) and how many the 311 call centre dealt with. I used a 311 dump file that compiled the monthly service requests that is accessible from: http://data.ottawa.ca/en/dataset/2014-311-monthly-service-request-submissions http://data.ottawa.ca/en/dataset/311-monthly-service-request-submissions http://data.ottawa.ca/en/dataset/2015-311-monthly-service-request-submissions I then used MySQL to retrieve all noise complaints from the datasets and compile them into a single excel file. From there I sorted the data according to times when the calls came in. This meant separating the time from the date value in excel by using ‘text to columns.’ From there I sorted all the calls into hour-long blocks (eg: 1 a.m. to 1:59 a.m.). I was able to tally up each hour-long block and see which hours had the highest call volume. It turns out that the 11 p.m. to 11:59 p.m. is the busiest hour. The graph is available here I also created an excel chart showing the total percentage:
Hour (1:00- 24:00) | Total Number of Calls | % of total Call Volume |
1:00-1:59 | 2742 | 8.9 |
2:00-2:59 | 1788 | 5.8 |
3:00-3:59 | 1357 | 4.4 |
4:00-4:59 | 794 | 2.5 |
5:00-5:59 | 480 | 1.5 |
6:00-6:59 | 816 | 2.6 |
7:00-7:59 | 614 | 1.9 |
8:00-8:59 | 617 | 2 |
9:00-9:59 | 554 | 1.8 |
10:00-10:59 | 444 | 1.4 |
11:00-11:59 | 505 | 1.6 |
12:00-12:59 | 467 | 1.5 |
13:00-13:59 | 465 | 1.5 |
14:00-14:59 | 518 | 1.6 |
15:00-15:59 | 502 | 1.6 |
16:00-16:00 | 496 | 1.6 |
17:00-17:59 | 519 | 1.6 |
18:00-18:59 | 640 | 2.1 |
19:00-19:59 | 646 | 2.1 |
20:00-20:59 | 888 | 2.8 |
21:00-21:59 | 1329 | 4.3 |
22:00-22:59 | 2678 | 8.7 |
23:00-23:59 | 6365 | 20.7 |
00:00-00:59 | 4493 | 14.6 |
Total Call Volume | 30717 |
I also wanted to see which wards received the most complaints. Not surprisingly, Rideau- Vanier was the ward with the highest volume. This ward has both Sandy Hill and the ByWard Market in it and these are both notoriously loud neighbourhoods. I made another chart here
Ward | Number of Noise Complaints | % of Total Calls |
Ward 1- Orleans | 812 | 2.6 |
Ward 2- Innes | 664 | 3.1 |
Ward 3- Barrhaven | 970 | 3.1 |
Ward 4- Kanata North | 454 | 1.4 |
Ward 5- West Carleton- March | 226 | 0.7 |
Ward 6- Stittsville | 411 | 1.3 |
Ward 7- Bay | 1141 | 3.7 |
Ward 8- College | 1433 | 4.6 |
Ward 9- Knoxdale- Merivale | 967 | 3.1 |
Ward 10- Gloucester- Southgate | 1147 | 3.7 |
Ward 11- Beacon Hill- Cyrville | 986 | 3.2 |
Ward 12- Rideau- Vanier | 6511 | 21.1 |
Ward 13- Rideau- Rockliffe | 1546 | 5 |
Ward 14- Somerset | 4224 | 12.7 |
Ward 15- Kitchissippi | 1471 | 4.7 |
Ward 16- River | 1721 | 5.6 |
Ward 17- Capital | 2318 | 7.5 |
Ward 18- Alta Vista | 1093 | 3.5 |
Ward 19- Cumberland | 625 | 2 |
Ward 20- Osgoode | 238 | 0.8 |
Ward 21- Rideau-Goulbourn | 263 | 0.8 |
Ward 22- Gloucester- South Nepean | 615 | 2 |
Ward 23- Kanata South | 857 | 2.8 |