import pandas as pd1. Data Cleaning
a. Missing Data
b. Noisy Dataa. Missing Data
- Dataset - Detailed NFL Play-by-Play Data 2009-2018
- Guide : https://www.kaggle.com/code/rtatman/data-cleaning-challenge-handling-missing-values
nfl_df = pd.read_csv("data/nfl/NFL Play by Play 2009-2018 (v5).csv")
nfl_df.sample()/var/folders/r0/bggl6hf15j708chpxqrx5tp00000gn/T/ipykernel_6195/401957088.py:1: DtypeWarning: Columns (42,166,167,168,169,174,175,178,179,182,183,188,189,190,191,194,195,203,204,205,218,219,220,231,232,233,238,240,241,249) have mixed types. Specify dtype option on import or set low_memory=False.
nfl_df = pd.read_csv("data/nfl/NFL Play by Play 2009-2018 (v5).csv")| play_id | game_id | home_team | away_team | posteam | posteam_type | defteam | side_of_field | yardline_100 | game_date | ... | penalty_player_id | penalty_player_name | penalty_yards | replay_or_challenge | replay_or_challenge_result | penalty_type | defensive_two_point_attempt | defensive_two_point_conv | defensive_extra_point_attempt | defensive_extra_point_conv | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 137741 | 4045 | 2012091612 | MIA | OAK | OAK | away | MIA | OAK | 70.0 | 2012-09-16 | ... | NaN | NaN | NaN | 0 | NaN | NaN | 0.0 | 0.0 | 0.0 | 0.0 |
1 rows × 255 columns
nfl_df.info(verbose=True, show_counts=True)<class 'pandas.core.frame.DataFrame'>
RangeIndex: 449371 entries, 0 to 449370
Data columns (total 255 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 play_id 449371 non-null int64
1 game_id 449371 non-null int64
2 home_team 449371 non-null object
3 away_team 449371 non-null object
4 posteam 436129 non-null object
5 posteam_type 436492 non-null object
6 defteam 436492 non-null object
7 side_of_field 448771 non-null object
8 yardline_100 436301 non-null float64
9 game_date 449371 non-null object
10 quarter_seconds_remaining 449230 non-null float64
11 half_seconds_remaining 449206 non-null float64
12 game_seconds_remaining 449208 non-null float64
13 game_half 449371 non-null object
14 quarter_end 449371 non-null int64
15 drive 449371 non-null int64
16 sp 449371 non-null int64
17 qtr 449371 non-null int64
18 down 381409 non-null float64
19 goal_to_go 436664 non-null float64
20 time 449230 non-null object
21 yrdln 448771 non-null object
22 ydstogo 449371 non-null int64
23 ydsnet 449371 non-null int64
24 desc 449369 non-null object
25 play_type 436497 non-null object
26 yards_gained 449158 non-null float64
27 shotgun 449371 non-null int64
28 no_huddle 449371 non-null int64
29 qb_dropback 436497 non-null float64
30 qb_kneel 449371 non-null int64
31 qb_spike 449371 non-null int64
32 qb_scramble 449371 non-null int64
33 pass_length 174715 non-null object
34 pass_location 174715 non-null object
35 air_yards 175719 non-null float64
36 yards_after_catch 108907 non-null float64
37 run_location 131307 non-null object
38 run_gap 94921 non-null object
39 field_goal_result 9813 non-null object
40 kick_distance 50740 non-null float64
41 extra_point_result 11150 non-null object
42 two_point_conv_result 696 non-null object
43 home_timeouts_remaining 449371 non-null int64
44 away_timeouts_remaining 449371 non-null int64
45 timeout 436497 non-null float64
46 timeout_team 18656 non-null object
47 td_team 12833 non-null object
48 posteam_timeouts_remaining 436492 non-null float64
49 defteam_timeouts_remaining 436492 non-null float64
50 total_home_score 449371 non-null int64
51 total_away_score 449371 non-null int64
52 posteam_score 433952 non-null float64
53 defteam_score 433952 non-null float64
54 score_differential 433952 non-null float64
55 posteam_score_post 436492 non-null float64
56 defteam_score_post 436492 non-null float64
57 score_differential_post 436492 non-null float64
58 no_score_prob 448693 non-null float64
59 opp_fg_prob 448693 non-null float64
60 opp_safety_prob 448693 non-null float64
61 opp_td_prob 448693 non-null float64
62 fg_prob 448693 non-null float64
63 safety_prob 448693 non-null float64
64 td_prob 448693 non-null float64
65 extra_point_prob 449371 non-null float64
66 two_point_conversion_prob 449371 non-null float64
67 ep 435812 non-null float64
68 epa 433535 non-null float64
69 total_home_epa 449371 non-null float64
70 total_away_epa 449371 non-null float64
71 total_home_rush_epa 449371 non-null float64
72 total_away_rush_epa 449371 non-null float64
73 total_home_pass_epa 449371 non-null float64
74 total_away_pass_epa 449371 non-null float64
75 air_epa 174082 non-null float64
76 yac_epa 173699 non-null float64
77 comp_air_epa 436299 non-null float64
78 comp_yac_epa 436111 non-null float64
79 total_home_comp_air_epa 449371 non-null float64
80 total_away_comp_air_epa 449371 non-null float64
81 total_home_comp_yac_epa 449371 non-null float64
82 total_away_comp_yac_epa 449371 non-null float64
83 total_home_raw_air_epa 449371 non-null float64
84 total_away_raw_air_epa 449371 non-null float64
85 total_home_raw_yac_epa 449371 non-null float64
86 total_away_raw_yac_epa 449371 non-null float64
87 wp 433313 non-null float64
88 def_wp 433313 non-null float64
89 home_wp 435388 non-null float64
90 away_wp 435388 non-null float64
91 wpa 444050 non-null float64
92 home_wp_post 433334 non-null float64
93 away_wp_post 433334 non-null float64
94 total_home_rush_wpa 449371 non-null float64
95 total_away_rush_wpa 449371 non-null float64
96 total_home_pass_wpa 449371 non-null float64
97 total_away_pass_wpa 449371 non-null float64
98 air_wpa 173940 non-null float64
99 yac_wpa 173724 non-null float64
100 comp_air_wpa 436156 non-null float64
101 comp_yac_wpa 436064 non-null float64
102 total_home_comp_air_wpa 449371 non-null float64
103 total_away_comp_air_wpa 449371 non-null float64
104 total_home_comp_yac_wpa 449371 non-null float64
105 total_away_comp_yac_wpa 449371 non-null float64
106 total_home_raw_air_wpa 449371 non-null float64
107 total_away_raw_air_wpa 449371 non-null float64
108 total_home_raw_yac_wpa 449371 non-null float64
109 total_away_raw_yac_wpa 449371 non-null float64
110 punt_blocked 436497 non-null float64
111 first_down_rush 436497 non-null float64
112 first_down_pass 436497 non-null float64
113 first_down_penalty 436497 non-null float64
114 third_down_converted 436497 non-null float64
115 third_down_failed 436497 non-null float64
116 fourth_down_converted 436497 non-null float64
117 fourth_down_failed 436497 non-null float64
118 incomplete_pass 436497 non-null float64
119 interception 436497 non-null float64
120 punt_inside_twenty 436497 non-null float64
121 punt_in_endzone 436497 non-null float64
122 punt_out_of_bounds 436497 non-null float64
123 punt_downed 436497 non-null float64
124 punt_fair_catch 436497 non-null float64
125 kickoff_inside_twenty 436497 non-null float64
126 kickoff_in_endzone 436497 non-null float64
127 kickoff_out_of_bounds 436497 non-null float64
128 kickoff_downed 436497 non-null float64
129 kickoff_fair_catch 436497 non-null float64
130 fumble_forced 436497 non-null float64
131 fumble_not_forced 436497 non-null float64
132 fumble_out_of_bounds 436497 non-null float64
133 solo_tackle 436497 non-null float64
134 safety 436497 non-null float64
135 penalty 436497 non-null float64
136 tackled_for_loss 436497 non-null float64
137 fumble_lost 436497 non-null float64
138 own_kickoff_recovery 436497 non-null float64
139 own_kickoff_recovery_td 436497 non-null float64
140 qb_hit 436497 non-null float64
141 rush_attempt 436497 non-null float64
142 pass_attempt 436497 non-null float64
143 sack 436497 non-null float64
144 touchdown 436497 non-null float64
145 pass_touchdown 436497 non-null float64
146 rush_touchdown 436497 non-null float64
147 return_touchdown 436497 non-null float64
148 extra_point_attempt 436497 non-null float64
149 two_point_attempt 436497 non-null float64
150 field_goal_attempt 436497 non-null float64
151 kickoff_attempt 436497 non-null float64
152 punt_attempt 436497 non-null float64
153 fumble 436497 non-null float64
154 complete_pass 436497 non-null float64
155 assist_tackle 436497 non-null float64
156 lateral_reception 436497 non-null float64
157 lateral_rush 436497 non-null float64
158 lateral_return 436497 non-null float64
159 lateral_recovery 436497 non-null float64
160 passer_player_id 188267 non-null object
161 passer_player_name 188267 non-null object
162 receiver_player_id 173716 non-null object
163 receiver_player_name 173703 non-null object
164 rusher_player_id 136448 non-null object
165 rusher_player_name 136447 non-null object
166 lateral_receiver_player_id 94 non-null object
167 lateral_receiver_player_name 94 non-null object
168 lateral_rusher_player_id 17 non-null object
169 lateral_rusher_player_name 17 non-null object
170 lateral_sack_player_id 0 non-null float64
171 lateral_sack_player_name 0 non-null float64
172 interception_player_id 4630 non-null object
173 interception_player_name 4630 non-null object
174 lateral_interception_player_id 23 non-null object
175 lateral_interception_player_name 23 non-null object
176 punt_returner_player_id 16724 non-null object
177 punt_returner_player_name 16719 non-null object
178 lateral_punt_returner_player_id 23 non-null object
179 lateral_punt_returner_player_name 23 non-null object
180 kickoff_returner_player_name 13441 non-null object
181 kickoff_returner_player_id 13442 non-null object
182 lateral_kickoff_returner_player_id 38 non-null object
183 lateral_kickoff_returner_player_name 38 non-null object
184 punter_player_id 23883 non-null object
185 punter_player_name 23713 non-null object
186 kicker_player_name 46702 non-null object
187 kicker_player_id 46726 non-null object
188 own_kickoff_recovery_player_id 94 non-null object
189 own_kickoff_recovery_player_name 94 non-null object
190 blocked_player_id 419 non-null object
191 blocked_player_name 409 non-null object
192 tackle_for_loss_1_player_id 23623 non-null object
193 tackle_for_loss_1_player_name 23623 non-null object
194 tackle_for_loss_2_player_id 8 non-null object
195 tackle_for_loss_2_player_name 8 non-null object
196 qb_hit_1_player_id 24213 non-null object
197 qb_hit_1_player_name 24208 non-null object
198 qb_hit_2_player_id 976 non-null object
199 qb_hit_2_player_name 976 non-null object
200 forced_fumble_player_1_team 4403 non-null object
201 forced_fumble_player_1_player_id 4403 non-null object
202 forced_fumble_player_1_player_name 4403 non-null object
203 forced_fumble_player_2_team 33 non-null object
204 forced_fumble_player_2_player_id 33 non-null object
205 forced_fumble_player_2_player_name 32 non-null object
206 solo_tackle_1_team 209699 non-null object
207 solo_tackle_2_team 2834 non-null object
208 solo_tackle_1_player_id 209699 non-null object
209 solo_tackle_2_player_id 2834 non-null object
210 solo_tackle_1_player_name 209696 non-null object
211 solo_tackle_2_player_name 2833 non-null object
212 assist_tackle_1_player_id 54419 non-null object
213 assist_tackle_1_player_name 54416 non-null object
214 assist_tackle_1_team 54419 non-null object
215 assist_tackle_2_player_id 54416 non-null object
216 assist_tackle_2_player_name 54416 non-null object
217 assist_tackle_2_team 54416 non-null object
218 assist_tackle_3_player_id 3 non-null object
219 assist_tackle_3_player_name 3 non-null object
220 assist_tackle_3_team 3 non-null object
221 assist_tackle_4_player_id 0 non-null float64
222 assist_tackle_4_player_name 0 non-null float64
223 assist_tackle_4_team 0 non-null float64
224 pass_defense_1_player_id 22738 non-null object
225 pass_defense_1_player_name 22738 non-null object
226 pass_defense_2_player_id 729 non-null object
227 pass_defense_2_player_name 729 non-null object
228 fumbled_1_team 6555 non-null object
229 fumbled_1_player_id 6555 non-null object
230 fumbled_1_player_name 6554 non-null object
231 fumbled_2_player_id 38 non-null object
232 fumbled_2_player_name 38 non-null object
233 fumbled_2_team 38 non-null object
234 fumble_recovery_1_team 6032 non-null object
235 fumble_recovery_1_yards 6026 non-null float64
236 fumble_recovery_1_player_id 6032 non-null object
237 fumble_recovery_1_player_name 6027 non-null object
238 fumble_recovery_2_team 47 non-null object
239 fumble_recovery_2_yards 47 non-null float64
240 fumble_recovery_2_player_id 47 non-null object
241 fumble_recovery_2_player_name 46 non-null object
242 return_team 30605 non-null object
243 return_yards 449350 non-null float64
244 penalty_team 32636 non-null object
245 penalty_player_id 32636 non-null object
246 penalty_player_name 31207 non-null object
247 penalty_yards 32618 non-null float64
248 replay_or_challenge 449371 non-null int64
249 replay_or_challenge_result 812 non-null object
250 penalty_type 31139 non-null object
251 defensive_two_point_attempt 436497 non-null float64
252 defensive_two_point_conv 436497 non-null float64
253 defensive_extra_point_attempt 436497 non-null float64
254 defensive_extra_point_conv 436497 non-null float64
dtypes: float64(135), int64(18), object(102)
memory usage: 874.2+ MBwith pd.option_context('display.max_rows', None, 'display.max_columns', None):
print(nfl_df.isnull().sum().to_frame()) 0
play_id 0
game_id 0
home_team 0
away_team 0
posteam 13242
posteam_type 12879
defteam 12879
side_of_field 600
yardline_100 13070
game_date 0
quarter_seconds_remaining 141
half_seconds_remaining 165
game_seconds_remaining 163
game_half 0
quarter_end 0
drive 0
sp 0
qtr 0
down 67962
goal_to_go 12707
time 141
yrdln 600
ydstogo 0
ydsnet 0
desc 2
play_type 12874
yards_gained 213
shotgun 0
no_huddle 0
qb_dropback 12874
qb_kneel 0
qb_spike 0
qb_scramble 0
pass_length 274656
pass_location 274656
air_yards 273652
yards_after_catch 340464
run_location 318064
run_gap 354450
field_goal_result 439558
kick_distance 398631
extra_point_result 438221
two_point_conv_result 448675
home_timeouts_remaining 0
away_timeouts_remaining 0
timeout 12874
timeout_team 430715
td_team 436538
posteam_timeouts_remaining 12879
defteam_timeouts_remaining 12879
total_home_score 0
total_away_score 0
posteam_score 15419
defteam_score 15419
score_differential 15419
posteam_score_post 12879
defteam_score_post 12879
score_differential_post 12879
no_score_prob 678
opp_fg_prob 678
opp_safety_prob 678
opp_td_prob 678
fg_prob 678
safety_prob 678
td_prob 678
extra_point_prob 0
two_point_conversion_prob 0
ep 13559
epa 15836
total_home_epa 0
total_away_epa 0
total_home_rush_epa 0
total_away_rush_epa 0
total_home_pass_epa 0
total_away_pass_epa 0
air_epa 275289
yac_epa 275672
comp_air_epa 13072
comp_yac_epa 13260
total_home_comp_air_epa 0
total_away_comp_air_epa 0
total_home_comp_yac_epa 0
total_away_comp_yac_epa 0
total_home_raw_air_epa 0
total_away_raw_air_epa 0
total_home_raw_yac_epa 0
total_away_raw_yac_epa 0
wp 16058
def_wp 16058
home_wp 13983
away_wp 13983
wpa 5321
home_wp_post 16037
away_wp_post 16037
total_home_rush_wpa 0
total_away_rush_wpa 0
total_home_pass_wpa 0
total_away_pass_wpa 0
air_wpa 275431
yac_wpa 275647
comp_air_wpa 13215
comp_yac_wpa 13307
total_home_comp_air_wpa 0
total_away_comp_air_wpa 0
total_home_comp_yac_wpa 0
total_away_comp_yac_wpa 0
total_home_raw_air_wpa 0
total_away_raw_air_wpa 0
total_home_raw_yac_wpa 0
total_away_raw_yac_wpa 0
punt_blocked 12874
first_down_rush 12874
first_down_pass 12874
first_down_penalty 12874
third_down_converted 12874
third_down_failed 12874
fourth_down_converted 12874
fourth_down_failed 12874
incomplete_pass 12874
interception 12874
punt_inside_twenty 12874
punt_in_endzone 12874
punt_out_of_bounds 12874
punt_downed 12874
punt_fair_catch 12874
kickoff_inside_twenty 12874
kickoff_in_endzone 12874
kickoff_out_of_bounds 12874
kickoff_downed 12874
kickoff_fair_catch 12874
fumble_forced 12874
fumble_not_forced 12874
fumble_out_of_bounds 12874
solo_tackle 12874
safety 12874
penalty 12874
tackled_for_loss 12874
fumble_lost 12874
own_kickoff_recovery 12874
own_kickoff_recovery_td 12874
qb_hit 12874
rush_attempt 12874
pass_attempt 12874
sack 12874
touchdown 12874
pass_touchdown 12874
rush_touchdown 12874
return_touchdown 12874
extra_point_attempt 12874
two_point_attempt 12874
field_goal_attempt 12874
kickoff_attempt 12874
punt_attempt 12874
fumble 12874
complete_pass 12874
assist_tackle 12874
lateral_reception 12874
lateral_rush 12874
lateral_return 12874
lateral_recovery 12874
passer_player_id 261104
passer_player_name 261104
receiver_player_id 275655
receiver_player_name 275668
rusher_player_id 312923
rusher_player_name 312924
lateral_receiver_player_id 449277
lateral_receiver_player_name 449277
lateral_rusher_player_id 449354
lateral_rusher_player_name 449354
lateral_sack_player_id 449371
lateral_sack_player_name 449371
interception_player_id 444741
interception_player_name 444741
lateral_interception_player_id 449348
lateral_interception_player_name 449348
punt_returner_player_id 432647
punt_returner_player_name 432652
lateral_punt_returner_player_id 449348
lateral_punt_returner_player_name 449348
kickoff_returner_player_name 435930
kickoff_returner_player_id 435929
lateral_kickoff_returner_player_id 449333
lateral_kickoff_returner_player_name 449333
punter_player_id 425488
punter_player_name 425658
kicker_player_name 402669
kicker_player_id 402645
own_kickoff_recovery_player_id 449277
own_kickoff_recovery_player_name 449277
blocked_player_id 448952
blocked_player_name 448962
tackle_for_loss_1_player_id 425748
tackle_for_loss_1_player_name 425748
tackle_for_loss_2_player_id 449363
tackle_for_loss_2_player_name 449363
qb_hit_1_player_id 425158
qb_hit_1_player_name 425163
qb_hit_2_player_id 448395
qb_hit_2_player_name 448395
forced_fumble_player_1_team 444968
forced_fumble_player_1_player_id 444968
forced_fumble_player_1_player_name 444968
forced_fumble_player_2_team 449338
forced_fumble_player_2_player_id 449338
forced_fumble_player_2_player_name 449339
solo_tackle_1_team 239672
solo_tackle_2_team 446537
solo_tackle_1_player_id 239672
solo_tackle_2_player_id 446537
solo_tackle_1_player_name 239675
solo_tackle_2_player_name 446538
assist_tackle_1_player_id 394952
assist_tackle_1_player_name 394955
assist_tackle_1_team 394952
assist_tackle_2_player_id 394955
assist_tackle_2_player_name 394955
assist_tackle_2_team 394955
assist_tackle_3_player_id 449368
assist_tackle_3_player_name 449368
assist_tackle_3_team 449368
assist_tackle_4_player_id 449371
assist_tackle_4_player_name 449371
assist_tackle_4_team 449371
pass_defense_1_player_id 426633
pass_defense_1_player_name 426633
pass_defense_2_player_id 448642
pass_defense_2_player_name 448642
fumbled_1_team 442816
fumbled_1_player_id 442816
fumbled_1_player_name 442817
fumbled_2_player_id 449333
fumbled_2_player_name 449333
fumbled_2_team 449333
fumble_recovery_1_team 443339
fumble_recovery_1_yards 443345
fumble_recovery_1_player_id 443339
fumble_recovery_1_player_name 443344
fumble_recovery_2_team 449324
fumble_recovery_2_yards 449324
fumble_recovery_2_player_id 449324
fumble_recovery_2_player_name 449325
return_team 418766
return_yards 21
penalty_team 416735
penalty_player_id 416735
penalty_player_name 418164
penalty_yards 416753
replay_or_challenge 0
replay_or_challenge_result 448559
penalty_type 418232
defensive_two_point_attempt 12874
defensive_two_point_conv 12874
defensive_extra_point_attempt 12874
defensive_extra_point_conv 12874- Is this value missing becuase it wasn’t recorded or because it doesn’t exist?
- Is your data MAR (Missing At Random)?
Ignoring the data point
nfl_df.dropna()| play_id | game_id | home_team | away_team | posteam | posteam_type | defteam | side_of_field | yardline_100 | game_date | ... | penalty_player_id | penalty_player_name | penalty_yards | replay_or_challenge | replay_or_challenge_result | penalty_type | defensive_two_point_attempt | defensive_two_point_conv | defensive_extra_point_attempt | defensive_extra_point_conv |
|---|
0 rows × 255 columns
Filling in missing values automatically
Constant,back and forward fill
https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.fillna.html
nfl_df.fillna(0)Mean, Median, Most Frequent, Constant
- Simple Imputer - https://scikit-learn.org/stable/modules/generated/sklearn.impute.SimpleImputer.html
- IterativeImputer : Imputer that estimates values to impute for each feature with missing values from all the others.
- KNNImputer : Imputer that estimates missing features using nearest samples.
import numpy as np
from sklearn.impute import SimpleImputerimp_mean = SimpleImputer(missing_values=np.nan, strategy='mean')
imp_mean.fit([[7, 2, 3], [4, np.nan, 6], [10, 5, 9]])
X = [[np.nan, 2, 3], [4, np.nan, 6], [10, np.nan, 9]]
print(imp_mean.transform(X))[[ 7. 2. 3. ]
[ 4. 3.5 6. ]
[10. 3.5 9. ]]