With the dataframe below I want to compare the values in Val1 with the value in the preceding row by group. I then want to create a new column containing the word "Abv" if the value is greater than the preceding row and "Blw" if the value is lower than the predecing row. If the values are equal the new column should be blank.
Here’s an extract:
| Ref1 | Val1 |
|---|---|
| A | 1 |
| A | 2 |
| A | 3 |
| A | 4 |
| B | 1 |
| B | 1 |
| B | 2 |
| B | 0 |
Desired result:
| Ref1 | Val1 | AbvBlw |
|---|---|---|
| A | 1 | |
| A | 2 | Abv |
| A | 3 | Abv |
| A | 4 | Abv |
| B | 1 | |
| B | 1 | |
| B | 2 | Abv |
| B | 0 | Blw |
>Solution :
Code
get groupby diff & chk condition
boolean masking with np.select
import numpy as np
s = df.groupby(['Ref1'])['Val1'].diff()
df['AbwBlw'] = np.select([s > 0, s == 0, s < 0], ['Abw', None, 'Blw'], None)
df
Ref1 Val1 AbwBlw
0 A 1 None
1 A 2 Abw
2 A 3 Abw
3 A 4 Abw
4 B 1 None
5 B 1 None
6 B 2 Abw
7 B 0 Blw