Fillna using groupby and mode not working-CodePudding

I have found several answers to this both here on Stackoverflow and other sites.

However, I keep running into errors I can't resolve.

If I fillna using this, it works fine, but this is just the column mode. It is not grouped.

df['installer'] = df['installer'].fillna(df['installer'].value_counts().idxmax())

If I try grouping with this syntax:

    df['installer'] = df.groupby(['region', 'basin'], sort=False)['installer'].apply(
        lambda x: x.fillna(x.mode()[0]))

I get this error:

KeyError: 0

If I try grouping with this, slightly different syntax:

    df['installer'] = df.groupby(['region', 'basin'], sort=False)['installer'].apply(
        lambda x: x.fillna(x.mode().iloc[0]))

I get this error:

IndexError: single positional indexer is out-of-bounds

Removing sort=False changes nothing.

Extra info:

    df['installer'] = df['installer'].astype(str).str.lower()

    print(df['installer'].isnull().sum(), '\n')  # zero nulls at this point

    df.loc[df['installer'] == '0', 'installer'] = np.nan
    df.loc[df['installer'] == 'nan', 'installer'] = np.nan
    df.loc[df['installer'] == '-', 'installer'] = np.nan

    # df['installer'] = df['installer'].fillna(df['installer'].value_counts().idxmax())
    print(df['installer'].isnull().sum(), '\n')  # 4435 null values here
    print(df3['installer'].value_counts().nlargest(25), '\n')
    df['installer'] = df.groupby(['region', 'basin'], sort=False)['installer'].apply(
        lambda x: x.fillna(x.mode().iloc[0]))
    # df['installer'] = df.groupby(['region', 'district_code', 'lga'])['installer'].fillna(df['installer'].value_counts().idxmax())
    print(df['installer'].isnull().sum(), '\n')
    print(df['installer'].value_counts().nlargest(25), '\n')

CodePudding user response：

Use transform:

vals = df.groupby(['region', 'basin'])['installer'] \
         .transform(lambda x: x.mode(dropna=False).iloc[0])
df['installer'] = df['installer'].fillna(vals)