# Import necessary packages
import openpyxl
import pandas as pd
# Create dataframes for each Excel sheet
shipping = pd.read_excel( 'shipping1997.xlsx', sheet_name='shipping', engine='openpyxl')
states = pd.read_excel( 'shipping1997.xlsx', sheet_name='states', engine='openpyxl')
commodities = pd.read_excel( 'shipping1997.xlsx', sheet_name='commodities', engine='openpyxl')
distances = pd.read_excel( 'shipping1997.xlsx', sheet_name='distances', engine='openpyxl')
# Change data type of adjacent column in distances data frame
distances['adjacent'] = distances['adjacent'].astype('Bool')
distances.head()
# Add distance and adjacent columns to shipping data frame
distances['OriginDest'] = distances['origin'] + distances['dest']
shipping['OriginDest'] = shipping['origin'] + shipping['dest']
shipping_distance = shipping.merge(distances[['Distance(km)', 'adjacent', 'OriginDest']], on='OriginDest').drop('OriginDest', axis='columns')
shipping_distance
# change abreviations to full names in shipping data frame
dictionary = pd.Series(states.State.values,index=states.Abbreviation).to_dict()
shipping_distance['origin'] = shipping_distance['origin'].map(dictionary)
shipping_distance['dest'] = shipping_distance['dest'].map(dictionary)
shipping_distance