Titanic Missing Data Imputation Comparison
- This is a reproduction of a Stack Overflow answer I provided.
- You will see that the two fill methods, groupby fillna with mean and random forest regressor, are within a couple of 1/100’s of a year of each other
- See Comparison of groupby and RandomForestRegressor for the statistical comparison.
Fill nan values with the mean
- Use
.groupby,.apply, andfillnawith.mean. - The following code fills
nanswith the mean for each group,pclassandsex, for the entire dataset. - Titanic Age Analysis
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import pandas as pd
import seaborn as sns
# load dataset
df = sns.load_dataset('titanic')
# map sex to a numeric type
df.sex = df.sex.map({'male': 1, 'female': 0})
# Populate Age_Fill
df['Age_Fill'] = df['age'].groupby([df['pclass'], df['sex']]).apply(lambda x: x.fillna(x.mean()))
# series with filled ages
groupby_result = df.Age_Fill[df.age.isnull()]
# display(df[df.age.isnull()].head())
survived pclass sex age sibsp parch fare embarked class who adult_male deck embark_town alive alone Age_Fill
0 3 male NaN 0 0 8.4583 Q Third man True NaN Queenstown no True 26.50759
1 2 male NaN 0 0 13.0000 S Second man True NaN Southampton yes True 30.74071
1 3 female NaN 0 0 7.2250 C Third woman False NaN Cherbourg yes True 21.75000
0 3 male NaN 0 0 7.2250 C Third man True NaN Cherbourg no True 26.50759
1 3 female NaN 0 0 7.8792 Q Third woman False NaN Queenstown yes True 21.75000
Impute nan values with RandomForestRegressor
sklearn.ensemble.RandomForestRegressor- Kaggle: Titanic
- Age seems to be promising feature. So it doesn’t make sense to simply fill null values out with median/mean/mode.
- Based on the results here, I don’t think it makes much difference
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
from sklearn.ensemble import RandomForestRegressor
import pandas as pd
import seaborn as sns
# load dataset
df = sns.load_dataset('titanic')
# map sex to a numeric type
df.sex = df.sex.map({'male': 1, 'female': 0})
# split data
train = df.loc[(df.age.notnull())] # known age values
test = df.loc[(df.age.isnull())] # all nan age values
# select age column
y = train.values[:, 3]
# select pclass and sex
X = train.values[:, [1, 2]]
# create RandomForestRegressor model
rfr = RandomForestRegressor(n_estimators=2000, n_jobs=-1)
# Fit a model
rfr.fit(X, y)
# Use the fitted model to predict the missing values
predictedAges = rfr.predict(test.values[:, [1, 2]])
# create predicted age column
df['pred_age'] = df.age
# fill column
df.loc[(df.pred_age.isnull()), 'pred_age'] = predictedAges
# display(df[df.age.isnull()].head())
survived pclass sex age sibsp parch fare embarked class who adult_male deck embark_town alive alone pred_age
0 3 1 NaN 0 0 8.4583 Q Third man True NaN Queenstown no True 26.49935
1 2 1 NaN 0 0 13.0000 S Second man True NaN Southampton yes True 30.73126
1 3 0 NaN 0 0 7.2250 C Third woman False NaN Cherbourg yes True 21.76513
0 3 1 NaN 0 0 7.2250 C Third man True NaN Cherbourg no True 26.49935
1 3 0 NaN 0 0 7.8792 Q Third woman False NaN Queenstown yes True 21.76513
Comparison of groupby and RandomForestRegressor
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
print(predictedAges - groupby_result).describe())
count 177.00000
mean 0.00362
std 0.01877
min -0.04167
25% 0.01121
50% 0.01121
75% 0.01131
max 0.02969
Name: Age_Fill, dtype: float64
# comparison dataframe
comp = pd.DataFrame({'rfr': predictedAges.tolist(), 'gb': groupby_result.tolist()})
comp['diff'] = comp.rfr - comp.gb
# display(comp)
rfr gb diff
26.51880 26.50759 0.01121
30.69903 30.74071 -0.04167
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
34.63090 34.61176 0.01913
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
34.63090 34.61176 0.01913
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
30.69903 30.74071 -0.04167
41.24592 41.28139 -0.03547
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
21.76131 21.75000 0.01131
21.76131 21.75000 0.01131
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
34.63090 34.61176 0.01913
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
41.24592 41.28139 -0.03547
21.76131 21.75000 0.01131
30.69903 30.74071 -0.04167
41.24592 41.28139 -0.03547
41.24592 41.28139 -0.03547
41.24592 41.28139 -0.03547
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
28.75266 28.72297 0.02969
26.51880 26.50759 0.01121
34.63090 34.61176 0.01913
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
34.63090 34.61176 0.01913
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
21.76131 21.75000 0.01131
34.63090 34.61176 0.01913
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
30.69903 30.74071 -0.04167
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
34.63090 34.61176 0.01913
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
30.69903 30.74071 -0.04167
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
30.69903 30.74071 -0.04167
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
30.69903 30.74071 -0.04167
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
28.75266 28.72297 0.02969
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
34.63090 34.61176 0.01913
30.69903 30.74071 -0.04167
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
30.69903 30.74071 -0.04167
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
41.24592 41.28139 -0.03547
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
41.24592 41.28139 -0.03547
26.51880 26.50759 0.01121
34.63090 34.61176 0.01913
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
26.51880 26.50759 0.01121
26.51880 26.50759 0.01121
21.76131 21.75000 0.01131
Calculate means on a random training set
- This example calculates the mean of a random training set, an then fills the
nanvalues in the training set and the test set - Using
pandas.DataFrame.fillna, which will fill missing values in a dataframe column, from another dataframe, when both dataframes have a matching index, and the fill column is same.- Pclass/Sex and not based on indices,
pclassandsexare set as the indices, which is how.fillnaworks.
- Pclass/Sex and not based on indices,
- In this example,
trainis 67% of the data, andtestis 33% of the data.test_sizeandtrain_sizecan be set as needed, as persklearn.model_selection.train_test_split
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
import pandas as pd
import seaborn as sns
from sklearn.model_selection import train_test_split
# load dataset
df = sns.load_dataset('titanic')
# map sex to a numeric type
df.sex = df.sex.map({'male': 1, 'female': 0})
# randomly split the dataframe into a train and test set
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.33, random_state=42)
# select columns for X and y
X = df[['pclass', 'sex']]
y = df['age']
# create a dataframe of train (X, y) and test (X, y)
train = pd.concat([X_train, y_train], axis=1).reset_index(drop=True)
test = pd.concat([X_test, y_test], axis=1).reset_index(drop=True)
# calculate means for train
train_means = train.groupby(['pclass', 'sex']).agg({'age': 'mean'})
# display train_means, a multi-index dataframe
age
pclass sex
1 0 34.66667
1 41.38710
2 0 27.90217
1 30.50000
3 0 21.56338
1 26.87163
# fill nan values in train
train = train.set_index(['pclass', 'sex']).age.fillna(train_means.age).reset_index()
# fill nan values in test
test = test.set_index(['pclass', 'sex']).age.fillna(train_means.age).reset_index()
This post is licensed under CC BY 4.0 by the author.