SHARE
TWEET

Untitled

a guest Jun 18th, 2019 61 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. excel_file = r'/Users/amandawhiting/Desktop/PA_spending_excel.xlsx'
  2. df = pd.read_excel(excel_file)
  3. df = df.rename(columns={'DAMAGE_CATEGORY_CODE': 'damageCode',
  4.                      'FEDERAL_SHARE_OBLIGATED':'FedShareObligated', 'PROJECT_AMOUNT': 'ProjectAmount'})  
  5. df = df[df['FedShareObligated']>= 0]
  6. df = df[df['ProjectAmount'] >= 0] # Removes missing/null projects
  7. df = df[df['damageCode'] != 'A - Debris Removal']
  8. df = df[df['damageCode'] != 'B - Protective Measures']
  9. df = df[df['damageCode'] != 'Z - State Management']
  10. df = df[df['damageCode'] != 'H - Fire Management']
  11. df = df.drop_duplicates()
  12. df = df.reset_index(drop=True)
  13.  
  14. df2 = pd.read_csv("/Users/amandawhiting/Desktop/DisasterDeclarationsSummaries.csv", usecols = ['disasterNumber', 'fyDeclared', 'state'])
  15. df2 = df2[df2['fyDeclared'] > 1991]
  16. df2 = df2[df2['fyDeclared'] < 2017]
  17. df2 = df2.reset_index(drop=True)  # Resets index
  18. df2['disasterNumber'] = df2['disasterNumber'].astype(int)
  19.  
  20. fulldf = pd.merge(df, df2, left_on = 'disasterNumber', right_on = 'disasterNumber', how = 'inner',)
  21. fulldf = fulldf.drop_duplicates()
  22. fulldf = fulldf.reset_index(drop=True)
  23.  
  24. fulldf["Region"] = fulldf['state'].map(state_code)
  25.  
  26. df_state = fulldf.copy()
  27. df_fyr = fulldf.copy()
  28. df_region = fulldf.copy()
  29. df_damageCat = fulldf.copy()
  30.  
  31. fulldf["TotalProjectCost, 1.3%"] = round(fulldf["ProjectAmount"] * .013)                                                                                                                  
  32. fulldf["TotalProjectCost, 1.6%"] = round(fulldf["ProjectAmount"] * .016)                                                                      
  33. fulldf["TotalProjectCost, 15%"] = round(fulldf["ProjectAmount"] * .15)
  34. fulldf["TotalProjectCost, 46%"] = round(fulldf["ProjectAmount"] * .46)
  35.      
  36. df_state=df_state[["ProjectAmount"]].groupby('state').sum()
  37.  
  38. df_state['TotalProjectCost'] = ['${:,.2f}MM'.format(x) for x in     df_state['ProjectAmount'] * (.15) / 1000000]
  39.  
  40.  
  41. display(df_state)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top