Course Outline 2015
Mondays 8-10
Campus: Woodroffe
Room: WN205
Section: 310
Start date: 08-Sep-2015
Finish date: 19-Dec-2015
David McKie
181 Queen Street
1-613-288-6523 (office)
1-613-290-7380 (cell)
1-613-288-6490 (fax)
David.mckie@cbc.ca
Andrew Rabb
aw.rabb@gmail.com
613-276-0888
Bradley Horner
bradleymhorner@gmail.com
819-500-6722
Week one | Week two | Week three | Week four | Week five | Week six| Week seven | Week eight | Week nine | Week ten | Week eleven | Week twelve | Week thirteen | Week fourteen | Week fifteen |Tutorials | Final Assignment
COURSE DESCRIPTION
To teach students how to find information on the Internet and government hard drives using advanced search techniques and tools such as Excel, MySQL, Google Fusion Tables and ArcGIS Online to make sense of the information and render it more accessible readers, viewers and listeners through multi-media platforms. We will also learn how to negotiate for data that government officials are reluctant to release. Taken together, these skills comprise”computer-assisted reporting.” Our textbook, “Computer-Assisted Reporting: A Comprehensive Primer” , will be our guide and is available at the Algonquin College bookstore. Having the textbook is essential, as the lesson plans and in-class tests will be aligned with the material in various chapters.
At the end of this term, you will become adept searching affectively and with precision for datasets, analysing them for story ideas, or to obtain questions that could lead to stories. The story is the end product that we will always keep in mind when running through the exercises, though we will not be writing stories, per se. This is primarily a research methods course with a heavy emphasis on storytelling as the optimal end result. As such, we will begin each class with a story that has appeared in the news: either as an example of how computer-assisted reporting, also known as data journalism, was used, or how it could have been used. These discussions and the exercises will help you develop new reflexes that will allow you to examine news more critically. Given that we will be in the middle of an federal election, we will try to use election-related datasets as much as possible.
This will be a hybrid couse, so described because it will take place in two venues: in class and online. The course will be divided up into modules. Each module, lasting roughly three weeks, will teach a new skill. You will be introduced to a concept and skill in class, and then use our textbook and online tutorials to help learn the material. The tutorials and online troubleshooting will prepare you for the in-class tests. Do the work online, read the textbook, and the modules will go smoothly.
Through three news quizzes, you will also be tested on your knowledge of the day’s current events. Fall is a busy time. Apart from the unforeseen events that will inevitably make news make news, the next several weeks will be filled with significant happenings, such as the federal election. You will be expected to stay on top of these events, for they will become grist for our news quizzes. Being informed is your job as a journalist. For those who do so, the quizzes will be easy.
WHAT YOU WILL LEARN
1) How to find and analyze the credibility of information online
2) How to find data online
3) How to use a spreadsheet to analyze statistics
4) How to create a pivot table
5) How to use MySQL
6) How to use Google Fusion Tables and ArcGIS Online
7) How to advance your numeracy skills
8) How to become critical news consumers
9) How to use mapping software
|
Assignments and Deadlines
|
Percent | |
| Quizzes: There will be three of them during our 15 weeks together. The quizzes, worth five marks each, will deal with stories making news, or material that we’ve covered during the course and in the textbook. Each quiz will be graded out of five marks for a combined total of 15 percent of the course’s final mark. |
15% | |
| Assignment#1: (Sept. 28) In-class Excel assignment | 10% | |
| Assignment #2: (Oct. 5) In-class pivot table test | 10% | |
| Assignment #3: (Oct. 26) MySQL test | 15% | |
| Assignment #4: (Dec. 7) Mapping test. | 15% | |
| Assignment #5: (midnight, Dec. 8-17) Final assignment due | 25% | |
| Participation/professional conduct: For informative participation, completing tutorials, and professionalism displayed in class (no spending time on Facebook while the professor is lecturing) and on-line | 10% |
The emphasis will be learning how to use Excel to perform basic tasks such as sorting, filtering, making pivot tables, doing simple math such as percentage changes and ratios. The way we learn Excel will follow a simple pattern: in-class lesson, practice tutorial which will be posted on this syllabus, and then an in-class test to ensure that you’ve mastered the material. It impossible to learn these skills without lots of practice, so the emphasis will be on repetition, using newsworthy data. When possible, I will attempt to link the exercises to stories in the news, thus emphasizing the practical value of these skills in helping to find and tell stories. If you attend class, pay attention, read the textbook, do the tutorials, and come to class prepared, you will master the material easily and have fun in the process. The course is designed for success. If you regularly miss classes or neglect to do the online tutorials, neglect to buy a textbook, you will quickly fall behind and find it difficult to succeed. So for this reason, regular attendance is a MUST. If you must miss a class for a reason such as an illness, please give me a heads-up via email, just like you would an editor at work. The instructor also reserves the right to ask for a doctors’s note. If you miss a class for a legitimate reason, we will make arrangements for a make-up test. We will run this class like a professional newsroom, which doesn’t only mean regular attendance, but being on time, paying attention in class, refraining from distractions such as Twitter, Facebook and email correspondence and chatting with your neighbor. If you miss class and neglect to provide advanced warning, you will receive a zero for the assignnment and will not be given an opportunity for a make-up test.
GRADES
We will be using a 12-point grading scale with the marks to be as follows, meaning the final mark will be out of 120 with the following breakdown:
A+ = 12
A= 11
A-= 10
B+ = 9
B= 8
B-=7
C+=6
C=5
C-=4
Values will be rounded off. For instance, values 10.5 and higher will be rounded up to 11. Conversely, 10.4 and lower will be rounded down to 10.
COMMUNICATIONSWITH STUDENTS
This will be done primarily through e-mail correspondence and phone calls, given that I do not have an office at the college. The protocol will dictate that e-mailed queries to myself or our T.A.’s will be answered as promptly as possible. As such, it will be important for you to regularly check your e-mail account for communications such as revisions to instructions for assignments or readings. The excuse that “I forgot to check my email”
is unprofessional and will not be accepted. This is a dynamic course, which mean I frequently send e-mails alerting students to significant events. Please email me at my CBC account located at the top of the syllabus, as I do not use the Algonquin email.
PROFESSIONALISM
Because we’ll conduct ourselves as professional journalists, you will be expected to exhibit the appropriate behaviour. That means attending class, showing up on time; being prepared and ready to make a meaningful contribution based the preparation work you’ve been assigned; reading the assigned chapters in our textbook; paying attention to your instructor and colleagues and ignoring e-mail correspondence, Facebook and text messages; and promptly responding to e-mails from the instructor or T.A.’s. You’ll also be expected to stay on top of current events, which is part of our obligation as journalists. If you must miss class, you will be expected to communicate with the instructor via e-mail.
If an illness forces an absence, then you may be required to provide a doctor’s note. If it’s for an internship, then you’ll have to provide details. And if it’s a family emergency, I only need a minimal amount of information. I will take attendance to track your in-class attendance. Missed classes will mean missed marks. Ditto for missed in-class tests, which will be assigned based on the previous week’s work, and news quizzes. You will be allowed to use your textbook for in-class tests.
OUTLINE
Week One(TOP) Sept. 14
Introductions
Discussion of using Excel as a tool to analyze data.
Excel sorting and filtering exercise
Reading: Chapters 1-3 and 8 of Computer-Assisted Reporting
To download the Installing MySQL Tutorial, please click here.
Links:
Government open-data websites
http://www.davidmckie.com/reporting-methods-carleton-university-journalism-5206/#Open1
This fire hydrant cost Toronto drivers nearly $300,000 in parking tickets
http://www.theglobeandmail.com/globe-drive/culture/commuting/this-fire-hydrant-cost-toronto-drivers-nearly-300000-in-parking-tickets/article19985642/
City of Toronto’s Open Data website
http://www1.toronto.ca/wps/portal/contentonly?vgnextoid=9e56e03bb8d1e310VgnVCM10000071d60f89RCRD
Canada – Permanent and Temporary Residents
http://open.canada.ca/data/en/dataset/17b4a2d0-86d1-491c-b733-429bd31492ee
Facts & Figures 2014: Immigration Overview – Permanent Residents
http://open.canada.ca/data/en/dataset/2fbb56bd-eae7-4582-af7d-a197d185fc93
To download the tutorial, please click here.
Week Two (TOP) Sept. 21
On-line Excel tutorial
Downloading tutorial for MySQL
Reading: Chapter 6, pages 183-203
Week Three (TOP) Sept. 28
To download a copy of the in-class Excel test, please click here.
Check-in to see that everyone has downloaded MySQL
Introduction to pivot tables
To download the pivot table tutorial, please click here.
Links:
Which public servants earned more than $100,000? Search Ontario’s ‘Sunshine List’
http://www.theglobeandmail.com/news/national/2015-the-sunshine-list-ontario/article23597189/
Ontario’s sunshine list for 2015: Public-sector salaries over $100K
http://news.nationalpost.com/news/canada/canadian-politics/ontarios-sunshine-2015-public-sector-salaries-over-100k
Ontario’s ‘sunshine list’ tops 100,000 members for first time
http://www.thestar.com/news/queenspark/2015/03/27/ontarios-sunshine-list-tops-100000-members-for-first-time.html
Waterloo cosmologist tops local ‘sunshine list’ with $486,462 salary
http://www.therecord.com/news-story/5528965-waterloo-cosmologist-tops-local-sunshine-list-with-486-462-salary/
Week Four (TOP) Oct. 5
To download the Pivot table test, please click here.
MySQL exercise tutorial
Week Five (TOP) Oct. 12 (Thanksgiving)
To download the MySQL tutorial using the Ottawa restaurant inspection data, please click here.
Week Six (TOP) Oct. 19
On-line MySQL tutorial
To download the second MySQL tutorial using the city of Ottawa’s restaurant inspection data, please click here.
Week Seven (TOP) Oct 26
To download the MySQL test, please click here.
We will continue working with MySQL
Week Eight (TOP) Nov. 2
Continuation of MySQL and discussion of data for the final assignment.
To download the MySQL dump file for the city of Ottawa’s service calls
data for 2013-2015 (Jan-Sept), please right-click here.
To download the MySQL scripts used to create the master file in the dump file, please click here. NOTE: if the file adds a “txt” extension
in addition to the “sql” extension, simply delete the former.
To download the zip file containing the city of Ottawa’s parking violation data from 2010 to the June 8, 2015, please click here.
Reading: Pages 204 to 212 of chapter seven
Week Nine (TOP) Nov. 9
In_class Introduction to mapping and ArcGIS Online
To download the first mapping tutorial, please click here.
Links:
Contaminated sites cleanup to cost billions more, budget office says (CBC News)
A new view of Edmonton (Edmonton Journal)
http://www.edmontonjournal.com/news/living-on-the-edge/index.html
More discarded needles being found in Ottawa’s suburbs (CBC News)
To see more mapping stories, please click here to go to another syllabus.
Qs and As for the final assignment
When it is due? By midnight, Dec. 8, though earlier would be better.
What is required? If you are writing a story for Joe, it must have a data element that is either the focus of the story, or at least a major part of it. For instance, you may do a story about the ward that has the highest number of garbage complaints. This angle would come from the city’s 311 dataset that we’ve uploaded to MySQL. Or you might do a feature on the Ottawa police renewing their crackdown on prostitution in the Byward Market. The data element could be statistics from the Ottawa police or Statistics Canada on prostitution-related charges for the last five years.
What time period should the data cover? As is explained in the textbook, the longer the time period the better. The minimum should be three to five years. Anything less than that fails to provide a statistically significant trend.
What if I’m exempt from writing a story, and only have to submit data? A few of you fall into this category. You must still find and analyze a dataset that meets the criteria we’ve outlined. It must cover a significant time period, not just one month or one year, which really tells us nothing about a trend.
How will you be judging the assignment? I’m looking for your ability to use the skills we’ve learned to analyze the data. That means using Excel, MySQL or mapping — or all three — to mine the data for a newsworthy trend.
So, specifically, what will I be required to submit? Whether you’re writing a story, or just submitting data, I’ll need to see evidence of your work. If it’s a story, you’ll be required to upload it to a category on our syllabus that will be assigned. In the blog post itself, you’ll be expected to embed the map, chart or both, making sure that it has a title at the top and a source at the bottom. As for the data itself, you’re to email the ONE spreadsheet containing the data, which should have the original dataset complete with the URL if it’s taken from the web. The original dataset is to be on the first worksheet, which is to be properly labelled. The URL allows me to verify the source. The second worksheet should have a working copy, which must also be properly labelled. Subsequent worksheets can contain the pivot tables that produce the trends. You’ll be shown how to create a csv file from the pivot tables and upload them to ArcGIS Online, for instance. If the table is exported from from a MySQL, the same rules apply.
What is I have more than one dataset? Then paste them in subsequent worksheets in the same Excel workbook.
Will I have to submit an explanation of how I analyzed the data? Yes, this is critical. For it allows me to judge how much work you’ve put into the analysis. What I’m judging here is effort. You’ll need to conduct the kind of research we’ve discussed in class that explains the relevance of the data. This might involve talking to the officials who have created the dataset, or experts who can interpret the results. So for instance, if you’re doing that story about garbage complaints, you might want to talk to the city counsellor for that ward about the extent of the problem, or a residents group that has complained to the city. What I don’t want is for you to present a dataset with no research at all.
If I’m only doing the data portion of the assignment, do you still need an explanation? Yes. You can provide on a separate worksheet. You’ll be shown how to do this.
Week Ten (TOP) Nov. 16
To download the second mapping tutorial, please click here.
To download the third mapping tutorial, please click here.
Performing analysis using ArcGIS Online
http://doc.arcgis.com/en/arcgis-online/use-maps/perform-analysis.htm
To download the search and rescue data, please click here.
Reading: chapters eight and nine
Links
Incident-based crime statistics, by detailed violations
http://www5.statcan.gc.ca/cansim/a26?lang=eng&id=2520051
Tory, suburban ridings gain the most from enriched child benefits: analysis
http://ottawacitizen.com/news/politics/tory-suburban-ridings-gain-the-most-from-enriched-child-benefits-analysis
Government favours infrastructure projects to Conservative ridings
http://www.theglobeandmail.com/news/politics/government-favours-infrastructure-projects-to-conservative-ridings/article25492064/
Federal infrastructure fund spending favoured Conservative ridings
http://www.theglobeandmail.com/news/politics/federal-infrastructure-fund-spending-favoured-conservative-ridings/article25172781/
50 data-journalism ideas (NICAR)
http://mtduk.es/prez/50ideas.html#/
Week Eleven (TOP) Nov 23
To download part two of last week’s ArcGIS tutorial using the contaminated sites and the federal ridings, please click here.
In-class mapping quiz to test your knowledge of the terms we’ve been using in the tutorials, and which are explained on pages 206-209 of our textbook. There will be six questions with one of them being a bonus.
A continuation with ArcGIS, this time importing the city of Ottawa’s ward boundary file that you can download by clicking here.
We will combine the ward boundary file with the contaminated sites, and possibly discarded needles. Remember, we already have the two layers in ArcGIS Online.
Configure the pop-up boxes.
We will end with a session on cleaning up data before importing a file. We’ll be using the search-and-rescue data posted in the Nov. 16 section.
A possible Introduction to Geocoding — if we get that far!
Week Twelve (TOP) Nov. 30
Geocoding exercise using GPS Visualizer
To download the Tim Hortons inspection table, please click here.
To download the tutorial that explains how to obtain an API key for using Bing Maps in GPS Visualizer, please click here. You’ll need the API code to use the tutorial below.
To download the geocoding tutorial using MySQL, GPS Visualizer and ArcGIS Online, please click here.
To download the restaurant inspection dump file, please right click here and save.
To download zipped file 311 complaints from 2013-Sept 2015 exported from MySQL, please click here. (Note, depending on the browser, you may have to save the file by right-clicking on the “here” link.
A session on Google Fusion Tables
To download the 2014 Ward KML file for Fusion Tables, click here.
Noise bylaw complaints from 2013 to the first nine months of 2015
https://www.google.com/fusiontables/data?docid=1KNJoHXvJAv2A2NngjUQUknLc_KeUjIwdHGiJZ25u#map:id=3
City of Ottawa Open Data Catalog
http://data.ottawa.ca/en/?page=1
If you don’t have the 311 noise-complaints csv we used in class, please click here to obtain the file.
To download the city of Ottawa ward KML file, please either right-click and “save as” or simply click here.
To download the Fusion Tables tutorial for the city of Ottawa’s complaints data, please click here.
Week Thirteen (TOP) Dec. 7
To obtain the mapping test click here.
Greek On Wheels geocoded for those who couldn’t export the file from MySQL, please right-click here.
Tutorials(TOP)
From week one:
From week four:
From week five:
From week seven:
To download the MySQL test, please click here.
From week seven:
From week eleven:
From week eleven:
From week twelve:
Final Assignment(TOP)