Hi guys, in this short video I'm going to walk
you through how to download data from the US Census
Bureau American Fact Finder and then join that data to census tract and display them for Santa Clara County. So
what we are going to do is we are going to look up the education
attainment level using an Advanced Search. So I'm going to go
to factfinder dot census dot gov, and click on the advanced
search and say show me all so I'm going to type in the name of the table
that I'm looking up. So you can actually find a listing of all of these tables on this website.
So there is a ton of data available. So we want to do the location as Santa Clara county. So now we want to select what geographic units we want to look
at so we go over here to Geographies and we are
looking specifically at Census tracts. So I'm going to pull down
here and select census tracts for California and I want Santa Clara County And I want all tracts within Santa Clara County. That is the first option. Then I click add to your
selections and I close this window. So now I have my education attainment, and I have selected that this is the table that I want, so now I'm
going to say go and this gives me just the education attainment, or S1501. I'm going to choose the 2017 5 year estimates
because that will give me the greatest detail of data. Okay so now I can check over here with my
selection S1501 education attainment and I have census
tract all counties within Santa Clara, so now I click
download that is going to download a zip file, okay so in our estimate S1501 folder, we have several different files. Some of these are readme documents, some
are the metadata, and this is the one we are
interested in, the one with annotations. So let's go ahead and
open it up. I have it in Excel. You can either look at it in Excel or
in Google docs. So this is a very large file and it is going to kind of choke our
software if we import the whole thing. So we are only
going to import the fields that we are interested in. So this data, the data tha is from the census comes with two different
headers. The first one is a header that is easily interpreted by
machines and it is kind of a series of letters and numbers,
gibbley gook the second one is in English. Now this will not import well into a database
software so what we are going to do is we are going to
remove this second header and before you do that though, I
encourage to peruse these different records and look for what is interesting to you. I suggest that we look at HC03 Estimate VC11 which is the number of males over the age of 25 who are high school
graduates so that we what we are going to work with here.
I am going to delete these second header row. So I'm going to delete all of the fields except for the Geoid field
and the field that I am intereseted in. So there it is, so I am going to then highlight all of the fields that I do not need, and delete them, and then on the other side as well, okay so I am looking at just two fields and a whole bunch of records. I am going to then save this and we can see that this is a much much smaller file. Okay so now I am going to import it into my Geodatabase okay and I have my green check box so it was successful. Open my geodatabase and see my two layers are in there. Now let's add them to a map Okay so I am going to open up my tables and just inspect what we have here. So I have my geoid field which I can tell is a text field because it is justified to the left side notice that the numeric entries are justified so
that the shape length or shape area are justified to the
right. We can tell without even needing to look at the
table set up that this is a text entry. And the reason that it is text is
because we have this leading zero. So this a concatenation of the state the county and the census tract. So the state is 06, California, it is the sixth state alphabetically. 085 is the FIPS code for Santa Clara county and then the remaining
digits are the census tract. So this is our geoid and if we open our high school grads table we can see pull this out here, that we have this same information in our last digits of the id field. So what we need to do is we need to strip off the
first digits here, and just keep the remaining digits
starting with the 06. So to do this we are going to create a new field. I am going to call this geoid2. And I am going to make it into a text field. ... So after I make a change to my data I need to save it. Okay so then let's go back to the table and we can see that our new field is here. I am going to then open up the field calculator. So I'm going to type in the python
code block and now we can see that we have the matching geoid to this geoid okay so now we are ready to do the join. We go
over to the data tab and we select joins, and we
want to add a join. And we are going to use our geometry as our starting point and we are going to join using geoid to the male high school grads and we are going to use geoid2. So I simply say run. Now if I look at my census tract data I can see that I have this
estimate of my high school graduates over here and I can modify my symbology now to display
by male high school graduates. So we can see that the darkest census tracts of high school graduates at highest education level attained. There you go.
