How to group the data by identifier and find the difference of the adjacent data?


I have the following data

id    starting_point      ending_point
A        2525                 6565
B        5656                 8989
A        1234                 5656
A        4562                 6245
B        6496                 9999
B        1122                 2211

For the above data :

df['id'] = ['A','B','A','A','B', 'B']
df['starting_point'] =['2525','5656','1234','4562','6496','1122']
df['ending_point'] = ['6565','8989','5656','6245','9999','9999']

I would like to write a python code group them by their id (ie, A,B,...) and find the difference between sum of first and second ending point of A and sum of second and third ending point of A. In this case [(6565+5656) - (5656+6245)].

You can use groupby with custom function where select by iloc and get difference of sums:

df = df.groupby('id')['ending_point'] \
       .apply(lambda x: x.iloc[:2].sum() - x.iloc[1:4].sum()).reset_index()
print (df)
  id  ending_point
0  A           320
1  B          6778