text: # County median home prices (FHFA HPI & Census)
  import pandas as pd
  import numpy as np
  import array
  
  # House price data based on FHFA House Price Index
  hpi_df = pd.read_excel('HPI_AT_BDL_county.xlsx', skiprows=6) # Source: https://www.fhfa.gov/PolicyProgramsResearch/Research/Pages/wp1601.aspx
  
  # Replacing null HPI values w/ avg. of values before and after (if they exist- otherwise, values remain null)
  hpi_df.HPI = hpi_df.HPI.replace('.', np.nan).astype(float)
  
  hpi_df.HPI = hpi_df.HPI.fillna((hpi_df.HPI.shift() + hpi_df.HPI.shift(-1))/2) # Source: https://stackoverflow.com/questions/44032771/fill-cell-containing-nan-with-average-of-value-before-and-after
  
  # Filling the remaining missing HPI cells w/ the next most recent HPI value (since the relevant missing HPIs are only from 2013 onwards, the HPI will be the most recent value from the same FIPS)
  hpi_df.HPI = hpi_df.HPI.fillna(method='ffill')
  
  # Adding county/state column (to displaying location via interactive hover map)
  counties = pd.read_excel('CLF01.xls', usecols=[0,1])[2:]  # Source: https://www.census.gov/library/publications/2011/compendia/usa-counties-2011.html
  
  areaname_dict = {}
  for fips in counties['STCOU']:
      area = counties.Areaname[counties.STCOU == fips].iloc[0]
      areaname_dict[fips] = area
      
  hpi_df['Areaname'] = hpi_df['FIPS code'].map(areaname_dict)
  
  hpi_df = hpi_df.rename({'FIPS code': 'FIPS'}, axis=1)
  hpi_df = hpi_df[['Year', 'State', 'FIPS', 'HPI', 'Areaname']]
  hpi_df = hpi_df.sort_values(['FIPS', 'Year']).reset_index(drop=True)
  
  #--------------------------------------------------------------------------------------------------#
  # Converting HPI values to House Prices:
  # 2012-2016 median home prices (using this data instead of 2013-2017 data since 25 records were missing 2017 HPIs, whereas 2016 was only missing 13)
  home_prices1216 = pd.read_csv('ACS_16_5YR_Home_Prices.csv', skiprows=3, usecols=[4,6,7], encoding='latin')  # Source: https://factfinder.census.gov/ (searched for "Median Value of Owner-Occupied" in *advanced* search)
  home_prices1216.columns = ['FIPS', 'County', 'Med_Price']
  home_prices1216 = home_prices1216[home_prices1216.Med_Price != '-']  # Only two rows contained this filler value
  home_prices1216['Med_Price'] = home_prices1216['Med_Price'].astype(int)
  
  # Calculating annual home prices based on 2012-2016 avg. price & 2014 HPI
  adj_prices = array.array('f')
  for fips in hpi_df.FIPS[hpi_df.Year == 2016].unique():
      hpi14 = hpi_df.HPI[(hpi_df.FIPS == fips) & (hpi_df.Year == 2014)].iloc[0]
      price1216 = home_prices1216.Med_Price[home_prices1216.FIPS == fips].iloc[0]
      df = hpi_df[hpi_df.FIPS == fips]
      adj_prices.extend(df['HPI'] / hpi14 * price1216)
      
  hpi_df['Med_House_Price'] = [int(round(x,0)) for x in adj_prices]
  
  #--------------------------------------------------------------------------------------------------#
  # Adding Zillow House Price Data 
  # (data for counties missing from hpi_df - additional 78 counties)
  zillow = pd.read_csv('Zillow County Monthly.csv', usecols=[1,2,4,5] + list(range(15, 280, 12)), encoding='latin')  # Source: https://www.zillow.com/research/data/ (Home Values - ZHVI All Homes - County)
  zillow['FIPS'] = (zillow.StateCodeFIPS.astype(str) + zillow.MunicipalCodeFIPS.astype(str).apply(lambda x:x.zfill(3))).astype(int)
  zillow = zillow.drop(['StateCodeFIPS', 'MunicipalCodeFIPS'], axis=1)
  zillow.columns = ['County', 'State'] + [str(yr) for yr in range(1996, 2019)] + ['FIPS']
  
  zillowfips = list(zillow.FIPS.sort_values().values)
  zillow_extra = []
  for fips in zillowfips:
      if fips not in hpi_df.FIPS.unique():
          zillow_extra.append(fips)
          
  # Calculating state avg. house price % change (to replace zillow records w/ less than 8 price values recorded, since a larger sample is needed for a more accurate estimate)
  pct_chg_dict = {}
  for state in hpi_df.State.unique():
      st_df = hpi_df[hpi_df.State == state]
      st_chgs = []
      for fips in st_df.FIPS.unique():
          fp_df = st_df[st_df.FIPS == fips]
          st_chgs.append(fp_df.HPI.pct_change().mean())
      pct_chg_dict[state] = sum(st_chgs) / len(st_chgs)
      
  zillow_new = pd.DataFrame()
  
  for fips in zillow_extra:
      new_prices = []
      df = zillow[zillow.FIPS == fips].dropna(axis=1)  # Only keeping cols of years w/ no nulls
      
      if len(df.columns) > 8:  # Excludes 4 FIPS (too few home price values to calculate accurate rate of price change)
          cols = df.columns[2:-1]  # (96-18 cols)
          prices = list(df[cols].iloc[0].astype(int).values)
          avg_chg = pd.Series(prices).pct_change().mean()
          earliest_price = df.iloc[0][2]  # 96 price or earliest yr available after
          earliest_yr = int(cols[0])
          missing_yrs = range(earliest_yr - 1, 1988, -1)  # earliest year to 1991 (backwards)
          state = df.State.iloc[0]
          
          if len(df.columns) < 11:  # i.e. 9 or 10
              avg_chg = pct_chg_dict[state]  # If not previously assigned above
          
          for yr in missing_yrs:
              new_earliest = int(round(earliest_price/(1 + avg_chg),0))
              new_prices.append(new_earliest)
              earliest_price = new_earliest
          new_prices.reverse()
          
          new_df = pd.DataFrame({'Year':range(1989, 2019), 'FIPS':list(np.full(30, fips)), 'Med_House_Price':(new_prices + prices)})
          zillow_new = pd.concat([zillow_new, new_df])
          
  #--------------------------------------------------------------------------------------------------#
  # Combining Zillow & FHFA data
  no_HPI = hpi_df.drop('HPI', axis=1)
  zillow_new['Areaname'] = zillow_new['FIPS'].map(areaname_dict)
  zillow_new['State'] = [x.split(', ')[1] for x in zillow_new['Areaname'].values]
  zillow_new = zillow_new[no_HPI.columns]
  combined = pd.concat([no_HPI, zillow_new]).sort_values(['Year', 'FIPS']).reset_index(drop=True)
  
  combined.to_csv('county_home_8618.csv', index=False)