pandas groupby.agg() taking the mode of categorical variables where NaN is the only variable for a group

user_1577110 0
Viewed 1377

I want to find the most common value for each group. UPDATE: If there are real values and NaNs, I want to drop the NaNs. I only want NaN, when that is all the values.

Some of my groups have all their data missing. And I would like the result in these cases to be missing data (NaN) as the most common value.

In these cases the DataFrame.groupby.agg(pd.Series.mode) function returns an empty categorical. What I want is NaN.

A toy example follows ...

data = """
Group, Value
A,      1
A,      1
A,      1
B,      2 
C,      3   
C, 
C, 
D,
D,
"""

from io import StringIO
df = (
    pd.read_csv(StringIO(data),
                skipinitialspace=True)
    .astype('category')
)

df.groupby('Group')['Value'].agg(pd.Series.mode)

Which yields ...

A                                             1.0
B                                             2.0
C                                             3.0
D    [], Categories (3, float64): [1.0, 2.0, 3.0]
Name: Value, dtype: object

My question: is there a way to get NAN, or to detect the empty categorical and make that a NaN. UPDATED: Noting, that I cannot use dropna=False, as that would give me an incorrect answer for C above.

By way of context, my original DataFrame has 27 million rows, and my grouped frame has 6 million rows. So, I want to avoid slow solutions.

pandas dataframe group-by python
2 Answers

You can apply pd.Series.mode and then pd.to_numeric with errors="coerce":

x = df.groupby("Group")["Value"].agg(pd.Series.mode)
print(pd.to_numeric(x, errors="coerce"))

Prints:

Group
A    1.0
B    2.0
C    3.0
D    NaN
Name: Value, dtype: float64
user_100359850

You can use a custom aggregation and check if isna().all():

df.groupby('Group')['Value'].agg(lambda x: x.mode() if not x.isna().all() else np.nan)

# Group
# A    1.0
# B    2.0
# C    3.0
# D    NaN
# Name: Value, dtype: float64

Out of curiosity, timed with df = pd.concat([df] * 100000) (900,000 rows):

>>> def coerce(df):
...    x = df.groupby("Group")["Value"].agg(pd.Series.mode)
...    return pd.to_numeric(x, errors="coerce")
>>> %timeit coerce(df)
22.1 ms ± 2.79 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

>>> def isna(df):
...    return df.groupby('Group')['Value'].agg(lambda x: x.mode() if not x.isna().all() else np.nan)
>>> %timeit isna(df)
20.9 ms ± 732 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
user_131383640
Related