HUMBER DATA JOURNALISM TRAINING – Oct 29-30 and Nov 5-6

Share

David McKie
1-613-290-7380 (cell)
Email: davidmckiec@gmail.com

David and Fred are co-authors of
The Data Journalist: Getting the Story

 

David, Fred, Rob Cribb and Dean  Jobb are co-authors of
Digging Deeper Third Edition

David McKie and Jim Bronskill are co-authors of
Your Right To Know: How to Use the Law to
Get Government Secrets

The aim of the Excel, Tableau and ArcGIS training sessions is to allow you to become proficient enough to use these tools to help make sense out of simple data sets.

Excel

Though Google Sheets has many advantages, Excel is superior, especially for its ability to  handle larger datasets.

What you will learn:

  1. Finding and assessing open-data portals and Statistics Canada
  2. Downloading data from open-data portals
  3. Devising a checklist  when first looking at the data in Excel
  4. Filtering, sorting, simple functions
  5. Pivot table

Video Tutorials

Video one: downloading Excel files from the internet, sorting and filtering
Video two: creating pivot tables and using paste special to calculate rates
Video three: downloading Statistics Canada Tables

PDF Tutorials

Tutorial One: downloading Excel files from the internet, sorting and filtering

Tutorial Two: creating pivot tables and using paste special to calculate rates

Tutorial Three: Using Pivot Tables and Using Simple Functions to
Analyze Salary Data

Datasets that accompany tutorials

Downloading Excel from the internet, sorting, filtering, creating pivot tables and calculating rates

Using Pivot Tables and Using Simple Functions to Analyze Salary Data

Datasets for complimentary exercises

Excel file from the Canada Public Health Agency’s website

An Excel workbook containing COVID-19 infections by schools

Tableau

There are many visualization programs. For instance, DataWrapper has become the program of choice, especially now that Google’s Fusion Tables has been consigned to history’s dust bin. That being said, Tableau has  many advantages over many of its competitors, especially the ability to create interactive dashboards with just a few mouse-clicks.

What you will learn.

  1. How to import Excels files into Tableau.
  2. How to create simple charts.
  3. How to create an interactive dashboard.
  4. How to export and embed your visualization.

Video Tutorial

Video one: https://www.youtube.com/watch?v=Urk-XR05bg4&feature=youtu.be

PDF Tutorial

Tutorial one: Using Tableau to Visualize COVID_updated.pdf

Practice Data

Dataset for Tableau Practice Session: The Public Agency of Canada’s COVID-19 DATA with a cleaned up date column

Additional Tutorials

Getting data into Tableau:
https://www.youtube.com/watch?v=rrt8hyOaZRI

Working with data:
https://www.youtube.com/watch?v=a7nuVSsXWeM

Building dashboards:
https://www.youtube.com/watch?v=Es50LH0Pbiw

Datasets

One

ArcGIS Online

If the pandemic has taught us anything, it is the need to see where trends are happening. An event’s location conveys crucial information such as an outbreak in a low-income neighborhood or farm with temporary foreign workers, or how the vote counts are progressing in the hotly contested U.S. presidential election.

As long as you have data with geographic information such as longitude and latitude and a layer that contains boundaries such as neighborhoods, health or electoral districts, mapping becomes much easier than you think. As they say, sometimes seeing is believing.

We will be learning ArcGIS Online,  Esri Canada’s power  geospatial cloud-based platform, a space where you can make maps to visualize trends, collaborate and map.

What you will learn

  1. How to assess the value of datasets and shape files with geographic coordinates that can be mapped.
  2. How to import Excel tables and shape files.
  3. How to create colour schemes for your map.
  4. How to configure pop-up menus and use special markers like stick pins to identify key locations.
  5. How to export and embed your map.

PDF Tutorials

ArcGIS PDF tutorial_part one_updated.pdf

ArcGIS PDF Tutorial two – Creating thematic maps.pdf

Resources

Each of the first three sessions will be set up with an instructional video and a tutorial  that will be uploaded to this site a day in advance. Ideally, that should give you time to download and watch the tutorial and use the accompanying handout to repeat the steps you’ve just learned using live data. Our live sessions will cover much of the same ground, answer questions and explore further ways the data can be analysed. The ArcGIS Online tutorials will only be in PDF format.

Since we are still in the middle of a pandemic, we will be using related datasets from the European Centre for  Disease Prevention and Control, the Public Health Agency of Canada, and because you are in Toronto, the Toronto Public Health and the accompanying neighbourhood boundary file.

The Data Journalist is a textbook that I co-authored with University of King’s College professor Fred Vallance-Jones. The book is a handy reference for your library, which contains chapters on spreadsheets, data visualizations and mapping. If you want to ensure you have a copy in time for our sessions, you can download The Data Journalist e-book. You’ll need a Kobo device or reader (reader available free for AndroidiOS, Mac and PC).

Attached to each of the textbook’s chapter are tutorials  that contain many of the exercises we will be using.

Additional ArcGIS resources

Introduction to ArcGIS Online
https://storymaps.arcgis.com/stories/0bdde1dc046541b6af8b4d3a528491ca

Sharing your maps
https://storymaps.arcgis.com/stories/69d5030927764b23ba19cd0cb8b922d0

ArcGIS StoryMaps
https://storymaps.arcgis.com/stories/1fe82b574bcd4f07a9c803bb42067652

Learn ArcGIS
https://learn.arcgis.com/en/gallery/#?t=path&p=agol

ESRI Canada Media Program
https://cdn-mediaprogram.maps.arcgis.com/home/index.html

Getting Started with ArcGIS Developer Subscription
for Journalism Students
http://www.davidmckie.com/IntroArcGISOnline_media.pdf

ArcGIS Online Tutorial
http://www.davidmckie.com/ArcGISOnline_tutorial-FINAL.pdf

Open Data Portals

A list of open-data portals
http://www.davidmckie.com/open-data-portals/

Statistics Canada

To obtain the Statistics Canada tutorial on using data tables, please click here.

StatCan release schedules

Statistics Canada’s data tables

Canadian economic growth cools to 1.2% in August
https://www.cbc.ca/news/business/canada-economy-gdp-august-1.5783102

Gross domestic product by industry, August 2020
https://www150.statcan.gc.ca/n1/daily-quotidien/201030/dq201030a-eng.htm?HPA=1

Police-reported crime increases in 2019
https://www150.statcan.gc.ca/n1/daily-quotidien/201029/dq201029a-eng.htm?HPA=1

Incident-based crime statistics, by detailed violations, police services in Ontario
https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=3510018001

Statistics Canada said it will collect race-based crime data
https://www.rcinet.ca/en/2020/07/15/statistics-canada-said-it-will-collect-race-based-crime-data/

Population estimates, July 1, by census metropolitan area and census agglomeration, 2016 boundaries
https://www150.statcan.gc.ca/t1/tbl1/en/cv.action?pid=1710013501

Population estimates on July 1st, by age and sex
https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=1710000501

Canadian Statistical Geospatial Explorer
https://www150.statcan.gc.ca/n1/pub/71-607-x/2020010/71-607-x2020app-eng.htm

Census Profile, 2016 Census
https://www12.statcan.gc.ca/census-recensement/2016/dp-pd/prof/index.cfm?Lang=E

Population estimates, July 1, by census metropolitan area and census agglomeration, 2016 boundaries

2016 Census – Boundary files
https://www12.statcan.gc.ca/census-recensement/2011/geo/bound-limit/bound-limit-2016-eng.cfm

Gross Domestic Product by Industry – National (Monthly) (GDP)

Retail trade sales by province and territory (x 1,000)

Gross domestic product (GDP) at basic prices, by industry, monthly (x 1,000,000)

Statistics Canada’s incident-based crime statistics by detailed violations data

Salary Data

Alberta Public Disclosure of Salary and Severance

BC Public Sector Compensation Disclosures & Reports

Ontario Public Disclosure of Salary and Severance

Elections and political donations

UCP leads Alberta political parties following third quarter fundraising

Elections Alberta – Financial Disclosures

Conservatives top Liberals in Q3 fundraising

Elections Canada: Financial Returns Registered Parties

October 21, 2019 Federal Election Results 

Parliamentarians

Elections BC

Elections Saskatchewan

The National Post’s Follow the Money Project

 COVID-19 Datasets

Download today’s data on the geographic distribution of COVID-19 cases worldwide
https://www.ecdc.europa.eu/en/publications-data/download-todays-data-geographic-distribution-covid-19-cases-worldwide

Tableau COVID-19 hub
https://www.tableau.com/covid-19-coronavirus-data-resources

Coronavirus disease (COVID-19): Outbreak update
https://www.canada.ca/en/public-health/services/diseases/2019-novel-coronavirus-infection.html?topic=tilelink#a1

SANTÉ MONTRÉAL
https://santemontreal.qc.ca/en/public/coronavirus-covid-19/situation-of-the-coronavirus-covid-19-in-montreal/#c41383

COVID-19: Status of Cases in Toronto
https://www.toronto.ca/home/covid-19/covid-19-latest-city-of-toronto-news/covid-19-status-of-cases-in-toronto/

COVID-19 Articles

COVID-19 Research

ICES COVID-19 Dashboard
https://www.ices.on.ca/DAS/AHRQ/COVID-19-Dashboard

Impacts of COVID-19 on Canadians: First results from crowdsourcing
https://www150.statcan.gc.ca/n1/daily-quotidien/200423/dq200423a-eng.htm?HPA=1

Ontario-specific COVID-19 Analysis
https://howsmyflattening.ca/#/analysis

The computer algorithm that was among the first to detect the coronavirus outbreak
https://www.cbsnews.com/news/coronavirus-outbreak-computer-algorithm-artificial-intelligence/?fbclid=IwAR2e1E0vxJWa72aE1IY3Ih_LlcSC-aknnvEaWsyPnHxD7BTFgMpFfL3gOto

COVID-19 – The Tipping Point
https://www.cebm.net/

Are Hospitals Near Me Ready for Coronavirus? Here Are Nine Different Scenarios
https://projects.propublica.org/graphics/covid-hospitals

Covering the Pandemic – Columbia Journalism Review
https://www.cjr.org/covering-the-pandemic?utm_source=CJR+Daily+News&utm_campaign=fb133176fd-EMAIL_CAMPAIGN_2019_06_26_01_32_COPY_01&utm_medium=email&utm_term=0_9c93f57676-fb133176fd-174520961&mc_cid=fb133176fd&mc_eid=074a1d1b89

The U.S. Just Signed A $450 Million Coronavirus Vaccine Contract With Johnson & Johnson
https://www.forbes.com/sites/thomasbrewster/2020/03/30/the-us-just-signed-a-450-million-coronavirus-vaccine-contract-with-johnson–johnson/#6eb2aede2946

AstraZeneca teams up with Oxford University to develop coronavirus vaccine — first results from human trials expected in June or July
https://www.marketwatch.com/story/astrazeneca-teams-up-with-oxford-university-to-develop-coronavirus-vaccine-first-results-from-human-trials-expected-in-june-or-july-2020-04-30

Canadian Magazines Face Layoffs and Closures in the Wake of COVID-19
https://magsbc.com/2020/04/14/covid-19-devastates-magazine-publishing-sector/

Maps

Header Controller

Montreal: COVID-19 infection rates and median income by neighbourhood
https://caj-iij.maps.arcgis.com/apps/webappviewer/index.html?id=6249bbb269da4811bb91d4768347f050&extent=-8279407.1305%2C5668637.1371%2C-8132648.0362%2C5740258.6326%2C102100

Toronto neighourhoods
https://open.toronto.ca/dataset/neighbourhoods/

Toronto Property Boundaries
https://open.toronto.ca/dataset/property-boundaries/

Address Points (Municipal) – Toronto One Address Repository
https://open.toronto.ca/dataset/address-points-municipal-toronto-one-address-repository/

Toronto City Wards
https://open.toronto.ca/dataset/city-wards/

Ten Considerations Before You Create Another Chart About COVID-19
https://medium.com/nightingale/ten-considerations-before-you-create-another-chart-about-covid-19-27d3bd691be8

Journalists’s Toolbox: Presented by the Society of Professional Journalists
https://www.journaliststoolbox.org/2020/04/29/flu_and_miscellaneous_medicalhealth_sites/

Canada COVID-19 Situational Awareness Dashboard
https://phac-aspc.maps.arcgis.com/apps/opsdashboard/index.html#/e968bf79f4694b5ab290205e05cfcda6

Project Pandemic: Canada Reports on COVID-19
https://projectpandemic.concordia.ca/pages/maps-canada-cases

Novel coronavirus (COVID-19) cases in Nova Scotia
https://novascotia.ca/coronavirus/data/

COVID-19 in Ontario
https://covid-19.ontario.ca/

Flatten.ca
https://m.flatten.ca/?utm_expid=._DRLfVr1TKuYFMGoM-O_7g.1&utm_referrer=

Coronavirus: The Road to Economic and Social Recovery
https://www.descarteslabs.com/

Our World in Data
https://ourworldindata.org/grapher/daily-cases-covid-19?time=2020-03-17..&country=AUT+CZE+POL

Coronavirus Daily Global Tracker – Tableau
https://www.tableau.com/covid-19-coronavirus-data-resources

Coronavirus Locations: COVID-19 Map by County and State
https://usafacts.org/visualizations/coronavirus-covid-19-spread-map/

Datasets

Day One

COVID-19-geographic-disbtribution-worldwide-2020-10-28_fortutorial.xlsx

Crime and Population Statistics.zip

Day Two

COVID schools to map.zip

Master 2017-2019 Salaries for Humber Tutorial.xlsx

Tutorials

To obtain the pivot table tutorial, please click here.

Data Journalism Academy
https://sites.google.com/view/mj-basic-data-academy/home?authuser=0

https://sites.google.com/view/mj-basic-data-academy/excel-magic?authuser=0

Google Sheets guide

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.

Math Tools for Journalists
https://www.amazon.ca/Math-Tools-Journalists-Professor-Professional/dp/0972993746

Data Visualization: A Primer
https://open.canada.ca/en/data-visualization

Resources

All the Best Microsoft Excel Keyboard Shortcuts
https://www.howtogeek.com/361582/all-the-best-microsoft-excel-keyboard-shortcuts/

Excel Shortcuts for Mac to Make Your Life Easy
https://www.99signals.com/excel-shortcuts-for-mac/