[FIXED] Subtract and add values based on specific condition, considering baseline values

Issue

I have a dataframe, where I would like to transform based on grouping ids, count and sum.

pod date1   pwr1    pwr2    position
aa  q122    2        2      100
aa  q122    0        4      100
bb  q122    5        0      50
bb  q122    5        0      50
bb  q222    0        0      50
bb  q322    0        5      50
bb  q322    0        5      50

Desired

pod date    con retro   final   re_space
aa  q122    2   6       -4      101
bb  q122    10  0       10      48
bb  q222    0   0       0       48
bb  q322    0   10     -10      50

Doing

def f(x):
    d = {'con': [x['pwr1'].sum()],
         'retro': [x['pwr2'].sum()],
         'final': [x['pwr1'].sum() - x['pwr2'].sum()],
         're_space': [x['pwr2'].count() - x['pwr1'].count()]}  # <<< HERE
    return pd.DataFrame(d)


out = df.groupby(['id', 'date', 'positions']) \
        .apply(f).reset_index().drop(columns='level_3')



# Compute re_space with cumsum

out['re_space'] = out['positions'].astype(float) + out.groupby('id')['re_space'].cumsum()

This works ok, except it is still counting the values and subtracting or adding from the re_space despite a 0 or null value.

Any suggestion is appreciated. I am still troubleshooting

Solution

Try (x['pwr2'] !=0).values.sum() instead of x['pwr2'].count()

Corrected code:

def f(x):
    d = {'con': [x['pwr1'].sum()],
         'retro': [x['pwr2'].sum()],
         'final': [x['pwr1'].sum() - x['pwr2'].sum()],
         're_space': [(x['pwr2'] !=0).values.sum() - (x['pwr1']!=0).values.sum()]}  # <<< HERE
    return pd.DataFrame(d)

Outputs:

  pod date1  position  con  retro  final  re_space
0  aa  q122       100    2      6     -4     101.0
1  bb  q122        50   10      0     10      48.0
2  bb  q222        50    0      0      0      48.0
3  bb  q322        50    0     10    -10      50.0

Answered By - sharathnatraj