Humber ARI Data Training

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

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

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://youtu.be/uRaHksbDoZk

PDF Tutorial

Tutorial one: Using Tableau to Visualize COVID

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. 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 wil be learning ArcGIS
Online,  Esri Canada’s power  geospatial cloud 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.pdf

ArcGIS PDF Tutorial two – Creating thematic maps.pdf

Resources

Each of the 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.

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 Android, iOS, 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

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 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/

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

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

Tutorials

To obtain the pivot table tutorial, please click here.

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/