Home > Mobile >  Pandas Dataframe: substract and divided two rows value based on column condition
Pandas Dataframe: substract and divided two rows value based on column condition

Time:08-26

I have DataFrame:

A B C D
1 0.1 0.2 0.3
2 0.4 0.7 0.2
3 0 4.25 100
3 -2.5 4.20 70
3 -2.5 3.5 80
4 0 3.6 81
4 -5 3.5 77
4 -5 3.4 75
4 -5 3.1 74
5 0 3.2 75
5 0.1 3.3 73

Now , i want to skip first two rows. after that i want to substarct last row value and first row value for number '3' with coumn 'C' value then divide with same substactction but with coumn 'B' Value. basically, |3.5-4.25|/|-2.5-0| = | 0.3 |

i tried and skiped first two rows with

cols = ['A']
df[cols] = df[df[cols] > 2][cols]
df = df.dropna()

Expected output:

NEW_COL Result
1 0.3
2 0.1
3 1

could you please help me?

CodePudding user response:

IIUC, you can compute the last-first per group, then divide C/B and drop NAs:

out = (df.groupby('A')
         .agg(lambda g: abs(g.iloc[-1]-g.iloc[0]))
         .eval('C/B')
         .dropna()
         .reset_index(drop=True)
      )

output:

0    0.3
1    0.1
2    1.0
dtype: float64
  • Related