Python - Sum and count specific values with pandas pivot_table

Question

I have a pandas dataframe like

ACCOUNT AMOUNT STATUS 
1         -2      1
2         2       0
2         -1      0
1         2       1 
1         2       1

This is would like to get converted into an dataframe like

ACCOUNT  STATUS COUNT>0 COUNT<0 AMOUNT>0 AMOUNT<0 
1          1      2        1        4         2
2          0      1        1        2         1

So basically split if AMOUNT is > or < than 0 and then count and sum the result. I currently have the following, but can't get the split AMOUNT right.

Data = pd.pivot_table(trans, values =['Status', 'AMOUNT'], index = ['ACCOUNT'], aggfunc = {'Status':np.mean, 'AMOUNT': [np.sum, 'count'] } )

piRSquared · Accepted Answer · 2018-03-07 15:13:12Z

3

Using np.sign
This function returns an array of -1/0/1 depending on the signs of the values. Essentially giving me a convenient way of identifying things less, equal, or greater than zero. I use this in the group by statement and use agg to count the number of values, and sum to produce the total. After grouping by 3 vectors, I'll end up with a 3-layer multi index. I unstack in order to take the last layer and pivot it to be included with the columns. This last layer is the sign layer.

df.groupby(
    ['ACCOUNT', 'STATUS', np.sign(df.AMOUNT)]
).AMOUNT.agg(['count', 'sum']).unstack()

               count    sum   
AMOUNT            -1  1  -1  1
ACCOUNT STATUS                
1       1          1  2  -2  4
2       0          1  1  -1  2

Extra effort to mimic OP's expected output:
Here, I do the same things. But I add several steps that rename columns, combine layers, and take absolute values.

df.groupby(
    ['ACCOUNT', 'STATUS', np.sign(df.AMOUNT).map({-1: '<0', 0: '=0', 1: '>0'})]
).AMOUNT.agg(['count', 'sum']).rename(
    columns=dict(count='COUNT', sum='AMOUNT')
).unstack().abs().pipe(
    lambda d: d.set_axis(d.columns.map('{0[0]}{0[1]}'.format), 1, inplace=False)
)

                COUNT<0  COUNT>0  AMOUNT<0  AMOUNT>0
ACCOUNT STATUS                                      
1       1             1        2         2         4
2       0             1        1         1         2

edited Mar 7, 2018 at 15:13

answered Mar 7, 2018 at 15:03

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jezrael Over a year ago

Maybe you can explain solution more ;)

piRSquared Over a year ago

Just got done with getting what works! Now I can work on explanation (-:

BENY · Accepted Answer · 2018-03-07 15:08:32Z

1

This is try to fix your pivot_table

pd.pivot_table(df.assign(new=df.AMOUNT.gt(0)), values =['AMOUNT'], index = ['ACCOUNT','STATUS'],columns='new',aggfunc = { 'AMOUNT': [np.sum, 'count'] } ).abs()
Out[431]: 
               AMOUNT                  
                count         sum      
new             False True  False True 
ACCOUNT STATUS                         
1       1           1     2     2     4
2       0           1     1     1     2

answered Mar 7, 2018 at 15:08

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Ken Syme · Accepted Answer · 2018-03-07 15:07:28Z

You can do this better with groupby and unstack. I have also created a few extra columns to make things clearer.

data = pd.DataFrame(
    [[1, -2, 1],
     [2, 2, 0],
     [2, -1, 0],
     [1,  2, 1],
     [1,  2, 1] 
    ],
    columns = ['ACCOUNT', 'AMOUNT', 'STATUS']
)

data['AMOUNT_POSITIVE'] = data['AMOUNT'] > 0
data['AMOUNT_ABSOLUTE'] = data['AMOUNT'].abs()

result = (data
          .groupby(["ACCOUNT", "STATUS", "AMOUNT_POSITIVE"])['AMOUNT_ABSOLUTE']
          .agg(['count', 'sum'])
          .unstack("AMOUNT_POSITIVE")
         )

print(result)

And you get your table:

                count         sum      
AMOUNT_POSITIVE False True  False True 
ACCOUNT STATUS                         
1       1           1     2     2     4
2       0           1     1     1     2

Ilias N · Accepted Answer · 2022-11-30 14:56:23Z

-1

The next example aggregates by taking the mean across multiple columns.

table = pd.pivot_table(df, values=['D', 'E'], index=['A', 'C'],
                    aggfunc={'D': np.mean,
                             'E': np.mean})
table
                D         E
A   C
bar large  5.500000  7.500000
    small  5.500000  8.500000
foo large  2.000000  4.500000
    small  2.333333  4.333333

"""P.S this is from official documentation"""

edited Nov 30, 2022 at 14:56

answered Nov 24, 2022 at 4:43

Ilias N

11 bronze badge

1 Comment

AlexK Over a year ago

Please see how to answer. Your example does not use the data in the question and does not present input data, so it's impossible to understand how this solves the problem in the question.

Collectives™ on Stack Overflow

Python - Sum and count specific values with pandas pivot_table

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related