Simple example for teaching data journalism

Addresses in Knox County Quarantined for Meth-related Activity

The map below shows the 27 Knox County properties listed as quarantined in Tennessee Methamphetamine Task Force‘s Meth Lab Database. The database includes quarantine data dating back to 2006.

Seven Steps to Doing This!

You will be shocked at the simplicity of this project.

Step 0. Get a Google Account. The scraper we will be using will dump the information that is scraped directly to a Google Spreadsheet.

Step 1. Install the Scraper extension for Chrome. It is free and simple. After install you might have to restart Chrome (I can’t remember). Once you have the Scraper extension properly install, you should be able to right-mouse click in Chrome and see a new option: “Scrape similar…”

Scrape

Step 2. Find our data. In this case, we have to go to the Tennessee Methamphetamine Task Force’s website. Then we choose “Search for Meth Labs” link. We want to search for activity in Knox County, and we only want to see quarantined properties.

Scrape2

Step 3. Scrape our data using our new Chrome extension. After you run the search on the Task Force’s website, you will see the data for the 27 quarantined addresses. Select the whole row for one instances, right-mouse click, and choose “Scrape similar…”

Scrape3

After you choose “Scrape similar…”, a window will pop up displaying…hopefully…the data we want. It worked great for me.

Scrape4

From here you just choose “Export to Google Docs…” and after a few clicks – the first time you do this you will have to give permission to the Scraper extension to write to your Google Docs – the data should populate into a Google Spreadsheet.

Scrape5

Step 4. Prep the data a bit. The data is pretty much ready to go, but we want to make sure the address is clear enough that Google Maps can find it. We just need to pull the address from the five different address columns together in to one column. We can’t use the regular addition command (+), since these values are strings. Instead we need to combine them using the ampersand (&). We also need to make sure we include the proper spacing and punctation. Here is the formula I used:

=A2&" "&B2&", "&C2&", "&D2&" "&E2

The formula might be confusing, but really it just says “I want to the data from cell A2, than a space, then the data from cell B2, then a comma and a space…” and so on. Rewrite the code above into cell G2, see if it works, and then copy-and-paste the the formula from G2 to all the cells in column G.

Step 5. Import data into Fusion Table. Although Google Spreadsheets is cool, it doesn’t have the ability to do geolocation, or finding the address on a map. Therefore, we have to use another Google product, Fusion Tables. Fusion Tables is a free, fairly advance database software. Since most people don’t use Google makes you turn on Fusion Tables within your Google account.

To turn it on, go to your Google Drive and choose “Connect more apps…” Then search for Fusion Tables and install it.

Scrape6

Once it’s installed, go back to your Google Drive and under “create” you should be able to create a Fusion Table. When you choose to create a Fusion Table you will get to this screen:

Scrape7

Choose “Google Spreadsheet.” The spreadsheet you created in Step 3 & 4 should pop up. If it doesn’t just look through your Google Drive until you find it. When you find it, click it and follow the directions. Eventually, your data should show up in Fusion Tables.

Scrape8

Step 6. Map the data. Just click the Map tab. Seriously! It’s that easy. The only problem that might arise is that Fusion Table might not understand what column you want to map. If this does sink your project, all you have to do select “Address” instead of “City” in the drop down.

Step 7. Post the map on your website. Choose “Publish” on the map tab. Fusion Table will give you a warning telling you that if you want to publish the map you need to make the table public. Do that. Then copy the embed code and paste it in your blog or website.

DONE!