How to join two datasets to map in CartoDB
How to join two datasets to map in CartoDB
Transcript
all right we are going to merge two
datasets in cartodb this is like a four
second process so who knows why I'm
actually making a video so right here we
have a shapefile which is a map of all
of the counties in the US it doesn't
have any fun data associated with it
though so we need to join it to another
data set and if we look at it we can see
that there is this earth geo ID this is
a code that is unique to every County so
as long as we have a data set that also
has this code for every single row we
can join it to our map and luckily we
have this ACS American Community Survey
2014 5-year table s1 9 0 1 which is
about income in the United States and so
this is our data set there it is not
actually in Portuguese so we are going
to join all of these fun columns here I
believe that I don't know one of these
is median income but all we really care
about is geo ID and this table is going
to match fgo ID in this shapefile here
so all we're gonna do is we're gonna go
to one of our files let's say we're in
our shapefile
we're gonna go to edit we're going to
merge with data set so two different
ways we can do this we can do a column
join if they have a column in common or
a spatial join if we had a bunch of
points somewhere say I have a list of
all the waffle houses in America I could
figure out which County has the most
waffle houses that's not what I'm doing
I'm joining on a column that is shared
between the two I will click call and
join I will say I'm using FG OID this is
the correct table right here and I'm
gonna join it to geo ID next step I'll
keep all the columns they suggest merge
datasets
I wish had some elevator music that I
could play alright so now we see that
the tables have been joined together and
you can tell this because we can go
let's go to Matt view and this still
looks boring right it's still just that
one color but maybe we want to make a
map of where people in certain income
brackets live or where you know the
median income is higher versus lower
we're gonna click Wizards right here
we're gonna go to core plus wizard and
we're going to map one of our columns so
I have another document somewhere that
tells me what each of these are but we
will just pretend that we don't know and
there we go we have mapped we've joined
two data sets and then we've mapped them
and everything is gorgeous and beautiful
I don't like that color let's do this
one it's a little bit nicer a little bit
nicer if you wanted to do say a hover
State on here you can just go through
here and see SQL that's not it Wizards
not a info window that sounds about
right
there's some tutorials out there about
how to deal with that but basically
that's how you join two data sets there
you go
datasets in cartodb this is like a four
second process so who knows why I'm
actually making a video so right here we
have a shapefile which is a map of all
of the counties in the US it doesn't
have any fun data associated with it
though so we need to join it to another
data set and if we look at it we can see
that there is this earth geo ID this is
a code that is unique to every County so
as long as we have a data set that also
has this code for every single row we
can join it to our map and luckily we
have this ACS American Community Survey
2014 5-year table s1 9 0 1 which is
about income in the United States and so
this is our data set there it is not
actually in Portuguese so we are going
to join all of these fun columns here I
believe that I don't know one of these
is median income but all we really care
about is geo ID and this table is going
to match fgo ID in this shapefile here
so all we're gonna do is we're gonna go
to one of our files let's say we're in
our shapefile
we're gonna go to edit we're going to
merge with data set so two different
ways we can do this we can do a column
join if they have a column in common or
a spatial join if we had a bunch of
points somewhere say I have a list of
all the waffle houses in America I could
figure out which County has the most
waffle houses that's not what I'm doing
I'm joining on a column that is shared
between the two I will click call and
join I will say I'm using FG OID this is
the correct table right here and I'm
gonna join it to geo ID next step I'll
keep all the columns they suggest merge
datasets
I wish had some elevator music that I
could play alright so now we see that
the tables have been joined together and
you can tell this because we can go
let's go to Matt view and this still
looks boring right it's still just that
one color but maybe we want to make a
map of where people in certain income
brackets live or where you know the
median income is higher versus lower
we're gonna click Wizards right here
we're gonna go to core plus wizard and
we're going to map one of our columns so
I have another document somewhere that
tells me what each of these are but we
will just pretend that we don't know and
there we go we have mapped we've joined
two data sets and then we've mapped them
and everything is gorgeous and beautiful
I don't like that color let's do this
one it's a little bit nicer a little bit
nicer if you wanted to do say a hover
State on here you can just go through
here and see SQL that's not it Wizards
not a info window that sounds about
right
there's some tutorials out there about
how to deal with that but basically
that's how you join two data sets there
you go