How To Count Positive And Negative Numbers Of A Column After Applying Groupby In Pandas

have the following dataframe: token name ltp change 0 12345.0 abc 2.0 NaN 1 12345.0 abc 5.0 1.500000 2 12345.0 abc 3.0 -0.400000 3 12345.0 abc

Solution 1:

Use:

g = df.groupby('name')['change']
counts = g.agg(
    pos_count=lambda s: s.gt(0).sum(),
    neg_count=lambda s: s.lt(0).sum(),
    net_count=lambda s: s.gt(0).sum()- s.lt(0).sum()).astype(int)

Result:

# print(counts)     pos_count  neg_count  net_count
name                                 
abc           3          2          1
xyz           3          2          1

Solution 2:

You can create a new column in df with the sign of change and group by name and sign:

import pandas as pd
import numpy as np
df['change_sign'] = np.sign(df['change'])
df.groupby(['name','change_sign']).count()

You can then pivot if you need the result in columns instead of rows

Solution 3:

Use np.sign with Series.map for new column added by DataFrame.assign and then count values by SeriesGroupBy.value_counts:

count=(df.assign(type=np.sign(df['change'])
                      .map({1:'pos_count', -1:'neg_count'}))
        .groupby(df['name'])['type']
        .value_counts()
        .reset_index(name='count'))
print (count)
  name       type  count
0  abc  pos_count      3
1  abc  neg_count      2
2  xyz  pos_count      3
3  xyz  neg_count      2