2c:null 2d:["$","title",null,{"children":"Parsing and mapping US Census data | Dynamic Data Visualization 373 | Observable"}] 32:Tcba,## American Community Survey 5 year data The Census Bureau [provides over 20 families of APIs](https://www.census.gov/data/developers/data-sets.html). Each of these is further broken down by year and time span. We'll restrict our work here to deal with the most recent vintage (2021) of the American Community Survey 5 year summary data. This provides data on a wide range of topics covering social, economic, and demographic, characteristics of the US population. There is also data available for 3 year and 1 year time spas but that data tends to be available for fewer types of regions. ### The anatomy of an API query Let's take a look at a few API queries:

Picking that apart, we see: - URL Base for all APIs: `https://api.census.gov/data/` - Vintage: `2021` - The year for which we'd like to get data - API specification: `/acs/acs5` - In this case, American Community Survey 5 year data. - Others listed on their API page - Variables to get: `?get=NAME,B01003_001E` - Name of the region and some value of interest. - Predicates to specify the geographic region of interest: - `&for=us` - `&for=state` - `&for=state:37,39` - `&for=county&in=state:37` Of these, the URL Base, Vintage, and API specification are fairly simple. You might want to look back at older vintages and/or examine other APIs, but we'll stick with five year ACS data from 2021 here. The predicates specifying the geographic region a clearly a bit trickier but not too hard to get a grip on; the different types of regions and their hierarchical structure are detailed [on this page](https://api.census.gov/data/2021/acs/acs5/geography.html). The variables are by far the trickiest to comprehend. What, for example, might we expect `B01003_001E` to represent and how can we find variables that represent the things we are interested in? That's the main topic we focus on now. ## ACS variables The five year ACS summary data provides information on ${Object.keys(variables).length} variables different variables, all of which are described on this webpage:
https://api.census.gov/data/2021/acs/acs5/variables.html ### Examining the variables programmatically Rather than reading through the descriptions of all the variables, let's `fetch` the JSON version of that page so we can comb through it progammatically:33:[{"id":"4ecb2805459e3c5a9dd6127322dc5fe87ae5f98bdcacc608c33bbd1839284032ef91a71da5b747a8dcb71684929594307e5055f978aafbb2e4b65f343d6c6915","href":"https://static.observableusercontent.com/files/4ecb2805459e3c5a9dd6127322dc5fe87ae5f98bdcacc608c33bbd1839284032ef91a71da5b747a8dcb71684929594307e5055f978aafbb2e4b65f343d6c6915","name":"sex_by_age.csv","type":"text/csv","lastModified":1680612254791.942,"size":2583},{"id":"c0917466006822b00b2e6412e8039e908cc59892651dcfb68d302b56f453a8e73defa11c86b9227183bfebbf5ed2d24144b3246fdc4b49380fc719d48292ae1b","href":"https://static.observableusercontent.com/files/c0917466006822b00b2e6412e8039e908cc59892651dcfb68d302b56f453a8e73defa11c86b9227183bfebbf5ed2d24144b3246fdc4b49380fc719d48292ae1b","name":"states.json","type":"application/json","lastModified":1680612254791.942,"size":164626}] 30:{"id":"1f116876ab8b5b47","slug":null,"title":"Parsing and mapping US Census data","thumbnail":"55db7bf83ffc59fce1dc78202ab21684b1827ae6b1f1c9ecbe3346560a10c50f","access_level":"public","create_time":"$D2023-04-04T12:44:14.791Z","update_time":"$D2023-04-11T09:23:45.942Z","view_time":null,"first_public_time":"$D2023-09-07T00:29:44.525Z","fork":{"id":"e91ffeec38b91b19","slug":null,"title":"Parsing US Census data","owner":{"id":"d771c8ae812d5130","login":"mcmcclur","name":"Mark McClure"}},"stars":0,"starred":false,"owner":{"type":"team","id":"a07c87acf1b22f86","login":"ddv373","name":"Dynamic Data Visualization 373","avatar_url":"https://avatars-next.observableusercontent.com/aa07fb823a47852e43ceca20a9030290c7b3befd6ad2b87a17c17f4b47176d35","tier":"pro_2024"},"version":1329,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"c3301e515dd08a41","login":"mcmcclur","name":"Mark McClure","avatar_url":"https://avatars-next.observableusercontent.com/e615e819ce83d43faef607e48c53c5ff63943aa0ba9f5848499bd02fef7396f4","tier":null}],"members":[{"type":"individual","id":"c3301e515dd08a41","login":"mcmcclur","name":"Mark McClure","avatar_url":"https://avatars-next.observableusercontent.com/e615e819ce83d43faef607e48c53c5ff63943aa0ba9f5848499bd02fef7396f4","tier":null,"role":"editor"},{"type":"team","id":"a07c87acf1b22f86","login":"ddv373","name":"Dynamic Data Visualization 373","avatar_url":"https://avatars-next.observableusercontent.com/aa07fb823a47852e43ceca20a9030290c7b3befd6ad2b87a17c17f4b47176d35","tier":"pro_2024","role":"viewer"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":2,"collection":null,"type":"notebook","body":{"title":"Parsing and mapping US Census data","cells":[{"id":0,"value":"# Parsing and mapping US Census data\n\nThe US Census bureau publishes data on a ton of topics and makes it easily available to programmers via several APIs. The definitive introduction is certainly the official [Census Data API User Guide](https://www.census.gov/data/developers/guidance/api-user-guide.html). Still, the real challenge is that there is so much data that it can be difficult to parse it all to find what you're looking for. Even their [Data Discovery tool](https://api.census.gov/data.html) is a bit overwhelming. This notebook illustrates an approach that I sometimes use to facilitate that process. \n\nA concrete example of a map built on data discovered using this approach is this map of North Carolina counties shaded by Gini Index:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":752,"value":"make_map(true)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":760,"value":"As mentioned there, I'm really no expert on economics; rather, I stumbled upon the Gini Index in the US Census data by playing with this notebook. You can read a bit more about that map [in this notebook](https://observablehq.com/d/2cd910776a0daf15). One key aspect in the version above, though, is that that the county FIPS codes are displayed as the value of `COUNTYFP` on hover. That's a key identifier that also appears in Census a API call allowing you to connect API data to map regions.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":607,"value":"$32","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":167,"value":"variables = (\n await (\n await fetch(\"https://api.census.gov/data/2021/acs/acs5/variables.json\")\n ).json()\n).variables","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":193,"value":"The first three keys actually correspond to predicates used to form a query. Past that, the keys of that object are the actual variables. The value of each entry is itself an object with several keys of its own - notably the `label` and `concept` keys, that explain each variable in something akin to English. Let's grab the unique concepts and store them in an array. Turns out that there are ${concepts.length} of these:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":111,"value":"concepts = d3\n .sort(\n d3.filter(_.uniq(Object.values(variables).map((o) => o.concept)), (s) => s),\n (s) => s.length\n )\n .slice(0, -1)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":218,"value":"We sort by length of the string, since they can get ridiculously long; the longest one has ${concepts.slice(-1)[0].length} characters. I also sliced off the very last one on the original list, since it is a concatenation of *all* the others.\n\nNow, let's suppose we're interested in some general topic expressed as a word that should appear in one of those concepts. Type that word (say, INCOME, as an example) into the box below to filter the concepts and list all those that contain that word:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":361,"value":"viewof concept_matcher = {\n let input = Inputs.text({ value: \"INCOME\", label: \"Match 1:\" });\n d3.select(input).on(\"input\", function () {\n this.value = this.value.toUpperCase();\n });\n return input;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":573,"value":"viewof concept_matcher2 = {\n let input = Inputs.text({ value: \"\", label: \"Match 2:\" });\n d3.select(input).on(\"input\", function () {\n this.value = this.value.toUpperCase();\n });\n return input;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":223,"value":"viewof concept_table = Inputs.table(\n concepts\n .filter((s) => s.match(concept_matcher) && s.match(concept_matcher2))\n .map((s) => ({ concept: s }))\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":269,"value":"After filtering, there are ${concepts\n .filter((s) => s.match(concept_matcher) && s.match(concept_matcher2))\n.length} matching concepts displayed in the table. This still might be quite a lot but it's probably a lot less than we started with. Since they're sorted by string length, the simpler ones appear near the beginning so this is often good enough to find variables of interest. You can type a second word into the second box to narrow the results further. You could type RACE into that second box, for example, if you're interested in the relationship between income and race.\n\nNow, if you hover over the table, you should note that you can select a row using the checkbox that appears on the left. When you select exactly one row, the following snippet will grab all the variables for that concept and store it in `var_info` below:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":236,"value":"var_info = {\n if (concept_table.length == 1) {\n return Object.entries(variables)\n .filter((a) => a[1].concept == concept_table[0].concept)\n .map(([v, o]) => Object.assign(o, { var: v }));\n }\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":308,"value":"In Gini Index example for income inequality, I used \n\n GINI INDEX OF INCOME INEQUALITY\n\nas the concept to obtain my variable of interest. In that case, exactly one variable was displayed but there can be more. We'll take a look at an example where that happens in a bit.\n\nIn any event, once exactly one row is selected, we can list the actual variables that we would refer to in an API query:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":305,"value":"selected_vars = var_info ? var_info.map((o) => o.var).join(\",\") : \"\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":315,"value":"### Results\n\nNow, we should be able to execute a Census API call for a geographic region for all of these variables. Some results might be null since not every variable is available at all geographic levels; the call should run without an error, though.\n\nHere's the Census API call for the variables shown above for all 100 counties in North Carolina:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":803,"value":"https://api.census.gov/data/2021/acs/acs5?get=NAME,${selected_vars}&for=county:*&in=state:37","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":832,"value":"Of course, you'll probably want to change the geographic specification. After doing so, you can `fetch` the results:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":312,"value":"results = selected_vars\n ? await (\n await fetch(\n `https://api.census.gov/data/2021/acs/acs5?get=NAME,${selected_vars}&for=county:*&in=state:37`\n )\n ).json()\n : md``","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":818,"value":"Strangely, the result is not really JSON or CSV. Nonetheless, it's not hard to convert them to an array of objects appropriate for use in data visualizations:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":815,"value":"query_data = {\n let columns = results[0];\n return results.slice(1).map(function (a) {\n return Object.fromEntries(d3.zip(columns, a));\n });\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":836,"value":"### Using the results\n\nIf defined, the variable named `query_data` above should be usable in a data visualization. Typically, though, I download the results using Observable's download feature available through the three dot menu on the left side of the cell. I then upload that as a file attachment to a notebook.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":935,"value":"## Examples\n\n\n### An overly simple population choropleth\n\nAs an example, let's draw an Albers USA map of the US States shaded by population. From our perspective here, the key aspect is the following block of code that grabs data from an API call and translates it to a Javascript Map object to easily access population from a state's FIPS code. Note that the API call requests the variable `B01003_001E`, which I discovered represents population using the tools in this notebook.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":937,"value":"state_populations = {\n // The API call itself as discussed above.\n let results = await (\n await fetch(\n \"https://api.census.gov/data/2021/acs/acs5?get=NAME,B01003_001E&for=state\"\n )\n ).json();\n\n // Manipulate the results into proper JSON\n let columns = results[0];\n results = results.slice(1).map(function (a) {\n return Object.fromEntries(d3.zip(columns, a));\n });\n\n // Return the result as a Javascript Map object to easily access the\n // popoulation count from the state FIPS code.\n return d3.rollup(\n results,\n (a) => parseInt(a[0].B01003_001E),\n (o) => o.state\n );\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1122,"value":"Generate the map:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1310,"value":"Legend(map.scale, {\n tickFormat: (n) => `${d3.format(\",\")(Math.floor(n / 1000))}K`\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1297,"value":"map = {\n let w = d3.min([width, 1100]);\n let h = 0.6 * w;\n let pad = 10;\n let svg = d3\n .create(\"svg\")\n .attr(\"viewBox\", [0, 0, w, h])\n .style(\"max-width\", `${w}px`);\n\n let projection = d3.geoAlbersUsa();\n projection.fitExtent(\n [\n [pad, pad],\n [w - pad, h - pad]\n ],\n states\n );\n let path = d3.geoPath().projection(projection);\n\n let color_scale = d3\n .scaleQuantile()\n .domain(Array.from(state_populations.values()))\n .range(d3.schemeBlues[8]);\n\n svg\n .append(\"g\")\n .selectAll(\"path\")\n .data(states.features)\n .join(\"path\")\n .attr(\"d\", path)\n .attr(\"fill\", (d) =>\n color_scale(state_populations.get(d.properties.STATEFP))\n )\n .attr(\"stroke\", \"black\")\n .attr(\"stroke-width\", 1);\n\n svg.node().scale = color_scale;\n\n return svg.node();\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":973,"value":"For our purposes here, the important part is the `fill` property defined by:\n\n (d) => state_populations.get(d.properties.STATEFP)\n\nAnd here's the geographic data:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":948,"value":"// The geographic data file for the map\nstates = {\n let states = await FileAttachment(\"states.json\").json();\n states = topojson.feature(states, states.objects.states);\n return states;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1007,"value":"### An overly complicated bar chart\n\nWe can use Census data to build visualizations other than maps, of course. If we search for variables involving ` Age`, for example, we might find the `B01001` family of variables that gives of summary of populations by age group and sex. We can use that to generate a bar plot representing age distribution broken down by Male and Female:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1265,"value":"Swatches(d3.scaleOrdinal([\"Male\", \"Female\"], d3.schemePaired))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1259,"value":"paired_bar_plot = Plot.plot({\n marginLeft: 80,\n width: width,\n color: { domain: [\"Male\", \"Female\"], scheme: \"paired\" },\n x: {\n axis: null,\n domain: [\"Male\", \"Female\"] //d3.range(0, 90, 5).map((n) => (n == 85 ? `${n}-` : `${n}-${n + 4}`))\n },\n facet: { data: pop_by_age_sex, x: \"age_range\" },\n fx: {\n domain: d3.range(0, 90, 5).map((n) => (n == 85 ? `${n}-` : `${n}-${n + 4}`))\n },\n marks: [\n Plot.barY(pop_by_age_sex, {\n x: \"sex\",\n y: \"value\",\n fill: \"sex\",\n stroke: \"black\"\n }),\n Plot.ruleY([0])\n ]\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1272,"value":"You can expand that cell to see the Observable `Plot` code that generates the image. Let's focus on the data, though. While our last example on total population had just one variable, this one has 49; I've saved the information on the variables themselves in a file:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1137,"value":"sex_by_age_info = {\n let sex_by_age_info = await FileAttachment(\"sex_by_age.csv\").csv();\n\n // Supplement the data with an age_base\n sex_by_age_info.forEach(function (o) {\n let label_components = o.label.split(\"!!\");\n let sex = label_components[2];\n if (sex) {\n o.sex = sex.slice(0, -1);\n }\n let age_base = label_components[3];\n if (age_base) {\n age_base = age_base.slice(0, -6);\n if (age_base == \"Under 5\") {\n age_base = 0;\n } else if (age_base) {\n age_base = 5 * Math.floor(parseInt(age_base.split(\" \")[0]) / 5);\n }\n o.age_base = age_base;\n }\n });\n\n return sex_by_age_info;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1143,"value":"Note that the labels include things like\n\n Estimate!!Total:!!Male:!!25 to 29 years\n Estimate!!Total:!!Female:!!Under 5 years\n Estimate!!Total:!!Female:!!18 and 19 years\n Estimate!!Total:!!Male:!!85 years and over\n\nThus, there's some inconsistencies in the nature and sizes of these groups but, if you take a close look at the data though, you might note that it's possible to group things into blocks of five. That's the point of the `forEach` loop that supplements each variable with a \"base\" - that is the multiple of five that just ${tex`\\leq`} the age group. \n\nWith this in hand, we should be able to group our query results into the blocks of age range five. First, or course, we'll use the variable names stored in `sex_by_age_info` to form our query:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1148,"value":"sex_by_age_results = fetch(\n `https://api.census.gov/data/2021/acs/acs5?for=us&get=NAME,${sex_by_age_info\n .map((o) => o.var)\n .join()}`\n).then((r) => r.json())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1171,"value":"Let's pair these variables up with the the values obtained from the query:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1165,"value":"values = new Map(d3.zip(...sex_by_age_results).slice(1, -1))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1255,"value":"Finally, we'll use this to create an array of objects that we ultimately gets passed to `Plot`:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":1174,"value":"pop_by_age_sex = {\n let data = d3.rollup(\n sex_by_age_info,\n (a) => ({\n sex: a[0].sex,\n age_range: `${a[0].age_base}-${\n a[0].age_base == 85 ? \"\" : a[0].age_base + 4\n }`,\n value: d3.sum(a.map((o) => parseInt(values.get(o.var))))\n }),\n (o) => o.sex,\n (o) => o.age_base\n );\n\n return Array.from(data.values())\n .map((d) => Array.from(d.values()))\n .flat()\n .filter((o) => !o.age_range.match(\"undefined\"));\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1295,"value":"## Assignment\n\nFor this assignment, you're going to build a population chorpleth for the counties in the state that you played with last time. This time, though, we're going to work purely with US Census data so, in addition to the API data you'll get using the techniques in this notebook, you should also use the [US Census Cartographic mapping files](https://www.census.gov/geographies/mapping-files/time-series/geo/carto-boundary-file.html). We'll go through the process of grabbing those and generating TopoJSON in class.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":665,"value":"## Code","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":755,"value":"tippy_style","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":722,"value":"import { make_map, tippy_style } from \"2cd910776a0daf15\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":1262,"value":"import { Swatches, Legend } from \"@d3/color-legend\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1","titling":"auto"}} 31:"$30:body"