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})
['shipping', 'states', 'commodities', 'distances']
# 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'])