Using Groupby and Transform¶

[1]:

import transportation_tutorials as tt
import pandas as pd
import numpy as np

Questions¶

Within each FDOT District, what is the fraction of structurally deficient bridge deck area in each County?
Which county has the highest share of structurally deficient bridge deck area within its FDOT District? (Hint: the correct answer is PALM BEACH.)

Data¶

To answer the question, use the following data files:

[2]:

districts = pd.read_csv(tt.data('FL-COUNTY-BY-DISTRICT'))
districts.head()

[2]:

	County	District
0	Charlotte	1
1	Collier	1
2	DeSoto	1
3	Glades	1
4	Hardee	1

[3]:

bridges = pd.read_csv(tt.data('FL-BRIDGES'))

# Recall the necessary cleaning for the bridges data file
bridges = bridges.replace('-', 0)
bridges[['Poor #', 'SD #']] = bridges[['Poor #', 'SD #']].astype(int)
bridges.fillna(0, inplace=True)

bridges.head()

[3]:

	County	Total #	Good #	Fair #	Poor #	SD #	Total Area	Good Area	Fair Area	Poor Area	SD Area
0	ALACHUA (001)	111	64	47	0	0	64767	55794	8973	0.0	0.0
1	BAKER (003)	89	30	52	7	8	32162	19369	12282	510.0	623.0
2	BAY (005)	122	49	63	10	11	210039	98834	109628	1577.0	10120.0
3	BRADFORD (007)	62	23	37	2	2	9330	5492	3217	620.0	620.0
4	BREVARD (009)	241	160	81	0	0	364138	204179	159959	0.0	0.0