Pandas Groupby with Categorical Columns returns NaN

user_1780424 0
Viewed 868

There are no NaN in the pandas dataframe, and when I look at each group of the groupby, they only have the Int64Index, and none of the rest of the non-groupby columns. I am baffled.

What am I missing?

Here is a reproducible code:

df = pd.DataFrame({
    "a": np.random.rand(1000),
    "b": np.random.rand(1000),
    "c": np.random.rand(1000)
})

ranges = np.linspace(0, 1, 100)
df["a_bin"] = pd.cut(df.a, ranges)
df["b_bin"] = pd.cut(df.b, ranges)

print(df.groupby(["a_bin", "b_bin"]).c.mean())

and here is the result:

a_bin          b_bin           
(0.0, 0.0101]  (0.0, 0.0101]      NaN
               (0.0101, 0.0202]   NaN
               (0.0202, 0.0303]   NaN
               (0.0303, 0.0404]   NaN
               (0.0404, 0.0505]   NaN
                                   ..
(0.99, 1.0]    (0.949, 0.96]      NaN
               (0.96, 0.97]       NaN
               (0.97, 0.98]       NaN
               (0.98, 0.99]       NaN
               (0.99, 1.0]        NaN
Name: c, Length: 9801, dtype: float64

My pandas version is: 1.0.1

pandas-groupby pandas python
2 Answers

There are all possible combinations of categories, unused categories create missing values, check this.

So if need remove mising values:

print(df.groupby(["a_bin", "b_bin"]).c.mean().dropna())
a_bin          b_bin           
(0.0, 0.0101]  (0.0, 0.0101]       0.381681
               (0.0505, 0.0606]    0.148762
               (0.0909, 0.101]     0.313093
               (0.101, 0.111]      0.488104
               (0.313, 0.323]      0.518599

(0.99, 1.0]    (0.505, 0.515]      0.149027
               (0.576, 0.586]      0.099652
               (0.778, 0.788]      0.220360
               (0.828, 0.838]      0.166424
               (0.97, 0.98]        0.516558
Name: c, Length: 948, dtype: float64
user_29010020

As the previous answer suggested, you can simply use dropna() on the result. However, if you have many unused categories, the intermediate computation will have a huge number of nan values (especially when using multiple indices) which may ruin your performance.

Instead, in your call to groupby you should set the argument "observed" to True which will prevent the nan values from being generated.

print(df.groupby(["a_bin", "b_bin"], observed = True).c.mean())
user_148264250
Related