Network Data Exercise

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

	originobject	destobject	SCTG Codeint64	Value($ mil)object	Tons(000)object	Ton-miles(mil)object
0	Alaska	Alaska	0	5376	17448	2624
1	Alaska	Alaska	1	-	-	-
2	Alaska	Alaska	2	-	-	-
3	Alaska	Alaska	3	43	25	3
4	Alaska	Alaska	4	S	S	S
5 rows × 6 columns

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

	originobject	destobject	Distance(km)float64	adjacentbool
0	AK	AK	64.09190953	true
1	AK	AL	4581.716256000001	false
2	AK	AR	4036.34672	false
3	AK	AZ	3217.304887	false
4	AK	CA	2387.072321	false
5 rows × 4 columns

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

	originobject	destobject	SCTG Codeint64	Value($ mil)object	Tons(000)object	Ton-miles(mil)object	Distancefloat64	Adjacentbool
0	AK	AK	0	5376	17448	2624	64.09190953	true
1	AK	AK	1	-	-	-	64.09190953	true
2	AK	AK	2	-	-	-	64.09190953	true
3	AK	AK	3	43	25	3	64.09190953	true
4	AK	AK	4	S	S	S	64.09190953	true
5 rows × 8 columns