group id's according to their respective value in pandas panel

Question

In my panda panel I have two columns, 'id' and 'amount'. There are multiple transactions for the same id too. There can be positive and negative values in the 'amount'-column. Now, I want to group all id's where the amount is negative and count them. How can I achieve this?

Answer 1

Create boolean mask by compare by lt (<) and aggregate sum - True values are processes like 1s:

df = pd.DataFrame({
    'id': ['a','a','a','a','b','b','b','c','c'],
    'amount': [-4,-5,6,7,1,-2,3,4,8]
})
print (df)
  id  amount
0  a      -4
1  a      -5
2  a       6
3  a       7
4  b       1
5  b      -2
6  b       3
7  c       4
8  c       8

df1 = df['amount'].lt(0).groupby(df['id']).sum().astype(int).reset_index(name='count')

Or create index by id column and use sum only by level=0 (index):

df1 = df.set_index('id')['amount'].lt(0).sum(level=0).astype(int).reset_index(name='count')

---

print (df1)
  id  count
0  a      2
1  b      1
2  c      0