I have data with a lot of NaNs:
train_feat["portarbre"].value_counts(dropna=False)
portarbre
NaN 12958
Libre 6070
Semi-libre 4449
Architecturé 316
Pyramidale 7
Pleureur 4
Name: count, dtype: int64
And a column with no NaNs:
data["adr_secteur"].value_counts(dropna=False)
adr_secteur
6 6958
5 6728
2 6066
3 4249
4 4128
1 3376
38309 180
38151 92
38421 68
38249 41
38158 9
Name: count, dtype: int64
I have other columns also with less NaNs but this one will illustrate the issue. Even though there is a lot of NaNs that the easiest decision would be to just drop the column, filling those 12k of null values can be at least illustrative as an exercise. So I decided to fill the null values with the mode conditioned on the sous_categorie column like the following:
mode_sect_df = train_feat.groupby("portarbre")["adr_secteur"].agg(lambda x: x.mode().max()).reset_index()
mode_sect_df.columns = ["portarbre", "mode_sect"]
for index, row in mode_sect_df.iterrows():
haut_value = row["portarbre"]
mode_sect = row["mode_sect"]
train_feat.loc[train_feat["adr_secteur"] == mode_sect, "portarbre"] = \
train_feat.loc[train_feat["adr_secteur"] == mode_sect,"portarbre"].fillna(value=haut_value)
That is, I am filling the null values in the portarbre column by taking the mode corresponding to the adr_secteur column:
train_feat.pivot_table(index='adr_secteur', columns='portarbre', aggfunc='size')
portarbre Architecturé Libre Pleureur Pyramidale Semi-libre
adr_secteur
1 27.0 385.0 NaN 1.0 842.0
2 2691.0 1094.0 1.0 3.0 917.0
3 20.0 622.0 NaN NaN 655.0
4 61.0 653.0 NaN NaN 534.0
5 79.0 1627.0 3120.0 NaN 358.0
6 17.0 4069.0 1.0 3.0 1079.0
38151 NaN 3.0 NaN NaN 57.0
38158 NaN NaN NaN NaN 7.0
The problem is, after running the code I end with:
train_feat["portarbre"].value_counts(dropna=False)
portarbre
Libre 8453
NaN 4878
Semi-libre 4449
Pleureur 3122
Architecturé 2895
Pyramidale 7
Name: count, dtype: int64
I see two problems here (maybe you will see many others):
1- First is the obvious one: not all null values are being filled. In the first count there were 12958 nulls in the "portarbre" column. In the second, after the imputation, there are 4878. I don't see the error in the code. There is no nulls in the column adr_secteur', the one I am using as support to fill the nulls in the adr_secteur`.
2- The second is that more than one category can have the same mode:
mode_stade_dev = train_feat.groupby("hauteurarbre")["stadededeveloppement"].agg(lambda x: x.mode().max()).reset_index()
mode_stade_dev.columns = ["hauteurarbre", "mode_stade"]
hauteurarbre mode_stade
0 Moins de 10 m Arbre adulte
1 Plus de 20 m Arbre adulte
2 de 10 m à 20 m Arbre adulte
That is, in the first interaction in the for loop, when selecting Arbre adulte to fill the values, it will fill all nan values with Moins de 10 m and then there will be no more null values to be filled for the other modes. How can I improve this? I don't want to just select the mode of the column to fill all the null values in that column. Maybe this will work for that specific column, but I have others in the dataset that this won't be a good idea (for example, the category of a tree and the stage of its development has different modes depending on the category, but some of them will repeat raising this same issue that I am discussing here).
