This episode of Mean, Median, Moose, we look at topics in water. At the end of the episode you’ll also find an interview with Paul Connor, the executive directory of the Canadian Open Data Society.

Building ShipsCanada has a national shipbuilding strategy and, fairly often, much is written about it. It got us wondering, “how many ships does Canada build anyway?”. While the strategy is centered around military vessels, getting some idea about how many ships Canada builds in general compared to other countries might put some of the discussion in context. The answer is, in the scheme of things, Canada builds a tiny amount. Try and find us in this graph:

You can’t see it, because the number is miniscule. Interestingly, three rivals in South East Asia dominate global ship production. If you are wondering where the United States is, interestingly they’re 11th, behind France. Britain also does not make the top 10.

Container shippingSpeaking of ships, what about containers? The same UNCTAD website we got the ships built statistics also provides information on shipping. Here’s the containers shipped through the ports of Canada and some English speaking peers:

Canada interestingly punches above its weight compared to the US, which has about ten times the population. But, as you can see, while US shopping has grown dramatically since 2010, Canadian shipping has grown more slowly. Australia has fewer people than Canada, but, being an island, likely has more needs for shipping.

The Great LakesI talked about Ice Coverage of the Great Lakes in a our 2021 in our 12 Datasets of Xmas episode but I have returned to the NOAA data to look at water temperatures in the Great Lakes.

I have been playing around with a new data tool called Flourish, pretty much it creates interactive data visualizations that you can embed on your website for free. Upload an excel spreadsheet, select the type of visualization, tag columns to the axis and it spits out visualizations. Some advanced features are paid, and free exports do come with watermarks but it does a pretty good job of making interactive visualizations.

Below is over almost 30 years of daily average surface water temperatures on Lake Superior. For leap years I averaged Feb 28 and 29 temps together to normalize 365 days each year and to have a common x axis.

Here is a timeline of Lake St Clair Average Temperatures.

Great Lakes Plastics Cleanup While looking for data this month, I stumbled across Great Lakes Plastic Cleanup. It is a neat project that has some interesting potential data. With partnerships on both sides of the border, although none locally in the Windsor-Essex region. The GLPC deploys a range of technology tools from rovers that clean beaches, to passive capture basket and discharge catchments, to water based drones, to capture plastics and other waste.

They currently have some preliminary lake by lake data which is driven by their partner site locations. They are partnered with both municipal governments as well as private organizations like marinas or businesses that are on the waterfront.

Between 2020 and 2022 they removed over 150kg of plastic representing over 134,000 pieces from waterways and beaches. Their year 1 summary report outlines many of the specifics of their work.

Ontario Water QualityOntario streams are monitored through the sampling sites of the Provincial Water Quality Monitoring Network. This data, collected by conservation authorities in Ontario includes more than 400 individual sampling sites in rivers and streams. This is a rich data set – many sites are sampled every month for 59 individual measures including levels of lead, mercury, arsenic and nitrogen in the water. The data series for some sites goes all the way back to 1964.

The data set is available on Ontario’s excellent open data portal, data.ontario.ca. You can download it in the usual formats: CSV, TSV, JSON and XML.The data identifies the collection date and time, the specific analysis performed along with the sampling method and result.

There is a break in the data in March 2021, because the environment ministry changed to a new laboratory information system, and data headers are not consistent between the historical data and current data. For our demonstration we used the most recent data available.

Beyond that, the data set’s administrator warns that older data might contain transcription errors, and vague station location data. Locations for stations in the early years may have been provided by coordinates, road intersection descriptions and other descriptive spatial data.

Consumers of the data set are also warned to exercise caution interpreting field data like water temperature or dissolved oxygen since they are not performed in a laboratory and not subject to the usual quality assurance protocols.

Ontario’s open data portal is powered by CKAN, and all data sets can be consumed using the CKAN Datastore API. It’s a nice interface that is pretty easy to work with. It supports pagination to make consuming large data sets a little more comfortable.

The API returns a JSON object that contains the records requested along with metadata including a link to the data source, record limit, the number of records returned and links to the next data segment.

Doug built a Python script to demonstrate this data set. It’s called get-pwqmn-data and you can find it on Doug’s Github profile. Building on the work he did previously visualizing economic data, he decided to visualize lead content in streams and rivers.

The script downloads sampling station inventory data and drops it into a Petl table, then does the same for sampling observation data. The two data sets are merged to get friendly station names into our data set, then filtered for observations of lead content only. From there, a chart showing a history of lead content readings for each station is generated.

There is more than usual opportunity for reuse of this code since the API is standard on any CKAN instance, so interactions with the Ontario Open Data API are moved to their own module file which may be a useful starting point for many projects working with Ontario data or any other CKAN site.