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).