CAJ conference, May 28, 2022
David McKie
Canada’s National Observer
Co-author of The Data Journalist: Getting the Story;
Your Right To Know: How to Use The Law to Get government Secrets;
Digging Deeper: 3rd edition
davidmckiec@gmail.com
Phone: (C) 613-290-7380
Valérie Ouellet (She/Her)
Senior data journalist, CBC News Investigative Unit
Part-Time Lecturer, Toronto Metropolitan University
Mentor, CAJ Mentorship Program
valerie.ouellet@cbc.ca
416-919-7820
CAJ Data Journalism finalists – 2021
Valérie Ouellet, Naël Shiab, Sylvène Gilchrist
White men make up a third of Canada’s population but a majority of MPs — here’s why
CBC News Investigative Unit / Radio-Canada
Analysis of candidacies during Canadian federal elections
https://ici.radio-canada.ca/info/codesource/code-ouvert/2021/elections/candidatures/analysis.nb.html
Dataset on the Demographics of Canadian Federal Election Candidates (2008-2019)
https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/MI5XQ6
Sample candidates dataset
John Rieti, Angelina King, Katie Swyers, James Wattie
State of Our Schools
CBC News – Toronto
Sample schools dataset
Robyn Doolittle, Chen Wang, Tavia Grant
The Power Gap
The Globe and Mail
Sample salaries dataset
Tai Huynh
COVID-19 infection and vaccinations
The Local, Toronto
Kenyon Wallace, Ed Tubb, Brendan Kennedy
Making sense of COVID-19 data
Toronto Star
Toronto Star long-term-care analysis.xlsx
Toronto Star long-term-care analysis – Google Sheets
2021 Philip Meyer Award winners
The Wall Street Journal – How Tik Tok Figures You Out (first place)
Inside TikTok’s Algorithm: A WSJ Video Investigation – WSJ
The Wall Street Journal’s application form with story links and explanations of methodology
QUESTIONNAIRE_TikTok_WSJ.pdf
http://www.davidmckie.com/PRIMARY_TikTok_WSJ.pdf
The Palm Beach Post – ProPublica – Black Snow: Big Sugar’s Burning Problem (second place)
http://www.davidmckie.com/PBP_PP_BlackSnow_MeyerQuestionnaire.pdf
PBP_PP_BlackSnow_MeyerPrimaryMaterials.pdf
Asbury Park Press and ProPublica – “Gilded Badges: How New Jersey Cops Profit From Police Unions and Avoid Accountability” (third place)
APP_PP_GildedBadges_MeyerQuestionnaire.pdf
APP_PP_GildedBadges_MeyerPrimaryMaterials.pdf
Sigma Award winners 2022
The Sigma Awards Projects Database
https://sigmaawards.org/projects/
Government open-data portals
Federal
Open Government – Proactive disclosure and access to information
https://open.canada.ca/en
Statistics Canada tables
https://www150.statcan.gc.ca/n1/en/type/data
Canadian Institute for Health Information
https://www.cihi.ca/en
Elections Canada
https://www.elections.ca/home.aspx
National Pollutant Release Inventory Dashboard
https://www.canada.ca/en/environment-climate-change/services/national-pollutant-release-inventory/tools-resources-data/all-year-dashboard.html
Explore National Pollutant Release Inventory data
https://www.canada.ca/en/environment-climate-change/services/national-pollutant-release-inventory/tools-resources-data/exploredata.html
Charities
https://apps.cra-arc.gc.ca/ebci/hacc/srch/pub/dsplyBscSrch?request_locale=en
Pardon and Record Suspension Outcomes
https://open.canada.ca/data/en/dataset/df25d622-ed43-4298-97b4-a835d8774d5f
Long-term drinking water advisories affecting water systems South of 60 not financially supported by the Government of Canada
https://www.sac-isc.gc.ca/eng/1516134315897/1533663683531
Provinces and territories
B.C.
http://www.openinfo.gov.bc.ca/ibc/index.page?
http://www.data.gov.bc.ca/
Alberta
http://data.alberta.ca/
Saskatchewan Open Data
http://www.opendatask.ca/
Manitoba
https://www.gov.mb.ca/openmb/index.html
Ontario
http://www.ontario.ca/government/open-data-ontario
Québec Ouvert (les données ouvertes pour le Québec)
http://quebecouvert.org/
New Brunswick
https://gnb.socrata.com/
Nova Scotia
https://data.novascotia.ca/
Newfoundland and Labrador
http://open.gov.nl.ca/
Prince Edward Island
https://www.princeedwardisland.ca/en/search/site/?f%5B0%5D=type%3Aservice&f%5B1%5D=field_service_topics%3A2237
Yukon
https://open.yukon.ca/data/
Northwest Territories
https://www.opennwt.ca/
Nunavut
https://open.canada.ca/data/en/dataset?keywords=Nunavut&page=1
Cities
Edmonton
http://data.edmonton.ca/
Halifax
https://www.halifax.ca/home/open-data
Fredericton
http://www.fredericton.ca/en/open-data
Mississauga
http://www.mississauga.ca/portal/residents/mississaugadata
Montreal
https://donnees-montreal-ca.translate.goog/?_x_tr_sl=fr&_x_tr_tl=en&_x_tr_hl=en&_x_tr_pto=sc
SANTÉ MONTRÉAL
https://santemontreal.qc.ca/en/public/coronavirus-covid-19/situation-of-the-coronavirus-covid-19-in-montreal/#c41383
Oakville
http://oakville.ca/data/
Saskatoon
http://opendata-saskatoon.cloudapp.net/
Ottawa
http://data.ottawa.ca/en/?page=1
Ottawa – Daily COVID-19 Dashboard
https://www.ottawapublichealth.ca/en/reports-research-and-statistics/daily-covid19-dashboard.aspx
Toronto
https://www.toronto.ca/city-government/data-research-maps/open-data/open-data-catalogue/
Toronto – COVID-19: Pandemic Data
https://www.toronto.ca/home/covid-19/covid-19-pandemic-data/
Saint John
http://catalogue-saintjohn.opendata.arcgis.com/
St. John’s
http://www.stjohns.ca/event/open-data
Vancouver
https://opendata.vancouver.ca/pages/home/
Winnipeg
https://data.winnipeg.ca/
http://www.mbcdp.ca/local-data-portals.html
Datasets
Ontario Public sector salary disclosure 2021: all sectors and seconded employees (uploaded to Google Sheets)
https://docs.google.com/spreadsheets/d/1CrWiT8pr0uIqDpVwwKM_-hDAJ2MWBqKtVhzE1Px9Ibk/edit#gid=2062994767
Ontario Public sector salary disclosure 2021: all sectors and seconded employees (spreadsheet format)
http://www.davidmckie.com/tbs-pssd-compendium-salary-disclosed-2021-en-utf-8-2022-03-25.ods
National Pollutant Release Inventory data releases to air (uploaded to Google Sheets)
https://docs.google.com/spreadsheets/d/1SzP33VAnvkZO7F-F_JtX77M7zWZ5Wtx1h_Rc4zyeeK4/edit#gid=0
National Pollutant Release Inventory data releases to air
http://www.davidmckie.com/NPRI-INRP_IndustryIndustrie_Air_2016-2020.csv
Charities
http://www.davidmckie.com/Charities_results_2022-05-24-22-00-57.zip
Charities (uploaded to Google Sheets)
https://docs.google.com/spreadsheets/d/1D3SW_7KXayzO4VqHBzACfUBKCQf3DgJoqj9GB45b3Vw/edit#gid=2034124438
Charities – 2013-May 17, 2022 (uploaded to Google Sheets)
https://docs.google.com/spreadsheets/d/14oBe1x4XUKcVfCyhvsnNvYIZ_1Ek-wJKxck82RtuaCA/edit#gid=2005274531
Tutorials
CAJ22 – SLIDES – DATA CLEANING MAGIC – Google Slides
CAJ22 – SLIDES – DATA CLEANING MAGIC – PowerPoint presentation
Tutorial: A quick video how-to session on using the agency’s data tables.
https://www.statcan.gc.ca/eng/sc/video/howto
For the tutorial explaining how to download the federal donation data please click here.
To obtain the pivot table tutorial, please click here.
Follow this tutorial which explains how to download, save and open the donations table. Upload the Conservative Party leadership contribution file to Google Sheet. Determine which candidate raised the most money and attracted the highest number of donors.
Scraping in Google Sheets
https://www.benlcollins.com/spreadsheets/google-sheet-web-scraper/
Examples of scraping in Google Sheets
https://docs.google.com/spreadsheets/d/1GdoYvgKmMmrVz1gGOWO0yEMGzKlOuTh1yVZM8sC70bo/edit#gid=0
Google Sheets guide in Excel worksheets
SpiceUpYourSheetLife_ExcelGoogleSheetTips_BenCollins.pdf
Create a pivot table in Google Sheets with the help of this tutorial.
To obtain the tutorial on calculating per cents, please click here.
To obtain the specialized functions tutorial, please click here.
Resources
The Data Journalist
https://www.oupcanada.com/catalog/9780199020065.html
2022 King’s Summer Data School
https://kingsjournalism.ca/data-journalism-resources/2022kingsdataschool/
The Wall Street Journal Guide to Information Graphics: The Do’s And Don’ts Of Presenting Data Facts And Figures
https://www.amazon.ca/Street-Journal-Guide-Information-Graphics/dp/0393347281
10 principles for data journalism in its second decade
https://medium.com/@paulbradshaw/10-principles-for-data-journalism-in-its-second-decade-3b45e08a4793
PDF to Excel conversion: Your ultimate guide to the best tools
https://www.computerworld.com/article/3660643/pdf-to-excel-conversion-ultimate-guide-best-tools.html