import pandas as pd sheets = pd.read_excel('shipping1997.xlsx', sheet_name=None) sheets.keys()

ship_df = sheets['shipping'] ship_df.head()

state_df = sheets['states'] state_df.head()

commodities_df = sheets['commodities'] commodities_df.head()

distance_df = sheets['distances'] distance_df.head()

convert_abbreviation_to_name = dict( zip(state_df.Abbreviation, state_df.State) ) ship_df.replace(convert_abbreviation_to_name, inplace=True) ship_df.head(-5)

distance_df.replace(convert_abbreviation_to_name, inplace=True) distance_df.head()

distance_df.adjacent.astype('bool')

ship_df = ship_df.merge(distance_df, how='left', left_on=['origin', 'dest'], right_on=['origin', 'dest']) ship_df