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?
>Solution :
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