Pandas row sum for values > 0

Question

I have a dataframe of the following format

ROW   Value1    Value2  Value3    Value4
1      10        10      -5        -2
2      50        20      -10       -7
3      10        5        0        -1

I am looking to calculate for each row the sum of positive totals and sum of negative totals. So essentially, the resulting frame should look like

ROW   Post_Total    Neg_Total
1      20            -7
2      70            -17
3      15            -1

One thing I have in my dataset, a column can have only positive or negative values.

Any ideas on how this can be done. I tried subsetting by >0 but was not successful. Thanks!

Quang Hoang · Answer 1 · 2022-02-02T14:41:28.100

Since all columns can either have all positive or all negative, you can use all() to check for condition along the columns, then groupby:

df.groupby(df.gt(0).all(), axis=1).sum()

Output:

      False  True 
ROW              
1       -7     20
2      -17     70
3       -1     15

In general, I'll just subset/clip and sum:

out = pd.DataFrame({'pos': df.clip(lower=0).sum(1),
                    'neg': df.clip(upper=0).sum(1)
                  })

jezrael · Answer 2 · 2022-02-02T15:13:17.540

Use DataFrame.melt, but if performance is important better are another solutions ;):

df1 = (df.melt('ROW')
         .assign(g = lambda x: np.where(x['value'].gt(0),'Pos_Total','Neg_Total'))
         .pivot_table(index='ROW',columns='g', values='value', aggfunc='sum', fill_value=0)
         .reset_index()
         .rename_axis(None, axis=1))
print (df1)
   ROW  Neg_Total  Pos_Total
0    1         -7         20
1    2        -17         70
2    3         -1         15

Numpy alternative with numpy.clip:

a = df.set_index('ROW').to_numpy()
df = pd.DataFrame({'Pos_Total': np.sum(np.clip(a, a_min=0, a_max=None), 1),
                   'Neg_Total': np.sum(np.clip(a, a_min=None, a_max=0), 1)}, 
                   index=df['ROW'])

mozway · Accepted Answer · 2022-02-02T15:21:40.243

3

You could use:

(df.melt(id_vars='ROW')
   .assign(sign=lambda d: np.where(d['value'].gt(0), 'Pos_Total', 'Neg_Total'))
   .groupby(['ROW', 'sign'])['value'].sum()
   .unstack('sign')
)

Or alternatively, using masks.

numpy version (faster):

import numpy as np
a = df.set_index('ROW').values
mask = a > 0
pd.DataFrame({'Pos_Total': np.where(mask, a, 0).sum(1),
              'Neg_Total': np.where(mask, 0, a).sum(1)})

pandas version (slower than numpy but faster than melt):

d = df.set_index('ROW')
mask = d.gt(0)
pd.DataFrame({'Pos_Total': d.where(mask).sum(1),
              'Neg_Total': d.mask(mask).sum(1)},
              index=df['ROW'])

output:

     Pos_Total  Neg_Total
ROW                      
1         20.0       -7.0
2         70.0      -17.0
3         15.0       -1.0

edited Feb 02 '22 at 15:21

answered Feb 02 '22 at 14:32

mozway

194,879
13
39
75

2

I added another option – mozway Feb 02 '22 at 14:39
`np.sum(np.where(mask, a, 0), 1)` ? – jezrael Feb 02 '22 at 14:50
@jezrael thank it's better, I thought `nansum` would be faster but no… NB. I added a post with the [timing of all answers](https://stackoverflow.com/a/70957778/16343464) ;) – mozway Feb 02 '22 at 14:54
I check it and is forget for `ROW` indices in numpy solutions, added `np.clip` solution to my answer. Can you test performance? – jezrael Feb 02 '22 at 15:14
@jezrael yes, fixed it. The clip solution is not faster – mozway Feb 02 '22 at 15:23
Yop, I expected it. – jezrael Feb 02 '22 at 15:23

score 1 · Answer 4 · answered Feb 02 '22 at 14:36

Let us try apply

out = df.set_index('ROW').apply(lambda x : {'Pos':x[x>0].sum(),'Neg':x[x<0].sum()} ,
                                           result_type = 'expand',
                                           axis=1)
Out[33]: 
     Pos  Neg
ROW          
1     20   -7
2     70  -17
3     15   -1

score 0 · Answer 5 · answered Feb 02 '22 at 14:52

Timing of all answer in order or speed. Computed with timeit on 30k rows with unique ROW values.

# @mozway+jezrael (numpy mask v2)
940 µs ± 10.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

# @mozway (numpy mask):
1.29 ms ± 26.5 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

# @Quang Hoang (groupby)
4.68 ms ± 184 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

# @Quang Hoang (clip)
5.2 ms ± 91 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

# @mozway (pandas mask)
10.5 ms ± 612 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

# @mozway (melt+groupby)
36.2 ms ± 1.14 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

# @jezrael (melt+pivot_table)
48.5 ms ± 740 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

# @BENY (apply)
9.05 s ± 76.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

setup:

df = pd.DataFrame({'ROW': [1, 2, 3],
                   'Value1': [10, 50, 10],
                   'Value2': [10, 20, 5],
                   'Value3': [-5, -10, 0],
                   'Value4': [-2, -7, -1]})
df = pd.concat([df]*10000, ignore_index=True)
df['ROW'] = range(len(df))

Pandas row sum for values > 0

5 Answers5