'Pandas row sum for values > 0

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!

pandasrowsum


Solution 1:[1]

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)
                  })

Solution 2:[2]

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'])

Solution 3:[3]

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

Solution 4:[4]

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))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 BENY
Solution 4 mozway

Related Questions