My df:
| name | date | value |
|---|---|---|
| A | 2009-06-30 | some value 1 |
| A | 2010-06-30 | some value 2 |
| A | 2011-06-30 | some value 3 |
| B | 2019-12-31 | some value 4 |
| B | 2020-12-31 | some value 5 |
| B | 2021-12-31 | some value 6 |
I want
| name | date | value | position |
|---|---|---|---|
| A | 2009-06-30 | some value 1 | 2 |
| A | 2010-06-30 | some value 2 | 1 |
| A | 2011-06-30 | some value 3 | 0 |
| B | 2019-12-31 | some value 4 | 2 |
| B | 2020-12-31 | some value 5 | 1 |
| B | 2021-12-31 | some value 6 | 0 |
The final view will look like this (maybe pivot)
| name | 0 | 1 | 2 |
|---|---|---|---|
| A | some value 3 | some value 2 | some value 1 |
| B | some value 6 | some value 5 | some value 4 |
This is a serious problem for me. Please help)
CodePudding user response：
You can try a decending cumcount:
df['position'] = df.groupby('name').cumcount(ascending=False)
Then pivot:
df.pivot('name', 'position', 'value')