consider i have a data frame
| ID | Column B |
|---|---|
| 10 | item 1 |
| 10 | item 1 |
| 10 | item 1 |
| 9 | item 2 |
| 8 | item 3 |
| 8 | item 3 |
| 8 | item 3 |
| 8 | item 3 |
| 7 | item 4 |
| 6 | item 5 |
| 4 | item 6 |
| 4 | item 6 |
| 5 | item 7 |
| 5 | item 7 |
and i want to update a new column as result if the id column is in decreasing order i want something like this
| ID | Column B | result |
|---|---|---|
| 10 | item 1 | 1 |
| 10 | item 1 | 1 |
| 10 | item 1 | 1 |
| 9 | item 2 | 1 |
| 8 | item 3 | 1 |
| 8 | item 3 | 1 |
| 8 | item 3 | 1 |
| 8 | item 3 | 1 |
| 7 | item 4 | 1 |
| 6 | item 5 | 1 |
| 4 | item 6 | 2 |
| 4 | item 6 | 2 |
| 5 | item 7 | 2 |
| 5 | item 7 | 2 |
conditions are i should group the rows which are having the id columns with decreasing only by one value
i tried doing using the code df["result"] = (df["X2"] > df["X2"].shift(1)).cumsum()
CodePudding user response：
You can use diff to compare the successive values, if >-1, this means we start a new group, with help of cumsum:
df['result'] = df['ID'].diff().lt(-1).cumsum().add(1)
Output:
ID Column B result
0 10 item 1 1
1 10 item 1 1
2 10 item 1 1
3 9 item 2 1
4 8 item 3 1
5 8 item 3 1
6 8 item 3 1
7 8 item 3 1
8 7 item 4 1
9 6 item 5 1
10 4 item 6 2
11 4 item 6 2
12 5 item 7 2
13 5 item 7 2