excel_file = r'/Users/amandawhiting/Desktop/PA_spending_excel.xlsx'
df = pd.read_excel(excel_file)
df = df.rename(columns={'DAMAGE_CATEGORY_CODE': 'damageCode',
                     'FEDERAL_SHARE_OBLIGATED':'FedShareObligated', 'PROJECT_AMOUNT': 'ProjectAmount'})
df = df[df['FedShareObligated']>= 0]
df = df[df['ProjectAmount'] >= 0] # Removes missing/null projects
df = df[df['damageCode'] != 'A - Debris Removal']
df = df[df['damageCode'] != 'B - Protective Measures']
df = df[df['damageCode'] != 'Z - State Management']
df = df[df['damageCode'] != 'H - Fire Management']
df = df.drop_duplicates()
df = df.reset_index(drop=True)

df2 = pd.read_csv("/Users/amandawhiting/Desktop/DisasterDeclarationsSummaries.csv", usecols = ['disasterNumber', 'fyDeclared', 'state'])
df2 = df2[df2['fyDeclared'] > 1991]
df2 = df2[df2['fyDeclared'] < 2017]
df2 = df2.reset_index(drop=True)  # Resets index
df2['disasterNumber'] = df2['disasterNumber'].astype(int)

fulldf = pd.merge(df, df2, left_on = 'disasterNumber', right_on = 'disasterNumber', how = 'inner',)
fulldf = fulldf.drop_duplicates()
fulldf = fulldf.reset_index(drop=True)

fulldf["Region"] = fulldf['state'].map(state_code)

df_state = fulldf.copy()
df_fyr = fulldf.copy()
df_region = fulldf.copy()
df_damageCat = fulldf.copy()

fulldf["TotalProjectCost, 1.3%"] = round(fulldf["ProjectAmount"] * .013)
fulldf["TotalProjectCost, 1.6%"] = round(fulldf["ProjectAmount"] * .016)
fulldf["TotalProjectCost, 15%"] = round(fulldf["ProjectAmount"] * .15)
fulldf["TotalProjectCost, 46%"] = round(fulldf["ProjectAmount"] * .46)

df_state=df_state[["ProjectAmount"]].groupby('state').sum()

df_state['TotalProjectCost'] = ['${:,.2f}MM'.format(x) for x in     df_state['ProjectAmount'] * (.15) / 1000000]


display(df_state)