Fall 2022 Datathon Project: Transit and Housing in California
By Gain Boonvanich, Anita Ding, Yixin Huang, and Yehchan Yoo
Introduction to Problem and Why It is Important
Background and Domain Knowledge
Data and Methodologies
First, let's import some packages.
import pandas as pd
import openpyxl
import numpy as np
import plotly.express as px
import requests
import json
import matplotlib.pyplot as plt
import seaborn as sns
Gathering Housing and Population Data
We first downloaded the following data about housing units per capita in each county in California from U.S. Census.
Annual Estimates of County Housing Units for States: 2010 to 2019: https://www.census.gov/data/tables/time-series/demo/popest/2010s-total-housing-units.html
Annual Estimates of County Housing Units for States: 2020 to 2021: https://www.census.gov/data/tables/time-series/demo/popest/2020s-total-housing-units.html
County Population Totals: 2010-2019: https://www.census.gov/data/tables/time-series/demo/popest/2010s-counties-total.html
County Population Totals: 2020-2021: https://www.census.gov/data/tables/time-series/demo/popest/2020s-counties-total.html
# Annual Estimates of County Housing Units for California: 2010 to 2019
df = pd.read_excel('/work/population_housing_unit/CO-EST2019-ANNHU-06.xlsx')
new_header = df.iloc[2].values
new_header[0] = 'county'
df.columns = new_header
df = df.iloc[4:,:]
df = df.drop(columns=['Census', 'Estimates Base'])
df['county'] = df['county'].str.replace('.', '')
df['county'] = df['county'].str.replace('County, California', '')
df
# Annual Estimates of County Housing Units for California: 2020 to 2021
df2 = pd.read_excel('/work/population_housing_unit/CO-EST2021-HU-06.xlsx')
new_header = df2.iloc[2].values
new_header[0] = 'county'
df2.columns = new_header
df2 = df2.drop(df2.columns[1], axis=1)
df2 = df2.iloc[4:]
df2
df2['county'] = df2['county'].str.replace('.', '')
df2['county'] = df2['county'].str.replace('County, California', '')
df2
# Housing Data
house = df.merge(df2, on='county', how='left')
house = house.iloc[:58]
house.to_csv('california_county_houseunit_2010-2021.csv', index=False)
house
# County Population Totals for California: 2010 to 2019
pop1 = pd.read_excel('/work/population_housing_unit/co-est2019-annres-06.xlsx')
header = pop1.iloc[2]
pop1 = pop1.iloc[4:]
header[0] = 'county'
pop1.columns = header
pop1 = pop1.drop(pop1.columns[[1, 2]], axis=1)
pop1['county'] = pop1['county'].str.replace('.', '')
pop1['county'] = pop1['county'].str.replace('County, California', '')
pop1
# County Population Totals for California: 2020 to 2021
pop2 = pd.read_excel('/work/population_housing_unit/co-est2021-pop-06.xlsx')
header = pop2.iloc[2]
pop2 = pop2.iloc[4:]
header[0] = 'county'
pop2.columns = header
pop2 = pop2.drop(pop2.columns[[1]], axis=1)
pop2['county'] = pop2['county'].str.replace('.', '')
pop2['county'] = pop2['county'].str.replace('County, California', '')
pop2
pop = pop1.merge(pop2, on='county', how='left')
pop = pop.iloc[:58]
pop.to_csv('california_county_population_2010-2021.csv', index=False)
pop
# rereading house units csv
house = pd.read_csv('california_county_houseunit_2010-2021.csv')
house['county'] = house['county'].str.strip()
house
# rereading population csv
pop = pd.read_csv('california_county_population_2010-2021.csv')
pop['county'] = pop['county'].str.strip()
pop
# Making a DataFrame containing number of housing units per capita in 2017 for each California county
house_per_capita_2017 = pd.DataFrame(data={"county": house['county'].str.strip(), "2017.0": house["2017.0"]/pop["2017.0"]})
house_per_capita_2017['log_2017'] = house_per_capita_2017['2017.0'].apply(lambda x: np.log(x+1))
house_per_capita_2017
Looking at Transit Passengers
For this section, we downloaded data containing the total number of transit employees by county. (Link) Here, we tried to look at the relationship between number of transit employees and housing units per capita. We used data from 2017, as 2017 was the most recent year from which the transit employee data was available.
transit_data = pd.read_csv('transit_data.csv')
transit_data = transit_data.iloc[:59, :].drop(14, axis=0).rename(columns={'Unnamed: 0':'county'})
transit_data_2017 = transit_data.loc[:, ["county", "2017"]]
transit_data_2017['county'] = transit_data_2017['county'].str.strip()
transit_data_2017_pop = transit_data_2017.merge(pop[['county', '2017.0']], on='county')
transit_data_2017_pop['per_100000'] = transit_data_2017_pop['2017']/transit_data_2017_pop['2017.0']*100000
transit_data_2017_pop['log_per_100000'] = transit_data_2017_pop['per_100000'].apply(lambda x: np.log(x+1))
# Making county map of Log(Number of Transit Employees Per 100000)
r = requests.get('https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json')
counties = json.loads(r.text)
target_states = ['06'] # Just graphing California
counties['features'] = [f for f in counties['features'] if f['properties']['STATE'] in target_states]
names = {f['id']:f['properties']['NAME'] for f in counties['features']}
names_reverse = {f['properties']['NAME']:f['id'] for f in counties['features']}
df = pd.read_csv('https://raw.githubusercontent.com/plotly/datasets/master/fips-unemp-16.csv', dtype={'fips': str})
df_copy = df.copy()
df_copy['county'] = df_copy['fips'].apply(lambda x: names[x] if x in names.keys() else 'NULL')
df_copy = df_copy.merge(transit_data_2017_pop, on=['county'], how='outer')
fig = px.choropleth(df_copy, geojson=counties, locations='fips', color='log_per_100000',
color_continuous_scale='Viridis',
range_color=(min(df_copy['log_per_100000']), max(df_copy['log_per_100000'])),
scope='usa',
hover_data = ['county', 'log_per_100000']
)
fig.update_layout(margin={'r': 0, 't': 0, 'l': 0, 'b': 0})
fig.show()
# Making county map of Log(Number of Housing Units Per Capita)
df_copy = df.copy()
df_copy['county'] = df_copy['fips'].apply(lambda x: names[x] if x in names.keys() else 'NULL')
df_copy = df_copy.merge(house_per_capita_2017, on=['county'])
fig = px.choropleth(df_copy, geojson=counties, locations='fips', color='log_2017',
color_continuous_scale='Viridis',
range_color=(0, max(df_copy['log_2017'])),
scope='usa',
hover_data = ['county', 'log_2017']
)
fig.update_layout(margin={'r': 0, 't': 0, 'l': 0, 'b': 0})
fig.show()
plt.scatter(x=transit_data_2017_pop['log_per_100000'], y=house_per_capita_2017['log_2017'])
plt.title('Number of Transit Employees vs Number of Housing Units (County-wise, 2017)')
plt.xlabel('Log(Number of Transit Employees Per 100000)')
plt.ylabel('Log(Number of Housing Units Per Capita)')
plt.draw()
np.corrcoef(transit_data_2017_pop['log_per_100000'], house_per_capita_2017['log_2017'])[0][1]
Graphing Transit Financial Data/Fitting Linear Regression Model
transit_financial_data = pd.read_csv("transit_financial_data.csv").rename(columns={'date':'city'})
transit_financial_data = transit_financial_data[transit_financial_data.city.isin(['Los Angeles', 'San Diego', 'Orange', 'Riverside', 'San Bernardino', 'Santa Clara'])]
transit_financial_data.loc[:, "2011":] = transit_financial_data.loc[:, "2011":].apply(lambda x: x.str.replace(",","").str.strip()).astype(int)
transit_financial_data = transit_financial_data.set_index('city').loc[:, "2011":].reset_index()
transit_financial_data
We used data from the following link for housing units per capita data for this section. Like in the previous data set,We looked at six prominent Californian counties: Los Angeles County, San Diego County, Orange County, Riverside County, San Bernardino County, and Santa Clara County.
six_county_unit_per_capita = pd.read_csv("six_county_unit_per_capita.csv")
six_county_unit_per_capita = six_county_unit_per_capita.iloc[0:7, :]
six_county_unit_per_capita
import numpy as np
from sklearn.linear_model import LinearRegression
# Los Angeles Model
y = np.array(six_county_unit_per_capita["Los Angeles County Count_HousingUnit"])
x = np.array(transit_financial_data.iloc[0])[1:].reshape((-1, 1))
model = LinearRegression().fit(x, y)
r_sq = model.score(x, y)
r_sq
import seaborn as sns
import matplotlib.pyplot as plt
line_plot_df = pd.DataFrame(data={'finance':np.array(transit_financial_data.iloc[0])[1:].astype(int),
'unit_per_capita':np.array(six_county_unit_per_capita["Los Angeles County Count_HousingUnit"])})
sns.lmplot(line_plot_df, x='finance', y='unit_per_capita')
plt.title("Los Angeles")
plt.draw()
# Santa Clara Model
y = np.array(six_county_unit_per_capita["Santa Clara County Count_HousingUnit"])
x = np.array(transit_financial_data.iloc[1])[1:].reshape((-1, 1))
model = LinearRegression().fit(x, y)
r_sq = model.score(x, y)
r_sq
line_plot_df = pd.DataFrame(data={'finance':np.array(transit_financial_data.iloc[1])[1:].astype(int),
'unit_per_capita':np.array(six_county_unit_per_capita["Santa Clara County Count_HousingUnit"])})
sns.lmplot(line_plot_df, x='finance', y='unit_per_capita')
plt.title("Santa Clara")
plt.draw()
# San Diego Model
y = np.array(six_county_unit_per_capita["San Diego County Count_HousingUnit"])
x = np.array(transit_financial_data.iloc[2])[1:].reshape((-1, 1))
model = LinearRegression().fit(x, y)
r_sq = model.score(x, y)
r_sq
line_plot_df = pd.DataFrame(data={'finance':np.array(transit_financial_data.iloc[2])[1:].astype(int),
'unit_per_capita':np.array(six_county_unit_per_capita["San Diego County Count_HousingUnit"])})
sns.lmplot(line_plot_df, x='finance', y='unit_per_capita')
plt.title("San Diego")
plt.draw()
# Orange County Model
y = np.array(six_county_unit_per_capita["Orange County Count_HousingUnit"])
x = np.array(transit_financial_data.iloc[3])[1:].reshape((-1, 1))
model = LinearRegression().fit(x, y)
r_sq = model.score(x, y)
r_sq
line_plot_df = pd.DataFrame(data={'finance':np.array(transit_financial_data.iloc[3])[1:].astype(int),
'unit_per_capita':np.array(six_county_unit_per_capita["Orange County Count_HousingUnit"])})
sns.lmplot(line_plot_df, x='finance', y='unit_per_capita')
plt.title("Orange")
plt.draw()
# San Barnardino County Model
y = np.array(six_county_unit_per_capita["San Bernardino County Count_HousingUnit"])
x = np.array(transit_financial_data.iloc[4])[1:].reshape((-1, 1))
model = LinearRegression().fit(x, y)
r_sq = model.score(x, y)
r_sq
line_plot_df = pd.DataFrame(data={'finance':np.array(transit_financial_data.iloc[4])[1:].astype(int),
'unit_per_capita':np.array(six_county_unit_per_capita["San Bernardino County Count_HousingUnit"])})
sns.lmplot(line_plot_df, x='finance', y='unit_per_capita')
plt.title("San Bernardino")
plt.draw()
# Riverside County Model
y = np.array(six_county_unit_per_capita["Riverside County Count_HousingUnit"])
x = np.array(transit_financial_data.iloc[5])[1:].reshape((-1, 1))
model = LinearRegression().fit(x, y)
r_sq = model.score(x, y)
r_sq
line_plot_df = pd.DataFrame(data={'finance':np.array(transit_financial_data.iloc[5])[1:].astype(int),
'unit_per_capita':np.array(six_county_unit_per_capita["Riverside County Count_HousingUnit"])})
sns.lmplot(line_plot_df, x='finance', y='unit_per_capita')
plt.title("Riverside")
plt.draw()
Conclusion
Impact and Future Avenues
To be honest, our work right now is pretty much showing that the current transit cost allocation is pretty reasonable, and we could predict, based on how much transit money the state government of California could allocate, the housing units per capita which the private business sectors (real estate companies) have the powers over. We could potentially apply our models to other counties (additional data) besides the six that we analyzed, and we do believe that linear regression would be a reasonable model, although it is quite simple (but yet powerful).