Hey guys, in this video I wanted to do kind of a follow-up to my previous video, which
was on how to plot spatial data in R. And in the previous video, I did this example
of plotting gun violence incidents in the city of Chicago.
And so this is a map of the 77 communities in Chicago and each orange dot was a gun violence
incident.
And as kind of a prequel to doing that type of analysis, I had to do something that's
called geocoding my data, or geocoding data, which is basically--I had to take--I had a
data table or dataset that had--each row had an address, which was a gun violence incident.
And I had to geocode that, which means I had to turn each address into some kind of coordinate
reference system.
And in particular, I coded it using latitude and longitude.
In this video, I'm going to go over how you can geocode data, which you can then use to
do the spatial plotting analysis in the previous video.
This is kind of like a prequel to the previous video and it explains how to convert an address
into a coordinate, specifically latitude and longitude.
I have an example sample dataset from again the gun violence dataset that I was using
in the previous video, just a little bit cleaned up.
And so I have the first five incidents of gun violence in Chicago and as you can see,
I have a column in my data table that has the address and also the column for the city
but the city is always going to be Chicago.
And basically what I wanted to do is I want to get to a point where I have a column for
the latitude and a column for the longitude of the data, which is geocoding.
To geocode from an address to a latitude and longitude coordinate system, there's a couple
different ways that you can do it.
When I first looked online, there are a couple of websites that say that you can input or
give them a data table and they will automatically geocode.
But I found that a lot of those websites don't do it with very high accuracy.
So the way that I ended up geocoding my data was using a script that geocodes using Google
Maps and Google Maps obviously I found was high accurate.
That was the way I ended up going with.
And in particular, I found this script on this website and I'll put the link in the
description box below, which has a script that runs in Google Sheets.
Basically what I had to do was, I got my dataset, which was a CSV file.
And then I had to import it into a Google Sheets, which if you have a Google account,
if you go to Google Drive, and then just open a new Google Sheets--it's basically like an
Excel sheet but through the Google Drive system instead.
And basically you can just open a new Google Sheet and then load in or import your CSV
data table.
And so that's what I did already.
And before I follow the instructions on how to run the script that's on this website,
I had to clean up my data table a little bit.
Basically, the problem that I had for my data or the clean up that I had to do was I had
one column for address and another column for city, so the address by itself did not
have a city, state, or ZIP code.
If I tried to just geocode based on this column, that's not enough information for the script
to find this address.
I had to first concatenate the address and the city so that I could have that in one
column and then if I tried to geocode that, that would be enough information to find the
latitude and longitude coordinates.
Basically in the data table, which I did all of this in Google Sheets, I just had to concatenate
the address and city columns.
So I have some scripts here hilighted in yellow of what I did.
Basically, I had to concatenate the address line and the city, which I did using a function
in Google Sheets called concatenate.
Basically what I did was, if I just tried to concatenate address and the city columns
there would be no space in between the last letter of the address and Chicago, so it wouldn't
separate it into two different words.
So what I did was I created a new column that had a concatenation of a space and the word
Chicago so then if I tried to concatenate this column with the address I would get the
proper concatenation.
Basically--let me just erase this--let me use the fifth line or the fifth row as my
example, I went to this column and I said equal--this is equal to the function concat.
I inputed a space and then the value that I want is the value of Chicago.
That's going to be D5 and then when I click enter, that gives me space and then the word
Chicago.
And then for the next column that I created was the concatenation of the address and the
city.
That's basically just going to be using the concat function again and I'm going to concat
the address column and my newly created city column and then that gives me the address
and the city.
Now I'm ready to run a script that I will input the address and the city together to
get geocoded.
To do that, I'm just going to follow the directions that are on this website.
This website gives scripts for using either Google Maps or something called Yandex--I
don't know if that's from China or not--but I basically just used the first code.
I'm just going to copy this script, which was in the website, and then the instructions
for using this script in Google Sheets are given right here.
It says open a Google spreadsheet, go to script editor under the tools menu, and paste the
app script below.
I'm just going to do that.
I'm going to go to Tools, script editor, and I'm going to paste the function here.
And then once I have that function saved in my script editor, if I go back to my Google
Sheets and I try to find the function, it's now going to available for me to use.
And the name of the function is just geocode_google and it needs an address passed into it.
I'm going to pass in the address that I created in column J6.
I'm going to click enter.
That's going to run for a second, so it was loading for a second, and then that gives
me the geocoded data.
This is the coordinate reference system of latitude and longitude based on Google Maps
finding of this address and this city.
And then the last thing I did was, for running it in R, I just had to separate the latitude
and longitude.
To do that, I just ran this code split and I split it along the comma.
So instead of having one column with the latitude and longitude together, separated by a comma,
I just split that into two columns.
One for latitude and one for longitude.
I'm going to do split.
I'm going to pass K6 and then I'm going to say split along the comma and that will give
me a new row with a column of latitude and a column of longitude.
This is what I did to geocode all my data that I used for my previous video on spatial
plotting in R. I basically did that for all the rows in my dataset in Google Sheets and
then when I was done with Google Sheets, I basically exported the data table--the spreadsheet--as
a, probably a CSV file, and I used that file as an input in R. And one last thing to note
before I finish the video is that there is a quota.
I think it's also mentioned somewhere here in the website.
There's a quota for both Google Maps conversion or geocoding and I think also Yandex, even
though I didn't try that service.
But basically what that means is you can only do this conversion a couple hundred times
before it tells you basically that you reached a quota, or a maximum number of times that
it will do it for free.
The good thing is that the quota is reset every day.
I think I had over 1,000 rows in my original data table and it let me do a couple hundred--I
don't remember exactly--the exact number of the quota but it let me do a couple hundred
conversions before I reached the quota, but then the quota was reset the next day.
So I just had to basically wait one day to finish doing my entire data table but that
is something to note if you're trying to do the conversions.
There is a quota and I think that's only for the free version so maybe there's a premium
version where you can do it an unlimited number of times but I'm not sure about that.
But anyway, that's it for this video and thank you guys for watching.
*chiptune music*