#googlerefine

[paulbradshaw]

(How to get latitude and longitude data with Google refine in one minute +CartoDB interactive maps - YouTube)

[jrlittle]

Louisiana Population by County (Census 2000)

This post documents process.

Tools and Data:

Census Tiger Shapefiles in KML

Census population data

Google Fusion Tables

Google Refine (Open Refine)

Procedures:

Generate a population by county report for the state of Louisiana; download as CSV, upload into Fusion Tables

Download 2010 prototype KML shapefiles from Census TIGER of generalized county boundaries

Using Google Refine, open the KML Shapefile to extract (add columns) the GeoID and County Name from the KML description field.  It appears that the 'value.match' regular expression operator in Refine (GREL) cannot handle the full complexity of that field.  However, iterativng multiple times with value.replace produced the same result.  Save as CSV

Upload the modified KML shapefile into Fusion Tables; merge on GeoID field with the Census data, GEO_ID2.  (Admittedly, I had this census data lying around, but it would be important to have gathered something like unique state/county FIPS code, or GeoID in this case, to join upon.)

Change the Map Style -- in the mreged table -- to use the population field as the gradient for the county polygons

[jrlittle]

This is a test, a demonstration of data munging and mapping Open Data.  The data are from the Open Raleigh Project, refined in GoogleRefine and presented (geolocation mapping) using Google Fusion Tables.

The presentation layer is a map of bicycle thefts in Raleigh, NC (2012) using Google Fusion Tables.  The Fusion Tables tool allows for easy geolocation of the address (location) data.  The original data source included latitude and longitude; part of my test compared how Fusion Tables maps (geolocates) raw addresses versus X,Y coordinates.  Interestingly, Fusion Tables was able to map the original location data which was very messy but luxuriously complete -- all in one field, and included a full address as well as X,Y coordinates.  Not suprisingly, there was a small 2% error rate mapping the address data.  (Rows which haven't geocoded properly show up highlighted in yellow.)

The data originate from a dataset of 2012 Police Crime Reports found in the OpenRaleigh project.  This is a 45K observation (or "rows", or "incidents") dataset with 7 columns that is about 7MB in size.  

After gathering (downloading) the 7MB of data.  I explored the OpenRaleigh dataset using GoogleRefine (soon OpenRefine).  Refine allowed me to quickly get a sense of the shape of a subset of data (starting by limiting the incident report to "bicycle") and then refining the data to make the categories and address information more useful for Fusion Tables to present.  After munging the data, I exported a CSV file of refined data consisting of 300 observations (incidents) of bicycle theft in a file size of about 67Kb.  One of the refinements afforded by using GoogleRefine was to easily split out the lat/long (X,Y) coordinates into their own filed.  As noted, Google Fusion Tables did not need this data separated, but other applications are not so kind -- making this a good-to-know tool, one with a lot of power and a very gentle learning curve.

[datavissen]

Mocht je zelf aan de slag willen met datavisualisatie, dan kom je vaak vrij snel tot de conclusie dat voor maatwerk visualisatie je eigenlijk niet onder programmeren uit komt. Maar hoe dat je dat, een data visualisatie programmeren, en met welke tools? Op deze website is een handig overzicht te zien van verschillende tutorials met de op dit moment bekende frameworks voor datavisualisatie en data bewerking. Erg handig als zelf eens met code aan de slag wil!