10.29_Exercises

import pandas as pd shipping = pd.read_excel('shipping1997.xlsx', sheet_name=0) states = pd.read_excel('shipping1997.xlsx', sheet_name=1) commodities = pd.read_excel('shipping1997.xlsx', sheet_name=2) distances = pd.read_excel('shipping1997.xlsx', sheet_name=3)

state_names = dict(zip(states['Abbreviation'], states['State'])) shipping_full = shipping.copy() shipping_full['origin'] = shipping['origin'].map(state_names) shipping_full['dest'] = shipping['dest'].map(state_names) shipping_full

	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
Expand rows 5 - 107494
107495	Wyoming	Wyoming	39	S	S	S
107496	Wyoming	Wyoming	40	S	S	-
107497	Wyoming	Wyoming	41	-	-	-
107498	Wyoming	Wyoming	43	-	-	-
107499	Wyoming	Wyoming	99	-	-	-
107500 rows × 6 columns

distances = pd.read_excel('shipping1997.xlsx', sheet_name=3,dtype={'adjacent':bool}) distances.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

shipping_distances = shipping.merge(distances, on=['origin', 'dest']) shipping_distances

	originobject	destobject	SCTG Codeint64	Value($ mil)object	Tons(000)object	Ton-miles(mil)object	Distance(km)float64	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
Expand rows 5 - 107494
107495	WY	WY	39	S	S	S	38.37083439	true
107496	WY	WY	40	S	S	-	38.37083439	true
107497	WY	WY	41	-	-	-	38.37083439	true
107498	WY	WY	43	-	-	-	38.37083439	true
107499	WY	WY	99	-	-	-	38.37083439	true
107500 rows × 8 columns