Uploading & joining US Census Data (from data.census.gov) in ArcGIS Online

hi folks welcome back thank you so much for joining me today we are going to be using census data in ArcGIS online and go through the entire process of downloading a table of data from the Census and shapefiles and joining them in ArcGIS online so let's assume that the scenario is this we want to find out where within our County there are larger households and within a county we know that census tracts are a smaller unit of analysis we could also look at block groups or blocks but usually those numbers are really small so census tracts are often the best Geographic unit to look at so we'll be using the census tract level of data and so there are five steps to this the first is to download and clean the data from data census.gov the second is to download a shape file from the census Tyger files the third is to upload both of those things to ArcGIS online and finally join them and create a map out of them so we'll be going through each of these steps briefly and you can screenshot this and come back to it later on but we'll be using data census.gov and we'll be filtering using the geography filter to limit our data to census tracts and remember the cardinal rules of cleaning your table we have to find the geographic identifier and make sure that it's in the same format text or usually text in both the shapefile and the table of data the first row has to be a header with no spaces and the second should contain data not a sub header and then we'll have to save in CSV format and you'll want your data ultimately to look more or less like this right single header second row as data no spaces in the heading then we'll download the shape files from the census and we will keep them in a zipped folder which will then upload to our GIS online and the table in CSV format which we'll upload as a hosted layer and then join the table using the perform analysis summarize and join features tool and finally once we've done all those steps we can start visualizing the data all right so let's get started so the first thing we want to do is open data dot census.gov we can use the Advanced Search tab and the first thing we'll do so that we don't forget is to filter the geography to the census tract level we'll be using data for Orange County California so that we just have a smaller data set so we'll check all census tracts with an Orange County search notice it pulls up I'm easily almost 4500 results if we limit those two just tables we see 1345 tables still an easily estimate here and and that's all of the tables that would be available so we'll filter even further since we know that we want data on households household size we could look at household and family data or rather household and type and see if we then have fewer options here looks like that did not work ok it's still thinking here if all else fails we could also search by table number so here after a little bit of thinking the site is now pulling up the tables relevant to households and families and computers being a little bit slow so bear with me here in the meantime we can open up a second tab if your is this faster and type in census Tiger files and you'll notice the link that pops up under the add is the census gov tiger line shape files you can click on that and we'll just top this open and ready for when we come back to downloading the shape file it's still thinking okay here it goes so here we see the households and Families data and it's taking forever to load why don't we do this we'll go back to our filter and we'll type in the table number be 1 101 6 and so that didn't work so let's go back up to our advanced search and type in the table here B 1 101 6 so once we have the geography and we typed in the table number we were able to pull up this table which we can click on to view the table and we'll see that the most recent data are the 2018 ACS 5-year estimates as usual we do want to use the 5-year estimates and the most recent data is perfect here we can see that we have the margins of errors and the estimates let's go ahead and click on customize table and just for the purposes of this particular exercise we're going to remove the margins of error notice each census tract has an estimate the number of total households and then family households non-family households broken down by number of persons in the household as well as the margin of error if we uncheck margin of error we can just retain the estimates make sure that the geography is showing here that we have limited our geography and we do see census tracts in Orange County about the margin of error came back here okay and now we can click on download' make sure that the correct year is checked download as a CSV file and click download now ok so we have our data table which went into the Downloads folder now let's get our shape file so census Tiger files we can go to the web interface and we can get the most recent geography or we can do 2018 that'll be the same but we can select 2018 here for census tracts why census tracts because that is the geographic unit of analysis that our table is is at as well so we need to match the geographic level of the table to the spatial layer and we can select census tracts for California and then download and we see that we get a zipped folder tl tiger lines 20 1806 is the state code for California and tracks alright so we need to do a little bit of editing to the ACS data table so let's open up our download folder and we'll go to the ACS data table that we just downloaded and we can extract to this particular folder and once we open up that folder you'll see as usual we have the data file and then metadata which is the data about our data which gives us our variable definitions oh it looks like the margins of error were downloaded after all okay so here we see our variable names and luckily the subheadings tell us what each of these are but remember we're going to have to delete those subheadings so we first need to keep the variables that we are going to keep and get rid of everything else just to make this a little bit easier a bloating process so we can keep here the estimates for total number of households so if I like that to make it easier we can also keep all of these estimates of the number of persons in the household okay and so we can get rid of all of the margins of error I'm just going to highlight them by holding down ctrl this may be faster than highlighting the ones we're going to keep and then you can right click and delete and then we'll go and look for the next ones that are margin of error and delete those okay and now we have the total number of households total number of family households - and subsequent persons then non-family households and by household size okay so we can keep all of these now so we need to make sure that we get rid of these sub headers here and normally with if this were a smaller data set I would suggest changing these variable names to make it easier to identify so you might have something like total households and here total family households and I also like to keep the original variable name sometimes these get long and we'll end up truncated when imported into arcmap but it's helpful when they do preserve because you can always work backwards to verify if you've made a mistake okay so we're gonna delete this sub header then we need to create a geographic identifier that matches in the shape file so with census tract data the easiest way to do this is to use the geo ID column and we what we'll need ultimately is a number that looks like this oh six oh five nine and then the census track number and it needs to be in text format how do I know this because that's what is contained in the shape file the geographic identifier in the shapefile is in this format so to do that we will highlight this entire column and we're going to find place and we're going to find the one four zero zero zero zero zero so five zeros along with the US and we're gonna replace it with one single apostrophe and that one will then give us a GOI D column that should be text formatted as indicated by these green triangles in the upper corners of the cell okay and we can keep the name it doesn't matter but it may be helpful to use for labeling so we'll keep that in there so here I like to save as we're going to save as common delimited and I like to keep the original table name and then add edited at the end that I know that this is the original table number but now I've made changes to it and again remember to save in CSV format and we can close out of this so let's go to ArcGIS online nail to finish this process up so we'll sign in for my students we're using our enterprise login and we're going to go to content and we want to upload the two files that we have just added so we're under content we're going to add item from computer and we'll under the Downloads folder find the tiger lines and we can add that and notice it automatically detects it's a shape file and we might call it California census tracts and maybe use tracks as a tag and we can add that and it's almost done uploading here okay that took a while awkward silence [Music] so now we've uploaded the shape file we could give it a summary and a description that's always good practice but for speediness I'm gonna just add in a very quick summary here California census tracks downloaded from and we could just copy the website and we might add that it's 2018 data and copy the website here okay so we'll save that it may take just a moment here to complete creating the service and the next step that we'll want to do is to upload the table of data and then the final step will be to upload them and join them on a map alright so it looks like that finally completed these are no longer greyed out so we can go back to our content and add the second item from the computer it's inside the unzipped folder that we just edited right and since I use all caps which really annoys some people but it makes it easy for me to find my files found that quickly I can leave the title as is because it's quite descriptive it's the ACS table 2018 this is the table number the b1 101 six and I've edited and I might also use tracks as a tag because it is at the census track level you do want to publish this as a hosted layer and we can add this as a table we just want to verify that geo ID is being imported correctly which it's not notice that arcmap is trying to import it as a double but it should be a string that's the field type that was the point of us trying to add that apostrophe so sometimes CSV files especially when edited in Excel will be a little bit funky so we just need to make sure here that we've adjusted this to string everything else looks good so we can add the item almost there folks so we could do the same here adding a summary table of census tract data for Orange County California on household size by family and non family and census tract data it was ACS 2018 5-year estimates maybe we don't need that in the summary but we could add that in the description if we wanted to but we definitely want to include the data sources and even the table numbers in our data description okay so now that we have added both of these things we can start a new map or weakening the sheet from here you can open this in map viewer although it's a table so it won't be visible and once we have the table added we can also add the shape file that we uploaded and we'll see that we just uploaded that a moment ago and it should appear if you have my content here you shouldn't have too much content you should just have the table and the census shots potentially maybe a little but more if we've done other tutorials together so now we have the census tract shape file for all of California and then the table of data for Orange County so before we do anything else let's filter these census tracts to just Orange County census tracts and we can do that by limiting to the county code Orange County is oh five nine so we can filter by county F P being equal to oh five nine and it may take a second to draw here and now we have our Orange County census tracts okay folks we are almost there the last thing that we need to do now is to join these the table of data to these census tracts and we're going to do that by clicking on perform analysis summarized data and join features and the target layer is the census tract shape file what we're trying to join is this table of data and we need to join by fields and the fields are geo ID in both tables it's a one-to-one join meaning each census tract only has one corresponding census tract in the table and we can name the resulting layer something like California census tracts household size now we'll know that we have and it's actually not California right it's OC census chats joined to household size we can use the current map extent and save the results in our own folder now this process will use just a few credits and it will be the number of records in each of these so we have I believe 583 in each table 583 census checks so it's going to consume a few credits and that's fine we can then click run analysis and we're almost done hang in there hopefully your computer is faster than mine or your internet connection I never know which to blame and it is trying to join 583 separate records so it may take a moment and then when it's done you will be able to visualize your data so to summarize what we've done is downloaded and cleaned the census data table downloaded the shapefile in the corresponding Geographic unit of analysis uploaded both of them to ArcGIS online and joined the table to the feature layer and the last portion you know how to do so I'll leave you to do that and before we go let's just verify by opening up the attribute table that we do now have the joint information to our census tracts and if you scroll to the right of the table we do indeed have all of those variables that we've just added now in my haste I didn't change the variable names which makes it really difficult and error-prone so I highly recommend that you do add variable names to the front of these original variables but if you get confused you can always go back to the metadata file to look up the variable definitions right so total number of households is this first one total number of family households is a second and so on alright folks so we've just gone through the entire process of how to download census data and join it in arcmap and now you are free to visualize it on your own thank you so much for your attention

Channel: Social Determinants of health

Views: 2,140

Rating: 5 out of 5

Keywords: Census, GIS, Data, Mapping, data.census.gov, data.gov, shapefiles, Excel

Id: CoJ-jHUGXLM

Length: 23min 30sec (1410 seconds)

Published: Fri Mar 27 2020