Rolling subtraction in pandas

Question

I am trying do something like that.

ff = pd.DataFrame({'uid':[1,1,1,20,20,20,4,4,4],
                   'date':['09/06','10/06','11/06',
                           '09/06','10/06','11/06',
                           '09/06','10/06','11/06'],
                   'balance':[150,200,230,12,15,15,700,1000,1500],
                  'difference':[np.NaN,50,30,np.NaN,3,0,np.NaN,300,500]})

I have tried with rolling, but I cannot find the function or the rolling sub-class that subtracts, only sum and var and other stats. Is there a way? I was thinking that I can create two dfs: one - with the first row of every uid eliminated, the second one - with the last row of every uid eliminated. But to be honest, I have no idea how to do that dynamically, for every uid.

Answer 1

Use groupby with diff:

df = pd.DataFrame({'uid':[1,1,1,20,20,20,4,4,4],
                   'date':['09/06','10/06','11/06',
                           '09/06','10/06','11/06',
                           '09/06','10/06','11/06'],
                   'balance':[150,200,230,12,15,15,700,1000,1500]})

df['difference'] = df.groupby('uid')['balance'].diff()

Output:

   uid   date  balance  difference
0    1  09/06      150         NaN
1    1  10/06      200        50.0
2    1  11/06      230        30.0
3   20  09/06       12         NaN
4   20  10/06       15         3.0
5   20  11/06       15         0.0
6    4  09/06      700         NaN
7    4  10/06     1000       300.0
8    4  11/06     1500       500.0