Last active
October 23, 2020 10:12
-
-
Save BinarySpoon/1b2b9b258ed4148c139926d83adbf153 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# converting house data to quater --> | |
def convert_housing_data_to_quarters(): | |
states = {'OH': 'Ohio', 'KY': 'Kentucky', 'AS': 'American Samoa', 'NV': 'Nevada', 'WY': 'Wyoming', 'NA': 'National', 'AL': 'Alabama', | |
'MD': 'Maryland', 'AK': 'Alaska', 'UT': 'Utah', 'OR': 'Oregon', 'MT': 'Montana', 'IL': 'Illinois', 'TN': 'Tennessee', 'DC': 'District of Columbia', 'VT': 'Vermont', 'ID': 'Idaho', 'AR': 'Arkansas', | |
'ME': 'Maine', 'WA': 'Washington', 'HI': 'Hawaii', 'WI': 'Wisconsin', 'MI': 'Michigan', 'IN': 'Indiana', 'NJ': 'New Jersey', 'AZ': 'Arizona', 'GU': 'Guam', 'MS': 'Mississippi', 'PR': 'Puerto Rico', | |
'NC': 'North Carolina', 'TX': 'Texas', 'SD': 'South Dakota', 'MP': 'Northern Mariana Islands', 'IA': 'Iowa', 'MO': 'Missouri', 'CT': 'Connecticut', 'WV': 'West Virginia', 'SC': 'South Carolina', | |
'LA': 'Louisiana', 'KS': 'Kansas', 'NY': 'New York', 'NE': 'Nebraska', 'OK': 'Oklahoma', 'FL': 'Florida', 'CA': 'California', 'CO': 'Colorado', 'PA': 'Pennsylvania', 'DE': 'Delaware', 'NM': 'New Mexico', | |
'RI': 'Rhode Island', 'MN': 'Minnesota', 'VI': 'Virgin Islands', 'NH': 'New Hampshire', 'MA': 'Massachusetts', 'GA': 'Georgia', 'ND': 'North Dakota', 'VA': 'Virginia'} | |
df = pd.read_csv('City_Zhvi_AllHomes.csv',header=0) | |
# years to keep --> | |
def years_to_keep(list): | |
for i in range(len(list)): | |
for j in list: | |
if re.match('^19',j): | |
list.remove(j) | |
return list | |
#Removing Unecessary Columns First --> | |
cols_to_keep = list(df.columns.values) | |
cols_to_keep = years_to_keep(cols_to_keep) | |
cols_to_keep = [ele for ele in cols_to_keep if ele not in {'Metro','CountyName','SizeRank'}] | |
df = df[cols_to_keep] | |
df['State'] = df['State'].replace(states) | |
# Creating a new dataframe with --> | |
df_compiled = df.copy().set_index(['State', 'RegionName', 'RegionID']).stack(dropna=False) | |
df_compiled = df_compiled.reset_index().rename(columns={'level_3': 'year_month', 0: 'gdp'}) | |
df_compiled.drop_duplicates(inplace=True) | |
# Converting from monthly to quaterly format --> | |
df_compiled['quarter'] = df_compiled['year_month'].apply(convert_to_qtr) | |
df_compiled = df_compiled.drop('year_month', axis=1) | |
# return result dataframe --> | |
result = df_compiled.pivot_table(values='gdp', index=['State', 'RegionName', 'RegionID'], columns='quarter', aggfunc=np.mean) | |
result = result.reset_index() | |
result = result.drop('RegionID', axis=1) | |
result = result.set_index(['State', 'RegionName']) | |
return result | |
convert_housing_data_to_quarters() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment