# ---
# 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
# ---

# +
import os
import pandas as pd
import numpy as np
import csv

import seaborn

# +
csv_path='raw_data/Zip_Zhvi_3bedroom.csv'
df_3b=pd.DataFrame.from_csv(csv_path)

df_3b=df_3b.reset_index()
df_3b=df_3b.set_index('State')
df_3b.head()

df_3b=df_3b.rename(columns={'RegionName':'Zipcode'})
df_3b
len(df_3b)

df_3b=df_3b.loc['CA',:]
df_3b.head()
# -

df_3b['Bedrooms']='3'

df_3b

#take needed columns and order
subset=['RegionID','Bedrooms']
mask=df_3b.columns.isin(subset)
df_3b=pd.concat([df_3b.loc[:,mask],df_3b.loc[:,~mask]],axis=1)


df_3b=df_3b.loc[:,df_3b.columns!='RegionID']
df_3b

df_3b=df_3b.reset_index()
df_3b=df_3b.set_index('CountyName')
df_3b=df_3b.loc['Orange',:]
df_3b

# +

# subset=['State','Bedrooms','Zipcode','City','SizeRank','Metro']
# mask=df_1b.columns.isin(subset)
# df_1b_ed=pd.concat([df_1b.loc[:,mask]],axis=1)


#everything but prices by date, saved to be merged after drop of every nth column

# df_1b_ed

# +
# df_1b_ed_p2=pd.concat([df_1b.loc[:,~mask]],axis=1)

# df_1b_ed_p2

# +
#get last 88 columns


# clean=df_1b_ed_p2.iloc[:,-100:]
# clean.head()


# -

three_b_df=pd.DataFrame(df_3b.groupby(['City','Zipcode'])['2009-01','2010-01','2011-01','2012-01','2013-01','2014-01','2015-01','2016-01','2017-01','2018-01'].median())
three_b_df.head()

# +
three_b_df['Median Price']=three_b_df.median(axis=1)
three_b_df.head()



# -

three_b_df['Number of Bedrooms']=3
three_b_df.head()

three_b_df=three_b_df.rename(columns={'2009-01':'2009','2010-01':'2010','2011-01':'2011','2012-01':'2012','2013-01':'2013','2014-01':'2014','2015-01':'2015','2016-01':'2016','2017-01':'2017','2018-01':'2018'})
three_b_df

three_b_df.reset_index()

three_b_df.to_csv('3bedroom_data.csv')













StarcoderdataJupyterScriptsDedupFiltered