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()
