<jupyter_start><jupyter_text>Import and Load datasets<jupyter_code>import pandas as pd
import numpy as np
df2017 = pd.read_csv('/content/FY2017-4050-County-Level_Data.csv', encoding= 'unicode_escape', )
df2018 = pd.read_csv('/content/FY18_4050_FMRs_rev.csv', encoding= 'unicode_escape')
df2019 = pd.read_csv('/content/FY2019_4050_FMRs_rev2.csv', encoding= 'unicode_escape')
df2020 = pd.read_csv('/content/FY20_4050_FMRs_rev.csv', encoding= 'unicode_escape')
df2021 = pd.read_csv('/content/FY21_4050_FMRs_rev.csv', encoding= 'unicode_escape')<jupyter_output><empty_output><jupyter_text>Clean datasets<jupyter_code>def wrangle0(x0):
  x0 = x0.copy()
  x0 = x0.loc[x0['metro'] == 1]
  x0 = x0[['fips2010', 'metro_code', 'areaname', 'state_alpha','fmr_0']]
  x0 = x0.rename(columns = {'fmr_0': 2021})
  return x0
df2017_frm0 = wrangle0(df2017)
df2018_frm0 = wrangle0(df2018)
df2019_frm0 = wrangle0(df2019)
df2020_frm0 = wrangle0(df2020)
df2021_frm0 = wrangle0(df2021)
def wrangle1(x1):
  x1 = x1.copy()
  x1 = x1.loc[x1['metro'] == 1]
  x1 = x1[['fips2010', 'metro_code', 'areaname', 'state_alpha','fmr_1']]
  x1 = x1.rename(columns = {'fmr_1': 2021})
  return x1
df2017_fmr1 = wrangle1(df2017)
df2018_frm1 = wrangle1(df2018)
df2019_frm1 = wrangle1(df2019)
df2020_frm1 = wrangle1(df2020)
df2021_frm1 = wrangle1(df2021)<jupyter_output><empty_output><jupyter_text>Merge fmr0s and fmr1s<jupyter_code>df_fmr0 = df2017_frm0.merge(df2018_frm0, on=['fips2010', 'metro_code', 'areaname', 'state_alpha'])
df_fmr0 = df_fmr0.merge(df2019_frm0, on=['fips2010', 'metro_code', 'areaname', 'state_alpha'])
df_fmr0 = df_fmr0.merge(df2020_frm0, on=['fips2010', 'metro_code', 'areaname', 'state_alpha'])
df_fmr0 = df_fmr0.merge(df2021_frm0, on=['fips2010', 'metro_code', 'areaname', 'state_alpha'])
print(df_fmr0.shape)
df_fmr0.head()
df_fmr1 = df2017_fmr1.merge(df2018_frm1, on=['fips2010', 'metro_code', 'areaname', 'state_alpha'])
df_fmr1 = df_fmr1.merge(df2019_frm1, on=['fips2010', 'metro_code', 'areaname', 'state_alpha'])
df_fmr1 = df_fmr1.merge(df2020_frm1, on=['fips2010', 'metro_code', 'areaname', 'state_alpha'])
df_fmr1 = df_fmr1.merge(df2021_frm1, on=['fips2010', 'metro_code', 'areaname', 'state_alpha'])
print(df_fmr1.shape)
df_fmr1.head()<jupyter_output>(1963, 9)<jupyter_text>Clean Merged DataFrames- split area name to remove 'MSA', 'HUD Metro FMR Area'- explode cities to separate hyphenated cities<jupyter_code>def explode_str(df, col='areaname', sep='-'):
    s = df[col]
    i = np.arange(len(s)).repeat(s.str.count(sep) +1)
    return df.iloc[i].assign(**{col: sep.join(s).split(sep)})
def new_wrangle(x):
  x = x.copy()
  x['areaname'] = x['areaname'].str.split(',', 1, expand=True)[0]
  x['areaname'] = x['areaname'].str.strip()
  x = explode_str(x)
  x = x.drop(columns = ['fips2010'])

  return x<jupyter_output><empty_output><jupyter_text>FMR0<jupyter_code>df_fmr0 = new_wrangle(df_fmr0)
df_fmr0['areaname'] = df_fmr0['areaname'].str.strip()
df_fmr0['state_alpha'] = df_fmr0['state_alpha'].str.strip()
df_fmr0['city, state'] = df_fmr0['areaname'] + ', ' + df_fmr0['state_alpha']
print(df_fmr0.shape)
df_fmr0.head()
df_fmr0.to_csv('fmr0.csv', index=False)<jupyter_output><empty_output><jupyter_text>FMR1<jupyter_code>df_fmr1 = new_wrangle(df_fmr1)
df_fmr1['areaname'] = df_fmr1['areaname'].str.strip()
df_fmr1['state_alpha'] = df_fmr1['state_alpha'].str.strip()
df_fmr1['city, state'] = df_fmr1['areaname'] + ', ' + df_fmr1['state_alpha']
print(df_fmr1.shape)
df_fmr1.head()
df_fmr1.to_csv('fmr1.csv', index=False)<jupyter_output><empty_output><jupyter_text>Forecasting<jupyter_code>!pip install prophet
from prophet import Prophet
fmr0 = pd.read_csv('/content/fmr0.csv')<jupyter_output><empty_output><jupyter_text>Setting fmr0 - select specific rows- melt dataframe and get columns necessary to run prophet<jupyter_code>print(fmr0.shape)
fmr0.head()
fmr0_melt = fmr0.copy()
fmr0_melt = fmr0_melt.drop(columns=['metro_code', 'areaname', 'state_alpha'])
# to get in the form prophet needs
fmr0_melt = (fmr0_melt.melt(id_vars=['city, state'], 
                         var_name='ds', 
                         value_name='y')
                         ).reset_index(drop=True)
fmr0_melt.head()
# create city list
cities_list = list(fmr0_melt['city, state'].unique())
len(cities_list)
def rnd_series(city):
    subset = fmr0_melt[fmr0_melt['city, state']==city]
    dates =(pd.DataFrame({'ds':pd.to_datetime(fmr0_melt['ds'])}))
    return subset
series = [rnd_series(city) for city in cities_list]
series[0]<jupyter_output><empty_output><jupyter_text>Running Prophet on df<jupyter_code>def run_prophet(series):
    model = Prophet()
    model.fit(series)
    forecast = model.make_future_dataframe(periods=10, freq='Y')
    forecast = model.predict(forecast)
    forecast = forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]
    forecast['city, state'] = series['city, state'].iloc[0]
    forecast = forecast[['city, state','ds', 'yhat', 'yhat_lower', 'yhat_upper']]
    forecast[['yhat', 'yhat_lower', 'yhat_upper']] = forecast[['yhat', 'yhat_lower', 'yhat_upper']].astype(float)
    forecast[['yhat', 'yhat_lower', 'yhat_upper']] = forecast[['yhat', 'yhat_lower', 'yhat_upper']].apply(np.ceil)
    return forecast
f = run_prophet(series[0])
f.head()
for i in range(len(series)):
    f = run_prophet(series[i])
    f.to_csv('fmr0_predictions.csv', mode='a', index='False')
rental_prediction = pd.read_csv('fmr0_predictions.csv')
rental_prediction = rental_prediction.loc[rental_prediction['city, state'] != 'city, state']
rental_prediction.to_csv('fmr0_predictions.csv', index=False)
rent_long = rental_prediction[['city, state', 'ds', 'yhat']]
rent_long.dtypes
rent_long['yhat'] = pd.to_numeric(rent_long['yhat'],errors='coerce')
rent_long.head()
rent_long = rent_long.pivot_table(index = 'city, state',
                                  columns = 'ds',
                                  values = 'yhat')
rent_long = rent_long.iloc[3:]
rent_long
rent_long.to_csv('fmr0_long.csv', index=False )<jupyter_output><empty_output><jupyter_text>Forecasting 2<jupyter_code>fmr1 = pd.read_csv('/content/fmr1.csv')<jupyter_output><empty_output><jupyter_text>Setting fmr1 - select specific rows- melt dataframe and get columns necessary to run prophet<jupyter_code>print(fmr1.shape)
fmr1.head()
fmr1_melt = fmr1.copy()
fmr1_melt = fmr1_melt.drop(columns=['metro_code', 'areaname', 'state_alpha'])
# to get in the form prophet needs
fmr1_melt = (fmr1_melt.melt(id_vars=['city, state'], 
                         var_name='ds', 
                         value_name='y')
                         ).reset_index(drop=True)
fmr1_melt.head()
# create city list
cities_list = list(fmr1_melt['city, state'].unique())
len(cities_list)
def rnd_series(city):
    subset = fmr1_melt[fmr1_melt['city, state']==city]
    dates =(pd.DataFrame({'ds':pd.to_datetime(fmr1_melt['ds'])}))
    return subset
series = [rnd_series(city) for city in cities_list]
series[0]<jupyter_output><empty_output><jupyter_text>Running Prophet on df<jupyter_code>def run_prophet(series):
    model = Prophet()
    model.fit(series)
    forecast = model.make_future_dataframe(periods=10, freq='Y')
    forecast = model.predict(forecast)
    forecast = forecast[['ds', 'yhat', 'yhat_lower', 'yhat_upper']]
    forecast['city, state'] = series['city, state'].iloc[0]
    forecast = forecast[['city, state','ds', 'yhat', 'yhat_lower', 'yhat_upper']]
    forecast[['yhat', 'yhat_lower', 'yhat_upper']] = forecast[['yhat', 'yhat_lower', 'yhat_upper']].astype(float)
    forecast[['yhat', 'yhat_lower', 'yhat_upper']] = forecast[['yhat', 'yhat_lower', 'yhat_upper']].apply(np.ceil)
    return forecast
f = run_prophet(series[0])
f.head()
for i in range(len(series)):
    f = run_prophet(series[i])
    f.to_csv('fmr1_predictions.csv', mode='a', index='False')
rental_prediction1 = pd.read_csv('fmr1_predictions.csv')
rental_prediction1 = rental_prediction1.loc[rental_prediction1['city, state'] != 'city, state']
rental_prediction1.to_csv('fmr1_predictions.csv', index=False)
rent_long1 = rental_prediction1[['city, state', 'ds', 'yhat']]
rent_long1.dtypes
rent_long1['yhat'] = pd.to_numeric(rent_long1['yhat'],errors='coerce')
rent_long1.head()
rent_long1 = rent_long1.pivot_table(index = 'city, state',
                                  columns = 'ds',
                                  values = 'yhat')
rent_long1 = rent_long1.iloc[3:]
rent_long1
rent_long1.to_csv('fmr1_long.csv', index=False )<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16786599032676655, 'fraction_numerical': 0.04872006606110652, 'mean_word_length': 5.875912408759124, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 48, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '63978', 'license': 'MIT', 'n_tokens_mistral': 3655, 'n_tokens_neox': 3427, 'n_words': 508, 'path': 'notebooks/model/rental/rents.ipynb', 'repo_name': 'jiobu1/labspt15-cityspire-g-ds', 'size': '8477'}