re:dash map mashup
I’m new to using re:dash at work and I’m loving it so far to create quick and shareable / verifiable data visualizations.
Recently I wanted to map out some location usage patterns with data that didn’t fit well into the map visualization schema. In re:dash the maps visualization requires latitude and longitude pairs to create a map and place markers on that map.
But the beauty of re:dash is that I don’t have to wait for a data nerd to help me, I can mix up the data on my own and visualize it any number of ways looking for new perspectives. So I didn’t want to be slowed down when I don’t have all the requisite data for a map visualization, here’s how I worked around it.
Data => Google Spreadsheets = Maps
Here is some example data similar to mine where all you have is a Country Code for the location information instead of latitude and longitude.
I used the re:dash “Download Dataset” button to download the data as a CSV and then upload it a spreadsheet in google drive.
Because we only have the Country Code data we need to convert it to latitude and longitude pairs to create our map visualization. Google Public Data happens to provide this handy table called countries.csv which we can use to convert our Country Code data into latitude and longitude.
I copied the countries.csv into another Google Spreadsheet so I could use it in multiple places, but now we need to reference it from my other spreadsheet.
Querying one Google Spreadsheet from another
First you need to use ImportRange to access the data from the other spreadsheet, pass the ID of the document which you can see from the spreadsheet URL, it looks something like what you see in my example. The second parameter is the data range you want to query in the spreadsheet, exclude the header rows.
The above example passes this range data into the Query function and use an SQL like query to pull in the lat/lon columns you want; referencing columns by ids like Col1, Col2, etc. Note the crazy spreadsheet syntax (‘”&$B2&”’) for referencing the local cell that contains my country code. Got it? Sure!
At this point you should have your original data extended with two extra columns for latitude and longitude according to the provided Country Code column.
Querying Google Spreadsheets from re:dash
This should have been one of the easier parts, but was prone to lots of errors. The country names provided in the google CSV file contain non-ASCII characters which make re:dash angry, don’t include that column.
In your re:dash query simply use the spreadsheet ID, which can be seen in the URL address bar and looks like a long ID.
As you can see in the screenshot you can append a “|0” after the ID which indicates the sheet number (starting from 0). If you only have one sheet you don’t have to add this.
!Important! You need to share your spreadsheet with the re:dash “Service Account” email for the query to work, see the re:dash docs for more.
Now create your Map visualization as you normally would.
DONE!
(Of course you aren’t getting the full re:dash feature set, the spreadsheet won’t auto update the query for you, you need to manually update it until you can get the latitude and longitude information added to your database)
