The module on Census reporting

(Postdated)

We spent several weeks in September talking about the U.S. Census Bureau and how it is a source of data ripe for reporting on just about any beat.

Some of the materials germane to the module:

• Lecture notes on Working the Census
• Lecture notes on the American Community Survey statistics
• Ryan Robinson's slides on using data visualization to tell the story of the 10-1 Austin City Council.

Working in Excel

There first couple of labs in class were about working in Excel, the workhorse of the data journalist. Here are some of the handouts that I have worked on to support the lessons:

• Handout: Excel basics for reporters
• Data: Excel basics for reporters
• Handout: Common Excel formulas
• Handout: Crazy Excel formulas

I also keep some common (and not so common) string and math formulas and tips on my wiki. Beware, this is written for me, and I tend to drop stuff in without explanation.

The awesome Mary Jo Webster also has put together a handout for a NICAR conference of her favorite Excel tricks. It covers the same topics and more.

• Handout: Excel favorite things
• Data: Excel favorite things

As one of the most widely used programs in business, there is plenty of help for Excel out on the Web. When in doubt, Google it.

Speaking of Google, you can also do most everything in Google Sheets, which is also excellent for collaborative work since more than one person can be in the document at the same time.

• Here is a list of Google Sheets formulas
• Use Forms to crowd-source data, which you can then save into a spreadsheet.

If you don't have access to Excel, there are alternatives beyond Google:

• Libre Office
• Open Office

Folks I know and trust choose Libre over Open office, but I haven't used much of either. I'd be interested to hear from folks who have used the Access equivalent, Base, since Access is available for PC only.

How we do this: The inverted pyramid of data journalism

(Postdated)

Lecture notes: 2014-09-03

This lecture is inspired by this article by Paul Bradshaw: The inverted pyramid of data journalism.

OK, more than inspired. My lecture is the article. We’ll go over the story above together and talk about how it relates to what I hope we get out of this course.

Here are my additions:

Compile

• I love the phrase “Data journalism begins in one of two ways: either you have a question that needs data, or a dataset that needs questioning.” That’s my new signature phrase.
• The process of getting the data is probably the hardest, and often the most time consuming part of the process. If you are requesting data from an organization, don’t be surprised if you have to go back to them to ask for more. Here are some tips to avoid that.
• Ask for a record layout of the data before you submit your request. The record layout tells you what fields are available and what they mean. This let’s you know what to ask for.
• Talk to someone on the phone (yes, call them) who works with the data. If you are able, ask them how they would approach the questions you are asking.
• If you cover an agency, ask for their data expiry calendar. This not only tells you how often they shed/delete data, it also tells you all the data they have!
• Ask for CSV or Excel, but don’t give up with PDFs. There are tools …. Cometdocs, Tabula are two.
• Some Google search techniques:
• Search a specific site with site:example.com
• Quotes gives you exact word. “crime statistics” gives you the two words together
• Wildcard: “crime statistics * 2013” helps you narrow without being exact.
• Exclude with minus. to get crime statistics, but not FBI, use “crime statistics” -FBI
• Get specific file types with filetype:xls
• Use Advanced Search.
• More on advanced search operations.

Clean

If compiling is the most time consuming, cleaning is the most labor-intensive. You’ll spend more time cleaning data than using it, I assure you. We’ll cover data cleaning later in the course, but here are some tools in addition to what Bradshaw has mentioned.

• Open Refine is king, as mentioned.
• Excel can do a lot. We’ll spend the next two weeks on it. But ...
• Here also is a tutorial to string functions
• and a list of them on Microsoft’s site.
• Mary Jo Webster’s Excel Magic and corresponding data file.
• I list string functions I’ve used and want to remember on my wiki.
• Mr. People is awesome for working with names. I use it often.

Context

Here is where the critical mind of a reporter comes in.

• Numbers alone can’t tell a story. Or not a very interesting one. Find the people!
• There will be errors in the data. You will have to make subjective decisions, like creating categories, leaving out bad data. Explain yourself.

Combine

• As Bradshaw says, find the data set to compare. Variance in data or change over time are the key component to your story, almost guaranteed.
• Visualization is often how you combine data sets, and it can help you find the story as much as it can explain it. I build data visualizations first for the reporter to help them find the story. We’ll do this late in the course, using Tableau.
• We’ll try to get to correlation … seeing how two different data points relate. But remember: Correlation does not mean Causation!!!!

Communicate

Bradshaw continues in “Six ways of communicating data journalism”. Read it, because telling the story is what sets you apart from any fool who can use Excel.

And specifically on the “Visualize” part: Take my class in the Spring.

On using MySQL and Navicat for this class

One of the key skills that students will learn in Data-Driven Reporting is working with data using a relational database. As journalists, we'll be asking questions of our data just like it was any other source on our beat. What do you know and when did you know it? How does this relate to that? What are the trends over time? What sticks out as odd?

We'll do this by using a free, open-source database called MySQL, and a database client called Navicat Essentials, which costs about $10. (Not to be confused with full Navicat, which costs between $60-$100, but worth it if you have the cash.)

There are a ton of different kinds of databases out there, by MySQL is common enough to run most of the Internet, and many other data journalists in the community use it, so it is easy to find documentation and help on how to use it. It is also cross-platform (can run on Windows, Mac or Linux) and free to install, which is important for me, because I want you as students to learn something that you can carry with you no matter what kind of computer you use or your (or your company's) financial means to buy software.

MySQL will be loaded on our lab machines, but I encourage you to install it on your own computer. We'll carve out some lab time to do this together so I can help with any challenges you might face, but if you are technically inclined and want to get a head start you can download a nice Mac install here, or follow Windows directions here.

Navicat Essentials is the software that we will use to talk to the database. Again, there are tons of options here but I wanted to find something that was cross-platform and relatively inexpensive. There are other free managers out there, like MySQL Workbench, but Navicat Essentials has an excellent import wizard that is worth the $10.

What are you missing with Essentials vs the full version of Navicat? The two main differences are being able to import/export data in more formats (Excel being the most relevant) and the query builder wizard. I will use Essentials in class so that no one is left behind, but know I use the full version in my day-to-day work and was happy to expense the $130 to my company.

Data-Driven Reporting class is on the Fall 2014 Schedule

I'll be teaching Data-Driven Reporting in the Fall of 2014. The class is on the schedule and registration is open. It is restricted to Journalism majors.
J 333F DATA-DRIVEN REPORTING

Unique: 07985

Day & Time:  MW 600 to 730p

Room: CMA 4.146

Restricted to journalism majors. Electronic document retrieval and manipulation; spreadsheet and database management; and Internet skills. Includes collaborative work on major investigative projects. Journalism 333F and 338 may not both be counted.

Prerequisite: Journalism 310F (or 320D) and 311F (or 321C) with a grade of at least B- in each.

May be counted toward the quantitative reasoning flag requirement.