Get a new dataframe with difference of every two rows in Pandas

Question

I have a dataframe in Pandas like the above:

    A   B   C
0   1  10  43
1   2  12  34
2   1   9  57
3   2   7  47
4   1   6  30
5   2  10  31

What I would like to do is to calculate the differences of every two rows according to column A (essentially get the differences of all the other columns when A=1 - A=2). So, I would like to come up with something like this:

    B   C
0  -2   9
1   2   10
2  -4  -1

I know about the diff() function but it doesn't seem to do the thing I want. Is there a way?

Lousy

Answer 1

You can take the floor division of the index by 2 and use the result as a grouper, then take the first differences of the groups using DataFrame.diff():

df.groupby(df.index//2)['B','C'].diff(-1).dropna().reset_index(drop=True)

    B     C
0 -2.0   9.0
1  2.0  10.0
2 -4.0  -1.0

Dental

Answer 2

You can index by A and subtract:

x = df[df['A'] == 1].reset_index(drop=True).drop('A', axis=1)
y = df[df['A'] == 2].reset_index(drop=True).drop('A', axis=1)

x - y

Joyjoya