If you’re like me, you’re into maps, and you most likely want to graph meaningful things onto them. However, when given a dataset, usually the locations are not formatted in a way that is easy to map. Often they are given as strings (i.e. Paris, France), and it can be daunting to manually change each one of them into mappable geolocations, especially if the dataset is huge. This is where Geocode by Awesome Table, a free, downloadable extension to Google Maps, comes in handy.
Goal
Turn a large set of string locations into mappable geolocations.
Dataset
You can access the dataset here. This is Tate Artists data, which was one of the datasets on our midterm. Follow this tutorial on this Google spreadsheet!
Steps
1.
Download Geocode by Awesome Table on Google Sheets. Click on the ‘Extensions’ tab, select ‘Add-ons’, and click ‘Get add-ons’. Then, search for ‘Geocode by Awesome Table’ and download the extension.
2.
Click on the ‘Extensions’ tab, select ‘Geocode by Awesome Table’, and click ‘Start Geocoding’.
3.
Select ‘placeOfBirth’ in the options to select the column to geocode.
Geocoder will create two new columns ‘Latitude’ and ‘Longitude’ to the right of the column you selected to geocode.
4.
You have an option to visualize the map directly within this extension by clicking on the ‘Create Map’ button.
Generated map might look like this:
Sometimes you want ‘Latitude’ and ‘Longitude’ to be separate columns. Some software expects them to be formatted in the likes of ‘Latitude,Longitude’. We will now see how we might go about doing that.
5.
Create a new column to the right of the ‘Latitude’ and ‘Longitude’ columns, called ‘LatLongOfPlaceOfBirth’.
6.
In the first cell of this new column type:
CONCATENATE(<First item in 'Latitude' column>, ",", <First item in 'Longitude' column>)This will concatenate the latitude and longitude as ‘46.07, 13.23’
7.
Drag this function to the rest of the ‘LatLongOfPlaceOfBirth’ column to transform every cell in the column in this manner.
You notice that because the function was applied to all the cells in the column, some cells contain weird single commas. This is because the ‘Latitude’ and ‘Longitude’ cells were empty for that row, and the function tried to concatenate an empty string to another empty string. If left without being corrected, these anomalies will cause weird behaviors when we’re mapping. So how do we get rid of them?
8.
Modify the function from the previous step so it now looks like this:
=REGEXREPLACE(CONCATENATE(G2,",",H2), "^,$", "")This is a function that first concatenates the ‘Latitude’ and the ‘Longitude’ columns, and replaces any single commas as seen previously with an empty string. “^” means beginning of the string and “$” means the end of the string. So that regex means: match any string that has a single ‘,’ between the start and end of the string, aka a single comma.
9.
Repeat steps 3-8 for placeOfDeath.
10.
And voila! You have a cleaned, geocoded column that you can start mapping!
Additional Resources
If you want to know about a Flourish, a visualization tool to use this cleaned dataset on, visit here.
If you want to know more about the developer, Awesome Table, visit here.
