All posts by Donald Teuma-Castelletti

Ontario Border Wait Times

Share

For this assignment I will be looking at how varied the border wait times are at the Ontario borders, in the period from April 5, 2014 until September 30, 2015. The reason this data is so limited is because of the availability of the data on the open data portal from the Government of Canada. Though they do have data available from 2010 – 2014, this data has no values for the Ontario locations, despite having these borders listed. (The Ontario locations come up as having “No delay” for all recorded data between 2010 and 2014.)

Evidently, I took the data from the Government of Canada’s open data site, but this led to problems and extra work. Since April 5, 2014, all data compiled has switched over to quarterly status, as in it is released in three month periods, and made my job harder. I had to download all of the data, then filter each set individually to bring out solely the Ontario locations, then move it into descending order to get a better look at how long wait times ran that in that period. This wouldn’t be so bad if I simply had the longest wait time for that day for each location. But I didn’t. Instead, I had multiple times, all at seemingly randomly recorded intervals, for each day, with varying wait times throughout the day. This led to much confusion & cursing as the intervals became varied and the pure numbers of the data were so huge. This was also another reason as to why I decided against doing all of Canada and narrowed it down to Ontario only. (Also, another frustration, they stopped labelling the wait time with “minutes” after the third data set, leaving all of my column labels as just numbers instead of minutes.)

So bringing the focus sharper, I needed to then decide how it would be easiest to understand the data when mapped out. This led me to use the location of the border stop as the first row, before then dividing it up further into the three months of the quarterly period. As you see in the graphs, the location is divided into the three months for that quarter. Then, across the columns on the top, each number is the amount of minutes that that border stop had. Each bar on the graph is now a count for the number of times that month that that location had had that wait time.

To break this down, in the April 5 – June 30, 2014 graph, you can see that Fort Erie had 276 counts of a one minute wait time in June. For the same chart, in the same column, Queenston’s June had 531 counts of one minute wait times. One of the most interesting things on this graph is how at the 10 minute column, suddenly every single month has multiple counts of this wait time. Also noticeable is how this almost also occurs at the 30 minute mark. Besides this, great observations to make are how Fort Frances has three counts of the highest recorded wait time this entire quarter at 75 minutes, and how Queenston and Fort Erie both had the most amount of wait times for the month of June in 2014, as they consecutively had the highest counts in each column.

Therefore, looking at all of my data across the six graphs, my nut would be that Queenston and Fort Erie consecutively have the most amount of wait times from June 2014 until Sept. 2015. This becomes obvious when looking at the graphs, as these two locations have super high counts of wait times in not just the tiny one to 10 minute categories, but also past these, usually up to the 35 or 40 minute wait times. What is it about these locations that their wait times are recorded so meticulously, while all the other seem happy to round to the nearest 10 interval? Also interesting to further explore would be to look at what time of day had the highest wait times, but this would be hard without them recording the wait times at set intervals.

This data required multiple graphs in a system that was capable of handling this much data, therefore I used Tableau in order to graph my work. Even then it took a while for the amount of data to import and be worked over by the system. But this method was most efficient for the size and scope of the data. Most of the working copies (the data brought down to the ON level) had counts anywhere from 300,000 to 500,000.

Links to Graphs:

  1. April 5th – June 30th 2014: https://public.tableau.com/profile/donald.teuma.castelletti#!/vizhome/2014_04-05_06-30-Graph/Sheet1
  2. July 1st – September 30th 2014: https://public.tableau.com/views/2014_07-01_09-30_Graph/Sheet1?:embed=y&:display_count=yes&:showTabs=y
  3. October 1st – December 31st 2014: https://public.tableau.com/views/2014_10-01_12-31_Graph/Sheet1?:embed=y&:display_count=yes&:showTabs=y
  4. January 1st – March 31st 2015: https://public.tableau.com/views/2015_01-01_03-31_Graph/Sheet1?:embed=y&:display_count=yes&:showTabs=y
  5. April 1st – June 30th 2015: https://public.tableau.com/views/2015_04-01_06-03_Graph/Sheet1?:embed=y&:display_count=yes&:showTabs=y
  6. July 1st – September 30th 2015: https://public.tableau.com/views/2015_07-01_09-30_Graph/Sheet1?:embed=y&:display_count=yes&:showTabs=y