US Population Data

A meme floating around these days takes a swing at the existence of the electoral college stating "land doesn't vote, people do". While I don't subscribe to either side of that particular argument, the meme does a great job of highlighting how population data is essential for any analysis involving people. As such it is important to have a reliable source for population data.

Luckily in the U.S. we have the census bureau. Census.gov provides several datasets for our consumption. You can retrieve census data automatically using a pandas dataframe.

census.gov data

import pandas as pd

df = pd.read_excel('https://www2.census.gov/programs-surveys/popest/tables/2010-2019/state/totals/nst-est2019-01.xlsx')

df.head(10)

Next we need to clean up our data

You can see that our headers are in row index 2

We can extract our headers to a new series

new_header = df.iloc[2] 
new_header

table with row headers in column A and column headers in rows 3 through 4. (leading dots indicate sub-parts)               NaN
Unnamed: 1                                                                                                              Census
Unnamed: 2                                                                                                      Estimates Base
Unnamed: 3                                                                                                                2010
Unnamed: 4                                                                                                                2011
Unnamed: 5                                                                                                                2012
Unnamed: 6                                                                                                                2013
Unnamed: 7                                                                                                                2014
Unnamed: 8                                                                                                                2015
Unnamed: 9                                                                                                                2016
Unnamed: 10                                                                                                               2017
Unnamed: 11                                                                                                               2018
Unnamed: 12                                                                                                               2019
Name: 2, dtype: object

Next we only want state an territory data so we can remove any aggregate rows or non-data rows from our dataset

Looking at the above dataframe we can see that our states and territories start at row 10. We can therefore discard everything above row 10

df = df[10:]

df.head()

We can also see when reviewing the dataset that there are 5 non-data rows at the end of the dataset. We can discard these rows as well

df = df[:-5]

df.tail(10)

Next we can see that there are some null rows in our dataset. We can discard these as well

df.dropna(inplace = True)
df.tail()

To make this dataset compatible with our other state based data we need to remove the non alpha characters from out state names

df = df.replace('[^a-zA-Z0-9 ]', '', regex=True)
df.head()

Now we just need to reapply our headers

import numpy as np
df.columns = new_header
df.rename(columns = {np.NaN : 'States'}, inplace= True)
df.head()

Now its time to plot the data

import plotly.express as px
data = px.data.gapminder()

fig = px.bar(df, x='States', y='Census',
             hover_data=['Census'], color='Census',
             labels={'pop':'population'}, height=400)
fig.show()

Tying it all together

import pandas as pd
import numpy as np

df = pd.read_excel('https://www2.census.gov/programs-surveys/popest/tables/2010-2019/state/totals/nst-est2019-01.xlsx')
new_header = df.iloc[2] 
df = df[10:-5]
df.dropna(inplace = True)
df = df.replace('[^a-zA-Z0-9 ]', '', regex=True)
df.columns = new_header
df.rename(columns = {np.NaN : 'States'}, inplace= True)

import plotly.express as px
data = px.data.gapminder()

fig = px.bar(df, x='States', y='Census',
             hover_data=['Census'], color='Census',
             labels={'pop':'population'}, height=400)
fig.show()