How To Count All Positive And Negative Values In A Pandas Groupby?

Let's assume we have a table: df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'], 'B' : ['one', 'one', 'two', 'three', 'two', 'two

Solution 1:

You could do this as a one line apply (the first column being negative, the second positive):

In [11]: df.groupby('A').C.apply(lambda x: pd.Series([(x < 0).sum(), (x >= 0).sum()])).unstack()
Out[111]: 
     01
A        
bar  21
foo  23

[2 rows x 2 columns]

However, I think a neater way is to use a dummy column and use value_counts:

In [21]: df['C_sign'] = np.sign(df.C)

In [22]: df.groupby('A').C_sign.value_counts()
Out[22]: 
A      
bar  -1211
foo   13
     -12
dtype: int64

In [23]: df.groupby('A').C_sign.value_counts().unstack()
Out[23]: 
     -11
A          
bar   21
foo   23

[2 rows x 2 columns]