How to fix KeyError when assigning regions to states in a dictionary

Viewed 2019

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'
2 Answers

IIUC, you're looking for either:

# replace non-available keys with NA
fulldf["Region"] = fulldf['state'].map(state_region)

or

# keep the non-available keys intact
fulldf["Region"] = fulldf['state'].replace(state_region)

Try it:

state_region = {v: k for k, v in state_code.items()}

def get_region():
    result = []
    for i in fulldf['state'] :
        if i in state_region :
            result.append(state_region[i])
        else :
            result.append("NA")
    return result

Related