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:

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

To correlate with my glue story, I researched some data regarding impaired driving trends in Canada, by province.

The numbers were drawn from 2011 police records and were collected from the Statistics Canada website here.

This is the most recent, credible information on impaired driving in Canada.

In Ontario, approximately 100 people were injured and over 20 were killed in a total of 17,326 impaired driving incidents during the year of 2011.

These numbers are second only to our neighbouring province, Quebec, which saw more than triple the number of injuries.

According to the latest studies, the province of Quebec has the highest rate of injuries and death caused by impaired driving. Among the 16,820 impaired driving incidents that year, 349 people were injured and 25 were killed.

British Columbia, however, held the largest number of total incidents at 18,835 but had only 70 injuries and 14 deaths as a cause of impaired driving.

In researching the population of all provinces in Canada during 2011 I found that Ontario, British Columbia, Alberta and Quebec had the four highest populations. These four provinces also had the highest number of impaired driving incidents in the country.

The population data was found at the link below.
http://http://www12.statcan.gc.ca/census-recensement/2011/dp-pd/hlt-fst/pd-pl/Table-tableau.cfm?LANG=Eng&T=101&S=50&O=A

Based on this correlation, it could be said that a higher population will likely cause more impaired driving accidents.

The three provinces with lowest populations, Northwest Territories, Yukon and Nunavut also happen to have the three lowest impaired driving rates as well. They also have three of the fewest injuries and deaths related to driving under the influence.

While the four top populated areas have four of the highest injury and death rates.

What I found most interesting in the data was the percentage rate of change in impaired driving between the years of 2001-2011. Only two provinces had a negative rate of change, meaning less people were driving impaired. Ontario had a -28% rate of change in impaired driving while Quebec had an -18% rate of change.

The most shocking of the numbers is Newfoundland and Labrador, which had a 69% increase in impaired driving from 2001-2011. The Northwest Territories were not far behind with an increase of 58%.

Interactive graphs displaying the information can be found here:
https://infogr.am/impaired_driving_canada

My goal was to sort through City of Ottawa data found here, http://data.ottawa.ca/dataset/outdoor-rinks/resource/9ad7c75a-4b53-4e17-a8e1-601419f8a659 and try to determine which of Ottawa’s 264 Outdoor rinks or ODR’s are the best available. My reasoning behind this was while in school at Toronto, I looked up odr;s and found one open on the opposite side of the city, after commuting an hour on subway and 30 more on bus, after a short walk I found out it was just a puddle frozen over half of a tennis court.

The criteria I was looking for was, whether or not it was maintained, if there were; toilets/changing areas available, if it had boards and if there was lighting for playing after dark.

Some of the words were cut off, the 54 value is other types of ice surfaces, excluding rinks that qualify as a yes, while only 24 rinks of the 264 meet the specific standard, while 78 ice surfaces in total meet the criteria I had set.  Of the 264 rinks, only one is open; Rink of Dreams, which has a refrigerated ice surface.

Looking into the specific areas if you perhaps don’t need a toilet on site, or boards to enjoy your time on the rink, here is the data I sorted through to reach the results.

Of the 264, 169 had boards, meaning that games of shinny and not just free skate would be more likely on these surfaces.

This time of year when it is getting dark early, most of us don’t have time to hit the ice during daylight hours. It is important so see how many are lit, and interesting to see how each is set up, 123 claimed to have permanent lights, with 54 having temporary. 71 of the rinks did not meaning that it would be unlikely you could play after 4:30 pm this time of year. The Both and Lights values, were in the data columns and I can only assume the data was lacking on the specifics but if factored into having lights, 193 have lighting of some kind while 71 have none of any kind.

An interesting one was considering ice maintenance, of the 264 rinks only 17 do not receive maintenance. What is interesting is that the city only operates and maintains 33 of the 264 which is only 12.5 percent of the rinks, while 214 rinks are community operated.

While, important to some, the value of toilets or a change area, is a must for some people. Less than half of the rinks(94) have toilets, assuming that since most are community owned they would not have facilities at the ready leading to the gaudy 170 rinks that do not have restrooms.

Finally the last source, was relating to the ice surfaces specifically. Only one was artificial, leading to why it could be open so early, since on is open as of my knowledge despite the warm weather. 56 are your traditional ice rinks that you see hockey games on, 38 have double ice surfaces, and 52 are the “puddle” ice surfaces I have began to despise. Lastly 13 sported a combination of rink and puddle on site.

Using the Open Data Ottawa website, I acquired the statistics on bacteria levels at Ottawa beaches for the last three summers. There are 5 beaches monitored by the city: Brittania, Mooneys Bay, Westboro, Petrie Island River beach and Petrie Island East Bay. They are tested every day by Ottawa public health staff for E. Coli levels

E. Coli is known as an “indicator” bacteria, meaning that their presence is an indicator of fecal pollution and that other, more harmful pathogens are likely present as well.

If there are over 100 E. Coli per 100 mL of water, an advisory will be put out the next day to warn the public. If there are over 200 E. Coli per 100 mL, or more than 100 for two consecutive days, a no swimming advisory will be issued.

An advisory will also be issued after a heavy rainfall (20mm or more) since it can wash many contaminants into the river. On top of that, sewage overflows from sewers carrying both storm water and untreated sewage can also occur during a heavy rainfall, affecting all beaches downstream.

Here is a map of the combined (storm water+sewage) sewer overflows in Ottawa. This is where potential overflow caused by heavy rain will enter the river.

This is important to note because the beaches at Petrie Island are the only ones downstream of downtown Ottawa/Gatineau.

I put the data into excel and sorted it to show the number of days over 100 E. Coli and the number of days over 200 for each year. Then I graphed the data using the website DataHero.

I determined that the overall amount of no swim advisories were way down at all beaches this summer, with Petrie Island river beach, Westboro and Brittania only having 7.

For comparison, in the summer of 2014, Petrie Island river beach had 16.

I spoke with with representatives from Ottawa Riverkeeper and Ottawa Public Health, to discuss possible reasons for this decrease in no swim advisories. They speculated that the work done by the city on the combined storm water and sewage systems downtown could be partially responsible. They said a particularly dry summer could also be a factor. Additional engineering controls by the city to deter birds, such as stringing wires and even using drones to scare them off could also have contributed.

Brittania beach has historically been one of the cleanest beaches and the representative from Ottawa Public Health explained that the large pier at the beach deflects some contamination away from the shore. There are also wires used to deter birds. It is also the farthest beach upstream from the Ottawa/Gatineau downtown, which is also beneficial.

In conclusion, bacteria levels in the Ottawa river are caused by many different environmental factors including rainfall, positioning on the river, and wildlife. No swim advisories are down significantly this year, and one of the reasons could potentially be the work done by the city on its combined storm water and sewage systems.

Sources:

Data set:http://data.ottawa.ca/dataset/beach-water-sampling-data/resource/ab85fe8e-98c4-4388-9dc3-2839890f637d

http://www.petrieisland.org/water-quality-archives

http://www.ottawariverkeeper.ca/beach-closures/

http://app07.ottawa.ca/blogs/physicians/2015/07/23/is-it-is-safe-to-swim-at-ottawas-beaches/

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

Ontario colleges saw a dramatic rise in enrollment for game development, design and programming programs between 2005 and 2011. These programs didn’t exist before 2005.

Algonquin College had the largest share of enrollments in 2011. George Brown College, in Toronto, was a near second.

Both graphics were sourced from https://www.ontario.ca/data/college-enrolments-1996-2011

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

This is a test post from Daniel Prinn.

With all the talks of refugees coming to Canada in the next few months, I figured it was time to understand the lives of the newcomers in our capital. Using Statistics Canada, I was able to find the National Household Survey of Canada for our two closest statistical years: 2006 and 2011.

After a preliminary look at the data, I narrowed the information to concentrate on the percentage change of immigrants in Ottawa. I gathered the data sheets on excel and created a table that reflected the total population in private households in Ottawa to the number of immigrants, then filtered to create a percentage.

What I learned here is that Ottawa is on an upward trend with immigrants, considering that it has risen from 22.3 per cent of the total population to 23.4 per cent.

This led me to a new question: Do the newcomers tend to choose a specific area in the city or do they just go anywhere?

From there I created a bar graph that calculated the most recent data from the National Household Survey of Canada (2011) that measured the concentrations of immigrants within each ward.

I did this by separating the number of private households by citizens to the immigrants within the ward to see how many immigrants make up each ward.

Here is what I discovered:

As the graph shows, Gloucester-Southgate holds the highest immigrants per-capita in Ottawa. These numbers equate to 16,000 immigrants out of 47,910 private home dwellers in Gloucester-Southgate, or ward 10.

On the bottom-left, you will find a map of Gloucester-Southgate inside the city of Ottawa, which is broken down into 23 wards.

What makes these pictures significant is that Gloucester-Southgate does not make up a large part of the Ottawa city map.

So why do immigrants tend to go there?

“Southgate is near the airport so the cost of living goes down considering the noise pollution,” said Samir Kadou, a community leader in the ward. “It doesn’t matter where you’re from, the newcomers get our city appeal right when they land in the country.”

Was he saying that there was no specific nationality that made up their majority? I had to look deeper to understand.

So I created two tables that counted the majority continents of origin that new immigrants were coming from: America, Europe, Africa and Asia. I made one with the 2006 data and one with the 2011 data.

Next, I measured the top-3 concentrations of ethnicities within each ward by sorting each column from largest to smallest to see if I could find any patterns between the two surveys. What I found was that immigrants have always stuck to Gloucester-Southgate, although it ranked second in overall immigration numbers in 2006, behind River ward.

But in 2011, it topped the list. Although several continents contributed to the ascension, a big reason it rose was from continued immigration of Asians & Middle Easterner’s, who seem to hold a strong community in the ward. Asians and Middle Eastern’s reached the top spot in Gloucester-Southgate in 2006 as well, suggesting that they may be developing roots in the area.

But as the pie chart below shows, although Gloucester-Southgate may be saturated with the most immigrants in the Ottawa region by-far, nearly two thirds of the population of that community come from Canadian heritage or from generational living. This should help ease some feelings of discomfort for those who worry about immigration policies.

Sources and data:

http://data.ottawa.ca/dataset/12718f08-4005-4d42-a14e-41c85c5badc9/resource/5477481b-94d5-43ea-a524-717459d3d41f/download/2006censusdata-.csv

http://data.ottawa.ca/dataset/12718f08-4005-4d42-a14e-41c85c5badc9/resource/bdf88935-5001-4f8a-9024-7a4f1984032f/download/2011nhswarddata.csv

One of the prime interests of a city’s safety board should always be their roads. Ottawa may not be a city buzzing with energy – after all, it is a government town – but it’s one of Canada’s largest and with a sizeable population, the safety of the roads should always be of concern.

I set out to find out the five W’s of vehicle accidents in Ottawa. With this information, it would make it much easier for the city to identify and attempt to lower the amount of collisions that tale place in Ottawa.

You can find the information I used here and here.

The first set of data is stretched out five years from 2009 to 2013 (the latest road safety and collision report the City of Ottawa has released), and the second is from 2007 to 2011, in which I only took data on the age of all people that are involved in accidents, seeing as the first set of data failed to represent that specific category.

All my analysis was done through Excel, sorting and creating charts with the data. I also used ArcGIS, but we’ll examine that side of the data more near the end.

I found the time frame that had the biggest spike in accidents was the day’s second rush hour from 3 p.m. to 6 p.m. Oddly enough, there is a large difference from when everyone is travelling to work than leaving, even though around the same amount of cars are typically on the road.

I found that the age range that tend to be invloved with the most car accidents is 15 to 29, and it isn’t a close race. Drivers seem to be the most aggressive at a young age, I guess.

The time of day is one thing, but the bigger picture may be in weeks and months. Overall, more collisions are taking place as the week gets closer to the weekend and then drop right off.

And as you probably would’ve wondered, the months that seem to be the magnets for vehicle collisions are the ones in the cold of winter.

Those findings can be explained by the increase in poor conditions on the roads. Of course, most of the year the ground is dry, so you would expect the bulk of collisions to be on dry roads – which, they are – but rain, snow, ice and slush do contribute to the danger.

But probably the biggest weapon in all of the data is location. Sure, a city can pinpoint who tend to be in these accidents, what the roads are like at the time and what time they happen at, but it would be most helpful if they knew where to put up “slow” signs and show drivers where they need to be more cautious.

These are the top locations for collisions in Ottawa.

To make this an even better visual, I took the only maps the City of Ottawa released of fatal collisions from 2011-2013 and mapped them out with ArcGIS.

The yellow pins are 2011 collisions, the red are from 2012 and the green are the latest fatal collisions released for 2013.

Here are the maps I used: 2011, 2012, 2013.

So if you were going to pinpoint the most likely aspects of a collision in Ottawa, the description would go as follows.

A person in their early 20’s at West Hunt Club and Woodroffe collided with another vehicle on a Friday in January at 4 p.m while the roads were snow-free.