import pandas as pd shipping_excel = pd.ExcelFile('shipping1997.xlsx') # get a list of all the sheet names (could loop through and get a df for each one) sheet_names = shipping_excel.sheet_names print(sheet_names) # read the data from the sheets shipping_df = pd.read_excel(shipping_excel, sheet_name='shipping') states_df = pd.read_excel(shipping_excel, sheet_name='states') # make sure the adjacency comes in as a boolean when reading this df distances_df = pd.read_excel(shipping_excel, sheet_name='distances', dtype={'adjacent':bool})

# create a dictionary of state abbreviations and values using the zip function states_dict = dict(zip(states_df['Abbreviation'], states_df['State'])) # map the values to the abbreviation keys for both the shipping and distances df's shipping_df['origin'] = shipping_df['origin'].map(states_dict) shipping_df['dest'] = shipping_df['dest'].map(states_dict) distances_df['origin'] = distances_df['origin'].map(states_dict) distances_df['dest'] = distances_df['dest'].map(states_dict) shipping_df

shipping_df.merge(distances_df, on=['origin', 'dest'])