Mean, Median, and Moose

This month on Mean, Median, and Moose, geeking out on tools.

Creating a dashboard of LCBO Top 20’s with Observable FrameworkFor my tool, I picked the Observable Framework. This is an interesting cross between a static website generator and Observable’s JavaScript notebooks. Framework has a good getting started guide that will get you the feel for how this works.

First you need some data to populate your data application. I chose an interesting project called LCBO stats. This project is an open source API which gets its data by scraping the LCBO website. You can then use the API to get rankings of LCBO products. From there you can look up their pricing history. Pretty cool!

To extract the data from LCBO stats, I created a simple data loader which is just a little script that outputs a data file – in this case formatted as JSON. We’ll use a “FileAttachment” API later to have Observable Framework automatically execute this script and insert the data into the website. What’s interesting and unique about this is that the Observable Framework supports data loaders in a variety of languages. All you need is a simple program to get the data and dump it to standard out. My data loader takes two parameters, field (to specify what to rank by) and sort (to specify ascending or descending order), so that I can use a single one for all of my charts. I didn’t do any aggregations because the API doesn’t support it and I didn’t want to programmatically page through all the results and do the aggregations myself, because that would be a rude thing to do with a free public API (ie: use a bunch of resources to dump all the data available).

I then displayed the data using a sort of “Markdown file on steroids” which the Observable Framework excels at. This includes a markdown header to specify the themeing and other options. A JavaScript block to retrieve the data and a block to display the data using Observable Plot. Finally you just insert the results from the JavaScript calls into some HTML.The ergonomics of this are amazing. No tagging HTML tags with IDs and telling the chart framework to dump the data there. The code and the presentation mix effortlessly. Traditionally that’s a bad idea, but the notebook-like nature of these definitions means that you can create complicated things and they’re still easy to understand.

And here’s the end result: https://johnhaldeman.observablehq.cloud/lcbo-top-20s/

Data Pipeline DemoMany people use Python for data transformation. Python libraries like PETL and pandas provide many of the tools needed for one-shot data analysis and transformation work. Data notebooks provide interactivity and make for a pretty compelling set of tools for data professionals.

We’ve talked a lot about those tools on this show, which makes sense because they’re the basic tools you need to work the datasets we deal with every month. ETL is only one part of the picture, though, so with that in mind let’s zoom out and look at some of the options out there for data pipelines.

Tech vendor IBM defines a data pipeline as a method in which raw data is ingested from data sources, transformed, then ported to a data store. The ETL tools and methods we’ve talked about are a key component of a data pipeline, but as data volume and complexity grows, you will increasingly feel a need for a tool to organize, orchestrate, and perform your ETL tasks.

When Doug’s development team needed a job runner for a recent project, they landed on Sidekiq. Sidekiq is a solid job processing project with a lot of relevant features for creating a data pipeline. It runs tasks asynchronously, pulling jobs from a queue stored in a Redis data store. Notification and complex error handling are all supported well. Here’s a screenshot of the Sidekiq web UI.

The project was a success and Sidekiq did everything they wanted it to. If you use Ruby, and your tasks are mostly independent of each other and don’t require modeling dependencies, Sidekiq is a good fit for organizations at many sizes.

A more robust feature set is found in the Mara project. It’s a loose set of Python libraries implementing many data warehousing features. Mara core modules provide access control, schema management, ETL tools, and pipelines. Along with a target data store, it’s potentially a complete solution for an organization’s data. Unfortunately, Mara doesn’t come with a lot of documentation. It’s pretty tough to build a working local copy of the Mara example system without reading Mara source code. Mara’s Github page has lots of activity and this code is clearly widely used, but adopting it requires a significant investment of technical capacity.

For Doug’s use case, and maybe yours, Spotify’s Luigi project comes pretty close to the sweet spot. It’s a lot more robust and widely-used than Mara, and has features that Sidekiq lacks. Crucially, Luigi models workflows as directed acyclic graphs (DAGs), which allows for structured workflows with dependency management.

Doug built a Luigi demo you can find in this GitHub repository. The demo uses Luigi to run a short workflow of dependent tasks and populate a local SQLite database from multiple sources.

The scenario for the demo is a company that needs to compensate its employees for expenses incurred in travel across North America. There is a CSV containing a data set of random names and synthetic expenses in different North American currencies in the repository.

The public data source for exchange rates in this demo is the Bank of Canada Valet API. It’s a really rich service with tons of useful data, including daily exchange rates.

The demo is contained in a Docker container that starts up the Luigi Central Scheduler when the container is started. It’s configured to expose the Luigi service’s web interface, which provides a web interface for monitoring current and recent jobs.

Luigi doesn’t itself schedule task execution, which may sound surprising since we’re talking about the Luigi Central Scheduler, but the service is concerned with scheduling the execution of tasks within workflows, not with firing off workflows. For that, the Luigi people recommend writing your own service or using an operating system scheduling facility like cron.

The demo is configured with three tasks;

FetchRates loads exchange rate data
ImportExpenses loads expense data
GenerateReport generates a report

GenerateReport depends on ImportExpenses, which in turn depends on FetchRates.

In the container, running the main Python script adds a GenerateReport task to the Luigi scheduler, which then fires off the other two tasks in turn to satisfy the dependencies of GenerateReport.

Luigi’s scheduler determines whether a task is complete or not by checking whether its output exists. If the output is already in place, Luigi won’t start the task. In the case of the demo, a report CSV is the output of GenerateReport.

There’s more detail in the demo README file. Check it out, we hope you find it useful.

Creating a Dashboard in Power BI:As a data analyst, I work with a variety of tools, but Power BI is my go-to.Why Power BI? It’s simple—it allows me to tell a story with data. Instead of staring at endless rows and columns, Power BI turns those numbers into interactive, engaging visuals. It’s like giving the data a personality, making it easier for users to explore, understand. Plus, it keeps things fun—because let’s face it, if you can make data interesting, you’ve won half the battle. Even if someone has never used Power BI before, they can quickly get the hang of it and dig into the data themselves.

For the dashboard I created for this podcast, I downloaded the Data Science Salaries dataset from Kaggle—a fantastic resource when you want to get your hands on real-world data for practice or portfolio projects. The dataset includes fields like:

Work Year: The year of the salary data.
Experience Level: Junior, mid, and senior and executive level professionals.
Job Title: From Data Analysts to Data Scientists to Data Engineers (and many more..)
Salary in USD: Standardized salary values for easy comparison.
Company Location and Employee Residence: Where the jobs are based and where employees live.
Remote Ratio: How much of the role can be done remotely.
Company Size: Whether the company is small, medium, or large.

Anyone who works with data knows that it’s never clean straight out of the box. This dataset was no exception—it had missing values, duplicate entries, and some inconsistencies in how fields like experience levels and company locations were recorded. That’s where Power QueryEditor comes in.

Power Query is one of my favorite features in Power BI because it’s like having an inbuilt toolkit for cleaning and transforming data. I used it to remove duplicates, handle missing values, and normalize some of the abbreviations. What’s nice is that all of this happens within Power BI, so you don’t need a separate tool to clean your data. Once it’s transformed, you can jump right into building your visualizations.

In my day-to-day work as a data analyst, Power Query is a lifesaver when dealing with large datasets that need wrangling before I can even start analyzing them. Plus, if I need to export the transformed data from Power BI, I can easily use DAX Studio to push it back into a CSV format.

Building the Dashboard:

After cleaning the data, I got to the fun part—building out the dashboard. One of the things I love about Power BI is how versatile it is in terms of design and functionality. Here’s a rundown of some key elements I included:

Slicers: I added slicers to allow users to filter by year and experience level. This makes the dashboard dynamic—whenever someone selects a different year or level of experience, the entire dashboard updates to show relevant data. It’s an interactive way to explore trends and comparisons.
KPI Cards: I used KPI cards to display high-level metrics like average salaries, highest and lowest salaries, and year-over-year comparisons. These cards give users a quick snapshot of the key data points they’re likely to care about.
Salary Forecast: One of the cooler features of Power BI is the built-in forecasting function. For the salary data, I created a line chart that not only shows past salaries but also forecasts future trends based on historical data. This is a great feature when you want to project potential future outcomes, and it’s surprisingly easy to set up.
Top 10 Jobs by Salary: I always like to give users something to rank, so I included a chart that highlights the top 10 data science job titles based on average salary. It’s interesting to see which roles are leading the pack and how salaries differ across positions.
Maps: (Why Maps? Because They’re Awesome)

I also included a map visualization in the dashboard. Now, I’ll admit—I’m a bit biased toward using maps in my dashboards because they look amazing and are incredibly useful for visualizing data geographically. In this case, I used a map to show salary distribution across different regions, using a color gradient to make it visually appealing and easy to understand at a glance.

I took it a step further by using bookmarks to create different map views. So, you can toggle between maps showing average salary, highest salary, and lowest salary—adding even more interactivity to the dashboard.

Feel free to explore the dashboard yourself here.

View Details

Today on Mean, Median, and Moose you’ll be treated to data we collected or generated all by ourselves!

Asking ChatGPT to Create Some Sample DataI have been playing around with ChatGPT and a few other online AI tools. I wondered how well it would replicate re-world data and preference at a population level. I wondered how closely it could replicate election results by randomly generating a series of poll responses.

First thing, I did was I had it view election coverage from the Windsor Star, CBC Windsor, CTV Windsor for the 2021 Election. I copied the links to ChatGPT asked it to summarize each story and made sure that it noted how parties performed in Windsor West.

I asked Chat GTP to review a number of news stories that I found by googling Federal Government News Windsor 2024, against asking ChatGPT to summarize the stories and not the important policy issues highlighted in them.

The top political issues in Windsor, Ontario, include:

Healthcare: Improving access and funding for healthcare services, especially mental health care and home care services.
Housing: Increasing the supply of affordable housing, implementing rent controls, and addressing homelessness.
Economic Development: Boosting local industries, including the auto sector, and improving job opportunities.
Education: Enhancing funding for schools and addressing educational disparities.
Environmental Policies: Promoting renewable energy projects and sustainable development initiatives

Based on these economic priorities and past election results, I asked ChatGPT to create a fake dataset of 250 responses of a poll about the riding of Windsor West in Windsor Ontario Canada including respondents: age, gender, level of education and household income level. To give additional contest on the demographic figures I had ChatGPT summarize Windsor West Riding Profile for each of the respondent categories.

Use past elections, survey data and local news as a basis to create the responses to the following questions:

Question 1: Did you vote in the last federal election?

Question 2: Which party did you vote for?

Liberal
NDP
Conversative
Green Party
People Party of Canada

Question 3: How likely are you to vote in the next election?

Very Likely
Likely
Neutral
Unlikely
Very Unlikely

Question 4: What is your top issue in the next election?

Although it offered a Python output that stumped me and I was lazy, so I asked for a CSV file. ChatGPT out 250 rows of data like this.

Respondent ID,Age,Gender,Education Level,Household Income,Voted in Last Election,Party Voted For,Likely to Vote Next Election,Top Issue

1,45,Male,Bachelor’s Degree,$50,000 – $75,000,Yes,Liberal,Very Likely,Economy

2,34,Female,High School Diploma,$25,000 – $50,000,No,N/A,Likely,Healthcare

3,29,Male,Master’s Degree,$75,000 – $100,000,Yes,NDP,Neutral,Environment

4,54,Non-binary,Some College,<$25,000,Yes,Conservative,Very Likely,Education 5,62,Female,Associate Degree,>$100,000,No,N/A,Unlikely,Housing

You can view the data here.

| Created Sample of 253 | Conservative | Green Party | Liberal | N/A | NDP | People’s Party of Canada | | Count of Party Voted | 35 | 34 | 37 | 111 | 35 | 1 |

Based on ChatGPT review of history and estimate of the future we could expect 57% turnout in Windsor West in the next Election up from the 43% in 2021.

| Created Sample of 142 Voters | Conservative | Green Party | Liberal | NDP | People’s Party of Canada | | Vote Percentage | 24.6% | 23.9% | 26.1% | 24.6% | <0.1% |

Now this is an unweighted sample and digging through cross times, find significantly over samples higher levels of education and incomes for the Windsor West riding. The top issues also made me laugh for a bit.

| Row Labels | Conservative | Green Party | Liberal | N/A | NDP | People’s Party of Canada | Grand Total | | Economy | 1 | 9 | 1 | 1 | 12 | | Education | 21 | 4 | 17 | 11 | 7 | 60 | | Environment | 2 | 7 | 3 | 42 | 6 | 60 | | Healthcare | 5 | 19 | 3 | 16 | 19 | 62 | | Housing | 6 | 4 | 5 | 42 | 2 | 59 | | Grand Total | 35 | 34 | 37 | 111 | 35 | 1 | 253 |

Conservatives don’t care about the economy, Greens don’t care about the Environment, only people who didn’t vote want housing. Seems an accurate representation of my riding (sarcasm).

Collecting Personal Data (Katie)I’ve been an avid user of Daylio, a mood tracking app, for over 5 years now. It started at a time when I felt a lot of anxiety and unrest, so I wanted to be more mindful of my moods. I’ve tracked my range of emotions every day for years, with a reminder on my phone popping up every 3 hours to choose my mood and activities I’ve been doing. Gradually, I noticed my moods transition from more negative to positive, and I mostly select “Good” as my mood these days.

A little over a month ago, I was faced with a new reason to collect personal data like this – I had noticed my fatigue, a symptom of my multiple sclerosis, seemingly impacting me more and more. But how could I really be sure without any data to back this up? And if it was happening, were there any patterns I could recognize to help with it? So, I transitioned my Daylio mood tracking to fatigue tracking, changing the 5 mood levels to 5 fatigue levels instead: Exhausted, Fatigued, Neutral, Awake, and Energetic. I also added activities like “Meal”, “Coffee”, and “Snack” to see if there was a correlation between my fatigue and when I was eating or drinking coffee.

Daylio offers a wealth of statistics and charts once you’ve been tracking for a week or more. While I only have fatigue data for the month of May so far, I took a look at the stats Daylio has to offer to see if my assumption that I’m feeling fatigued often is true, and to see if there was any correlation with the activities I had listed, which you can see below.

In May, I made 244 entries in Daylio (approximately 8 entries per day, with an entry about every 2 hours from 7am to 9pm). My average fatigue rating was 3.1 (Neutral), with a total of 3 Energetic, 66 Awake, 139 Neutral, 34 Fatigued, and 2 Exhausted fatigue levels entered in the month.

This told me that while I wasn’t doing terribly with my fatigue, I also wasn’t doing as well as I wanted. Ideally, I’d be “Awake” or “Energetic” over 50% of the time, not 28% of the time. I entered “Neutral” so often that Daylio considered my fatigue quite stable though, scoring me an 86/100 in their stability chart.

So, now I knew I was probably more fatigued than I’d like to be, and I took a look at the activities I had logged to see how these might be impacting me. According to Daylio’s “Most Influential Activities” chart, I was most awake after having a snack, shopping, having coffee (no surprise there!), when I was at work (much more surprise there), and after having a meal. Going for a walk, watching a movie, going to bed, traveling, and seeing my family were activities aligned with poorer fatigue levels.

Now, here’s where I had to take some of this with a grain of salt. While I could see snacks, shopping, coffee, and meals helping me feel more awake, “Work” likely appeared on the list simply because it was my most logged activity at 98 entries, and I also almost always have a meal and coffee while at work. I could see this when clicking on “Work” as an activity in Daylio’s stat tracker and viewing “Related Activities” with “Meal” occurring 22 times on the same day as work and there being a 71% relation between the two, and similarly with “Coffee”, at 67%.

Similarly and intuitively, I know “Going to bed” made the negative list since of course I’m more tired right before bed, “Movies” appeared negatively since I almost only watch a movie right before bed. In the same way, “Family” made the list as I always go to see my family Monday night right after work, when I’m naturally more tired.

All in all, tracking my fatigue in this way helped me be more mindful of it, confirmed my average fatigue level, and helped me see that eating and a cup of coffee are ways I can boost my energy level, even if only temporarily, whereas exercising through a walk might not be as energy-boosting for me as it is for others. Tracking your own personal data can be simple with the wide variety of tracking apps out there now, and it’s a great way to get to know yourself better, increase your mindfulness, and tackle personal goals in a highly intentional and analytical way.

Developing a Walking TourA few years ago, Doug’s company Parallel 42 Systems built a government-funded walking tour app. P42 requested and was granted the permission to commit the code written for this project to the commons. It’s called Pytheas, and since the code is free for anyone to use, they’ve been using it! Last year P42 collaborated with local LGBTQ+ activists on a tour of sites of historic significance to the queer community, and this year their gift to our local community is a curated cross-border art tour focused on murals.

The code is mostly written, so implementing a new Pytheas tour is all about data collection.

There are some data points needed before a mural can be included in the tour;

Geographic coordinates
A photograph of the mural
The address of the nearest building

Ideally, the data for a mural includes the following information;

The name of the artist
A brief biography of the artist
A description of the work, its origin, and its meaning

Murals are inherently ephemeral, and generally not well-documented. Data collection involved automated and manual steps. To get started, the team looked for existing data sets of murals in the two cities.

In Windsor, the Free for All Walls Festival in 2023 added dozens of murals to local streets. This project has an excellent website and map which provide a good starting point, and crucially for this project, artist information for each of the murals.

In Detroit, the Visit Detroit Mural Guide and the City’s Mural Map also provided some useful hints, though the City of Detroit’s map does not surface many of the key pieces of information we needed.

Starting with this seed data, Doug and the P42 team next determined the desired route for each tour based on background knowledge of each city and the objective of the tour, which is to promote cross-border tourism in the region.

P42 used social media to ask residents of each city their favourite murals, documented them along with the murals from the seed data that appear on our target route.

The next step was the development of a complete list of candidate murals. This work was performed by a pair of site surveys. The first survey was conducted via Google Maps Street View. Street View was used to find murals, and to understand the immediate environment around the murals.

At this point, P42 had a list of about sixty candidate murals on either side of the border that fit the basic criteria. The spreadsheet of candidate mural locations was geocoded with Geoapify, which is a service we’ve talked about on this show before. That geocoded spreadsheet was uploaded to Google Maps for use by the photographers hired to walk the routes and get photos for the tour.

P42’s photographers used telemetry tools to capture a GPX document identifying the locations of the murals, and performed on-the-spot curation of the murals and the tour by being the first ones to walk it.

Using QGIS, P42 converted the GPX files to CSV, hand-modified them to reflect the structure and naming convention of the geoJSON files that power Pytheas, and finally converted them to geoJSON. Supplementary information from various sources was manually added to the final geoJSON document.

You can see the results at https://motownmurals.tours. If you’re not local to Windsor/Detroit, you’ll be too far away to take the tour, but you can browse the list of murals and check out the photos.

The MMM group chatBehind the scenes at Mean, Median, and Moose there’s a rollicking instant message group we use a little bit to coordinate the show, but mostly to post funny tweets, memes, and complaints about local politics. Given that we’ve been doing this for a few years, we wondered what kind of data the group chat itself could provide. To limit the scope a bit we analyzed all our messages from 2023 – all 29,272 of them. That’s an average of 80 messages a day. 20 messages per person per day….

Broken down by chat participants we see the most messages come from John, then Doug, Frazier and Katie in that order.

John is also the most prolific link sharer, but this time Frazier comes in at number 2:

What about the time of the message? We can see that May and June were the most popular months for posting:

December is the least popular – I think likely because this is when the Mean, Median, and Moosers might be busy with the holidays. Ironically some of our most popular episodes and posts are the Christmas specials.

Finally, we can’t do an analysis without a heatmap, so here’s where the Mean, Median, Moosers were most active according to day and hour. The weekdays below start on Sunday (numbered as 1).

View Details

This month on Mean, Median, and Moose, we look at Canadian data on Cannabis.

Statistics Canada InfographicsI was a little behind this month so I dug into a couple of different data sets. First I looked up what infographics were available from Statistics Canada. From fall 2023 there was a graphic that compared booze to pot sales in Canada from 2021-2022.

An archived graphic from 2021 showed how legalization scaled up cannabis

These are the only two infographics from Statistics CAnada that I could find, there other government of Canada infographics related to cannabis –like this one for legal risk from Health Canada; impaired driving, boating, flying;

Munchie SpecialsI also did some digging into what local restaurants had specials for 4/20. Digging through 40 local restaurant facebook pages I found 3 who had some sort of 4/20 special. Two are local places, while two are chains who had these specials beyond our community.

Cannabis Retail
In 2018, StatsCan launched the Cannabis Stats Hub, which has been discontinued and replaced with a page listing twelve data sets Statistics Canada publishes around Cannabis. Many of these, like the table of Cannabis consumer prices, have been discontinued since legalization.

Since legalization of Cannabis in 2018, Statistics Canada has reported an increasing number of data sets related to the production, distribution and sale of cannabis products in Canada.

Some of the currently-available data sets cover topics like prevalence of cannabis use, the value of cannabis produced in Canada, and household spending on cannabis. Besides these topics, there is plenty of detail on the retail sale of cannabis in Canada.

Retail sales are reported annually, broken down by province and type of cannabis. So far, there are two reporting years spanning 2021/22 and 2022/23. The data is provided in a number of different formats for download, including a format labeled “for database loading” which is terrific if you want to use SQL to query your data or drop it into Excel and manipulate it with pivot tables.

We did both. Here’s a screenshot of the Excel output showing sales by geography in each of the two periods. The data includes a summary value for all types of cannabis, so you have to be careful to filter out that value (or all other values) to get a valid result.

You might notice that retail cannabis sales were zero in both periods for Northwest Territories and Nunavut. Great news for Nunavut tokers – there is now a single licensed retailer in Iqaluit. The Northwest Territories now has a whopping six stores licensed for cannabis retail.

Here’s the code of a SQL query to generate a similar result, assuming you’ve loaded the data using the column names in the CSV file into a table called “CannabisSales”:

SELECT geo, Type_of_cannabis, sum(CASE WHEN ref_date = '2021/2022' THEN value2 ELSE 0 END) AS '2021/2022', sum(CASE WHEN ref_date = '2022/2023' THEN value2 ELSE 0 END) AS '2022/2023'FROM CannabisSalesWHERE Type_of_cannabis <> 'Total cannabis products'GROUP BY GEO,Type_of_cannabisORDER BY geo,Type_of_cannabis Dried cannabis flower is by far the most popular type of cannabis purchased by consumers. It burns up a little over two-thirds of the dollars spent by consumers. Dried flower is followed by inhaled cannabis extracts, which are more commonly known as vaping products. They’re another 22% of sales or so. Edibles are notable among the “long tail” types of cannabis, eating up just under 5% of the market.

Statistics Canada also produces a data set breaking down the Net income of cannabis authorities, along with associated government revenues. In 2021/2022, governments realized about $1.2 billion in excise taxes, provincial sales taxes, GST and other revenues. In the following period that number was just under $1.5 billion, while retailers’ net income landed just under $2 billion – a healthy growth in sales and government revenue! This data is offered broken down by province and territory as well, if you want to drill down further geographically.

Cannabis sales are regulated provincially, so information about retail stores is provided at the provincial level. In Ontario, the AGCO’s data inventory contains a few data sets on the cannabis retail license lottery program and data on which municipalities opted in and which opted out of cannabis retail sales.

In Ontario, the AGO maintains a web page that provides a list of license applications by application status, which is also downloadable in CSV format. If you’re curious about the subset of retailer applicants currently in their public notice period, which offers local residents an opportunity to respond to the application, you can find that information on the AGCO site as well.

Using a handy geocoding tool, we converted this data into a list of geographic points and imported it into QGIS. It’s a bit sparse for a heat map, so we used QGIS point clustering functionality to show the number of cannabis retail outlets in a given community. You might notice an irregular line of single retail stations in Northern Ontario. That mostly follows the Trans-Canada Highway. There are only two retail stores in Ontario north of the highway.

Wackiest Tobacky Sold on Government Cannabis WebsitesWhen cannabis was first legalized, it seemed most available products had a moderate amount of THC, with the highest percentage in the low 20s. As your average Canadian began their foray into legal cannabis, it could be safe to assume they didn’t immediately buy cannabis with the highest THC percentage they could find. Now, it seems the Canadian customer has been demanding wackier tobacky than in that first year or two, with higher and higher THC levels reaching into the upper 30 percent. To see just how wacky the tobacky gets in each province, we took a look at the government cannabis website in each province (as available!). Immediately on diving into this manual data scraping adventure, we learned there’s no government cannabis website in Alberta, Manitoba, Nunavut, Saskatchewan, or the Yukon, so those are excluded from this analysis. Additionally, while BC, PEI, and Quebec have government cannabis websites, there’s no option to sort by THC level (perhaps an attempt to keep their residents in their right minds!?), so those too have been excluded from this analysis on the account of it being a bit too time-consuming to scroll through pages of cannabis products in search of the highest THC level. This left New Brunswick, Newfoundland, Nova Scotia, Northwest Territories, and Ontario to duke it out for the wackiest tobacky sold on their websites. To keep this analysis doable and comparable, only cannabis sold as whole dried flower was considered. See the ranking in the very fancy table below!

| Province | Name, Origin, Brand, Type | THC | CBD | Price Per Gram | | ON | Mega Breath, ONTrue Fire & Co Ltd.Hybrid | 33-39% | 0-1% | $10.06/g | | NS | Banana Mints, NSEastcannIndica | 30-38% | 0-1% | $9.99/g | | AnimalZ, NSEastcannIndica | 30-38% | 0-1% | $10.71/g | | NB | Organic Kiwi Banana Cabana, NBEco Growers ChoiceIndica | 33-37% | <1% | $13.14/g | | NT | Gelatti Kush, QCWest Island CultureSativa | 30-37% | 0-1% | $11.21/g | | NL | Death Star, SKBold GrowthIndica | 30-36% | 0-1% | $9.68/g |

The aptly named Mega Breath, sold on Ontario’s OCS and produced in Ontario, takes the cake for having the highest % THC whole dried flower product available, ringing in at a whopping 33-39% THC. This True Fire product is described as “a powerful hybrid strain with a unique and potent blend of indica and sativa. The aroma of Mega Breath is pungent and earthy with hints of pine and slight sweetness that lingers in the air. Its taste is just as impressive with a smooth and complex flavour profile that combines notes of spice, diesel and citrus.” It comes with a price tag of $10.06/g, making it a pretty sweet deal. The OCS seemed to have the most information available of the government websites, with information like grow method, grow medium, and grow lighting listed, and craft cannabis stamps on products – there was far less information available on the remaining websites.

Banana Mints and AnimalZ are next in line, each sold on Nova Scotia’s NSLC website and produced in Nova Scotia. Each are indica Eastcann products with 30-38% THC. Banana Mints has a price tag of $9.99/g, and Animal Z is priced at $10.71/g. Though very similar in type, THC, and price, Banana Mints’ flavours are listed as “kiwi, banana, and earthy”, while AnimalZ has “nutty, berry, gas” flavours (someone please explain why gas is a desirable flavour!). The NSLC website was lacking descriptions for its products, but it did mark its weed with “Proudly Nova Scotian” if it was grown in the province, and you can check local store availability too!

Cannabis New Brunswick is third up, selling Organic Kiwi Banana Cabana, an indica with 33-37% THC produced in New Brunswick by Eco Growers Choice. It’s sold for $13.14/g, with the description reading, “The experience begins with intense aromas of kiwi complemented with hints of sweet citrus. The taste and flavour of the banana is as powerful as the immediate onset – which comes through with notes of melon, honey, and pineapple. Just like a ‘Copa Cabana’, who could ask for anything more… this strain packs a punch with a strong cerebral effect, followed by a hard-hitting trance of relaxation leaving you feeling heavy headed, calm and focused to take on the night with your friends on the beach.” Sounds nice (though expensive!)! New Brunswick also had by far the coolest website, with senses like “fruit” listed for products, local store availability, and even the ability to leave reviews for products! Good job, NB.

Fourth, we have Northwest Territories’ website, Releaf, which is actually just a privately owned chain with the only delivery available in the territory, so naturally, the government has designated it as their “official” cannabis website. Sadly, it seems it is close to shutting down as it can’t get enough of the right products for delivery, or so the website reads. Gelatti Kush, a sativa produced in Quebec by West Island Culture, has the highest THC on their website with 30-37% at a cost of $11.21/g. No description or flavours available for this one.

Last is CannabisNL, with the indica Death Star product from Saskatchewan’s Bold Growth available at a price of $9.68/g and coming with 30-36% THC. It has “diesel, earthy, and pine” flavours. No descriptions available on the CannabisNL website either.

There’s no real consistency in the type or brand of cannabis with highest THC offered on each website, though they do all have less than 1% CBD! Similar price points across the board too, minus Organic Kiwi Banana Cabana’s slightly higher price. It’s interesting to see the different approaches taken by each province to the cannabis rollout since legalization, and time will tell if we keep seeing higher and higher THC products released!

Cannabis related search results on the Libraries and Archives Canada catalogLibraries and Archives Canada maintains the catalog for Canada’s National Archives and National Libraries. Anybody can search the catalog online. We decided to do some searches for Cannabis related topics and graph them by publishing data. First up, the obvious “Cannabis” search:

Interestingly the publications peak in the 1980’s rather than recent history, which you may expect given recent legalization. You may also think that 1780 is a little early for Canadian documents about Cannabis, and you’d be right. The lone record in the 1786 was a land petition for Lower Canada for a Mr. Benjamin Weed. Clearly archives Canada is smart enough to know “weed” is a synonym for “cannabis”, but not smart enough to know the proper name “Weed” is not – a much harder problem.

Let’s take a look at some Cannabis related cultural figures and how they’re represented in the archives:

Willie Nelson by far the most popular of the four. If you’re wondering what kind of Snoop Dogg related material our national archive organization stocks, the earliest published item for the artist is a CD insert for issue 179 of a weekly UK music industry publication called “The Tip Sheet”. “Snoop’s upside ya head” appears alongside a Celine Dion’s “All by myself” and a cover of Randy Bachman’s “You ain’t seen nothing yet” by an artist known as Loverman on the insert. Strangely, issue 179 appears to be the only issue of “The Tip Sheet” stocked by Library and Archives Canada.

View Details

This edition of Mean, Median and Moose, we look at discontinued data sets!

Canada Year BookLess of a data set, more of a product, is Statistic Canada’s annual Canada Year Book, in production from 2006 to 2012. Funny enough, this seems to have been a previously discontinued but revived product that was discontinued again, as it’s also available from 1867 to 1990! Billed as “the premier reference on the social and economic life of Canada and its citizens”, each year book is presented in almanac style with more than 500 pages of tables, charts, and analytical articles on every major area of Statistics Canada’s expertise. This was fondly used as a reference in many a high school research project.

The idea is, you can click on any of the chapters, ranging from “Business performance and ownership” to “Families, households, and housing” to “Prices and price indexes” and get a quick snapshot of Canadian life as it related to the topic that year. This made for a great quick-reference resource and one that could make for a fantastic starting point for exploring data and history, particularly for students.

It is interesting to see how the year books changed over the years, even from just 2006 to 2012. For example, 2006’s “Education” chapter provided much more commentary than 2012’s Education chapter, commenting generally on schools’ serving special needs students, the denominational system and its abandonment and uptake by province, immigrants lifting the education level in Canada, and the financing of education. In contrast, 2012’s Education chapter simply lists statistics on student enrolment, graduation, tuition costs, and adult training, with little narrative behind these statistics. Particularly eye-catching given the current tuition situation in the 2006 chapter is: “Undergraduate tuition fees have almost tripled since the early 1990s. In 2004/2005, university tuition fees averaged $4,172, compared with $1,464 in 1990/1991.” Seems university tuition has been on a steady rise even longer than our memories might allow, reaching an average of $5,366 in 2012 according to that year’s Year Book.

Another interesting event to see reflected in the series? The 2008 Recession, featured prominently in the 2009 “Business performance and ownership” chapter, the “Economic accounts” chapter, the “Income, pensions, spending and wealth” chapter, and many others. Again, this earlier Year Book takes more of a commentary approach, with the “Economic accounts” chapter introduction reading, “Until 2008, Canada had gone a record 16 years since its last economic downturn and had been riding a seven-year boom in commodity prices. But the economy in 2008 was unlike any in recent memory. For many younger workers and investors, 2008 was their first experience with a recession.” While younger Canadians were experiencing their first economic downtown in 2009, they were also disproportionately “browsing, blogging, chatting, and downloading” compared to other Canadians as the “Information and communications technology” chapter details (and at the high speed of 5-9 mbps!). If you can believe it, StatsCan mentions 500 internet service providers operating in Canada at the time!

These are such digestible and interesting accounts of Canadian history, and it’s a shame this time capsule series has been discontinued!

Canadian Coin IssuanceThe Royal Canadian Mint has a website that contains all the numbers for the mintages for various Canadian coins. You can go, learn about the histories of each coin type and then see how many coins were issued every year. Unfortunately none of this data is in an easy to consume format, but never fear, Mean, Median, and Moose are here with their document.querySelectAll() super powers. You can find an easy to consume CSV file and some graphs with the numbers on the Observable notebook here.

So, what does this have to do with discontinued data sets? Well, this is a data set about something that was discontinued – the penny – but the data set is alive and well. See what we did there? The last penny was minted in 2012, but before that it constituted the bulk of Canadian minting in terms of number of coins:

The youngsters or new immigrants reading this might also be interested to know that the toonie is a modern invention, making its first appearance in 1996:

You might also be interested to know that the loonie was not substantially minted until 1987. Doug still yearns for the old school $1 bill but will have to settle for crossing the border to see one in the modern age.

Speaking of that initial mintage of the toonie in 1996, you can see that it’s a big one. So large that, in terms of face value, that year dwarfs all others:

$2 being worth 200 pennies makes that bar the biggest by far. If you look at the coin and value numbers in general though you can see there’s been a big reduction since about 2013 even though the economy is larger. As you’ve probably guessed, there’s a lot less coins being issued since we do fewer and fewer transactions with hard currency.

Reporting and Trends on Data Gaps There are literally dozens of discontinued Statistics Canada datasets that I could talk about: ending of annualized tracking of marriage and divorce rates; to shifts in Census methodologies; to more eclectic data sets like Salaries and salary scales of full-time teaching staff at Canadian universities ending. To reliability issues emerging in critical economic surveys: here in 2016 and here in 2023 as fewer Canadians complete the surveys.

Statistics Canada actually has a page where they answer some common questions on “Does Statistics Canada Collect this information”. Some of these data items are certainly “nice to have” – dog and cat pet data or the proportion of the population that is vegetarian or vegan. Others seem more critical – stats on abortion rates, homelessness, classroom sizes in schools.

Reporting was done by the Globe and Mail in 2019. As part of a broader series on comparing Canada’s data landscape to other countries, they created an interactive tool for readers to ask their data questions, they identified 30 gaps in 2019 – ranging from how many people live in Nursing homes in Canada (Statistics Canada doesn’t know and there is no centralized count) to Eviction rates (they looked at an innovative student from Princeton as a potential pathway forward.

Statistics Canada 2022-23 Department Results report breakdowns the activities and costs the Statistics Canada undertook in a particular year. To a degree this kind of replaces some of the year books that Katie was talking about as the documents breakdown by different topic areas a summary of reports and studies that were accomplished as well as a few meta-narratives. With over 30+ pages dedicated covering the core services this annual document is not lean on what they covered.

More interestingly they breakdown their spending and the led me to noticing acknowledgements like this recent study by Statistics Canada had this under its title.

The study on Food Insecurity in Canada was released in November of 2023 uses data from the 2021 Canadian Income Survey to gain a better understanding of food insecurity, with a focus on families both below and above the poverty line and across income quintiles. The study also uses data from the 2019 Survey of Financial Security to examine the net worth of families who are more likely to be food insecure. Now sponsorship and cost recovery isn’t completely new for Statistics Canada.

That being said, the question you have to ask is when over $500 million per year in revenue why are there still data gaps?

Census of New France, 1665-1666Although there are still censuses in Canada, we count this one as a discontinued data set because the French colony of Canada as a component of New France ceased to exist in 1763. The Borealis data repository is an academic data repository in Canada. The repository hosts a collection of pre-Confederation census data that is available to the public. Though we didn’t make any maps this time around, there is historical GIS data available in this repository, which creates the potential for some really interesting data projects!

The first census in North America happened under the administration of Jean Talon, who was the Intendant of New France at the time. This was a newly-created position responsible for the entire civil administration of the colony, including statistical data. By instituting the first census and in some cases personally conducting the census door-to-door, Talon earns the title of the first official statistician in Canada.

We made an Observable notebook visualizing some key findings from the census, particularly the demographic information that led Talon to institute a program importing young French women to pair with unmarried colonists. This policy is deeply connected with another element that will strike a modern viewer of this census data: there were certainly many thousands of residents of the territory called New France not counted in this census because they were indigenous people, and the “shortage” of women in New France was a consequence of French policy discouraging marriage between French settlers and the indigenous population.

We also included a heat map and data viewer for the fascinating data on professions and trades in New France. The list of occupations is interesting in itself – shoemakers are distinguished from wooden shoemakers, an important enough difference in 1665 for there to be two categories. The biggest categories are “Carpenter”, with 35 people following this trade in the colony and “Servants,” with dozens of servants in every region. Another notable category is “Gentlemen of Leisure,” of which 15 resided in Quebec at this time.

View Details

This month on Mean, Median, and Moose we look at data related to fast food in Canada. Aside from the regular discussion about data, make sure you listen to the interview at the end with Saskatchewan open data extraordinaire Andy Dyck.

Statistics Canada on Fast Food I was a little late planning this month and so I went back to my safety blanket of Statistics Canada. I had some hope about this topic as in June 2023 they produced a report called “Is Canada Becoming A Fast Food Nation. Annual sales at restaurants reached $7.7 billion per month as of April. Statistics Canada does not explicitly state “Fast Food” rather compare full service restaurants, limited service restaurants, drinking places and specialty food services. Generally “fast food” falls under the limited service restaurant.

What we see in the data is that Canadians are divided over their restaurant preference.

The impact of the pandemic is clear on full service restaurants’ recipes. While fast food stayed pretty stable, the decline in sit down eating is clear. By April of 2022, spending had “returned to normal” and saw the two restaurants matching each other in sales. Another interesting item is that in non COVID years, there seems to be some seasonality with higher spending in summer months than winter months.

Another way to sort of look at “fast food” data is through average consumer spending. The Annual Household Spending Survey (not annual anymore) tracks how average households spend on a variety of necessities, products and services. This includes two classifications of Restaurants: Meals as well as Snacks and Beverages. It is likely there are some fast food establishments captured in the Restaurant Meals category while I suspect the snack and beverage category is fast food by definition.

When we break out the provinces, we do see some interesting variations.

NFLD, Sask and BC all saw increased average spending on meals at Restaurants (likely from) Restaurants despite COVID. It is curious that only Ontario and Manitoba have been on a consistent down trend bucking the major of countries that saw their peak in 2019 before retreating in 2021.

Finally there is a very obscure dataset on E-commerce sales as a percentage of total sales (and value) available on Statistics Canada Website where you can clearly see the disruption of COVID-19 on the Food/Restaurant space.

Tim Hortons LocationsFor this month’s data set we did some web scraping of the Tim Hortons locations website to get a data set of all the Tim Hortons locations. We found 3,943 locations. Here’s the top 40 cities ranked by the number of Tim Hortons locations each has:

If you were expecting a simple population graph, you were just about right. It’s fun to look at the anomalies though. Mississauga has as many Tim Hortons as Edmonton even though it is 70% the size of Edmonton in terms of population and 86% denser. Scarborough, a Toronto district is listed as a separate entity, skewing its numbers lower than they should be even though it is still ranked as first. Dartmouth, NS has about as many Tim Hortons in Guelph and Cambridge Ontario even though the city is about half the size. Sudbury has as many Tim Hortons as Regina for a city three quarters as large. BC, Canada’s third largest province has just three cities breaking the top 40.

We then decided to convert the addresses to latitudes and longitudes in order to generate a map showing a heatmap of Tim Horton’s locations. Here’s the result:

Toronto is indeed the center of the Tim Horton’s universe. Here’s a closeup of Southern Ontario:

And just for fun, Alberta:

This got us thinking. What are the two closest Tim Hortons to each other? According to the Tim Hortons Website address list, that would be 1515 Main St East, Milton ON L9T 0W2 and 3025 James Snow Pkwy N, Milton, ON L9T 7S3 which according to Google’s geocoding APIs are 1.2 meters away from each other, but different addresses. Turns out the Tim Hortons is part of an Esso station on the corner that just happens to occupy two addresses. If you remove like that by finding the closest two Tim Hortons more than 100 meters away from each other, you find that the nearest Tim Hortons are across the street from each other on King Street West in downtown Toronto. Interestingly Google Maps doesn’t seem to know about the one at 150 King Street, only the one at 145 King Street – perhaps it’s a part of an corporate office:

The Tim Horton’s website says it’s there though if you look hard enough!

Vegetarian Options at the Top 5 Largest Fast Food ChainsAs a vegetarian, it can be harder than you’d think to find a tasty option at one of Canada’s top fast food chains. While some chains have strived to provide more veg-friendly options in recent years, others have decided to steer clear of the vegetarian market entirely. According to ScrapeHero, the top 5 fast food chains by number of locations in Canada are Tim Hortons, Subway, Starbucks, McDonald’s, and A&W. To see which of these chains is the most veg-friendly, we took a look at their menu to see how many of their main lunch or dinner options (at least 380 calories+) were vegetarian-friendly and which chain gives you the best bang for your vegetarian buck. Of course, menus can differ across Canada, so we looked at the mains (with no customization applied) on each chain’s menu at their location closest to the center of Toronto (which is the center of Canada of course).

McDonald’s rings in with the highest number of main options on their menu at 49 options, but incredibly, has not one vegetarian option on their extensive menu. Subway and Tim Hortons have a very similar number of mains on their menu at 34 and 28 respectively, and each has 5 vegetarian options on their menu. While A&W has 26 mains, it has only 1 vegetarian option. Starbucks, as more of a coffee shop than food service chain, has a very small menu with only 6 mains listed, but of these 6 mains, 3 are vegetarian! This means proportionally, Starbucks by far wins out as most veg-friendly with 50% veg options, followed by Tim Hortons at 18%, Subway at 15%, A&W at 4%, and McDonald’s at a big fat 0%.

| Chain | # of Mains | # of Veg Options | % of Veg Mains | | McDonald’s | 49 | 0 | 0% | | Subway | 34 | 5 | 15% | | Tim Hortons | 28 | 5 | 18% | | A&W | 26 | 1 | 4% | | Starbucks | 6 | 3 | 50% |

Now, which of these chains might offer the best “calories for dollar” value item if you’re on the hunt for a vegetarian lunch or dinner? Tim Hortons’ grilled cheese melt comes out on top at 500 calories for $5.79, which gets you 86 calories per dollar paid. It takes second and third places too with the cilantro lime veggie loaded wrap at 530 calories for $6.79, getting you 78 calories per dollar, followed by the habanero veggie loaded wrap at 500 calories for $6.79, getting you 74 calories per dollar. Coming in last place is Subway’s 6” veggie patty sub at 390 calories for $7.99, getting you 49 calories per dollar paid (who wants a veggie patty anyway?).

| Chain | Main | Calories | Price | Calories/Dollar | | Subway | Green Goddess Veggie Wrap | 750 | $10.49 | 71 | | Green Goddess Veggie Bowl | 670 | $10.49 | 64 | | 6” Green Goddess Sub | 490 | $7.79 | 63 | | 6” Mozzarella Bella Sub | 530 | $8.49 | 62 | | 6” Veggie Patty Sub | 390 | $7.99 | 49 | | Tim Hortons | Grilled Cheese Melt | 500 | $5.79 | 86 | | Cilantro Lime Veggie Loaded Wrap | 530 | $6.79 | 78 | | Habanero Veggie Loaded Wrap | 500 | $6.79 | 74 | | Cilantro Lime Veggie Loaded Bowl | 560 | $7.99 | 70 | | Habanero Veggie Loaded Bowl | 530 | $7.99 | 66 | | A&W | Beyond Meat Burger | 500 | $8.29 | 60 | | Starbucks | Crispy Grilled Cheese | 450 | $6.45 | 70 | | Apples, PB, & Trail Mix Snack Box | 390 | $6.25 | 62 | | Tomato and Mozzarella Sandwich | 380 | $7.45 | 51 |

Seems like Tim Hortons takes the cake for a good amount of vegetarian choices while also offering the highest value. Subway and Starbucks are also great options for veggies, and A&W will work in a pinch. Vegetarians should avoid McDonald’s at all costs.

Pizza MapsIn our neck of the woods, pizza is always a hot topic. Windsor, Ontario prides itself on its local pizza style just as neighbouring Detroit does. The excellent mapmaking community at DETROITography made a city map a few years back identifying the “territory” of chain and local pizza places. Their method was intriguing and easy to apply – they identified pizza place locations using health department restaurant inspection records. It’s an approach that works anywhere the local health unit publishes this sort of data, so Doug used the same approach in his local community to map out our own pizza places.

The Windsor-Essex County Health Unit publishes food safety inspection records on its website. Unfortunately, this data is not available in downloadable form, and the structure of the website makes automated screen scraping impossible. Fortunately, for a small data set manual scraping is a viable technique. Searching for “Pizza” in the facility name produces five pages of results. Copying-and-pasting each data table page into Excel, then manually searching for known pizza-selling establishments without “Pizza” in the name returns a reasonably complete data set of this sort of restaurant in the region.

The resulting spreadsheet contains restaurant locations defined by a street address, which we’ll need to convert into map coordinates. Doug used a tool called Geoapify, which provides good geocoding results by combining data from multiple sources. Results include a confidence level and source identification.

Armed with this spreadsheet, Doug used QGIS to map the data, using Statistics Canada geographic boundaries to supply a base map and polygons for analysis. Joining these two layers by processing them using the QGIS built-in “Join attributes by nearest” tool. This tool connects a point layer, like our list of pizza places, with a polygon layer and adds attributes to the merged layer to include data about the nearest point to each polygon, including distance from the identified point.

The Windsor Pizza Map is pretty colorful and shows the variety of pizza experiences available to residents of Windsor. The distribution of pizza places follows transportation arteries and population density. A majority of pizza territory in the city is marked “other” here, representing the 41 pizza places in the city that have one or two locations. Roughly thirty percent of the city’s pizza offerings are locally-owned and operated “mom and pop” shops, a number that increases significantly when you recognize there are also many highly successful local chains like Antonino’s, Armando’s, Naples, and so on.

Windsor likes to think of itself as the pizza capital of Canada, and this map helps make a decent case. If you’re curious what your city’s pizza (or shawarma, or coffee …) map might look like, you might be interested in following along with Doug’s tutorial on YouTube.

View Details

This month on Mean, Median, Moose, we continue the tradition of presenting twelve data sets during the holiday season. As usual we’ll do one dataset for each province and territory, combining Nunavut and Northwest Territories into one to make it an even twelve.

Nunavut and Northwest TerritoriesFor Nunavut and Northwest Territories, we decided to see if we could get a flavor of what MPs in the house of commons talk about when referring to the territories. To do this we extracted all Hansard entries for the territory MPs as well as all the entries that mentioned the territories from 2002 to 2023. Doing basic word counts yielded bland results as most of spoken language (especially in the house of commons) is “filler” words – glue that sticks sentences together such as “the”, “to”, “and”, “of”, etc. Here’s an example of the top words for Nunavut for example:

| Word | Count | | the | 29,471 | | to | 17,581 | | and | 14,914 | | of | 14,885 | | in | 9,594 | | that | 7,727 | | a | 7,483 | | is | 7,401 | | for | 6,052 | | are | 4,725 |

Not very exciting. As such, we used a little code and a parts-of-speech tagger to extract all nouns from the data set instead. That tagger is what’s known as a “Brill tagger”. Without further adieu, here are some graphs. Nunavut first:

Some expected entries here. Like “Lori” and “Idlout” which is the name of the current sitting MP for Nunavut. But there are some other interesting items like “Uqaqtittiji” which is the Inuktitut word for “Speaker” – which the MP uses to address the speaker of the house – as it is done in the Nunavut legislature.

Here’s the one for the Northwest Territories:

Interesting differences here are the presence of “Oil” (207th on Nunavut’s list) and the NWT neighbor “Yukon” (202nd for Nunavut). Interestingly Quebec makes both territories’ top 50, but no other province does. The next province mentioned for NWT is Alberta – 85th in ranking. Nunavut is 56th on the NWT list while “Northwest” is 203rd on Nunavut’s list.

Newfoundland and Labrador For Newfoundand and Labrador, it is one of the provinces I have never been to and honestly probably one that I know the least about, so I made a post in r/newfoundland which seems to be one of the largest Newfoundland and Labrador communities on Reddit. I asked if there was some interesting data, or information that redditors would like explored. A number of comments beyond the standard economic and social datasets that we usually talk about came up, related to the genetic diversity (or not) of the region and stories like the Texas Vampires as one redditor pointed out. Exploring the high proportion of boil water advisories and how smaller communities in NFLD struggle to afford the equipment on their small tax bases. Being Newfoundland, fish stocks and Northern Cod fisheries; to the popularity of Talk Radio in NFLD (30% market share in 2007).

My chosen data set was the CLEAR Wild And Country Food In NL Database. This data comes from the Civic Laboratory for Environmental Action and Research. This open database leverages research and historical reports to try and track what wild game and foraged foods that people have eaten in NFLD for 8,000 years! The database is cited with most entries have an academic or research study backing the location and type of food that was gathered and consumed.

New BrunswickI also reached out to the great r/newbrunswickcanada reddit community for their thoughts on interesting data, lots of great ideas (post is now deleted as I broke the advertising rule by linking to the podcast) – Oops!

Looking at Bilingualism as it is the only official bilingual province with the unique subdialect of Chiac French, a number of ideas around a recent neurological syndrome that had no identified cause, baby eels (elvers) production as an emerging fishing industry was suggested as well to exploring the regions indigenous Treaty of Peace and Friendship that covers the province; Bay of Fundy tides and many more ideas.

The question I landed on was how many people work for the Irving Group of Companies? The Irving Companies are family businesses, and are not publicly traded yet they cover a wide range of sectors and industries that has resulted in what some would say an unhealthy level of market control in critical sectors of a provincial economy. These companies have grown widely and are vertically integrated within sectors meaning that various companies operate as supply chain partners to one another. This means their pulp and paper mill used the Irving trucking company to move logs,

The result is the Irvings (Arthur and James) are 10th and 11th richest people in Canada and combine the family is 4th richest. These listings have some significant variation depending on the source as differing measures of wealth result in fluctuation list to list. A 2015 news series from the Maine Monitor looked at the Irvings influence and how they are one of the largest landowners in the state.

As for determining how many people work for the Irving Companies it is hard to get an exact number. As the companies are not publicly traded and therefore not required to disclose employee information.

For context in total the Province of New Brunswick at the time of the 2021 Census had a Labour Force of approximately 389,000 people. Depending on the reporting the Irvings employ anywhere from 15,000 or 20,000 to 1 in 12 New Brunswickers (32,000 people). These numbers also vary widely as trying to capture full vs part time staff in service sectors like gas stations; seasonal staff in forestry and shipping businesses; and contractors in other sectors makes it a challenge.

For context the Province of New Brunswick in 2020 Government Profile reported 39,146 permanent employees in the New Brunswick Public Service. Many of us have heard of one company towns but you could argue that New Brunswick is a one company province (not actually) but the closest I could find.

Thanks to the good people of the New Brunswick and Newfoundland & Labrador reddit communities for the ideas and I hope to come back to some of the other dataset in future episodes when I have more time to dig in.

Ontario As I live in Ontario, I figured I could find something interesting to talk about myself. For this, I am teasing a early xmas present I gave myself as I purchased a custom data set on income inequality for the Province for a 2024 project.

The map above illustrates by Census Dissemination area the change between 90/10 ratios between the 2016 and 2021 Censuses by interval count which means the same number of DA fall into each range. By comparing this gap we can see if the top and bottom of the income scale are moving together or further apart. As a result a negative number is actually a good thing,as the gap closed over time.

Withover 22,000 Census Dissemination Areas in Ontario the distribution of the ration change is highly concentrated with only a handful outliers which is to be expected. This dataset hasn’t been fully cleaned yet as there are several thousand non-comparable DAs where data is suppressed in either the 2016 or 2021 Census.

A far tighter mapping gives a view of this with the vast majority of Ontario seeing less than +/-1 change in ratio between 2016 and 2021.

This is somewhat surprising as COVID measures were still in place at the time of the 2021 Census, you would think those supports, which focused on the lower end of the income spectrum would narrow the income ratios more significantly.Looking at urban centres we do see more variation.

Toronto

Kitchener Waterloo Cambridge

London

British ColumbiaOne might think BC would have a great open data portal, but one would be wrong. The provincial data portal is sadly lackluster with few data sets available, and even fewer that are any fun. So, we decided to take a look at American data that intersects with BC – border crossing entry data from the US Department of Transportation’s Bureau of Transportation Statistics. BC currently has three border crossings with Alaska, which are Pleasant Camp BC – Dalton Cache AK, Fraser BC – Skagway AK, and Stewart BC – Hyder AK. While it appears Stewart BC – Hyder AK is currently open (fun fact side note: Hyder brands itself as “the friendliest ghost town in Alaska”!), no data is collected for Hyder since you aren’t actually required to report to anyone going into Hyder (just going into Canada, remember to report to the CBSA office or give them a call after hours)! So, that leaves the Pleasant Camp BC – Dalton Cache AK and Fraser BC – Skagway AK border crossings for data to examine.

The Bureau has an excellent interactive data table and dashboard to pull annual, monthly, and percentage change data from US border crossings and display data in charts or on a map. Taking a look at Dalton Cache AK and Skagway AK, Skagway is certainly much more popular, seeing 442,225 entries in 2023, while Dalton Cache only saw 50,173. While Skagway has gotten significantly more popular since the Bureau’s oldest available data in 1996, seeing a 74.8% increase in border crossing entries, Dalton Cache saw a 31.4% decrease. Skagway’s tourism people win, particularly as their bus and train passenger traffic is the main driver of the increase, seeing 154% and 270% increases, respectively. Meanwhile, Dalton Cache saw a 76% decrease in bus passenger crossing entries and a 100% decrease in pedestrian crossing entries. Trains have just never gone there.

See the above table for all the numbers, or check out the data tool yourself for more fun!

QuebecJust like every year, finding data in Quebec that’s not only in French can be difficult. However, this year we came across Hydro Quebec’s Open Data portal, which is surprisingly well done! Each data set contains a description, files, and a host of thoroughly explained additional information that makes using the data sets much easier than what you sometimes find on other open data portals.

Maybe the most fun data set Hydro Quebec has is its tree and shrub directory! This data set contains “all of Hydro‑Québec’s data on over 1,700 plant species and varieties found in Québec. The directory can be used to search for a tree or shrub based on specific features such as light requirements, hardiness zone, height at maturity, etc. Also, each plant species and variety is associated with a safe planting distance from medium‑voltage lines.” How cool is that! Now, this data set is the actual data that can be accessed through hyperlinks configured on an API; however, Hydro Quebec has a “Choose the Right Tree or Shrub” page where you can search the data as a user who might know nothing about APIs.

You can search by the name of species or variety of tree or shrub, or you can choose a plant type, safe planting distance, hardiness zone, shape, light requirements, height, solid humidity, and/or spread to find a tree or shrub that aligns with what you’re looking for. For fun, we entered that we were looking for a “deciduous” plant with a safe planning distance of “7.5m”, a hardiness zone of “2b”, a “creeping shrub” shape, “full sun” light requirements, and “average” soil humidity, and the search spit out the “common bearberry” and “garland flower” as shrubs that aligned with this criteria, as well as provided a full record for each with much more information about them. Definitely one of the more unique and useful data tools we’ve seen!

Saskatchewanhttps://andrewjdyck.substack.com/s/open-data-saskatchewan

Ah, Andy Dyck. You are our go-to Saskatchewan data guy, and while Katie didn’t want to put in her email address to access your (maybe outdated) Saskatchewan Open Data portal that we covered in our December 2020 holiday show, we see you did start a Substack in 2021 instead that doesn’t require an email, with three wonderful data sets with such intriguing names as, “Honey, I shrunk your discretionary income”, “Moe money, more problems”, and “Catching a killer in Saskatchewan from the sky”. We think we should invite you to our panel at this point, Andy. You’re practically one of us. The “mode” to our “mean, median, moose”.

Anyway, we took a look at his “Catching a killer” analysis piece about the murders on the James Smith Cree Nation in Northern Saskatchewan since that 2022 tragedy is one likely remembered across Canada, and we don’t often get to look at this type of analysis. After getting the emergency alert about the situation, Andy took to Reddit where he learned the RCMP had sent up a plane to circle above Regina looking for the suspects and their vehicle. Learning this, Andy dug deeper into what RCMP and municipal police were doing with aerial surveillance in the province and into public tracking of private and commercial aircraft. Andy used ADS-B signals transmitted by aircraft and the ADS-B Exchange interface to do this, tracking the Shock Trauma Air Rescue Services (STARS) helicopters that were deployed to help injured victims, the Saskatoon Police Plane, and RCMP planes around the province. See his post to see the planes in the image below actually moving along their flight paths!

Through this tracking, he was able to discern when the STARS helicopters were flying injured victims to hospitals and where and when surveillance was happening, producing crazy surveillance flight path images like the one below.

Pretty cool stuff, Andy! Thanks again for being our go-to Saskatchewan data guy, and just another shameless friendly reminder that we’d love to have you on the show.

YukonThe Yukon Open Data Portal is larger than you might think with over 3,000 data sets to choose from. One that caught our eye was a shape file containing the key wildlife areas for various animals found in the Yukon. Here’s a sample of the key wildlife areas we found in the data set:

There’s many more! Take a look at this Observable notebook where you can select your own animal and see their key habitats in the Yukon.

AlbertaFor a change of pace let’s look at songs about Alberta. To do this we found a website that allowed for lyrics searching (a free source of raw open data containing lyrics is actually quite difficult to find). For Alberta itself we found 306 songs containing Alberta. For a population of 4.3 million, that’s 71 songs per million. “Alberta” might be skewed here because of the blues standard “Alberta” about a woman named Alberta, not the province. Here’s the songs per million for the largest ten Alberta cities:

| City | Population (2021) | Lyric Mentions (Lyrics.com) | Mentions per Million | | Calgary | 1,306,784 | 262 | 200 | | Edmonton | 1,010,899 | 117 | 116 | | Red Deer | 100,844 | 7 | 69 | | Lethbridge | 98,406 | 6 | 61 | | Airdrie | 74,100 | 22 | 297 | | St. Albert | 68,232 | 4 | 59 | | Grande Prairie | 64,141 | 1 | 16 | | Medicine Hat | 63,271 | 5 | 79 | | Spruce Grove | 37,645 | 0 | 0 | | Leduc | 34,094 | 0 | 0 |

Airdrie blows it out of the park due to the John Prine song “Paradise” which references a “Airdrie Hill”. Unfortunately Airdrie Hill is located in Kentucky, not in the town on the outskirts of Calgary. The song has since been covered numerous times. Medicine Hat gets a more legitimate boost from the Guess Who hit “Running back to Saskatoon” which mentions Medicine Hat and has also been covered numerous times. Red Deer gets the same boost though. The lyrics are “Red Deer, Terrace and Medicine Hat, Sing another prairie tune”. If you like this kind of stuff, you might like Maclean’s article exploring the place names mentioned in Tragically Hip songs.

ManitobaWe’ve covered the City of Winnipeg’s portal before, when we reviewed open data portals in Canada. It’s an impressive repository of data and resources. Winnipeg’s commitment to transparency and accountability is visible in features like the open budget tool, an interactive dashboard that enables detailed exploration of capital spending in Winnipeg.

The Winnipeg portal contains over 1,200 datasets. It’s got the routine open data content that you expect from any municipal government portal: infrastructure shape files, 311 data and so on, and a whole lot more – the striking thing about this portal is the breadth and depth of the data available. Many municipalities are great at publishing the “easy” stuff but it shows a commitment to open data when Winnipeg does the work to expose data that’s probably a bit messier and tougher to cleanse and aggregate like freedom of information requests, incidents of reported illicit substance use, traffic counts, and so on.

For this edition of the podcast, we zeroed in on business license data provided by the City of Winnipeg. This data set includes, by category, the business licenses issued by the City of Winnipeg. The business name, its location and the status of the business and license are all included for each license. The series begins in 2020 and runs to the present day.

A data set like this might not seem particularly sexy but this kind of ground-level economic data can be used to illuminate trends in communities in real detail. It also comes with some challenges. This is a data set Winnipeg published on their own initiative – there is no Manitoba standard format for municipal reporting of business licenses. To utilize this data outside its original scope it is critical to understand what data is included and what data is not. The authors of the data may not have considered that.

The business license data set description identifies that it includes 15 categories of business that are licensed under the city’s Doing Business in Winnipeg bylaw. One of those categories is “rooming house,” a residential property that has been converted into a shared dwelling. The definition of a licensed rooming house in Winnipeg indicates that licenses are only required for this type of business if it was established prior to 1986, further stating that the legal regime makes it very difficult to establish a new rooming house business of this kind. Converting residential properties to rooming houses requires a complex, bedroom-by-bedroom approval process.

The current extent of licensed rooming houses by city ward in Winnipeg is illustrated on this map. The concentration of rooming houses presumably matches with municipal development patterns pre- and post-1986. This data set might be useful to help understand opportunities for expanding housing in Winnipeg, or it can be understood as a proxy for how land-use regulation has changed the complexion of the city as it has developed.

Nova ScotiaNova Scotia is recognized as a center of excellence in archival digitization, with 30 terabytes available online, representing 1.3 million of the 60 million records in the archives. They’ve got an online portal they call Transcribe that is an experiment in crowdsourcing. Anonymous members can visit the portal, see which documents have not been fully transcribed and get started transcribing. Doug tried it out and it’s definitely not for the faint of heart or those with vision problems – when transcribing, you’re looking at a photograph of an old document with all the idiosyncrasies of manually-produced text content. Users can attach their name to their transcriptions but it’s not mandatory, more for saving your place to come back to than tracking contributions.

Some of the results of this work have made it to the Nova Scotia Open Data Portal, including a data set called Death Registrations – 1970 Full Transcript, which includes demographic, geneological and cause-of-death data for the 6,764 people who died in Nova Scotia in 1970. The digital archive preserves some of the features of a handwritten data set – there are over two thousand individual values in the column for primary cause of death – so it’s got to be approached cautiously. I was curious about the distribution of age at death in this data set, and bucketing the ages is a pretty safe transformation that kept me away from interpretation. I thought it would be interesting to compare the distribution of age at death in 1970 against the age distribution of Nova Scotia deaths in 1991, so I took a look at Statistic’s Canada’s mortality data and compared the two.

Converting the absolute number of deaths in each year into a share of overall deaths within the 5-year age buckets used by Statistics Canada allows us to chart the change in distribution of age at death in Nova Scotia. Not surprisingly, child mortality was significantly reduced and many more people survived into their seventies.

We decided to take a chance and reach out to the good people at Nova Scotia Archives to see if they had any comments or other context about this interesting data set. They were very kind to reply:

While the birth, death and marriage records that we receive from Vital Stats come with some indexing they reflect a time period when electronic data was expensive, and the index was capped at a fixed number of characters for each. Thus place names and, in some cases, given names were often severely truncated to fit within the 32 spaces allowed for the index information. Our staff was able to adapt and extend the website coding to allow the public to enter data into the fields they saw displayed in the image of the actual record. In addition to enhancing the basic who, when and where about the birth, death or marriage information, the public were eager to add the ages of those married, their parents’ residence and occupations and numerous datapoints which were then added to the searchable record. Using the crowd sourced information, the Archives was able to enhance the data and its searchability to eventually allow our website users to discover information about midwives in the African Nova Scotian community or the number of marriages between military personal from away and Nova Scotians during the war years. We found the public was eager to help, but we also added some features to allow them to get directly to persons from identified communities or of a certain last name. This enhanced their interest and allowed quicker completion of the whole record.

– John MacLeod

PEIPrince Edward Island is a pretty small province! There are eight cities in Ontario with a population larger than the 156,947 recorded on Prince EdwDard Island in 2019. The capital city does not have an open data portal, though the federation of PEI municipalities has made some ambitious noises in that direction.

The PEI Open Data Portal is not a hotbed of digital activity. It contains 212 data sets across five categories: Education and Community, Environment and Food, Government and Economy, Health and Home and Transportation. Since 2020, only 26 datasets on the portal have received an update, and many of those are related to mandatory reporting under some federal regulation like the Highway Traffic Act. There are only a small number of data sets that are kept relatively up to date.

One interesting data set with a decently long series is the register of visits to historic sites in the province. Last updated at the beginning of 2020, this series goes back to 2004 and records visits to seven historic sites on the island that collectively make up the PEI Museum:

Acadian Museum
Basin Head Fisheries Museum
Beaconsfield Historic House
Eptek Art and Culture Center
Elmira Railway Station Museum
Green Park Shipbuilding Museum
Orwell Corner Historic Village

They all sound absolutely lovely and if you find yourself on PEI you should definitely visit them all. I used good old Excel to create a pivot table of the data, then did a bit of manipulation to produce this chart showing the aggregate count of visits to the museum, with each site represented by its own colour. Basin Head Fisheries museum is the overall winner, and it’s nifty to see that there’s a baseline level of interest in the Eptek Art and Culture Center that draws in visitors pretty consistently whether it’s tourist season or not. The Beaconsfield Historic House also seems durably attractive no matter the time of year. We’ll have to wait for a future update to see how COVID-19 affected visits to the museum.

View Details

Today on Mean, Median, and Moose, we look at the public data released by social media platforms.

TikTok Transparency DataSince 2019, TikTok has been collecting and releasing transparency data that relates to its four transparency commitments of keeping people safe, maintaining platform integrity, supporting independent research and content transparency, and upholding human rights. It releases five reports on either a quarterly or biannual basis that detail data for community guideline enforcements, government removal requests, intellectual property removal requests, information requests, and US user requests to know and delete, pursuant to the California Consumer Privacy Act. There is a wealth of data in these reports, so we examine only the most recent community guideline enforcements report below.

In the Q2 2023 Community Guidelines Enforcement report, TikTok publishes data on videos, engagements, and ads removed for violating its community guidelines, which prohibit child sexual abuse material (CSAM), youth abuse, bullying, dangerous activities and challenges, exposure to overly mature themes, and the consumption of alcohol, tobacco, drugs, or regulated substances.

According to data from Q3 2020 to Q2 2023, hundreds of millions of TikTok videos are removed each year, with a peak of 113,809,300 videos removed in Q2 2022. Interestingly, TikTok provides how many videos were removed by automation as well, and there is a notable increase in this amount in Q2 2023, from a height of 53,494,911 in Q1 2023 to 66,440,775 in Q2 2023. It is difficult to know whether this is because there are more videos being posted and/or violating guidelines or because TikTok has strengthened its AI automated removal functionality.

Interestingly as well, TikTok’s proactive removal rate has risen from a low of 91.3% in Q1 2021 to a high of 96.5% in Q2 2023, but its removal rate before any views has sank from a high of 90.5% in Q2 2022 to a low of 80.9% in Q2 2023. Again, you could speculate the reasons for this, one being that perhaps TikTok has indeed added more AI automation to its content monitoring and has perhaps replaced humans that respond to user-reported content violations. Canada saw a total of 885,398 videos removed in Q2 2023, with 90.3% removed proactively, 55% removed before any views, and 84% removed within 24 hours of being reported. Its removal rate before any views is the second lowest of any countries TikTok operates in, followed only by Japan at a rate of 39.8%.

When it comes to what TikTok is removing according to its policies, videos that have sensitive & mature themes account for almost 40% of removals, with regulated goods & commercial activities following at 28%. If you dig deeper into videos with sensitive & mature themes being removed, you will find nudity & body exposure account for 28.6% of these removals, followed closely by sexually suggestive content at 25.3%.

Perhaps the most unique data TikTok provides in this report is data on disrupting and removing covert influence networks. It lists data for accounts that have targeted European foreign relations with Russia, Iraqi foreign relations with Russia, discourse about China, political discourse in Greece, political discourse in Turkey, political discourse in Thailand, and conflict in Sudan. It is important to note it only lists data after the full removal process has been completed and in the quarter after which it was completed, so there may be many ongoing removal operations to remove covert influence networks. For each of these operations, it lists the detection source, # of accounts in the network, and # of followers of the network.

Covert Influence Network Removals Completed in Q1 2023

| Covert Influence Network Target | Detection Source | Accounts in Network | Followers of Network | | European Foreign Relations with Russia | Internal | 588 | 36,331 | | Iraqi Foreign Relations with Russia | Internal | 364 | 524,513 | | Discourse about China | Internal | 108 | 141,621 | | Political Discourse in Greece | Internal | 69 | 25,898 | | Political Discourse in Turkey | Internal | 68 | 61,379 | | Political Discourse in Thailand | Internal | 31 | 1,817 | | Conflict in Sudan | Internal | 17 | 10,213 |

The Iraqi Foreign Relations with Russia network seems to have had the most far-reaching impact, with 364 accounts in the network and 524,513 followers. TikTok describes this operation as, “We assess that this network operated from Iraq and targeted an Iraqi audience. The individuals behind this network created inauthentic accounts, including the creation of false personas and inauthentic media entities in order to artificially amplify content emphasizing the strategic importance of Russia in Iraq.” TikTok certainly has some of the most well-reported data of any social media platform, and there is much more to explore here for those who are keen.

Twitter Community NotesAt the end of 2022, Twitter/X rolled out a new feature called “Community Notes” where users can provide additional context to a tweet. That note is then rated “Helpful” or “Unhelpful” by other users on the site. If the note reaches a certain helpfulness threshold, it is displayed to all users on the site who are viewing the tweet. All community notes and their ratings are available from twitter to download. The algorithm that determines whether to show the community note is also publicly available. In this way you can download the notes, run the algorithm to verify the results and then run experiments with your own algorithms.

The algorithm itself is not as simple as taking the helpful ratings and subtracting the unhelpful ratings. Instead machine learning is used as well as measures on polarity. For example, it has mechanisms to boost the score for a note if raters that have disagreed in the past both rate it as helpful. For more details on the algorithm and a discussion of how it works Vitalik Buterin, a Canadian co-founder of Ethereum, has an interesting blog post on the topic.

For Mean, Median, and Moose, we decided to look at the helpfulness and unhelpfulness ratings of the community notes that are visible. These are the helpfulness ratings for the community notes currently visible on Twitter as posted on October 14, 2023 for Canadian topics. You can view a specific tweet by taking the ID and putting going to the URL in the format of https://twitter.com/MeanMedianMoose/status/;. Twitter ignores the author in the URL and takes you to the Tweet. To see the individual tweet IDs, you can go to the Observable notebook we built for this post.

You may notice that the tweet that the highest rated community note (in terms of number of “Helpful” ratings, not based on Twitter’s community note score) was attached to is now deleted – which happens quite a bit as often community notes are corrections to misleading or misinformed tweets, which can embarrass the original poster. The second note rated as helpful the most times is this one on a statistic related to Canada’s MAID program.

In many ways this tweet should be in a Community Notes hall of fame as there are two additional replies by the original author that also have visible community notes:

Which is the only example of a Community Note hat trick that I have ever seen. What about other topics? Here’s “Trudeau”:

And Poilievre:

Aside from the number of community notes for each topic, it’s interesting to note the ratio of helpful to unhelpful seem about the same at something one the order of ten to one. It’s also interesting to note that community notes with a modest number of ratings can also be shown – as low as 20 in some cases.

Just for fun, we ran a query to find the community note currently marked as helpful, but had the highest ratio of unhelpful to helpful ratings. For easier querying we remove the “Somewhat helpful” ratings”. It’s useful to look at outliers like these to try and understand algorithmic scoring better. Here are two that had more unhelpful than helpful ratings, with over 100 ratings total. Many of the others in this category have under ten, which is interesting in itself.

On the podcast we talked about tradeoffs in moderation and different kinds of moderation types. We also talked about a game you can play to make some of the tradeoffs apparent: Trust and Safety Tycoon.

Google Transparency Data on Government Requests for Content Removal Google maintains an open data source that they update every June 30th and Dec 31st with the number of requests from governments to take down content from one of their platforms or services.

The download file is a csv that lists country, date/year, which google service and a type/reasons of the removal of the content. The full file has over 16,000 rows of data with requests from numerous countries. For Canada, since 2011 there have been 1,393 removal requests by the Government of Canada. They cross 18 “reasons” ranging from porngraphic content to fraud to suicide promotion.

In many cases there are only a handful of removals each year on a specific google product. One issue with this data is as google products evolved over time, some complaints show up in different categories. Google Earth, Streetview and Maps only have a handful of complaints but it is unclear if complaints are being nested within those or the “Other Google Map Related Products”.

Not surprisingly issues like fraud, privacy and defamation are the most common triggers for government complaints. The mysterious “other” is the fourth highest, unfortunately there is limited clarification on what this category is. There are also some categories that could likely be merged like Adult Content and Obscenity/Nudity.

The impact of COVID seems to be clear on government requests to Google. As Bill C-18 was introduced in 2022 – the Online News Act, at first I thought maybe the government reduced the number of requests to Google as they were pressuring them to pay for news but the timing doesn’t work out.

Large number of Fraud complaints in 2019 against the Google Voice platform drove the surge in request that year. It is also possible that Goggle limited a vulnerability that was driving government actions.

Meta (Facebook) Political and Public Interest AdvertisingAdvertising on Meta platforms is a popular way for organizations to get their message out. In the wake of a number of issues in the 2016 US general election, Meta Implemented new policies around ads about social issues, elections and politics. Advertisers who want to place this type of ad are required to prove their identity through an authorization process. Meta has also implemented transparency around this kind of ad by publicly disclosing information about this kind of advertising, which gives us some data to play with.

We’ve looked at these ads before, so for this edition of the podcast we wanted to provide some tools for people interested in exploring the ads themselves. Facebook exposes this information through its Graph API. Doug built a Python script to collect data about Facebook ads in Canada relating to a set of search terms. The data used in this podcast and post was extracted for the period Jan 1, 2023 – Oct 20, 2023.

The Graph API is well-documented and easy to work with. A quick registration process on the Meta Developer website gets you access to the Graph API.

Data returned about an ad includes the cost of the ad, targeting information and the page purchasing the ad placement. For this analysis,

For this demonstration, we used an Ipsos poll from September, 2023 that reports the top 10 issues identified by Canadians polled. We were curious whether spending on ads would be proportionate to public interest.

The issues identified by Canadians, according to Ipsos were as follows:

Cost of living
Health care
The economy
Housing affordability
Inflation
Taxes
Climate change
Poverty
Government deficits and debt
COVID-19

Meta does not provide precise data about the number of impressions an ad gets or how much money was spent. Instead, they supply a range of values in which the ad falls. For simplicity of analysis we used the higher value for both spend and impressions.

This chart shows the amount of money spent on public-interest ads mentioning each of the identified terms, keeping in mind the caveat about the value ranges provided by Meta.

The universe of ads reported back to us is 3,000 ads across the ten searched categories. Interestingly, the number of ads by search term has a mainly similar distribution to the distribution by total spent, with one significant outlier: 23.1% of the money spent on public-interest ads in this period mentioned inflation, but fewer than 10% of the ads purchased mentioned inflation. Digging in to the data on these specific ads, we find significant spends by groups you might expect to be interested in stoking inflationary fears: the Canadian Taxpayers’ Federation, the Conservative Party of Canada are all big spenders on ads mentioning inflation, but the single biggest spender on that term was a Facebook page called “Adorable Pets.” We aren’t sure if that’s a miscategorization by Facebook, an error by the page administrator, or something more sinister, but it represents about a quarter of the reported spend in the category.

The results trend downwards as you would expect based on Ipsos ranking, but there are some surprising individual results.

The notebook also contains an interactive pie chart that shows the relationship between the top five pages in each category, by either spend or impression, relative to all other advertisers. Here’s an example

You can find the data we used for analysis and an interactive version of these charts inside this Observable Notebook, including a data browser to look through all the results we found in detail.

View Details

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.

View Details

This week on Mean, Median, and Moose, all things European!

Canada-Europe TradeStatistics Canada provides detailed information on goods and services trade between Canada and its trading partners throughout the world. Let’s take a look at trade with Europe.

Trade in Services The UK is the standout in terms of services trade with Canada – both with exports and imports. Interestingly the imports and exports are fairly balanced when it comes to services. As you’ll see this is not going to be the case for goods.

What about the trend over time? We can see strong services trade growth with European nations since 2010. Interestingly the second largest partner used to be Switzerland but it has stagnated and, with very strong growth from Germany and France, is now fourth.

Trade in GoodsWhat about trade in goods (note that all export figures below exclude re-exports which is why they are labeled domestic exports)?

Here we see notable imbalances. Canada has a surplus with the United Kingdom and deep deficits for Germany, France, Italy and many others. Here’s trade volumes over time:

The United Kingdom used to be first, but in 2021 was overtaken by Germany. So what did Canada import from Germany and others?

We import a lot of consumer goods from many major trading partners. Germany also exports industrial machinery and cars to Canada. Norway is an interesting outlier with Canada importing many energy products – presumably oil products originating from the North Sea. In terms of Canada exporting to Europe, this is what we see:

You may have been expecting energy exports or maybe motor vehicles. While Canada does export a large amount of those goods, it’s to the United States, not Europe. Bonus points to those who can guess what metallic mineral we export to the United Kingdom is vast amounts. We’ll talk about it on the podcast.

You might find the categories to be too broad. The good news is that there are a lot of tools to see individual details about what products are exported in more detailed. A good one is the OEC. Go to the site to drill down into countries and see what we export and import from them in the tree map.

Second Language EducationSomething Canada is not known for is its stellar second language education. For a country with two official languages, according to StatsCan, the rate of English-French bilingualism is rather low, at 18% in 2021, and it has not budged much since a rate of 17.7% in 2001. Among young people aged 18 to 24, this rate is only slightly higher at 25.2%. Additionally, this rate has been increasing only in Quebec and declining outside of Quebec since 2001.

While 46.4% of Quebeckers and 34% of New Brunswickers can conduct a conversation in English and French, the next highest rate of bilingualism is only 14.2% in the Yukon Territories.

Looking at StatsCan Table 37-10-0009-01, in the 2020/21 school year, it seems second language programs are only compulsory in Quebec and the Northwest Territories from grade 1 onward to grade 9, with other provinces’ second language programs becoming compulsory in grade 4 or 5 onward to grade 8 or 9.

Number of Students in Official Languages Programs in 2020/2021

Oppositely, the EU has identified multilingualism as “one of eight key competences needed for personal fulfillment, a healthy and sustainable lifestyle, employability, active citizenship and social inclusion.” The European Commission’s First European Survey on Language Competencies conducted in 2011 in 14 EU member states with students in the last year of lower secondary education or the second year of upper secondary education found 42% of students were proficient at a B1 or B2 language level in a foreign language, meaning they could independently conduct a conversation in a foreign language learned in school, which could be English, French, German, Italian, or Spanish.

In the majority of the participating member countries, the first foreign language is compulsory, and in most countries, this first foreign language is English. Results are better in English as a first and second foreign language than other languages, which the study finds is likely due to its popularity in media and its perception of usefulness to students.

Like with Canadian provinces, however, there is a wide range in ability across countries, with a high of 82% of students at an independent English user level in Sweden and Malta but a low of only 14% in France.

While the survey did not report exactly when most students begin learning a first foreign language, it did report that generally, students reported beginning learning a first foreign language before or during primary education, and earlier onset is related to higher proficiency in the foreign language tested, as is learning a larger number of foreign languages. This is perhaps an interesting lesson for Canada to learn, where compulsory foreign language learning does not typically begin until a student is in grade 4 or 5.

TourismTracking tourism between Canada and Europe was a bit more difficult than I assumed. I previously used the Statistics Canada database on Travelers to talk about COVID Impacts on travel. I went back to that database to pull data on Europeans coming to Canada.

The Top 5 Countries of Origin are: the UK, France, Germany, the Netherlands and Switzerland which account for 1.66 million visitors from March 22 – Feb 23. During that same period a total of 2,235,390 arrived from Europe in total, including 3 people from the Faroe Islands.

Ontario and Quebec attract the largest share of European tourists attracting – 871,000 and 704,000 respectively – the majority from Central and Northern Europe. Surprisingly the Yukon sees more visitors than PEI, although this may have more to do with the lack of direct flight connections as Whitehorse Yukon is home to a direct flight from Frankfurt Germany while there are no flights that I could find directly to PEI.

Unfortunately when looking at Canadians traveling to Europe things get a bit more complicated. Statistics Canada does report on outward bound travel from Canada to other parts of the World but due to not knowing the end destination they can’t say which country specifically Canadians are traveling too.

The EU doesn’t have (based on what I could find) travel data for Canadian Tourists specifically. Part of the problem is the EU’s Open Data portal hosts data from its member nations and governments (both national and subnational). The problem as I see it is there are very few distilled datasets that bring together the national data into a broader European snapshot.

The World Bank does track overall global tourism data and the chart below illustrates how Canada compares to the major European tourism nations. Overall, the EU attracted almost 1 billion tourism arrivals in 2019.

One of the overarching challenges with Tourism data is that tourists are often captured at the first point of entry. Often landing at a major hub airport/city this point of entry captures the data and then the tourists disburse. This creates significant challenges with subnational data collection and multi-national data in the case of the EU. I feel like many of the European datasets are inflated with cross border travel as someone from Belgium goes to France for a weekend. As Ontario is the size of much of Western Europe, the ability for inter-european travel makes tracking actual tourism numbers far more difficult.

Comparing European and Canadian Educational AttainmentEducation is a critical factor in every aspect of life, and educational attainment is used internationally as a proxy for the “overall value of human capital in a country or region”, as the Organization for Economic Cooperation and Development (OECD) factbook puts it. In other words: measuring educational attainment is an exercise in identifying the skills available in the labor force.

Educational systems vary around the world, but the OECD has identified four definitions of educational attainment which are used to compare countries’ performance in education. Using an OECD statistic is cheating our challenge a little bit, but Doug was short on time this month and it’s an interesting topic.

To compare Canada to Europe, Doug checked out the European Union’s Eurostat website and downloaded the tables associated with educational attainment, then downloaded data from Statistics Canada’s page covering the same measure. Europe publishes this information as simple tabular data so with an hour or so of Excel time, Doug was able to derive a comparable pivot tables from the Stats Can data and combine the two data sets. To get the broadest population comparison, data for people aged 25-64 was used.

The OECD’s definition of educational attainment has six definitions, four of which Doug used in his comparison work. They are “Less than primary, primary and lower secondary education” which is also referred to as “Below upper secondary” education. Basically people without a high school diploma. Next is “Upper secondary, post-secondary non-tertiary and tertiary education” which includes everyone not covered in the previous item. “Upper secondary and post-secondary non-tertiary education,” that last one is courses of education that follow the acquisition of a vocational qualification at secondary school. Europe publishes two subsets of that last category, one for general and another for vocational education but Canada does not seem to follow suit. These sub-categories were omitted from the visualizations. The final category is for graduates of tertiary education, which we’d call colleges and universities.

From there, he created a set of four Observable notebooks, each containing a tool to compare European countries and regions to Canada for one type of educational attainment. You can check them out at these links: Below Upper Secondary, Upper Secondary and Post-Secondary Non-Tertiary, Tertiary, and the big one: Upper Secondary and Above. In these notebooks, you can select a European country or a regional grouping (like the EU 20 or EU 27) to compare against Canada’s performance.

Here’s Canada vs. Europe on attainment below upper secondary:

And this chart is Canada vs. Europe on tertiary education:

I would have combined these into a single notebook, but I have a bad habit of writing code instead of changing the source data, because writing code is more fun. Rather than copy and paste my JavaScript data manipulation madness into a giant pile of spaghetti, I decided to create four smaller piles of spaghetti that are mostly the same. If anyone out there listening decides to fork and use one of these notebooks, I hope you appreciate my choice.

– Doug

In general, Canada performs quite well in these comparisons, with a very high percentage of adult Canadians possessing a post-secondary degree and a much lower percentage of Canadians not finishing secondary school than the EU-27 average (20.5% of Europeans fell into this category in 2022 while only 7% of Canadians did). Although these numbers are designed to be comparable across countries and education systems, there are surely local details that serve to create a bit of fuzz in the numbers. To pick one example, Canada doesn’t really have a system that compares to Germany’s dual vocational education system which emphasizes streaming school leavers into vocational education as an alternative to university. Our colleges are considered tertiary institutions, which perhaps helps explain Canada’s position leading the OECD in tertiary educational attainment.

View Details

Today on Mean, Median and Moose, we have data on climate, disasters, and the weather.

Car accidents and the weatherThe Transport Canada National Collision Database provides detailed statistics for Canadian motor vehicle accidents for the period between 1999 to 2019. There are a variety of measures you can slice and dice the data on (interestingly though none related to geography). Let’s take a look at the weather related data.

First, let’s look at collisions based on weather events:

Surprised? Surely it’s easier to get into a car accident when it’s raining or snowing outside? That may be the case, but it’s important to consider that there may be more clear and sunny days within the time period you’re looking at. The per-day average may look very different. Unfortunately for us there’s no location data and it’s not sunny everywhere in Canada all at once – so we can’t calculate that out. What a shame.

One thing we can do though is look at other dimensions like severity of data. To normalize this data we can take the severity measures and normalize by the number of accidents in the category. For example, this see if weather has a noticeable effect on the number of vehicles per collision:

Interestingly clear and sunny still beats out inclement weather for the number of vehicles per collision. What might be happening here is individual crashes being so numerous that they drown out the 20 car pileups caused by freezing rain.

What about people per collision?

Raining starts to show up here interestingly. Now for injuries and fatalities. Visibility is the most dangerous when it comes to injuries – perhaps a result of having less time to slow down?

Fatalities tell an even more stark story:

Deadliest and Costliest Natural DisastersOur panel is fortunate not to encounter natural disasters too frequently in Windsor ON, but natural disasters can hit anywhere in Canada, and over the years, some have been very deadly and some very costly. The Canadian Disaster Database holds data on all disasters in Canada, including natural, technological, and conflict events that have happened since 1900. It classifies a significant disaster event as one in which one or more of these criteria are met:

10 or more people killed
100 or more people affected/injured/infected/evacuated or homeless
An appeal for national/international assistance
Historical significance
Significant damage/interruption of normal processes such that the community affected cannot recover on its own

The data it holds describes where and when a disaster occurred; the number of injuries, evacuations, and fatalities; and an estimate of the costs. We took a look at meteorological – hydrological events, which includes avalanches, cold events, droughts, floods, geomagnetic storms, heat events, hurricanes/typhoons/tropical storms, other storms, storm surges, storms and severe thunderstorms, tornadoes, wildfires, and winter storms, from 1900 to 2023 to see which disasters were the deadliest and the costliest over this time period.

According to the database, the top 3 deadliest natural disasters in Canadian history were all heat events, with the deadliest being a cross-Canada heat event that began on July 5, 1936 and lasted until July 17, 1936, causing 1,180 fatalities as temperatures reached greater than 32°C in most regions through that period. The second deadliest heat event occurred in Vancouver and Fraser BC in July 2009, causing 455 fatalities, and third deadliest occurred in Ontario and Quebec in 2010, causing 280 fatalities.

It is interesting to see the evolution of data collection as the 1936 event data provides very little context, while the 2010 event data comes with additional statistics, listing that in Toronto, paramedics received 51% more complaints about breathing problems and 39% more calls related to fainting, in Montreal, heat-related deaths doubled, and across 8 health regions of Quebec, there was a 33% increase in mortality rate.

The fourth and fifth deadliest natural disaster events are much more historically interesting than the first three, with the fourth deadliest event being a storm and severe thunderstorms event over Lakes Huron, Erie, and Ontario from November 7, 1913 to November 13, 1913, that caused 270 sailors to drown when 34 ships went down during the storm that saw winds up to 140 km/h – entire crews of eight ships were lost! The fifth deadliest natural disaster was a wildfire event in Cochrane and Matheson ON on July 29, 1916 that caused 233 fatalities and 8000 evacuated from the area as both towns were entirely destroyed by a fire that resulted from a small blaze started by lightning and was made worse by fires started by sparks from a passing locomotive.

As for the costliest natural disasters in Canadian history, a winter storm from January 4, 1998 to January 10, 1998 takes the cake, causing an estimated total of $4,635,720,433 in costs, which includes federal and provincial disaster financial assistance arrangements (DFAA) payments, provincial department payments, municipal costs, other government department costs, insurance payments, and NGO payments. The second costliest event was the April 2016 wildfires in Fort McMurray, costing an estimated total of $4,068,678,000 and resulting in 2 fatalities and 90,000 people evacuated from their homes. The third costliest was the June 2013 flooding across southern Alberta at an estimated total of $2,715,742,000, which also saw 4 fatalities and 100,000 people evacuated from their homes.

It’s important to note the database displays cost data in the dollar amount of the year that the event took place or the year the specific payment was made, so it can be difficult to compare events, but the database does provide a “Consumer Price Index Normalization” conversion tool to help with this. Additionally, there is no standardized guideline for collecting cost and loss data, and financial data can take years to finalize, so estimates are sometimes provided in the interest of keeping the database current. Overall, there is a lot of missing or “unknown” data in the database, so this has to be kept in mind when considering the accuracy and completeness of the data.

US Extreme Weather EventsThat National Oceanic and Atmospheric Administration (NOAA) in the US oversees weather forecasting and data collection. They keep a dataset from 1950-2022 (updated annually) of extreme storms and weather events. There are three types of data location data – with latitude and longitude based locations of weather station based on two criteria.

Events in this official NOAA database are selected based upon the following criteria:

Storm has sufficient intensity to cause loss of life, injuries, significant property damage, and/or disruption to commerce.
Storm events contains rare, unusual, weather phenomena or significant meteorological events, such as extreme temperature.

The other two dataset these same events types contain fatalities and longer form detail data including estimates of damage cost data. I had tried to find an equivalent dataset for Canada, the closest I could find was the Disasters dataset that Katie discussed and as you can see by the criteria they are close but not necessarily apples to apples. Within this data there is different types of data being collected which also creates challenges:

In 2021 there were over 61,000 unique extreme weather events in the United States.

Another interesting piece with this data is the sourcing. They track where each of these events occur and who spots them. Social media is an emerging source for active short term events like Debris Flows (land slides), flash floods etc. while traditional weather forecasting and spotting tend to hand warnings for thunderstorms or hurricanes.

Finally the latitude and longitude data is available for approximately 53,000 of the 61,000 events that occurred in 2021 allowing a map like this to be created.

Each dot is one of the storm events geo located to the latitude and longitude point, the colouring is based on the month which the storm event occurred.

Consuming Canadian Climate DataCanada has data from weather stations across the country going back to the 19th Century. The earliest observations in the data set go back to 1840, with a decent amount of data series starting in 1870. Weather data is often distributed in binary formats that require special software tools to deal with, but Canada’s Ministry of the Environment and Climate Change publishes weather station data series with data available down to the hour in some cases. The main challenge for a non-specialist in working with this data set is that each individual data series within the larger series is provided as a separate CSV file, for example the daily data series is available as a set of 12 CSV files per year, one for each month, each containing a row summarizing the daily observations at that station. This multiplies quickly and is very hard to manage if you are looking for data across a larger geographic area than a single station.

To help with this problem, Doug hacked together a Python script that automates the process of downloading monthly data files, processing them, and populating a single database table with the result. It uses a slightly modified version of Environment and Climate Change’s Station Inventory spreadsheet to source data for individual stations.

By selectively removing rows from the Station Inventory spreadsheet, you can specify any subset of stations to collect observations for, and modifying the start and end dates of each data series in the spreadsheet will serve to limit the data consumed to the specified period. The code is available on GitHub and is dedicated to the public domain for anyone to use and modify.

Downloading the data one file at a time takes a decent amount of time – a few seconds per monthly report – and the database table can become very large. The data volume is also a consideration – after processing the first 90 or so stations on the list (out of a little less than 9,000) the table sits at around 13 million rows. It’s a good idea to limit your data intake to only the stations and years you’re interested in.

Once you’ve got the data you want in the table you can use SQL queries to slice and dice the data any way you’d like. The project’s SQL folder contains a few sample queries to get you started.

View Details

This month on Mean, Median, and Moose we look at a smorgasbord of economic indicators!

New Motor Vehicle SalesIf you drive past your local auto dealer regularly, you might notice how many cars are or not on the lot over the span of a few months. Sales of new motor vehicles is a key economic indicator since it provides a snapshot of consumer demand for a big ticket item, and it is one of the easier economic indicators for your average person to understand since many of us have bought or will buy at least one new car in our lifetime.

The New Motor Vehicle Sales table (20-10-0001-01) available on StatsCan shows the number of units sold per month, unadjusted for seasonality. In the last 5 years, from January 2018 to December 2022, a total of 8,870,067 vehicles were sold across Canada, but they were not sold in even numbers across these years or months.

In 2018 and 2019, new motor vehicle sales saw a very similar number of sales and similar seasonality to sales with 2,045,721 sold in 2018 and 1,980,150 sold in 2019. The beginning of the pandemic brought a predictable huge drop in sales, to a low of only 47,508 sold in April 2020. While you might expect new motor vehicle sales to have recovered to 2018 and 2019 levels by now, they haven’t. In 2021 and 2022, there is the usual January dip in sales to below 100,000 units, though this is notably lower than January dips in 2018, 2019, and 2020, which never went below 100,000 units sold. Similarly, while there is the usual spring spike in sales from March to June in 2021 and 2022, they remain well below the peaks of over 205,000 units sold in May 2018 and 2019, hitting only a peak of 173,881 units sold in March 2021.

Arguably, much of this effect is due to the supply chain shortage that resulted from the pandemic, though in recent months, while car lots have returned to their full capacity, car sales have not. It will be interesting to see whether a recovery occurs in the typically high sales season of March to June this year.

Measures of ProductivityThe OECD hosts some measures of productivity for its member nations and well as the world. Let’s take a look at how Canada compares with some of its peers. Below is the GDP per hour worked in terms of USD. Note that these figures are not normalized to inflation:

Next up, labour compensation growth. We’ve included the means below as red. Interestingly Canada compares more favorably to the United States on this measure.

Inequality indicatorsThe 2021 Census saw the release for the first time of economic inequality indicators for Canada, provinces and communities. The census data includes gini-coefficients (or index) for various income types (before tax/after) as well as adjusted measures. It also includes the P90/10 ratio, which is the ratio of gap between the incomes of at the 10th and 90th decile.

Although not economic indicators in their own right, there is a range of research the connects economic growth and income inequality. One of the items that Statistics Canada did with this census is go back to 2016 data and calculate these indicators from the last Census so we have some comparison. Due to the scope of the data I focused in on Ontario CMAs. For context Canada’s Gini-Coefficient for adjusted after tax income in 2020 was 0.302, Ontario was 0.308 – internationally it can be found here (note the values are multiplied by 100 compared to Statistics Canada values).

Overall Canada and Ontario preform pretty well on income inequality. Unsurprisingly Toronto and Hamilton CMAs are near the top of the Ontario list but they are joined by Windsor which is a bit of a surprise given its affordable reputation.

The Standard Deviations of the coefficients have shrunk between the last two censuses. As inequality did drop during that period. It has to be pointed out that the 2020 Census data is skewed by COVID income supports that likely prop up the bottom end of the income spectrum.

As I was just starting to play around with this data I wanted to see how these factors related to other economic indicators. So I ran some basic correlation tests on the Gini-coefficients against census data on unemployment rates and educational attainment.

Now these correlations should be taken with a grain of salt as a more robust analysis could be done but on first pass it is certainly it does peak my interest. As a lower gini-coefficient is better the positive relationship with unemployment makes sense as low unemployment likely drives lower income inequality. The fact that the relationship got weaker between 2015 and 2020 is an interesting element that may need to be explored.

The negative correlation between educational attainment also makes sense as educational attainment rises it helps reduce the gini-coefficient across ontario. That fact that this is increasing in strength helps illustrate the growing power of educational outcomes and future economic success.

Finally there is the P90 to P10 ratio which is the income ratio between the 90th percentile income and 10th percentile income. This went down across the Ontario CMAs that were measured, despite some permanent government programs we need to think about the impact of the COVID relief programs on this data.

The Bank of Canada Valet APIChanging gears a bit and looking at data sources for baseline economic data, one of the key tools in this space is the Valet API offered by Canada’s central bank. The Bank of Canada does a significant amount of research and analysis as part of their mandate, which also includes matters like printing money, monetary policy and the financial system. One of their key data products is the Valet API which offers programmatic access to global financial data.

The Valet API is set up for ease of use and that makes it nicely beginner-friendly. It does not require authentication or special headers, and data can be accessed in CSV, JSON or XML formats. The API is organized into lists of data series and grouped data series. The API will return information about lists and series as well as the individual data points within a data series or group of data series. These data points are called “observations” within the context of the API.

The Valet API contains almost ten thousand individual data series, including highly detailed economic data feeds and survey responses on a variety of topics. Some of the highlights include exchange rate data for dozens of foreign currencies, rates of return on instruments like treasury bills and bonds, commodity price indexes by industry, interest rates, consumer price index data, and the Bank of Canada’s internal future projections for a variety of indicators.

It’s a great resource and there are a few tools out there for working with it, including an R package and a Python package. Doug decided to add his own tool to the mix. Factotum is a Python command-line utility that access the Bank of Canada API, collects data from the specified series for a specified period, and outputs an image file containing a reasonable-looking graph of the requested data. It’s a pretty simple little tool and the code should be a good starting point for someone who’d like to play with economic data.

Here are some images that we generated to show off Factotum’s capabilities:

Five Year fixed mortgage rate

Public Perception a “Big recession” is imminent

Percentage of credit cards in arrears

New cash bills printed

Crude oil and bitumen as a percentage of total exports

View Details

This month we are looking at data on social media in Canada.

Social Media in Canada in 2022Social media has continuously evolved since its inception, from the early days of Friendster and MySpace to the platforms of today. The Social Media Lab at the Toronto Metropolitan University studies this evolution and its implications for Canadian society, putting out a report on the state of social media in Canada every few years based on a census-balanced online survey of 1500 Canadian adults. We took a look at the 2022 report, which starts by looking at the adoption of social media by Canadians in 2022 and how this has changed since 2020.

Facebook continues to be the social media of choice for Canadians, with 80% having an account, followed by YouTube at 62%, Instagram at 51%, Twitter at 40%, and LinkedIn at 37%; however, all five of these platforms saw their adoption rate either remain stable or decline since 2020, with LinkedIn in particular experiencing a 7% drop. At the other end of the scale, while their user base may not be as high, adoption rates have increased for TikTok, moving from 15% in 2020 to 26% in 2022; Reddit, moving from 15% in 2020 to 19% in 2022; and Twitch, moving from 9% in 2020 to 13% in 2022. It is important to note that these three platforms, and TikTok in particular, are popular with those under 18, who were not included in this survey.

Of course, we know having an account doesn’t mean you are actually using that platform. The report goes on to rank frequency of use of platforms at a “daily”, “weekly”, or “less often” frequency.

Facebook again ranks first at 70% daily usage and 15% weekly usage, but TikTok takes second place with 65% daily usage and 19% weekly usage despite its much lower adoption ranking. It is followed next by YouTube at 61% daily usage and 25% weekly usage, Instagram at 60% daily usage and 21% weekly usage, and Snapchat at 54% daily usage and 22% weekly usage. Despite being in the top 5 spots for adoption, Twitter and LinkedIn don’t make the top 5 for usage.

Across all platforms except for TikTok, where daily usage rose by 2%, daily usage has fallen between 3% and 14% since 2020, with Reddit experiencing the 14% drop in daily usage. The report speculates this is due to pandemic restrictions being lifted across the country by the summer of 2022, but it is interesting to question whether this is simply one of the reasons rather than the central reason.

Canadian social media users vs. podcast listenersThe Canadian Internet Use Survey is a detailed survey produced by Statistics Canada detailing how Canadians use the internet. The latest data we have is from 2020. We’ll be looking at how podcast listeners and social media users compare. A “podcast listener” here is someone who has listened to a podcast in the last 12 months. Similarly, “social media user” here are those who have used social media or as Statistics Canada calls it in the survey “social networking” websites in the last 12 months. You can get this and (and much more) data, as well previous year’s data in the PUMF files that Statistics Canada provides here.

Let’s start with gender:

Proportionally, more women use social media than men in the survey, while podcasts are proportionately slightly more popular among men. As expected, overall social media is more popular than podcast listening.

What about among the provinces?

These look a lot like population graphs – we didn’t weight the numbers here, just used them raw so watch out for that. Still, there’s something interesting going on here with Quebec and Ontario. Social media is only slightly more popular in Ontario than Quebec, but podcasts are much more popular in Ontario – could there be a difference here related to language?

Indeed it looks like almost no french-only Canadians listen to podcasts, but most of those Canadians use social media. What about people going to school?

You can see that almost everyone going to school uses social media in some way, while about half of those going to school listen to podcasts. What about education?

The more educated you are, the more likely you are to listen to podcasts. The effect is there for social media too, but not as strongly. There’s been a lot of talk about how social media makes you miserable. The CIUS asks questions related to life satisfaction and mental health. Let’s take a look at those:

“Life satisfaction” here is the results from people being asked to rate their satisfaction with life on a 11 point scaled (0 to 10). Not much different there, although people who don’t listen to podcasts and/or use social media do have the highest odds of being happiest. Although, interestingly they also have the highest odds of being most unhappy. Here are the results in terms of self-reported mental health.

Finally, here’s household type and income quintiles:

Nothing specifically jumps out of a different between podcast listeners and social media users there. How about income:

Podcast listeners skew higher income.

As you can see the PUMF data is extremely detailed. Statistics Canada asks a lot more questions. Go check it out and let us know what you find.

Social Media and Access to InternetIn their “Digital 2022: Canada” report, Hootsuite reported 33.30 million social media users in Canada (87.1% of population) with a year-over-year change of +3.4%. Statistics Canada reported a figure of 81.8% in 2018 for a similar measure, which was unfortunately a one-off survey and is not reported annually. According to the Hootsuite data, Canadian Internet users spend an average of 1h53m per day on social media, which is a larger amount of time than all media engagement besides consumption of television programming.

The average Canadian internet user visits 6.2 different social network platforms at least once per month. Hootsuite’s data says the top five social media platforms are Facebook, Facebook Messenger, Instagram, Whatsapp, and iMessage. Twitter comes in at number 6, followed by Pinterest, Tiktok and Snapchat rounds out the top 10. There’s lots more good stuff to be found in the full report here: https://datareportal.com/reports/digital-2022-canada

I wanted to find out information about social media use broken down by income, but that information is not readily available. The closest proxy to be found is in Statistics Canada’s breakdown of internet users (table 22-10-0144-01 https://www150.statcan.gc.ca/t1/tbl1/en/cv.action?pid=2210014401).

Since 90% of Internet users in Canada also use social media, I feel this provides at least a rough proxy for social media use. Zeroing in on this data we see that there is a significant drop from the second quartile to the lowest, with only 75.9% of people in the lowest family income quartile having access to the Internet at home compared to 85.7% in the second quartile. The top three income quartiles are clustered between 85.7% and 90.7%, which suggests to me that there is a real financial barrier to Internet access and, by extension, participation in social media among the lowest income Canadians.

Mastodon UsersSince the controversial takeover of Twitter by Elon Musk, many users have been looking around for an alternative microblogging platform. It’s far too early to tell if there is going to be any kind of real user exodus from Twitter but I wanted to see if there had been any impact on the alternative platforms so far. Mastodon is a distributed social network that is often cited as an alternative to Twitter. Simon Willison gets this information by finding Mastodon instances using JSON data from the Mastodon instances web page, then adding up the total number of users reported by each instance. He had been tracking this information for just over a week when we recorded the podcast and caught a significant adoption wave:

6.4 million people is a big number. Since the first time I looked at this data, it’s jumped over 7.5 million. but it pales in comparison to any of the major social media platforms. By direct comparison, Twitter reported 237.8 million daily active users in the 2nd quarter of 2022, the most recent quarter available.

Social Media and Trust in InstitutionsA research study from Western Australia University caught my eye as they looked at the linkages between between social media and the decline in democracy that has been a pattern in last decade or so. To do so they reviewed over 500 academic articles compiling information on the relationship social media and democratic decline.

From: A systematic review of worldwide causal and correlational evidence on digital media and democracya, Combinations of variables in the sample: digital media (A), political variables (B) and content features such as selective exposure or misinformation (C). Numbers in brackets count articles in our sample that measure an association between variables. b, Geographic distribution of articles that reported site of data collection. c,d, Distribution of measurements (counted separately whenever one article reported several variables) over combinations of outcome variables and methods (c) and over combinations of outcome variables and digital media variables (d).

The data illustrates some relationships between social media and declining trust in institutions but also relationships with participation rates and polarization. The researchers went on to explore whether the factor has positive or negatively reinforcing impact on democratic norms.

Directions of associations are reported for various political variables (see Fig. 1d for a breakdown). Insets show examples of the distribution of associations with trust, news exposure, polarization and network homophily over the different digital media variables with which they were associated.

What the researchers found is that there is a relationship between social media, decline in trust in institutions and non-democratic views. In my opinion some of this has to due with how social media is now seen as a trusted place for news by many.

PEW research has done a number of polls and studies on the impact of social media on democracy. Just last week (December 6th) a multi-nation study on the perceptions of social media and democracy found

There are a wide range of demographic factors that shape this perception with younger people tending to see social media as more positive and being a driving source of information, while older adults tend to see it more negatively.

The 2023 Edelman Trust Barometer was just released which found that Trust with institutions has reached an all time low in over a dozen countries. The research was produced by the Edelman Trust Institute and consisted of 30-minute online interviews conducted between November 1st and November 28th, 2022. The 2023 Edelman Trust Barometer online survey sampled more than 32,000 respondents across 28 countries.

The same survey found four factors drive polarization – social media was one of the top drivers and the linkage with finding truth and trust both in information from social media as well as the media they view through social media.

View Details

It’s Christmas at Mean, Median and Moose! That means we’ll stretch ourselves to finding twelve Christmas related data sets. Let’s see what we managed to find this year! If you want to see 2021’s charts you can find them here.

iTunes ChartsLet’s start with looking at the top 10 ranked movies on iTunes over 2020 to 2022. 2021 was a quieter year than 2020 when it came to Christmas movies on iTunes. We’re looking at a bounce back this year though with Benedict Cumberbach’s The Grinch coming in strong as the number one ranking. This data from Flixpatrol. You can access an interactive version of this graph here.

Last Minute GiftsAlways find yourself scrambling for a last minute gift over the holidays? The good news is, you’re not alone. Google Trends web search data from 2019 to 2021 shows just how common this is for Canadians. It ranks interest over time for a search term, with this meaning: “Numbers represent search interest relative to the highest point on the chart for the given region and time. A value of 100 is the peak popularity for the term. A value of 50 means that the term is half as popular. A score of 0 means there was not enough data for this term.”

December ranks consistently as the month with the highest interest in searching for last minute gift ideas, with interest in the week before Christmas increasing year-over-year with a score of 39 in 2019, 52 in 2020, and 100 in 2021. Of the five provinces with data, funny enough, Nova Scotians take the cake with the most interest in this search term, with an interest score of 100, followed by BC at 88, Ontario at 72, Alberta at 38, and Quebec at 16. And the city with the most gift procrastination? Vancouver, which is trailed by Ottawa at a score of 86, Edmonton at 73, Winnipeg at 70, and Toronto at 51.

Polar Bear PlungeA tradition for some more adventurous individuals new years day often has them jumping into a body of water! One of the oldest Polar Bear Plunges in Canada is in Vancouver which stretches back to the 1920s. Since 1976 data is available for how many registered participants were involved as well as the water temperature. Each year a trophy is given out to the first man and women to reach the 100M buoy off shore.

The large spike in 2020 in registrations isn’t clear but this plunge is one of many across the region it is possible they expanded registration or attached a new incentive/sponsor. This made me interested in whether the plunge was attracting more people from the Vancouver area proportionally to the region’s populations.

The charts look very similar to one another but the relatively flat second chart shows that there is some proportional growth as the plunge in attendance has kept pace with the region’s population growth.

Building a Mathematically Perfect SnowmanThe folks at OmniCalculator make a lot of nifty online calculators, including one that helps you figure out the exact size of snowman you can build in your yard given the depth of snowfall, how much of the snow you can use, and the size of your yard, or the amount of snow you need to build a snowman of a given size. It’s a bit of nerdy Christmas fun with other fun features like calculating your snowman’s life expectancy and the weight of each of the balls in the snowman. There’s even a little discussion of how to get the most aesthetically pleasing snowman by adhering to the golden ratio. It’s great fun and you should check it out!

The Canadian City Christmas Snowman IndexThe snowman calculator is so much fun that we developed an index to identify the Christmas Snowman Capital of Canada (a gorgeous tourism hashtag if we ever saw one).

Based on the information put forward in the calculator, the freezing point is the ideal temperature for a snowman and obviously you need to have some snow on the ground to build one, so our index counts the number of days in December that the high temperature is between -2 and +2 Celcius with at least some precipitation. The more ideal snowman days in December, the higher your city ranks.

It’s pretty labour-intensive to aggregate Canadian weather data because it tends to be collected by weather stations and there are a lot of individual weather stations in Canada. Fortunately, Statistics Canada publishes temperature and precipitation data in aggregate for 13 major cities in Canada going back to 1940, so we used that as the basis for our work.

St. John’s is the Christmas Snowman Capital of Canada, with a total of 654 ideal snowman days in December since 1940. Toronto is a distant second at 355, leading a cluster of pretty good snow cities that include Halifax, Ottawa, Montreal and Moncton. Prairie and Northern cities fare poorly in this index with Saskatoon at the bottom of the rankings having only 14 ideal snowman days in December since 1940.

Average Price for Egg Nog Ingredients -JohnIt’s getting more expensive to make Christmas’ strangest and most delicious drink, but it’s not the sugar and instead the proteins. Interestingly, while milk has been somewhat stable except for recent history, eggs have had a steady increase that started even before the pandemic.

Non-Resident Antarctica VisitorsEver wonder how many holiday visitors we get from the southernmost continent of the world? Given Antarticaca’s population of between only 1,000 and 5,000 seasonally, surprisingly, according to Table 24-10-0050-01 from Statistics Canada, we’ve gotten quite a few!

| Month/Year | Non-Resident Visitors | | December 2017 | 0 | | December 2018 | 75 | | December 2019 | 38 | | December 2020 | 5 | | December 2021 | 25 |

Non-Resident Visitors Entering Canada from Antarctica, December 2017-2021Visits from our southernmost continent peaked in 2018 at 75, after no visits at all in 2017! 38 Antarcticans managed to visit in December 2019, and despite COVID travel restrictions, 5 Antarticans visited us in December 2020 and 25 in December 2021. Where in Canada are these Antarticans headed to, you might ask?

Destination of Non-Resident Visitors Entering Canada from Antarctica, December 2018

Looking at the peak travel year in December 2018, 42 of the 75 Antarctica visitors were headed to Quebec, with another 22 headed to BC, 5 to Ontario, 3 to Nova Scotia, 2 to Saskatchewan, and 1 to Manitoba.

Wikipedia Articles to do with both “Christmas” and “Canada”We wanted to know the nature of Canadian Christmas wikipedia articles. There is no article for “Christmas in Canada”, but there is a South Park episode with a 1,000 word write up called “It’s Christmas in Canada”. The actual content specifically related to Canadian traditions is relegated to a subsection of the “Observance of Christmas by Country” article. That subsection describing Canada contains 440 words.

What about the rest of the 27,827 articles containing both the words “Christmas” and “Canada”? The word counts here follow the long tail common in content generated on the internet.

Note that we’re only looking at the top 10,000 “most relevant” articles here because Wikipedia (somewhat understandably) won’t let you page past that result.

So, what were the longest articles containing both “Christmas” and “Canada” – the ones populating the furthest reaches on that long tail? Here’s the list of the top 20 in word counts:

| Title | Words | | Timeline of the COVID-19 pandemic in the United Kingdom (July–December 2021) | 39752 | | Culture of the United Kingdom | 33472 | | Canada convoy protest | 32659 | | Timeline of the COVID-19 pandemic in the United Kingdom (July–December 2020) | 32001 | | Platinum Jubilee of Elizabeth II (category Monarchy in Canada) | 31707 | | Elvis Presley | 27467 | | List of United States Christmas television episodes | 27432 | | Observance of Christmas by country | 26565 | | Culture of England (section Celebration of Christmas) | 26535 | | Drake (musician) (redirect from Drake (Canadian musician)) | 25881 | | List of Jews in sports | 25771 | | Carrie Underwood (category Canadian Country Music Association Song of the Year winners) | 24765 | | Mariah Carey (redirect from Mariah Carey’s Merriest Christmas) | 24484 | | 2022 FIFA World Cup (redirect from Christmas World Cup) | 24190 | | Charles III (redirect from Charles III of Canada) | 24171 | | The Beach Boys (section British Invasion Shut Down All Summer Long and Christmas Album) | 24004 | | List of Downton Abbey characters | 23398 | | Justin Bieber (redirect from JB (Canadian singer)) | 23338 | | Spice Girls | 23110 | | Dolores O’Riordan (category Irish expatriates in Canada) | 22809 |

You can see that many of these articles don’t have much to do with either Christmas or Canada directly. Here’s a chart showing the breakdown of mentions for each of them:

Christmas Bonuses in CanadaIn 2019, ADP which is a national HR and Payroll firm in Canada commissioned a survey by Leger asking employees about their preferences for work related to holiday bonuses and activities.

ADP Canada Study on Holiday Rewards (CNW Group/ADP Canada Co.)Across Canada – Atlantic Canada was most likely to get a monetary bonus; BC was most likely to get additional time/days off; where as the Saskatchewn, Manitoba and Alberta were most likely to not expect anything from their employers and have to come into the office.

Retail Sales Christmas BumpMany retailers count on high Christmas sales to make their year. We wanted to see which retail categories get the most benefit from the holiday season, so we took the Statistics Canada data for monthly retail sales, categorized using the North American Product Classification System (NAPCS) and compared the annual average sales to sales in December for 2019, 2020 and 2021 by measuring the percentage increase in sales. The measurement specifically is the change (December – the average) divided by the average, so a value of 168 means that sales were more than two and a half times the monthly average.

We produced a heat map to show these values over time, which you can see below. The top categories over the past three years were skiing and snowboarding equipment which sells 168% faster than of the normal pace in December, which is probably a seasonal rather than holiday impact, toys and games excluding video game hardware and software which do an extra 155% of average monthly sales in December, Video game consoles and Men’s Sleepwear at 128% and 127% respectively, followed by video game software and fine jewelry at 126%.

At the other end of the spectrum is a bunch of seasonal stuff that sells significantly less in December than the annual average: outdoor home furniture, home and garden supplies, motor homes and campers, lawn and garden equipment and boats, which points to a flaw in our analysis: the size of the effect of seasonal sales may drown out the effect of gift purchases across many categories. It’s all a bit of holiday fun so we won’t worry too much about that! This graph shows you just a few results. To see the entire chart check out Doug’s Observable notebook on the topic.

Sales of Sleepwear, Underwear, Lingerie, and HosieryWith the holiday season comes an explosion of store displays of pajamas and the expectation of receiving a cozy onesie or a pack of underwear under the tree. But do Canadians stock up on their PJs and undies all year round, or is the holiday season the time to buy? Looking at Table 20-10-0017-01 from Statistics Canada, with monthly data from the Retail Commodity Survey, there’s a clear pattern of getting these essentials right around the holidays!

Women spend a lot more on their lingerie, sleepwear, and underwear overall, with sales totaling over 2 billion dollars in 2021, while men spent just over 1 billion dollars on their sleepwear, underwear, and hosiery in 2021. Sales peaked in December each year for both women and men, with average sales of $239,131,000 in December 2019, 2020, and 2021 for women and $182,899,000 for men. Looking at the lowest sales between 2019-2021, we see what is likely the COVID shutdowns coming into play as both women and men’s sales dropped to their lowest point between March 2020 and May 2020, with women’s sales at only $61,432,000 in April 2020 and men’s sales at only $25,054,000. Safe to safe Canadians do love their annual holiday PJ and underwear restock!

Air Travel over the HolidaysThe Canadian Air Transport Security Authority is a Crown corporation responsible for securing specific elements of the air transportation system – from passenger and baggage screening to screening airport workers. They track the number of people who are screened at airports on a daily basis on their website. They publish this data for the 8 and 17 largest airports in Canada.

You can clearly see in the 2020 and 2021 data the impacts of the COVID-19 pandemic. You can also see the recovery in air screenings through 2022 as people began to travel again. When you zoom in on December to look at the holiday travel season you can see in more detail the travel days and how the ebb in flow during the month.

Based on the 2019 data by the second week of January air travel tends to have a sharp decline as people return from holidays.

View Details

This month we are looking at data on pandemic impacts in Canada. Shifting Remote – Productivity and Preferences Perhaps one of the most talked about effects of the COVID-19 pandemic has been the overwhelming and almost overnight shift to remote work that occurred at the beginning of the pandemic, forced by public health measures andContinue reading "Retirements, Return to office and RoundTrips"

View Details

This month on Mean, Median and Moose we look at income in Canada through four lenses: choosing you partner, measures of low income, what people who make different amounts spend their money on, and taxes. Income between married and common law partners Statistics Canada collected data in the 2016 census on the differences in incomeContinue reading "Money, Marriage, and Millionaires"

View Details

This month on Mean, Median, and Moose, we’ll look at the Ontario election in three different ways: fundraising, districts, and trolls on social media. Political financial contributions Elections Ontario collects and reports on all contributions to political parties and campaigns in Ontario. You can see the data at this link. We decided to take aContinue reading "Donations, Divisions, and Disrespect"

View Details

In this month’s Mean, Median, and Moose we explore the gig economy in Canada. Measuring the Gig Economy in Canada There has been little research into the extent of the gig economy in Canada. Statistics Canada asked some questions as part of the October 2016 Labour Force Survey – whether a respondent had offered orContinue reading "Instagram, Influencers, and the Informal Economy"

View Details

This month we’ll be talking about protest movements in Canada. The inspiration for this month’s topic was the “Freedom Convoy” protests that took place from late January to mid-February. We wanted to see how it compared to other protests in Canada and around the world. Freedom Convoy Timelines During the protests it seemed like theContinue reading "Rallies, Rebels and Revolutions"

View Details

In today’s podcast we’ll look at housing data in Canada and what it costs to put a roof over your head. Available Data Roundup CMHC has a web portal that consolidates all of their data offerings in one place. Their “Housing in Canada Online” (HiCO) portal looks like it contains some quite interesting data butContinue reading "Housing, Homes and Huts"

View Details

Today’s post is all about Christmas! We have twelve holiday related datasets and some great graphs to go with them. It’s our Christmas present to you! Let’s get to it. Pumpkin Spice Ingredient Imports Pumpkin spice is so popular in the Christmas season that it’s become a bit of a cliche. While pumpkin spice hasContinue reading "Twelve Datasets of Christmas"

View Details

This month we talk a look at provincial and national referendums in Canada. For this month’s podcast we compiled a list of all the provincial and national referendums in Canada that we could find. You would think a list like this would be easy to find, but it wasn’t. We managed to find some resourcesContinue reading "Reviewing and Ranking Referendums"

View Details

In this post we have a grab-bag of election related topics to go through. The first two deal with data on the results of the 2021 election. The third topic is about indigenous representation in the house of commons. And the last topic is on methods for comparing data on voting polls between elections. CombiningContinue reading "Election Data Roundup"

View Details

In today’s post we’ll look at Ontario city population growth projections and then talk about comparing financial data between cities. Ministry of Finance Population Projections Our post is city themed, but we’re going to cheat a little by comparing census divisions rather than municipalities but the population growth numbers coming out of the Ministry ofContinue reading "Comparing Ontario Cities"

View Details

Today on Mean, Median, and Moose, we look at eight legged blood suckers, phone company cell coverage, self reported bike accidents, and community edited maps! What do these data sets have in common? They’re all involving data collected by volunteers in the general public. eTick eTick is a collaboration of a variety of universities, publicContinue reading "Ticks, Telecoms, and Trails"

View Details

This month in Mean, Median, and Moose, we take a look at Canadian’s close encounters in their spare time. From body checking hockey players, to bluff charging bears. From playing space invaders to sighting them, we’ve got you covered. Leisure Infrastructure Infrastructure matters when it comes to leisure activities. If there is no bike trail,Continue reading "Arenas, Aliens, and Animal Crossing"

View Details

Today on Mean, Median, and Moose we’re talking about public consultations! Keep reading for some data on those. Take a listen to the podcast for a great interview with Nader Shureih of Environics Analytics. Also, if you want to know how we build some of our maps, you can watch a tutorial on DeckGL hereContinue reading "Public Consultations in Canada"

View Details

This Month’s Podcast. Also Available on Spreaker This month we’re taking a look at criminal justice data in Canada. Justice is an area that has long been subject to statistical methods. Police statistics on crime were first published in Canada in 1921. These statistics were spotty and not generally comparable from year to year until the Uniform Crime Reporting System came into service in 1962. This long history and high level of public interest in crime and justice means that there is a wealth of information available for analysis. We took a look at the information available from Statistics Canada and developed some visualizations of data we found interesting. Before we get to that, let’s take a look at the most significant public ranking of Canadian municipalities related to Crime and Justice.

Macleans has a ranking for the most dangerous places in Canada. It is a good interactive listing tool that enables people to sort by type of crime, crime severity index which come from Statistics Canada, and a 5 year trend in crime. A clear link to their full methodology is available on their website. One of the interesting elements is that they rank as “most dangerous” which is somewhat contrary to how municipalities or other groups would often rank the “safest”. This could be to drive clicks to their website. The rankings break out a wide range of crimes, from violent and severe crimes, to fraud to firearms to impaired driving to various drug charges.

There are a number of interesting findings in this data.

First it appears that of the top 20 most “dangerous communities” all of them are West of Ontario based on the “all crime” ranking. The average Crime Severity Index score in Canada is 75.01. North Battleford Saskatchewan scored a CSI 366. On the other end, LaSalle Ontario which is a suburb of Windsor where we are all situated scored a 15.

There are a couple of outliers regarding youth crime rankings. Granby Quebec was ranked 14th in youth crime but 46 on the Crime Severity Index while Collingwood scores a 43 in Crime severity index but 18th most youth crime. Swift Current ranks 6th in youth crime while having the CSI of 83. Most of the other communities in the top 20 have a CSI over 100. For impared driving Whistler BC ranks number one so obviously people are having a lot of fun going to or from the ski hills.

The homicide capital of Canada is Thompson Manitoba which also has the second highest CSI score overall. I would point out that for some of the smaller or more rural areas, due to certain criminal offenses being rare or having small populations you get some skewed data. Thompson is the Cocaine production/trafficking with 92 incidence, Toronto had 504 but had a trafficking per population of 17.05 compared to Thompsons’ 650.

Indigenous Populations It has been widely reported that BIPOC populations are over represented in the criminal justice system, we can’t do this topic the justice it deserves in a segment on our show and it may be a future show that we comeback and discuss BIPOC data. Just this week Windsor Police Services for the first time released data on the use of force by racialized groups which was reported on our local CBC News. One snapshot that we found was a Statistics Canada research project completed in 2019 that compared crime, incarceration etc. rates between 2007/08 and 2017/18 for individuals that identified as indigenous.

Percentage of Adults in Custody Identifying as Indigenous Justice Department’s State of the Criminal Justice System Dashboard The Justice Department manages a “State of the Criminal Justice System Dashboard”, which shows high-level outcomes of the criminal justice system in Canada and their performance indicators. The nine outcomes are Safe Communities, Fair and Accessible, Confidence in the System, Operation of the System, Resolution Mechanisms, Correctional Supervision, Victims and Survivors, Indigenous People, and Marginalized and Vulnerable People. You can also see more detailed information, including an explanation of what the indicator is, why it is important, its limitations, and its geographical coverage. Data can also be exported to a spreadsheet, which of course makes it much easier to work with.

Some of the indicators can be compared to make them more useful, so it’s a bit unfortunate that there isn’t a tool within the dashboard to easily compare indicators, but perhaps that’s something that will be added in the future! For example, in the Correctional Supervision outcome, there is an indicator called “Mental health services in federal corrections” which tells you the percentage of individuals under federal correctional supervision who identified as having a mental health need and did receive mental health services in response to this need. However, this doesn’t really tell you much on its own as there’s no hard number in this indicator of how many individuals did identify a need. Under the Marginalized and Vulnerable People outcome, there is an indicator called “Mental health needs in federal corrections” that does identify how many individuals indicated they had a mental health need. So by comparing the two numbers for 2016/2017, for example, then we can see that of the 1,106 individuals who identified a mental health need, 929 received mental health services, leaving 177 individuals who did not receive mental health services for their identified need.

This is a relatively transparent dashboard otherwise, with limitations of the data even identified for each indicator along with accompanying resources. You can see the Justice Department is trying to make this usable for the average person. There are, of course, always questions around methodology of how outcomes and indicators were selected. The dashboard preamble states these were “identified through extensive research and feedback from multi-phased consultations with criminal justice system partners, stakeholders, experts and other Canadians”, which doesn’t tell us much about the methodology used in this research. There is a “Data Development” tab that tells us a whopping thirty-eight additional indicators or areas have been identified as important for monitoring and reporting on performance, but they are not yet included. This does demonstrate a definite data gap in the dashboard. Nonetheless, it is still a useful tool and a good starting point for making this data user-friendly.

Statistics Canada Justice Data Statistics Canada hosts a portal for crime and justice data, which tracks a few national key indicators and hosts 858 datasets on courts, crime, police services and victimization. A lot of this data is interesting and valuable but we found only a few data sets that met our criteria for this month’s podcast: data published over a long period of time that is available at a fairly low level of aggregation.

A lot of this data is published only at the national or provincial level, covers a relatively short period of time, or is presented to the public in the form of infographics and dashboards. Some data that would be really valuable to have are spread across data sets covering different time series. For example, spending by police departments is available in detail for current and recent years, but only rolled up to the national level.

One dataset that did meet our criteria for making an interesting visualization was Table 35-10-0077-01, “Police personnel and selected crime statistics, municipal police services.” It goes back to 2000 and provides a set of comparable statistics for police services across the country. We used Python and the petl library to process the data as published into a format more friendly for visualization, and then built a tool to generate charts comparing selected municipalities. You can find the Python source here and try out the visualization tool here. We’ve included a sample comparing selected Ontario municipalities in this post.

According to Statistics Canada, “The Crime Severity Index (CSI) measures changes in the level of severity of crime in Canada from year to year. In the index, all crimes are assigned a weight based on their seriousness. The level of seriousness is based on actual sentences handed down by the courts in all provinces and territories.” This is an improvement on the traditional “crime rate” calculation which expressed a simple count of all criminal incidents reported to and by police divided by the population. No matter how serious the offence, they are all weighted the same in a crime rate calculation. The Crime Severity Index is intended to provide a clearer view of the seriousness of crime in a given jurisdiction.

Crime Severity Index Over Time for a Select Group of Jurisdictions – Check out the visualization tool here to see others Police officers per 100,000 population is a measure used internationally and within countries to indicate the relative police presence in the population.

Police Offices per 100k Population for a Select Group of Jurisdictions – Check out the visualization tool here to see others The “clearance rate” is calculated by dividing the number of crimes that result in a charge being laid (“cleared”) divided by the total number of crimes recorded. In Canada, the source of these statistics is the Uniform Crime Reporting Survey performed by Statistics Canada. This rate is weighted in a similar way to the Crime Severity Index: more serious offences are assigned a higher weight than less serious offences. There are some interesting criticisms of this measure as an indicator – that it can incentivize police to lay charges whether they have solved a crime or not, and that the behaviour of criminals influences clearance rates which obscures them as a measure of the effectiveness of the criminal justice system.

Weighted Clearance Rate for a Select Group of Jurisdictions – Check out the visualization tool here to see others The Statistics Canada table reports a number of metrics related to officers and civilian staff, including the number of officers who are women. This allows us to chart the percentage of women officers in each police service.

Proportion of Women Officers for a Select Group of Jurisdictions – Check out the visualization tool here to see others If you’re interested in a study that delves into representation of women and indiginous people within police forces in Canada as a whole, Statistics Canada published a review of police resources in 2019. There are some interesting statistics and graphs in that paper. Here’s a few of our favorites:

That shows how we had a period of policy funding per capita increases in the decade between 1999 and 2009. Since 2009 we’ve levelled off on per capita police funding in real terms. How about police officer salaries? It looks like the RCMP tops the list for those but it’s civilian workforce is paid the least.

It’s interesting how the OPP civilian salaries are nearly on par with their officers and that is not the case in other forces. Finally, similarly to other areas of the workforce, police forces are ageing with the youngest force being independent First Nations forces and the oldest being the OPP.

Statistics Canada – Court Workload Data That’s all about what crimes are occurring and how policing is conducted. What about after charges are laid? We looked into visualizing the Canada court workload indicators and built another tool to look at those indicators. Just like the graphs above, this uses Vega Lite to create the charts and Observable to expose the code and make it interactive. You can choose the geographic regions, the offence category you’re interested in, and the workload statistic. Here’s the initiated cases for homicides for all the provinces (If you’re wondering about Quebec, that province’s data was only incorporated in 2015).

Cases Initiated for Homicides Across Canada’s provinces. To change the statistic, the offence, and/or the included provinces you can use the interactive tool located here Let’s look a little more closely at one offence – Fraud, for Canada’s provinces. The provinces with the most initiated cases are Alberta, Ontario, Quebec and BC. This makes sense given their populations, but you’ll notice the initiated cases do not perfectly reflect population – Alberta is over represented. It would be interesting to normalize this particular measure to the population of the province.

Cases Initiated for Fraud Across Canada’s provinces. To change the statistic, the offence, and/or the included provinces you can use the interactive tool located here Alberta’s caseload (the number of open cases) has also been growing at around the same time that initiated cases have increased – that is, more cases are in the queue.

Caseload for Fraud for Alberta, BC, Ontario, and Quebec. To change the statistic, the offence, and/or the included provinces you can use the interactive tool located here You can also take a look at the number of cases currently in progress delineated by their age. Even though Alberta’s caseload has been increasing in this area, the case age does not appear to differ greatly from the other provinces. Here’s the largest group (aged 6 to 12 months) for example showing all provinces between 20 and 26% of cases being this old.

Caseload for Fraud for Alberta, BC, Ontario, and Quebec where cases are 6-12 months old. To change the statistic, the offence, and/or the included provinces you can use the interactive tool located here A small number of cases are older than five years (thank goodness).

Even so, median, which aggregates over the entire set, shows a high case age for Alberta, but also for Quebec.

You can go through the offences and provinces in this way and get an understanding of what the caseloads and wait times are like for various offences. Happy digging!

View Details

This Month’s Podcast. Also Available on Spreaker Canada is home to over 170,000 not-for-profits. Just over half of these are registered charities. The vast majority of them are small – less than 10% of charities have 10 or more full time staff, and 79% of charities have less than $500,000.00 per year in funding.

The Canadian Senate produced a report on the charitable sector that is worth a look, and Statistics Canada published a survey on board diversity and make-up.

From the perspective of open data there are two main challenges in the nonprofit sector. First, we have little data on the sector’s labour market and economic impact. Imagine Canada reports that the charitable and nonprofit sector contributes roughly 8% of Canadian GDP in a typical year, which is more than retail and close to the value of the mining, oil and gas sector. Two million Canadians work in the sector and more than 13 million people volunteer with charities and nonprofits. Beyond that, the data from the sector on its makeup and impact is fairly limited.

There have been some notable efforts to change that in recent years. Back in 2015, the Ontario Nonprofit Network released the report Towards a Data Strategy for the Nonprofit Sector, which identifies what they call the key principles that should guide a nonprofit data strategy:

Effective use: Nonprofits should put data to effective use to serve their communities – not just collect it, but proactively use it.
Responsible use: Data should be created, collected and accessed responsibly and ethically, with attention to power dynamics that could mar the use of data and with respect to the privacy and safety of those involved.
For public benefit (not for profit): Nonprofits and governments should be committed and able to access data for public benefit use.

For ONN, the four essential components of a successful data strategy are:

Standards
Policy
Skills & Resources
Leadership

These components are necessary for any data strategy, and the charitable and nonprofit sector are no different.

Powered By Data is an organization funded by MakeWay whose mission is to maximize the availability and impact of data for public good. They’ve done some work publishing data about grants with MakeWay and the Ontario Trillium Foundation which we used in producing this month’s episode. Listen to the podcast to hear our interview with Michael Lenczner and Ben McNamee of Powered By Data.

Federal Grants The Federal Government Open Data portal has a data set listed that consolidates all the Grants and Contributions reports submitted by federal institutions for proactive disclosure. We decided to take a look at the grantees and departments, attempting to remove the subnational governments, in order to characterize NGO and private sector grants.

Here were the top grant recipients for everyone on that list that spans 2005-2020 with a couple of grants also listed from 2004.

Code for this visualization including SQL excepts can be found here The Canada Foundation for Innovation is an arm’s-length agency of the federal government that is responsible for a lot of the investment in technological equipment and facilities. Extranational organizations such as the world food program and world bank are also heavily featured. Interestingly Ryerson University and Norquest college also received a large amount of grants. ESL language training for immigrants looks to be the reason why. SUCCESS is another organization tied to providing new immigrants services. The now famous WE Charity Foundation grants are also listed, even though they were never paid in the end. If you are interested in some of the others on the list or want to know more about these grants, the federal government provides a searchable database for that too.

That’s just the grant amounts. What about the total number of grants? Here’s the top 20 for that:

Code for this visualization including SQL excepts can be found here A lot of educational institutions here. Most of this is related to grants that professors apply to. These are smaller grants but there’s a large number of them.

How about these together? If we take the value of grants and divide by the number of grants we can get an idea where the largest single grants were going to.

Code for this visualization including SQL excepts can be found here What about the value of grants given by each department? If you do that you can get a feeling for the changes in political priorities as time goes on. It’s interesting seeing the intensification of global affairs and immigration related grants in 2017.

Code for this visualization including SQL excepts can be found here Quite a bit of interest in immigration and global programs in 2016 and 2017, but the grant funding did not seem to be sustained. To try and gain an understanding of the nature of the grants that happened during spikes like those, we included a small tool at the site where the data and code for these visualizations is located. You can select the department in question to see a simple line graph of grant value:

And also see a trellised bar chart showing per-year distribution of total grant value and number of grants for various grant sizes.

Looks like that period of investment into immigration and refugee programs was concentrated in a large number of grants valued between one and ten million dollars.

MakeWay Grants As we mentioned earlier, Powered By Data worked with MakeWay to create a portal that provides data on the grants MakeWay issues. It’s got a nice user interface that you can use to sort, group and summarize their grants interactively, or you can download the information in CSV format for manipulation. We used the CSV to take a closer look and generate a few graphs based on 2020 grant data.

This chart shows the distribution of all grants in Canadian provinces and territories. MakeWay distributed over $4 million in 2020, mostly to charities BC and Ontario. This data was a little bit of extra effort to generate as the charity’s location is not provided in the CSV. We used Google’s geolocation tool to identify the location of the organizations by name, which covered about 95% of the entities identified and manually looked up the rest on the CanadaHelps index of charities.

That’s the raw distribution of money and here is the average size of a grant broken down by province and territory. Larger average grants in Ontario and Newfoundland.

Ontario Trillium Fund Grants The Ontario Trillium Foundation also has an open data portal, providing information about the grants that they distribute in Ontario. They publish CSV files of their grants since 1999, and a geographic concordance file that maps Trillium catchment areas to Census Districts, which gave us a chance to build a nice digital map showing the per-capita investment in different regions in Ontario. We looked at the distribution of money in 2019, since we felt the oversized allocation of money through Trillium in 2020 might be a bit skewed compared to a more “typical” year.

How Can We Rank Charities? Every year, Maclean’s publishes a top 100 list of Canada’s “best” charities. Charities included in Maclean’s Top 100 list must:

Have a Charity Intelligence report updated for the 2016 tax year or more recent
Have $2 million or more in annual revenue
Be thought of as charities by most Canadians. For example, universities are registered charities that accept donations, but we exclude them from the ranking.

In 2020, the top charity on the list was the Calgary Food Bank. Maclean’s assesses charities based on the following criteria:

Financial metrics (60 percent)

Overhead ratio: 15 percent. The proportion of a charity’s annual revenue that goes to administrative costs, awarding more points to leaner charities. The exception is when a charity reports spending less than 2 percent of its revenue on overhead, less than what is considered enough to operate productively.
Fundraising ratio: 15 percent. Full points go to charities that keep the amount of every donated dollar spent on fundraising under 15 cents, with partial points awarded up to a maximum of 35 cents. Note: Not all charities separate administrative and fundraising spending in their financial statements – in these cases, the percentage of total revenue going to those two types of costs combined is used.
Charity reserves: 15 percent. More points to charities that keep donor money circulating, with full marks going to those that keep savings accounts under the amount that would be required to fund program expenses for three years.
Compensation of highest-paid employee: 15 percent. Points are taken from charities that pay their executive directors less than $80,000 a year, regardless of size, or overpay them by more than $100,000 compared to other charities with similar operating costs

Transparency (40 percent)

Ease of access to financial statements: 10 points. Full points go to charities that publish audited financial statements on their websites covering at least two of the most recent fiscal years.
Results transparency: 30 points. Points for charities that clearly demonstrate what they are doing with donors’ money.

Mean, Median, and Moose: Recent Episodes

None