Mapping Census Tracts in Excel.
Excel supports mapping by county, zip code, state, country. But they don't natively support census tracts. In today's video, you will see how to download population data and a map KML file from Census.Gov.
Learn how to import the KML file into the 3D Map feature in Excel and make a map from it.
This video also covers:
Downloading a CSV with population by census tract from Data.Census.Gov.
Downloading a KML file by Census Tract
Importing and merging that data in Excel.
Table of Contents
(0:00) Excel Mapping Other Jurisdictions
(0:51) Four Video Sections
(1:19) Examining KML File in Notepad++
(2:14) Opening 3D Map
(2:54) Importing KML to 3D Map
(3:30) Adding KML Field to Map
(5:06) Getting Population by Census Tract
(6:48) Where to get KML File
(7:18) Importing Population
(8:08) Importing Census Tracts
(9:01) Merging Population with KML Data
(9:58) Wrap-up
More questions answered in at the 8-9 minute mark:
how do i open a kml file in excel
how to open a kml file in excel
how do i convert a kml file to excel
how do i open a kml file in excel
how do i open a kml file in excel
how do i convert a kml file to excel
how to import kml to excel
how to make a kml file from excel
excel in kml
how to open a kml file in excel
how do i open a kml file in excel
how to open kml in excel
Excel supports mapping by county, zip code, state, country. But they don't natively support census tracts. In today's video, you will see how to download population data and a map KML file from Census.Gov.
Learn how to import the KML file into the 3D Map feature in Excel and make a map from it.
This video also covers:
Downloading a CSV with population by census tract from Data.Census.Gov.
Downloading a KML file by Census Tract
Importing and merging that data in Excel.
Table of Contents
(0:00) Excel Mapping Other Jurisdictions
(0:51) Four Video Sections
(1:19) Examining KML File in Notepad++
(2:14) Opening 3D Map
(2:54) Importing KML to 3D Map
(3:30) Adding KML Field to Map
(5:06) Getting Population by Census Tract
(6:48) Where to get KML File
(7:18) Importing Population
(8:08) Importing Census Tracts
(9:01) Merging Population with KML Data
(9:58) Wrap-up
More questions answered in at the 8-9 minute mark:
how do i open a kml file in excel
how to open a kml file in excel
how do i convert a kml file to excel
how do i open a kml file in excel
how do i open a kml file in excel
how do i convert a kml file to excel
how to import kml to excel
how to make a kml file from excel
excel in kml
how to open a kml file in excel
how do i open a kml file in excel
how to open kml in excel
Transcript of the video:
I'm pulling my hair out all night on this one. Episode 2376 Excel 3D Map with Custom KML Shapes.
In this case population by census tract. Thanks to this question from PWRO 12 hours ago on my video "Cool Ways to Analyze ZIP codes in Excel".
Is it possible to quickly, that's hilarious, create this type of map using different jurisdictional boundaries other than postal codes? How would one go about doing this?
Well, I knew that back here on the insert tab, the map chart doesn't support it, but 3D map that's only in Excel for Windows will support custom KML files.
I knew it, I had never tried it before, and have been beating my head against the wall all night.
Thanks to Suat Ozgur for helping me to at least be able to parse this KML data into Excel.
First off, there's going to be four parts to this video.
I imagine it's going to be insanely long and most people are only going to need to watch the first quarter of it, how to do the mapping and 3D map.
Just so I can document where I got the population data, where to get the KML file, and then finally, Suat’s code to clean and merge that.
Most of you will only need to watch the first quarter and you can be on with your life because you already have a KML file and you already have data for it.
So what does a KML file look like? Well, it's basically XML with a .KML extension.
There's 5,000 census tracts, but yet it goes down to row 120,000.
For each record we have all of these fields, and then below that, some latitude and longitude coordinates to draw the polygon.
So looking at these fields for one particular census tract, I originally thought, oh look, tract CE 134.09, 134.09.
I figured that would be the field, but it turns out that every county has a census tract one and a census tract two and a census tract three.
So once I actually looked at the census.gov data, the population data, it has this long geo ID and that mapped to this AFFGO ID. So this is the field that I need to map.
Figuring out which field matches a field in your data is really important when we get to 3D map. Okay, so here we go. Here's the Excel file.
Much later in this video, I'll show you how we put all of those two files from the Census Bureau into this file.
But AFFGO ID is the key field, that's the important part, and I'm going to map it by population.
I'm also going to color it by county, which is name LSAD County. We start with our data here. We go to the insert tab, we go to 3D map, and let that load.
And of course, they choose to map by county. That's not what we're going to do.
But before we can add a field here, we have to tell Excel about our KML file.
So I have a folder out here, C:AA, KML maps, and there's the file that I downloaded from the Census Bureau. Two other files here.
Back here on the home tab, we go to custom regions.
We're going to import a new set. We choose that set, click 'Open'.
All right, now let's give it a better name.
So these are census tracts, and the important part here is which field matches a field in our data.
And so that's the AFFGO ID, and we click 'Import'.
Now that that is imported, we can add a field and it's the AFFGO ID over here under select one.
Of course, they have no clue what it is, and you come down here to custom region KML.HSHP, as opposed to custom region set.
And we say that that's the one we want to map by.
And you have to check right here that hundred percent.
When I first started doing this and I was just using the tract, I was getting like 3% matches.
It was only the tracts that were completely unique that didn't appear in any other counties. And then we're going to change to a region map here.
All right? And it just did it, it just drew in all of the little census tracts.
We're going to say that we want to map population as the value, and then the county right here.
This is the county field, will be the category, so we get different colors. All right, so this is great.
Usually I get rid of the legend here, but in this case, the legend is super useful.
We'll just come here to my county, Brevard County is kind of in green.
So Brevard County goes from zero to 11,426, and the most dense, that's probably like right there. All right, let's see. Can I find myself up here on Merritt Island?
Yep, right there. So we're in census tract 698.01, population 3,621. All right, so there we go.
That's success. Pulling in a KML file and mapping data by the custom KML.
For most of you, that's all you needed. Thanks for stopping by. We'll see you next time for another net cast from MrExcel.
Okay, but let's talk about everything we did to get to this point to have this data.
Where to get the population data, tricky. It's tricky. We come out here at data.census.gov.
To get data by census tract, you're going to go to advanced search.
First thing you want to do is go to geography and say that I want to by census tract and they want to say which state?
So I'm going to choose Florida, and then all census tracts within Florida.
If you're just doing a county thing, by all means just get the county information. And then topics, we want populations and people.
Populations and people, all right? So that sets our filters first. Then we go to tables.
And right here, P1, this is the table that we need. It is a huge table, and it's too large to display.
With a smaller county, you would actually actually see what is going on here, all 140 columns.
Okay, I am in Google Chrome. Super annoying here.
Then when you click 'Download Table', a little counter comes up here, shows how far, and then the file will never download.
Thanks, Google. You're blocking it as a pop-up. Click 'Download Table'.
We want 2020, download CSV, and it is preparing your files. Pop-ups were blocked.
Always allow pop-ups and redirects from data.census.gov. And again, download CSV.
Yeah, there we go.
Okay, so there's the folder, and inside that folder is the several different fields, column descriptions, table notes, but that's the one that has all of our data.
Okay, next part of the video, where to get the KML file.
I would just google this, cardiographic boundary files. It's a long URL up there.
And cruise down through here for the thing that finally shows census tracts. They're offering a KML file.
You have to choose the state and it downloaded that zip code file without any hassle, and just extract all of these to a new folder on your computer.
Okay, let's talk about how to import that data into Excel.
So on the data tab from Tech CSV, we're going to go to where you saved the census data, that file first, that's the population. Click 'Transform Data'.
I only need the first three columns, so I choose column one, shift click on column three, and then say remove the other columns. I'm going to get rid of row one.
So remove rows, remove top rows, and number of rows, remove one, and then I'm going to promote these headings.
Use first row as headers. Geography is fine. Geography area name.
I'm going to change this total to say population, and then we'll give this a name of population.
The census tracts.
Well, this is one that unfortunately you're not going to be able to build using the Power Query Editor.
Down in the YouTube description, you can download this workbook.
You're going to come into Power Query, go to census tracts, and edit right here where your file is.
Once you've edited the path and name of the file, things are going to work out pretty good. You can look here, we end up with all of the fields.
It's gotten rid of all the geography, little latitude and longitude diagrams.
In my case, I filtered down to the five fields that I felt that I needed grouping each item into 0, 0, 0, 0, and then 1, 1, 1, 1, 1. And then finally, pivot the columns into this.
We don't really need group, and this AFFGID is going to be the piece that I need to map to population. We call the census tracts.
That's great. Over here, new query, combine, merge queries.
We're going to start from the census tracts. We're going to link in the population, and it is AFFGOID here and geography here.
We're looking for 5122 of 5122, that means that everything is working.
That's really good. And then out here, we want to bring the population out, click 'Okay'.
And they call it population one because we already have a name of population. I'm going to name that 2020.
This is population by census tract.
I'm going to do close and load to bring it into Excel, right? So there you go.
From here, just go back to the first section of the video in order to create the 3D Maps.
Well, hey, thanks to the PWRO for sending that great question in.
Thanks to Suat Ozgur for helping me parse that KML file. Power Query made short work of it.
I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel.
In this case population by census tract. Thanks to this question from PWRO 12 hours ago on my video "Cool Ways to Analyze ZIP codes in Excel".
Is it possible to quickly, that's hilarious, create this type of map using different jurisdictional boundaries other than postal codes? How would one go about doing this?
Well, I knew that back here on the insert tab, the map chart doesn't support it, but 3D map that's only in Excel for Windows will support custom KML files.
I knew it, I had never tried it before, and have been beating my head against the wall all night.
Thanks to Suat Ozgur for helping me to at least be able to parse this KML data into Excel.
First off, there's going to be four parts to this video.
I imagine it's going to be insanely long and most people are only going to need to watch the first quarter of it, how to do the mapping and 3D map.
Just so I can document where I got the population data, where to get the KML file, and then finally, Suat’s code to clean and merge that.
Most of you will only need to watch the first quarter and you can be on with your life because you already have a KML file and you already have data for it.
So what does a KML file look like? Well, it's basically XML with a .KML extension.
There's 5,000 census tracts, but yet it goes down to row 120,000.
For each record we have all of these fields, and then below that, some latitude and longitude coordinates to draw the polygon.
So looking at these fields for one particular census tract, I originally thought, oh look, tract CE 134.09, 134.09.
I figured that would be the field, but it turns out that every county has a census tract one and a census tract two and a census tract three.
So once I actually looked at the census.gov data, the population data, it has this long geo ID and that mapped to this AFFGO ID. So this is the field that I need to map.
Figuring out which field matches a field in your data is really important when we get to 3D map. Okay, so here we go. Here's the Excel file.
Much later in this video, I'll show you how we put all of those two files from the Census Bureau into this file.
But AFFGO ID is the key field, that's the important part, and I'm going to map it by population.
I'm also going to color it by county, which is name LSAD County. We start with our data here. We go to the insert tab, we go to 3D map, and let that load.
And of course, they choose to map by county. That's not what we're going to do.
But before we can add a field here, we have to tell Excel about our KML file.
So I have a folder out here, C:AA, KML maps, and there's the file that I downloaded from the Census Bureau. Two other files here.
Back here on the home tab, we go to custom regions.
We're going to import a new set. We choose that set, click 'Open'.
All right, now let's give it a better name.
So these are census tracts, and the important part here is which field matches a field in our data.
And so that's the AFFGO ID, and we click 'Import'.
Now that that is imported, we can add a field and it's the AFFGO ID over here under select one.
Of course, they have no clue what it is, and you come down here to custom region KML.HSHP, as opposed to custom region set.
And we say that that's the one we want to map by.
And you have to check right here that hundred percent.
When I first started doing this and I was just using the tract, I was getting like 3% matches.
It was only the tracts that were completely unique that didn't appear in any other counties. And then we're going to change to a region map here.
All right? And it just did it, it just drew in all of the little census tracts.
We're going to say that we want to map population as the value, and then the county right here.
This is the county field, will be the category, so we get different colors. All right, so this is great.
Usually I get rid of the legend here, but in this case, the legend is super useful.
We'll just come here to my county, Brevard County is kind of in green.
So Brevard County goes from zero to 11,426, and the most dense, that's probably like right there. All right, let's see. Can I find myself up here on Merritt Island?
Yep, right there. So we're in census tract 698.01, population 3,621. All right, so there we go.
That's success. Pulling in a KML file and mapping data by the custom KML.
For most of you, that's all you needed. Thanks for stopping by. We'll see you next time for another net cast from MrExcel.
Okay, but let's talk about everything we did to get to this point to have this data.
Where to get the population data, tricky. It's tricky. We come out here at data.census.gov.
To get data by census tract, you're going to go to advanced search.
First thing you want to do is go to geography and say that I want to by census tract and they want to say which state?
So I'm going to choose Florida, and then all census tracts within Florida.
If you're just doing a county thing, by all means just get the county information. And then topics, we want populations and people.
Populations and people, all right? So that sets our filters first. Then we go to tables.
And right here, P1, this is the table that we need. It is a huge table, and it's too large to display.
With a smaller county, you would actually actually see what is going on here, all 140 columns.
Okay, I am in Google Chrome. Super annoying here.
Then when you click 'Download Table', a little counter comes up here, shows how far, and then the file will never download.
Thanks, Google. You're blocking it as a pop-up. Click 'Download Table'.
We want 2020, download CSV, and it is preparing your files. Pop-ups were blocked.
Always allow pop-ups and redirects from data.census.gov. And again, download CSV.
Yeah, there we go.
Okay, so there's the folder, and inside that folder is the several different fields, column descriptions, table notes, but that's the one that has all of our data.
Okay, next part of the video, where to get the KML file.
I would just google this, cardiographic boundary files. It's a long URL up there.
And cruise down through here for the thing that finally shows census tracts. They're offering a KML file.
You have to choose the state and it downloaded that zip code file without any hassle, and just extract all of these to a new folder on your computer.
Okay, let's talk about how to import that data into Excel.
So on the data tab from Tech CSV, we're going to go to where you saved the census data, that file first, that's the population. Click 'Transform Data'.
I only need the first three columns, so I choose column one, shift click on column three, and then say remove the other columns. I'm going to get rid of row one.
So remove rows, remove top rows, and number of rows, remove one, and then I'm going to promote these headings.
Use first row as headers. Geography is fine. Geography area name.
I'm going to change this total to say population, and then we'll give this a name of population.
The census tracts.
Well, this is one that unfortunately you're not going to be able to build using the Power Query Editor.
Down in the YouTube description, you can download this workbook.
You're going to come into Power Query, go to census tracts, and edit right here where your file is.
Once you've edited the path and name of the file, things are going to work out pretty good. You can look here, we end up with all of the fields.
It's gotten rid of all the geography, little latitude and longitude diagrams.
In my case, I filtered down to the five fields that I felt that I needed grouping each item into 0, 0, 0, 0, and then 1, 1, 1, 1, 1. And then finally, pivot the columns into this.
We don't really need group, and this AFFGID is going to be the piece that I need to map to population. We call the census tracts.
That's great. Over here, new query, combine, merge queries.
We're going to start from the census tracts. We're going to link in the population, and it is AFFGOID here and geography here.
We're looking for 5122 of 5122, that means that everything is working.
That's really good. And then out here, we want to bring the population out, click 'Okay'.
And they call it population one because we already have a name of population. I'm going to name that 2020.
This is population by census tract.
I'm going to do close and load to bring it into Excel, right? So there you go.
From here, just go back to the first section of the video in order to create the 3D Maps.
Well, hey, thanks to the PWRO for sending that great question in.
Thanks to Suat Ozgur for helping me parse that KML file. Power Query made short work of it.
I want to thank you for stopping by. We'll see you next time for another net cast from MrExcel.
