How to count all positive and negative values in a pandas groupby?
13,976
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]:
0 1
A
bar 2 1
foo 2 3
[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 -1 2
1 1
foo 1 3
-1 2
dtype: int64
In [23]: df.groupby('A').C_sign.value_counts().unstack()
Out[23]:
-1 1
A
bar 2 1
foo 2 3
[2 rows x 2 columns]
Author by
Stanpol
Updated on July 19, 2022Comments
-
Stanpol almost 2 years
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', 'one', 'three'], 'C' : np.random.randn(8), 'D' : np.random.randn(8)})
Output:
A B C D 0 foo one -1.304026 0.237045 1 bar one 0.030488 -0.672931 2 foo two 0.530976 -0.669559 3 bar three -0.004624 -1.604039 4 foo two -0.247809 -1.571291 5 bar two -0.570580 1.454514 6 foo one 1.441081 0.096880 7 foo three 0.296377 1.575791
I want to count how many positive and negative numbers in column C belong to each group in column A and in what proportion. There are much more groups in A than foo and bar, so group names shouldn't be in the code.
I was trying to groupby A and then filter, but didn't find the right way. Also tried to aggregate with some smart lambda, but didn't succeed.
-
DSM over 10 yearsI think I'd prefer
df["C_sign"] = np.sign(df.C)
. -
Andy Hayden over 10 years@DSM agreed, that's much nicer