Finding data through the web inspector

One of my students wanted to harvest data from a government site on nuclear facility decommissioning. The data is published online, but not in the most friendly format.

There was a map:

Followed by many tables:

It's possible to scrape the data (maybe import.io, or Chrome scrapers and more) but the one I want to cover here is looking for the file that feeds that map, using the Chrome's Developer Tools.

• Go to the page: http://www.nrc.gov/info-finder/decommissioning/
• Do Command-Option-I or go to Chrome menu > More tools > Developer tools.
• Click on the Network tab, then refresh the page.

• Now scroll through the results. This is a list of every file that the browser downloaded to create the web page. We are looking for something that might be the data for the map. Typically we're looking for something that ends in .xml for .json
• Sure enough, we find something called decomissioning.xml

• Right-click on that entry and choose Open Link in new Tab and you get the XML file: http://www.nrc.gov/admin/data/gmaps/decomissioning.xml

What to do with this, though? I googled "xml to csv converter" and found this site: http://www.convertcsv.com/xml-to-csv.htm

• I took the url to the xml file and entered it into the proper field and loaded it.
• Then in the box below, it converted that file to CSV, which I was then able to download.

Which gave me a pretty clean csv file I can use in Excel.

Pretty slick, eh?

Categories: Spring 2015, Tips

Using importHTML to scrape websites

This is a quick post to show how you can scrape data from websites using Google Spreadsheets. (There are ways to do this in Excel as well).

Let's start with the page, this table of rushing stats on NFL.com. The fact that it is tabular data gives us hope we can scrape it.

Start a new spreadsheet in Google Docs. Go to the first cell and put in this formula:

=IMPORTHTML("http://www.nfl.com/stats/categorystats?tabSeq=0&statisticCategory=RUSHING&conference=null&season=2014&seasonType=POST&d-447263-s=RUSHING_YARDS&d-447263-o=2&d-447263-n=1","table",1)

The function you are calling is IMPORTHTML and it takes three arguments:

• URL: This is the url you are pulling from. It needs to be in quotes.
• QUERY: If you are looking for a table or a list. This refers to the HTML of the page. In our case, it is a table, so it is looking for the <table> tag in the code. This should be in quotes. "list" is your other option.
• INDEX: This is which table on the page. We started with 1 and found it, but some pages might have more than one table on the page. If you put in the number 2 it would look for the second table on the page.

What you get in return is all the tabbed material getting pulled into your page. It is tied to the formula, so if you want to keep it or don't want it to change, copy the content and paste special as values.

Not every page will work, but it is simple enough to try. There are plenty of other discussions on the web about this function, so you can search if you have trouble.

Categories: Spring 2015, Tips

Shaping data and the Tableau Data Shaper

Here is a good post that explains how you might want to clean up and *shape* your data for display in Tableau. How you format your data before import can determine a lot about what you can do with it once in Tableau.

The Tableau Add-in for Excel is a great help for this. Here is a post with detailed instructions about it, but I have some short cuts here, because our school machines don't give you admin access and you might not be able to download and run the .exe file.

• Download this file (Tableau.xlam) and put it in Documents > My Tableau Repository.
• Go into Excel > File > Options > Add-ins
• At the bottom, choose Manage > Excel Add-ins and click Go
• Browse to fine the Tableau.xlam file in Documents > My Tableau Repository.
• Click OK through the boxes and you should end up with the "Tableau" menu in Excel.

Categories: Handouts, Spring 2014, Tableau, Tips

Getting a Lat/Long from Google Maps

UPDATE 4.13.2014: It looks like the "new" Google Maps has returned the old functionality of being able to right-click on any spot in a map and choose "What's here" to get a decimal-based latitude and longitude. So I think the directions below are irrelevant now.

----------------------

It's easiest to do from the "classic" google maps. Who knows how long that will be available, so I've explained how with the "new" maps as well.

• Go to https://www.google.com/maps?output=classic
• Type in the address and hit return and make sure Google Maps takes you to the right place.
• Right-click on the map at the location and choose “What's here.”
• That will put something like this in the search bar: “30.258659,-97.744548”
• Put the first number that usually starts with “30” in the Latitude field.
• Put the second number that usually starts with ”-97” in the Longitude field.

If you have the “new” google maps, you have to do some extra work to get lat/long. It's easier to just use the “classic” link above, but if you insist:

• Type in the address and hit return to find the location
• X-out the location in the search bar so the pin goes away.
• Click on the map where the pin was, (and then maybe click again) and a window will come up showing the address and the lat, long, but it will formatted wrong. It will be something like: 30° 22.096', -97° 42.209'. Copy that text.
• Go to http://dbsgeo.com/latlon/ and paste in the text into Place Name.
• Make sure it takes you to your location, then from the “Latitude, Longitude” under the map copy the “30” number into Latitude into the homicide database, and the ”-97” number into Longitude.

Categories: Spring 2014, Tips

Automatic legends in Google Fusion Tables

Jean Yoo found a hidden gem in Google Fusion Tables that I wasn't aware of ... an Automatic Legend.  This will add a legend showing readers how your polygon fills (and I assume points) are styled.

I KNOW it was not there when I started using Fusion Tables. I wonder how long I've been staring at it.

To use it, go to your Change Map Styles window (the same place you define your buckets for the fill) and look at the bottom of the list. There is the Automatic Legend. You can set a title and you can decide in which corner you want the legend. Very handy.

Categories: Fusion Tables, Spring 2013, Tips