import pandas as pd
shipping = pd.read_excel('shipping1997.xlsx', sheet_name=0)
states = pd.read_excel('shipping1997.xlsx', sheet_name=1)
commodities = pd.read_excel('shipping1997.xlsx', sheet_name=2)
distances = pd.read_excel('shipping1997.xlsx', sheet_name=3)
state_names = dict(zip(states['Abbreviation'], states['State']))
shipping_full = shipping.copy()
shipping_full['origin'] = shipping['origin'].map(state_names)
shipping_full['dest'] = shipping['dest'].map(state_names)
shipping_full
distances = pd.read_excel('shipping1997.xlsx', sheet_name=3,dtype={'adjacent':bool})
distances.head()
shipping_distances = shipping.merge(distances, on=['origin', 'dest'])
shipping_distances
