Building on the basics of interviewing data we covered last week, this lesson will show you how to merge in outside datasets to introduce context that’s missing from your primary source.
Part 1: Tonight’s role model
Our inspiration is a piece published in the early weeks of the coronavirus pandemic by Sharon Lerner, then a reporter at The Intercept. It’s titled “Coronavirus numbers reflect New York City’s deep economic divide.”
“I’m not a data journalist,” Lerner, who now works at ProPublica, told me in a phone interview. “I don’t think of myself that way.”
But she did have an interest in the longstanding disparities measured by public health scientists, and a lingering curiosity about how the city’s vital statistics reports could be used to cover them.
When a mysterious new virus swept across the city, she sensed that the moment had come to put that data to work.
Whether she calls herself a data journalist or not, that was Lerner displaying the data state of mind I’ve promoted in class. Once she had the idea, she grabbed the data the city was publishing, lassoed in the Census figures needed to provide the missing context and, boom, she had a story.
“They were pretty simple calculations,” Lerner said. “It was obviously a very rudimentary approach but you could already see these patterns.”
Tonight, we’ll follow in her footsteps by working through an updated version of the city’s COVID statistics, learning how to merge ZIP code totals with Census data to see if the trend that Lerner identified early on in the coronavirus story held up over the years that followed.
Part 2: The latest tally
The webpage that Lerner accessed is long gone, one of many coronavirus dashboards hastily rushed onto the web.
A more recent accounting of the city’s COVID statistics is available on GitHub, a popular site owned by Microsoft where developers commonly share open-source code and data. There you can find a repository named coronavirus-data maintained by the NYC Department of Health and Mental Hygiene.
Click into the “totals” folder. There, you’ll find a file called data-by-modzcta.csv. This features case and death counts, already adjusted for population size, with each row representing something called a “MODZCTA.” Click the “raw” button in the upper-right corner to view the raw CSV data. Download it to your computer.
Open a new Google Sheets workbook and import the CSV into a new tab.
Give the same once over we’ve been trained to give every dataset. Don’t move on until you actually find official definitions of each column, and answer the elusive question: “What the hell is a MODZCTA?”
Part 3: Add context from the Census
Go to data.census.gov, the authoritative source for U.S. demographic data.
Now the real fun begins. Because the Census gathers so much data, it can be extremely difficult to find the right table for your specific needs. We don’t have time to go over everything here in detail; that could be an entire semester on its own.
Instead, I’m going to give you a few pointers, like a more experienced colleague or a source might.
The table we’re after is B19013, which contains the most commonly cited measure of household earnings: the median income.
Since we’re focusing on small ZIP code geographies, we’ll need to use the five-year estimates from the annual American Community survey, which means trading off the freshness of the most recent one-year survey for the increased accuracy of five-year samples that reach many more people and therefore reduce the margin of error.
So you’ll want to navigate to that table, find the little switch that flips to the most recent five-year estimate, and then select all ZCTAs in New York state in the sidebar. That will ultimately take you to a page like this one, where you should hit download to get the data zipped up with some documentation alongside a CSV.
If you can’t get there yourself, try this link into the Census site.
Unzip that file and import the CSV into a new tab in your Google Sheets workbook. You do that by selecting “Insert new sheet” during the import process. After it’s in, rename the tab to “census” for clarity.
Look carefully at your dataset and you’ll see a problem. The NAME column that includes the ZIP Code has a ZCTA5 prefix that will make it difficult to merge this dataset with other tabs. We’ll need to strip that before we’re able to link up our datasets.
In this case, that can be accomplished by splitting off the last five characters of the NAME into a new column. That can be done in Google Sheets by using the RIGHT function, like this: =RIGHT(B2, 5), where B2 is the cell containing the NAME value.
Rename the E column as ZCTA and then put that formula in the E2 cell. That will suggest an “autofill” of all the following cells using the same formula. You should accept that. If not, you can grab the box in the lower right corner of the cell and drag it down manually.
Have you figured out what the other columns in the dataset are? Look and see if you can find the answers in the metadata included in the Census ZIP file.
Part 4: Crosswalking ZCTAs
Now all we’re missing is a way to merge the traditional Census ZIP code areas with the city’s custom MODZCTAs.
In your earlier research, you should have found the city’s open-source crosswalk on GitHub. This will provide the crucial link between the city’s custom geographies and the standard Census tabulation areas.
Download that next and import the crosswalk into a third Sheets tab. Click on the tab at the bottom and rename it to “crosswalk” for clarity.
Now, after all the buildup, it’s time for the big moment. Your first join.
First, let’s go back to your Census data tab and add a new column, which will hold the NYC code for each row. I suggest clicking on the E column’s header to select it, right-clicking to see an options list, and then selecting “Insert 1 left” to create a new column at the beginning. Then rename it to MODZCTA.
Next, we’ll use the XLOOKUP function to match each ZCTA to the corresponding code in the A column of the crosswalk tab. After it makes a match, we’ll want it to return the value in the B column, which contains the MODZCTA. If no match is made, we’ll have it return “N/A”, which is what we expect for all of the codes outside the five boroughs.
In the patois of Google Sheets, that translates to the following formula: =XLOOKUP(VALUE(F2), crosswalk!A:A, crosswalk!B:B, "N/A"). Put that into the first cell of the new MODZCTA column and then copy it down for all rows. For it to work, you’ll need to make sure the ZCTA values in column F are numeric using the VALUE function.
Now for the final link. Go back to your COVID data tab and add a new column for the MODZCTA in the A column. Then use a similar XLOOKUP formula to match each ZCTA to the crosswalk and return the corresponding median income in the Census tab.
That will be =XLOOKUP(VALUE(B2), census!E:E, census!C:C, "N/A").
Part 5: Finish the job
Now look carefully at the results. You should see several warts in the data that require some kind of attention. Take a moment to identify them and think about how you might address the issues.
Then compare your results to Sharon Lerner’s 2020 story. Look for the pattern she identified and consider whether it held up over the years that followed.
We’ll take a moment to do that together as a class and see what we find.
Homework Assignments
Task 1: Find two datasets that belong together
Come up with a measurable question that cannot be fully answered with one dataset alone. Then find a dataset that can add useful context, such as population, income, race, housing conditions or geography.
Task 2: Complete a first join
Import both datasets into Google Sheets and use XLOOKUP to bring at least one useful field from the contextual dataset into the primary dataset.
Inspect the result. How many rows matched? Which rows did not match?
Task 3: Find an answer
Use your joined dataset to ask questions and develop a finding. Look for a comparison, pattern or trend that was not visible before you added the contextual data.
Task 4: Prepare to present
Be ready to explain your finding to the class, share the evidence behind it and one limitation or complication in the data.