Connect Excel with Census.gov Population Data

[gmooney]

Hello,

I have been trying to use ChatGPT and even Microsoft CoPilot to walk me through connecting a blank Excel workbook to some Census.gov population data.

It discusses using API to do this and I am not very versed on API.

I think the api that I want is this:

http://api.census.gov/data/2020/acs/acs5

However, when I do either an Excel Web query or just a blank query of the query steps within the api all I get in Excel is a 6 row table without the data.

The query behind the above api is:

{
"@context": "https://project-open-data.cio.gov/v1.1/schema/catalog.jsonld",
"@id": "http://api.census.gov/data/2020/acs/acs5.json",
"@type": "dcat:Catalog",
"conformsTo": "DCAT-US Schema v1.1 (Project Open Data Metadata Schema) | resources.data.gov",
"describedBy": "https://project-open-data.cio.gov/v1.1/schema/catalog.json",
"dataset": [
{
"c_vintage": 2020,
"c_dataset": [
"acs",
"acs5"
],
"c_geographyLink": "http://api.census.gov/data/2020/acs/acs5/geography.json",
"c_variablesLink": "http://api.census.gov/data/2020/acs/acs5/variables.json",
"c_tagsLink": "http://api.census.gov/data/2020/acs/acs5/tags.json",
"c_examplesLink": "http://api.census.gov/data/2020/acs/acs5/examples.json",
"c_groupsLink": "http://api.census.gov/data/2020/acs/acs5/groups.json",
"c_sorts_url": "http://api.census.gov/data/2020/acs/acs5/sorts.json",
"c_documentationLink": "Developers",
"c_isAggregate": true,
"c_isCube": true,
"c_isAvailable": true,
"@type": "dcat:Dataset",
"title": "American Community Survey: 5-Year Estimates: Detailed Tables 5-Year",
"accessLevel": "public",
"bureauCode": [
"006:07"
],
"description": "The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a broad range of topics about social, economic, demographic, and housing characteristics of the U.S. population. Summary files include the following geographies: nation, all states (including DC and Puerto Rico), all metropolitan areas, all congressional districts (117th Congress), all counties, all places, and all tracts and block groups. Summary files contain the most detailed cross-tabulations, many of which are published down to block groups. The data are population and housing counts. There are over 64,000 variables in this dataset.",
"distribution": [
{
"@type": "dcat:Distribution",
"accessURL": "http://api.census.gov/data/2020/acs/acs5",
"description": "API endpoint",
"format": "API",
"mediaType": "application/json",
"title": "API endpoint"
}
],
"contactPoint": {
"fn": "American Community Survey Office",
"hasEmail": "mailto:acso.users.support@census.gov"
},
"identifier": "https://api.census.gov/data/id/ACSDT5Y2020",
"keyword": [
"census"
],
"license": "Deed - CC0 1.0 Universal - Creative Commons",
"modified": "2021-07-13 00:00:00.0",
"programCode": [
"006:004"
],
"references": [
"Developers"
],
"spatial": "US",
"temporal": "2020/2020",
"publisher": {
"@type": "org:Organization",
"name": "U.S. Census Bureau",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Department Of Commerce",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Government"
}
}
}
},
{
"c_vintage": 2020,
"c_dataset": [
"acs",
"acs5",
"cprofile"
],
"c_geographyLink": "http://api.census.gov/data/2020/acs/acs5/cprofile/geography.json",
"c_variablesLink": "http://api.census.gov/data/2020/acs/acs5/cprofile/variables.json",
"c_tagsLink": "http://api.census.gov/data/2020/acs/acs5/cprofile/tags.json",
"c_examplesLink": "http://api.census.gov/data/2020/acs/acs5/cprofile/examples.json",
"c_groupsLink": "http://api.census.gov/data/2020/acs/acs5/cprofile/groups.json",
"c_sorts_url": "http://api.census.gov/data/2020/acs/acs5/cprofile/sorts.json",
"c_documentationLink": "Developers",
"c_isAggregate": true,
"c_isCube": true,
"c_isAvailable": true,
"@type": "dcat:Dataset",
"title": "American Community Survey: 5-Year Estimates: Comparison Profiles 5-Year",
"accessLevel": "public",
"bureauCode": [
"006:07"
],
"description": "The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a broad range of topics about social, economic, demographic, and housing characteristics of the U.S. population. The Comparison Profiles include the following geographies: nation, all states (including DC and Puerto Rico), all metropolitan areas, all congressional districts, all counties, all places and all tracts. Comparison Profiles contain broad social, economic, housing, and demographic information. The data are presented as both counts and percentages. There are over 2,400 variables in this dataset.",
"distribution": [
{
"@type": "dcat:Distribution",
"accessURL": "http://api.census.gov/data/2020/acs/acs5/cprofile",
"description": "API endpoint",
"format": "API",
"mediaType": "application/json",
"title": "API endpoint"
}
],
"contactPoint": {
"fn": "American Community Survey Office",
"hasEmail": "mailto:acso.users.support@census.gov"
},
"identifier": "https://api.census.gov/data/id/ACSCP5Y2020",
"keyword": [
"census"
],
"license": "Deed - CC0 1.0 Universal - Creative Commons",
"modified": "2021-07-13 00:00:00.0",
"programCode": [
"006:004"
],
"references": [
"Developers"
],
"spatial": "US",
"temporal": "2020/2020",
"publisher": {
"@type": "org:Organization",
"name": "U.S. Census Bureau",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Department Of Commerce",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Government"
}
}
}
},
{
"c_vintage": 2020,
"c_dataset": [
"acs",
"acs5",
"profile"
],
"c_geographyLink": "http://api.census.gov/data/2020/acs/acs5/profile/geography.json",
"c_variablesLink": "http://api.census.gov/data/2020/acs/acs5/profile/variables.json",
"c_tagsLink": "http://api.census.gov/data/2020/acs/acs5/profile/tags.json",
"c_examplesLink": "http://api.census.gov/data/2020/acs/acs5/profile/examples.json",
"c_groupsLink": "http://api.census.gov/data/2020/acs/acs5/profile/groups.json",
"c_sorts_url": "http://api.census.gov/data/2020/acs/acs5/profile/sorts.json",
"c_documentationLink": "Developers",
"c_isAggregate": true,
"c_isCube": true,
"c_isAvailable": true,
"@type": "dcat:Dataset",
"title": "American Community Survey: 5-Year Estimates: Data Profiles 5-Year",
"accessLevel": "public",
"bureauCode": [
"006:07"
],
"description": "The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a broad range of topics about social, economic, housing, and demographic characteristics of the U.S. population. The ACS 5-year data profiles include the following geographies: nation, all states (including DC and Puerto Rico), all metropolitan areas, all congressional districts, all counties, all places and all tracts. The Data profiles contain broad social, economic, housing, and demographic information. The data are presented as both counts and percentages. There are over 2,400 variables in this dataset.",
"distribution": [
{
"@type": "dcat:Distribution",
"accessURL": "http://api.census.gov/data/2020/acs/acs5/profile",
"description": "API endpoint",
"format": "API",
"mediaType": "application/json",
"title": "API endpoint"
}
],
"contactPoint": {
"fn": "American Community Survey Office",
"hasEmail": "mailto:acso.users.support@census.gov"
},
"identifier": "https://api.census.gov/data/id/ACSDP5Y2020",
"keyword": [
"census"
],
"license": "Deed - CC0 1.0 Universal - Creative Commons",
"modified": "2021-07-13 00:00:00.0",
"programCode": [
"006:004"
],
"references": [
"Developers"
],
"spatial": "US",
"temporal": "2020/2020",
"publisher": {
"@type": "org:Organization",
"name": "U.S. Census Bureau",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Department Of Commerce",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Government"
}
}
}
},
{
"c_vintage": 2020,
"c_dataset": [
"acs",
"acs5",
"subject"
],
"c_geographyLink": "http://api.census.gov/data/2020/acs/acs5/subject/geography.json",
"c_variablesLink": "http://api.census.gov/data/2020/acs/acs5/subject/variables.json",
"c_tagsLink": "http://api.census.gov/data/2020/acs/acs5/subject/tags.json",
"c_examplesLink": "http://api.census.gov/data/2020/acs/acs5/subject/examples.json",
"c_groupsLink": "http://api.census.gov/data/2020/acs/acs5/subject/groups.json",
"c_sorts_url": "http://api.census.gov/data/2020/acs/acs5/subject/sorts.json",
"c_documentationLink": "Developers",
"c_isAggregate": true,
"c_isCube": true,
"c_isAvailable": true,
"@type": "dcat:Dataset",
"title": "American Community Survey: 5-Year Estimates: Subject Tables 5-Year",
"accessLevel": "public",
"bureauCode": [
"006:07"
],
"description": "The American Community Survey (ACS) is an ongoing survey that provides data every year -- giving communities the current information they need to plan investments and services. The ACS covers a broad range of topics about social, economic, demographic, and housing characteristics of the U.S. population. The subject tables include the following geographies: nation, all states (including DC and Puerto Rico), all metropolitan areas, all congressional districts, all counties, all places and all tracts. Subject tables provide an overview of the estimates available in a particular topic. The data are presented as both counts and percentages. There are over 66,000 variables in this dataset.",
"distribution": [
{
"@type": "dcat:Distribution",
"accessURL": "http://api.census.gov/data/2020/acs/acs5/subject",
"description": "API endpoint",
"format": "API",
"mediaType": "application/json",
"title": "API endpoint"
}
],
"contactPoint": {
"fn": "American Community Survey Office",
"hasEmail": "mailto:acso.users.support@census.gov"
},
"identifier": "https://api.census.gov/data/id/ACSST5Y2020",
"keyword": [
"census"
],
"license": "Deed - CC0 1.0 Universal - Creative Commons",
"modified": "2021-07-13 00:00:00.0",
"programCode": [
"006:004"
],
"references": [
"Developers"
],
"spatial": "US",
"temporal": "2020/2020",
"publisher": {
"@type": "org:Organization",
"name": "U.S. Census Bureau",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Department Of Commerce",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Government"
}
}
}
},
{
"c_vintage": 2020,
"c_dataset": [
"acs",
"acs5",
"pums"
],
"c_geographyLink": "http://api.census.gov/data/2020/acs/acs5/pums/geography.json",
"c_variablesLink": "http://api.census.gov/data/2020/acs/acs5/pums/variables.json",
"c_tagsLink": "http://api.census.gov/data/2020/acs/acs5/pums/tags.json",
"c_examplesLink": "http://api.census.gov/data/2020/acs/acs5/pums/examples.json",
"c_groupsLink": "http://api.census.gov/data/2020/acs/acs5/pums/groups.json",
"c_sorts_url": "http://api.census.gov/data/2020/acs/acs5/pums/sorts.json",
"c_documentationLink": "Developers",
"c_isMicrodata": true,
"c_isCube": true,
"c_isAvailable": true,
"@type": "dcat:Dataset",
"title": "2020 American Community Survey: 5-Year Estimates - Public Use Microdata Sample",
"accessLevel": "public",
"bureauCode": [
"006:07"
],
"description": "The American Community Survey (ACS) Public Use Microdata Sample (PUMS) contains a sample of responses to the ACS. The ACS PUMS dataset includes variables for nearly every question on the survey, as well as many new variables that were derived after the fact from multiple survey responses (such as poverty status). Each record in the file represents a single person, or, in the household-level dataset, a single housing unit. In the person-level file, individuals are organized into households, making possible the study of people within the contexts of their families and other household members. Individuals living in Group Quarters, such as nursing facilities or college facilities, are also included on the person file. ACS PUMS data are available at the nation, state, and Public Use Microdata Area (PUMA) levels. PUMAs are special non-overlapping areas that partition each state into contiguous geographic units containing roughly 100,000 people each. ACS PUMS files for an individual year, such as 2020, contain data on approximately one percent of the United States population.",
"distribution": [
{
"@type": "dcat:Distribution",
"accessURL": "http://api.census.gov/data/2020/acs/acs5/pums",
"description": "API endpoint",
"format": "API",
"mediaType": "application/json",
"title": "API endpoint"
}
],
"contactPoint": {
"fn": "American Community Survey Office",
"hasEmail": "mailto:acso.users.support@census.gov"
},
"identifier": "https://api.census.gov/data/id/ACSPUMS5Y2020",
"keyword": [
"census"
],
"license": "Deed - CC0 1.0 Universal - Creative Commons",
"modified": "2022-01-31 00:00:00.0",
"programCode": [
"006:004"
],
"references": [
"Developers"
],
"publisher": {
"@type": "org:Organization",
"name": "U.S. Census Bureau",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Department Of Commerce",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Government"
}
}
}
},
{
"c_vintage": 2020,
"c_dataset": [
"acs",
"acs5",
"pumspr"
],
"c_geographyLink": "http://api.census.gov/data/2020/acs/acs5/pumspr/geography.json",
"c_variablesLink": "http://api.census.gov/data/2020/acs/acs5/pumspr/variables.json",
"c_tagsLink": "http://api.census.gov/data/2020/acs/acs5/pumspr/tags.json",
"c_examplesLink": "http://api.census.gov/data/2020/acs/acs5/pumspr/examples.json",
"c_groupsLink": "http://api.census.gov/data/2020/acs/acs5/pumspr/groups.json",
"c_sorts_url": "http://api.census.gov/data/2020/acs/acs5/pumspr/sorts.json",
"c_documentationLink": "Developers",
"c_isMicrodata": true,
"c_isCube": true,
"c_isAvailable": true,
"@type": "dcat:Dataset",
"title": "2020 American Community Survey: 5-Year Estimates - Public Use Microdata Sample Puerto Rico",
"accessLevel": "public",
"bureauCode": [
"006:07"
],
"description": "The Public Use Microdata Sample (PUMS) for Puerto Rico (PR) contains a sample of responses to the Puerto Rico Community Survey (PRCS). The PRCS is similar to, but separate from, the American Community Survey (ACS). The PRCS collects data about the population and housing units in Puerto Rico. Puerto Rico data is not included in the national PUMS files. It is published as a state equivalent file and has a State FIPS code of \"72\". The file includes variables for nearly every question on the survey, as well as many new variables that were derived after the fact from multiple survey responses (such as poverty status). Each record in the file represents a single person, or, in the household-level dataset, a single housing unit. In the person-level file, individuals are organized into households, making possible the study of people within the contexts of their families and other household members. Individuals living in Group Quarters, such as nursing facilities or college facilities, are also included on the person file. Data are available at the state and Public Use Microdata Area (PUMA) levels. PUMAs are special non-overlapping areas that partition Puerto Rico into contiguous geographic units containing roughly 100,000 people each. The Puerto Rico PUMS file for an individual year, such as 2020, contain data on approximately one percent of the Puerto Rico population.",
"distribution": [
{
"@type": "dcat:Distribution",
"accessURL": "http://api.census.gov/data/2020/acs/acs5/pumspr",
"description": "API endpoint",
"format": "API",
"mediaType": "application/json",
"title": "API endpoint"
}
],
"contactPoint": {
"fn": "American Community Survey Office",
"hasEmail": "mailto:acso.users.support@census.gov"
},
"identifier": "https://api.census.gov/data/id/ACSPUMS5YPR2020",
"keyword": [
"census"
],
"license": "Deed - CC0 1.0 Universal - Creative Commons",
"modified": "2022-01-31 00:00:00.0",
"programCode": [
"006:004"
],
"references": [
"Developers"
],
"publisher": {
"@type": "org:Organization",
"name": "U.S. Census Bureau",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Department Of Commerce",
"subOrganizationOf": {
"@type": "org:Organization",
"name": "U.S. Government"
}
}
}
}
]
}

 

[Rijnsent]

Hi Gmooney,
what census data are you looking for? When I start here: Developers -> you probably first want to get an API-key from them (they'll use that to make sure nobody is using too much of the API).

Next, from the datasets they have, you can arrive at e.g. this page/dataset: Economic Census (2002 – 2022)
If you check out the examples you end up on this page: Census Data API: /data/2022/ecnbasic/examples
One example of an API-call could be: https://api.census.gov/data/2022/ecnbasic?get=NAICS2022,NAICS2022_LABEL,NAME,GEO_ID,ESTAB,RCPTOT,EMP&for=state:24&key=YOUR_KEY_GOES_HERE (in that example list). That returns data from that ECNBASIC set and returns a couple of variables for state nr 24, but as you see, it also needs a valid API-key to work.

When you have a correct API-call, you can use the Data->From Web->PowerQuery to get that data into a table in Excel.
Cheers,
Koen

 

[gmooney]

Hi Gmooney,
what census data are you looking for? When I start here: Developers -> you probably first want to get an API-key from them (they'll use that to make sure nobody is using too much of the API).

Next, from the datasets they have, you can arrive at e.g. this page/dataset: Economic Census (2002 – 2022)
If you check out the examples you end up on this page: Census Data API: /data/2022/ecnbasic/examples
One example of an API-call could be: https://api.census.gov/data/2022/ecnbasic?get=NAICS2022,NAICS2022_LABEL,NAME,GEO_ID,ESTAB,RCPTOT,EMP&for=state:24&key=YOUR_KEY_GOES_HERE (in that example list). That returns data from that ECNBASIC set and returns a couple of variables for state nr 24, but as you see, it also needs a valid API-key to work.

When you have a correct API-call, you can use the Data->From Web->PowerQuery to get that data into a table in Excel.
Cheers,
Koen

Hi there,

I did aquire an API and the data that I would like to use is population data at the MSA level but I am having a hard time finding the right starting location on Census.gov to get to the actual URLs that I would want to get. The naming for population datasets seems confusing so not sure where to start.

 

[Rijnsent]

Starting from the developers page I arrive here: Available APIs
From there, I assume you're looking for the Census data, but I too find that hard to locate. When I try this page: Explore Census Data -> that gives a drag & drop topics & subjects interface. When you select the right geography and the right topic/subject, there is an API button. So from here: Explore Census Data -> you get this as an API call: https://api.census.gov/data/2023/acs/acs5/subject?get=group(S0101)&ucgid=pseudo(0100000US$3100000) -> that is the one you can pull into Excel. You will need the metadata to see what the columns mean. This is a bit from that same query in PowerQuery:

 

[gmooney]

Starting from the developers page I arrive here: Available APIs
From there, I assume you're looking for the Census data, but I too find that hard to locate. When I try this page: Explore Census Data -> that gives a drag & drop topics & subjects interface. When you select the right geography and the right topic/subject, there is an API button. So from here: Explore Census Data -> you get this as an API call: https://api.census.gov/data/2023/acs/acs5/subject?get=group(S0101)&ucgid=pseudo(0100000US$3100000) -> that is the one you can pull into Excel. You will need the metadata to see what the columns mean. This is a bit from that same query in PowerQuery:

View attachment 122232

Thank you.......This is getting me closer and looks like the data I would like. When I add the api web query it loads with 1 column that shows list for each row. If I click on List for 1 row it looks like it is showing me 915 column headers?

 

[Rijnsent]

Yup, that's the whole table - which is 90% empty... So diving a bit deeper:
When you download the table (from the Explore data link) you get as meta-data:

So that gives an idea what all the columns should contain - my guess is that the !! is a separator between the label-fields.

Next, when you go to American Community Survey 5-Year Data (2009-2023) , there are some examples of API calls (scroll down a bit).

With that info I guessed that the fix could be in the part after /subject?get=... Try this one, it will only give back the total population per area (as that's code S0101_C01_001E) and the population of age 21 and over S0101_C02_027E:

https://api.census.gov/data/2023/acs/acs5/subject?get=GEO_ID,NAME,S0101_C01_001E,S0101_C02_027E&ucgid=pseudo(0100000US$3100000)

It does take some fiddling to get the right data, try e.g. changing the 2023 to 2015, you'll get the older data. However, the labelling seems inconsistent as S0101_C02_027E in 2015 shows unrealistic percentages for part of the population of 21 and over. This is probably not exactly how the API is intended to work as the GET doesn't use a key, but hey, it works . As a bonus: they seem to have a course for developers, if you really want to dive in that might be the way to go.