23.4.15

Exploring Toronto 311 calls Part1: Why people are calling?

9:02 AM  ,


Toronto 311Service Request - Customer Initiated contains information on on customer initiated service requests received by the city of Toronto for Solid Waste Management, Transportation Services, Toronto Water, Municipal Licensing & Standards, and Urban Forestry related request.

In the April session of the Toronto OpenRefine Meetup we took an hour to explore the data set and prepare it to map in using Google Fusion Table. For this example we will use only the 2015 calls available to this date:  from January 1st to March 31. Download it from here: http://opendata.toronto.ca/311/service.request/SR2015.zip


The 311 Service Request - Customer Initiated data set have three fields:
  • CREATION DATE 
  • SERVICE REQUEST LOCATION 
  • SERVICE REQUEST TYPE
In this first article we will explore the relationship between the type of request and its creation date.


What is the most common request

New to OpenRefine? Learn how to use text facet and search.
A simple text facet sorted by count on the SERVICE REQUEST TYPE field show that the Replacement for Organic bin is the most common request among the 375 available. 



However we can see that several top request are regarding Garbage Bin exchange or replacement. How many type of service request concerned this topic?

Create a text filter on the SERVICE REQUEST TYPE and enter " bin". The first facet refresh to display the 52 matching request type:


Toronto frozen pipe issue

Last February the city of Toronto set up a SWAT team to respond to an “unprecedented” number of calls from residents and businesses without water due to frozen pipes. (article). Can we track this event in the data?

Let combine three facets to:
  • text facet on the SERVICE REQUEST TYPE
  • text search on the key word "water" for the field SERVICE REQUEST TYPE
  • timeline facet on the CREATE DATE field


We do see a spike in the number of request and by adjusting the time line facet we see its started from February 15 until mid march.




Looking at the time of call

The 311 line is open 7 days a week 24h a day. However how distributed are the call?  The CREATION DATE field is detailed at the second. We can extract the hours of the day in a separate column for analysis.

Create a new column based on CREATE DATE and use the following GREL expression to extract the hour:

value.datePart("hour").toNumber()

By formatting the output as number we can create a numeric facet to see the distribution of the call through the day:

Even if the service is open all day long, the majority of the call are between 8AM and 7PM:


What type of calls are the most common at night between 10PM and 5AM? Because we cannot select all the call in one facet, we will need to first bookmark, or the star, the call between 10PM and midnight and then those between midnight and 5AM.




New to OpenRefine? Learn how to use bookmark row using start.

... Can sleep at night? Blame your neighbour or the weather!