# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ### NHPD data aggregation

# #### Load and filter data

import pandas as pd

# Read in data and see first few lines`b
nhpd = pd.read_csv("data/active_and_inconclusive_properties.csv")
print(nhpd.shape)
nhpd.head()

# +
# Subset to Hillsborough, Miami-Dade and Orange counties
nhpd_fl = nhpd[nhpd['County'].isin(['Hillsborough','Miami-Dade','Orange'])]

# See number of records for each of the three counties
nhpd_fl['County'].value_counts()
# -

# #### Data Cleaning

# NA cleaning 
# Subset cols which have less NAs than max_NA_percentage and drop them 
max_NA_percentage = 0.5
print("Total Columns", nhpd_fl.shape[1])
nhpd_fl_without_na = nhpd_fl.loc[:, nhpd_fl.isnull().sum() < max_NA_percentage*nhpd_fl.shape[0]]
print("Total Columns after removing columns with high NAs", nhpd_fl_without_na.shape[1])

# We see that the percent of Extremely Low income households got dropped while removing columns with high NAs
# We need this column to find the census tracts with high percentage of low income households 
# We will impute this column with the mean per county
nhpd_fl.groupby('County').PercentofELIHouseholds.mean().reset_index(name = 'mean_PercentofELIHouseholds')
nhpd_fl['PercentofELIHouseholds'].fillna(value=nhpd_fl['mean_PercentofELIHouseholds'], inplace=True)

# NA cleaning after imputing PercentofELIHouseholds
# Subset cols which have less NAs than max_NA_percentage and drop them 
max_NA_percentage = 0.5
print("Total Columns", nhpd_fl.shape[1])
nhpd_fl_without_na = nhpd_fl.loc[:, nhpd_fl.isnull().sum() < max_NA_percentage*nhpd_fl.shape[0]]
print("Total Columns after removing columns with high NAs", nhpd_fl_without_na.shape[1])

nhpd_fl_without_na.head()

# #### Aggregate at a census tract level

# +
# Make lists of columns to sum and those to average at a census tract level and create a dictionary to pass to agg function 
columns_to_sum = ['ActiveSubsidies', 'TotalInconclusiveSubsidies',
       'TotalInactiveSubsidies', 'TotalUnits', 
       'NumberActiveSection8', 'NumberInconclusiveSection8',
       'NumberInactiveSection8', 'NumberActiveSection202',
       'NumberInconclusiveSection202', 'NumberInactiveSection202',
       'NumberActiveSection236', 'NumberInconclusiveSection236',
       'NumberInactiveSection236', 'NumberActiveHUDInsured',
       'NumberInconclusiveHUDInsured', 'NumberInactiveHud',
       'NumberActiveLihtc', 'NumberInconclusiveLihtc', 'NumberInactiveLihtc',
       'NumberActiveSection515', 'NumberInconclusiveSection515',
       'NumberInactiveSection515', 'NumberActiveSection538',
       'NumberInconclusiveSection538', 'NumberInactiveSection538',
       'NumberActiveHome', 'NumberInconclusiveHome', 'NumberInactiveHome',
       'NumberActivePublicHousing', 'NumberInconclusivePublicHousing',
       'NumberInactivePublicHousing', 'NumberActiveState',
       'NumberInconclusiveState', 'NumberInactiveState', 'NumberActivePBV',
       'NumberInconclusivePBV', 'NumberInactivePBV', 'NumberActiveMR',
       'NumberInconclusiveMR', 'NumberInactiveMR']
columns_to_average = ['TotalUnits',
 'StudioOneBedroomUnits',
 'TwoBedroomUnits',
 'ThreePlusBedroomUnits',
 'PercentofELIHouseholds',
 'FairMarketRent_2BR']

agg_dict = dict()

for col in columns_to_sum:
    agg_dict[col] = "sum"
for col in columns_to_average:
    agg_dict[col] = "mean"
    
print(agg_dict)
# -

# Aggregate the dataframe at a census tract level using the aggregate dictionary we just created
nhpd_fl_census_tract = nhpd_fl_without_na.groupby('CensusTract').agg(agg_dict)
nhpd_fl_census_tract.head()

# ### Combining with Housing loss data

# Read in housing loss data
hill_hl = pd.read_csv("data/hillsborough_fl_processed_2017_to_2019_20210916.csv")
miami_dade_hl = pd.read_csv("data/miami_dade_fl_processed_2017_to_2019_20210916.csv")
orange_hl = pd.read_csv("data/orange_fl_processed_2017_to_2019_20210916.csv")


# ### Combining with American Community Survey data

# +
# Read in 2014 acs data
hill_acs_2014 = pd.read_csv("data/hillsborough_acs5-2014_census.csv")
miami_dade_acs_2014 = pd.read_csv("data/miami_dade_acs5-2014_census.csv")
orange_acs_2014 = pd.read_csv("data/orange_acs5-2014_census.csv")

# Read in 2019 acs data
hill_acs_2019 = pd.read_csv("data/hillsborough_acs5-2019_census.csv")
miami_dade_acs_2019 = pd.read_csv("data/miami_dade_acs5-2019_census.csv")
orange_acs_2019 = pd.read_csv("data/orange_acs5-2019_census.csv")
# -

# Combine 2014 and 2019 data
hill_acs_2014['year'] = 2014
hill_acs_2019['year'] = 2019
#TODO: fix this
hills_acs = pd.concat([hill_acs_2014,hill_acs_2019], axis = 1)
hills_acs.head()

StarcoderdataJupyterScriptsDedupFiltered