import pandas as pd

shipping_file = pd.read_excel("shipping1997.xlsx", [0,1,2,3])

shipping_df = shipping_file[0] state_df = shipping_file[1] commodities_df = shipping_file[2] distances_df = shipping_file[3]

abbreviation_list = state_df['Abbreviation'].tolist() state_list = state_df['State'].tolist() abbrev_to_state = dict(zip(abbreviation_list, state_list))

new_shipping_df = shipping_df.copy() new_shipping_df['origin'] = new_shipping_df['origin'].map(abbrev_to_state) new_shipping_df['dest'] = new_shipping_df['dest'].map(abbrev_to_state) new_shipping_df.head()

distances_df['adjacent']= distances_df['adjacent'].astype('Bool') distances_df.head()

def getDistance(origin, destination): distance = distances_df['Distance(km)'][(distances_df['origin'] == origin) & (distances_df['dest'] == destination)] return distance.item() def getAdjacent(origin, destination): adjacent = distances_df['adjacent'][(distances_df['origin'] == origin) & (distances_df['dest'] == destination)] return adjacent.item()

shipping_df['Distance'] = shipping_df.apply(lambda x: getDistance(x['origin'], x['dest']), axis=1) shipping_df['Adjacent'] = shipping_df.apply(lambda x: getAdjacent(x['origin'], x['dest']), axis=1)

shipping_df.head()