Pandas and data imputation from a multiindex dataframe

Question

I have the following dataframe:

df = pd.DataFrame([[np.nan, 2, 20, 4],
                   [3, 1, np.nan, 1],
                   [3, 1, 15, 1],
                   [np.nan, 1, np.nan, 1],
                   [10, 1, 30, 4],
                   [50, 2, 35, 4],
                   [10, 1, 37, 4],
                   [40, 2, 30, 1]],
                  columns=list("ABCD"))

I want to fill the Nan values with their group means. Towards that purpose, I run the following:

df_mean=df.groupby(["B","D"]).mean()
df_mean

        A     C
B   D       
1   1   3.0   15.0
    4   10.0  33.5
2   1   40.0  30.0
    4   50.0  27.5

Is there a way to fill the dataframe df with the values computed in df_mean?

Answer 1

This is more of a programming question than a data science question and would therefore be better suited for stackoverflow stackexchange, but the following code should do what you're looking for:

df[["A", "C"]] = (
    df
    # create groups
    .groupby(["B", "D"])
    # transform the groups by filling na values with the group mean
    .transform(lambda x: x.fillna(x.mean()))
)

A	B	C	D
50	2	20	4
3	1	15	1
3	1	15	1
3	1	15	1
10	1	30	4
50	2	35	4
10	1	37	4
40	2	30	1