I am assigning regions to each individual state. My code reads from an excel file, and there are about 30k rows. I set up a dictionary assigning each state to a region as well as state abbreviations to each state name. I am trying to create a column that will populate each row item's region, but keep getting a KeyError at 'MA' (because there are no line items for this state in my Excel file).
I have tried writing a phrase using 'except' as well as 'if missing' and neither seems to clear the error and produce the desired results. I also tried removing MA from the dictionary, but the same error appears. I'm new to Python-- I am sure there is an easy fix here but do not know what it is.
states = {
'AK': 'Alaska',
'AL': 'Alabama',
'AR': 'Arkansas',
'AZ': 'Arizona',
'CA': 'California',
'CO': 'Colorado',
'CT': 'Connecticut',
'DC': 'District of Columbia',
'DE': 'Delaware',
'FL': 'Florida',
'GA': 'Georgia',
'HI': 'Hawaii',
'IA': 'Iowa',
'ID': 'Idaho',
'IL': 'Illinois',
'IN': 'Indiana',
'KS': 'Kansas',
'KY': 'Kentucky',
'LA': 'Louisiana',
'MA': 'Massachusetts',
'MD': 'Maryland',
'ME': 'Maine',
'MI': 'Michigan',
'MN': 'Minnesota',
'MO': 'Missouri',
'MS': 'Mississippi',
'MT': 'Montana',
'NC': 'North Carolina',
'ND': 'North Dakota',
'NE': 'Nebraska',
'NH': 'New Hampshire',
'NJ': 'New Jersey',
'NM': 'New Mexico',
'NV': 'Nevada',
'NY': 'New York',
'OH': 'Ohio',
'OK': 'Oklahoma',
'OR': 'Oregon',
'PA': 'Pennsylvania',
'RI': 'Rhode Island',
'SC': 'South Carolina',
'SD': 'South Dakota',
'TN': 'Tennessee',
'TX': 'Texas',
'UT': 'Utah',
'VA': 'Virginia',
'VT': 'Vermont',
'WA': 'Washington',
'WI': 'Wisconsin',
'WV': 'West Virginia',
'WY': 'Wyoming'
}
stateplusdc = states.keys()
state_abbrev = {v: k for k, v in states.items()}
state_code = {
'AK': '10','AL': '4', 'AR': '9', 'AR': '6', 'CA': '9', 'CO': '8',
'CT': '1', 'DC': '3', 'DE': '3', 'FL': '4', 'GA': '4', 'HI': '9', 'IA': '7', 'ID': '10', 'IL': '5', 'IN': '5', 'KS': '7', 'KY': '4', 'LA': '6', 'MA': '1', 'MD': '3', 'ME': '1', 'MI': '5', 'MN': '5','MO': '7', 'MS': '4', 'MT': '8', 'NC': '4', 'ND': '8', 'NE': '7', 'NH': '1', 'NJ': '2', 'NM': '6','NV': '9', 'NY': '2', 'OH': '5', 'OK': '6','OR': '10', 'PA': '3', 'PR': '2', 'RI': '1', 'SC': '4', 'SD': '8', 'TN': '4', 'TX': '6', 'UT': '8', 'VA': '3', 'VI': '2', 'VT': '1', 'WA': '10', 'WI': '5', 'WV': '3', 'WY': '8', 'PI': '9'
}
state_region = {v: k for k, v in state_code.items()}
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'] >= 0df2 = pd.read_csv("/Users/amandawhiting/Desktop/DisasterDeclarationsSummaries.csv", usecols = ['disasterNumber', 'fyDeclared', 'state'])
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)
df2['disasterNumber'] = df2['disasterNumber'].astype(int)
fulldf = pd.merge(df, df2, left_on = 'DISASTER_NUMBER', right_on = 'disasterNumber', how = 'inner',)
fulldf = fulldf.drop_duplicates()
fulldf = fulldf.reset_index(drop=True)
def get_region():
return [state_region[i] for i in fulldf['state']]
fulldf["Region"] = get_region()
fulldf.head()
Expected results: New column in existing table labeled "Regions" that populates each cell with the corresponding region for that state in that line.
Actual results: Key Error 'MA':
KeyError Traceback (most recent call last)
<ipython-input-403-13becd272809> in <module>
31 return [state_region[i] for i in fulldf['state']]
32
---> 33 fulldf["Region"] = get_region()
34
35 fulldf.head()
<ipython-input-403-13becd272809> in get_region()
29
30 def get_region():
---> 31 return [state_region[i] for i in fulldf['state']]
32
33 fulldf["Region"] = get_region()
<ipython-input-403-13becd272809> in <listcomp>(.0)
29
30 def get_region():
---> 31 return [state_region[i] for i in fulldf['state']]
32
33 fulldf["Region"] = get_region()
KeyError: 'MA'