I hope you can help me. I would like to create a new dataframe with the stay date agregation (count), also I wanted to transpose the rate codes column which would be duplicate given that they would be breaken down by reservation status
| stay | status | ratec |
|---|---|---|
| 01/01 | cancel | BA1 |
| 02/01 | active | AR2 |
| 03/01 | active | P12 |
| 03/01 | cancel | P12 |
| 01/01 | active | AR2 |
| 02/01 | active | AR2 |
I would like to transform the table like this (count of cancelled, no show and active separated): | stay | BA1ACT | AR2ACT|P12ACT| BA1 CA | P12 CA| |:---- |:------:| -----:|:---- |:------:| -----:| | 01/01| 1 | 1 | 0 | 0 | 0 | | 02/01| 0 | 2 | 0 | 0 | 0 | | 03/01| 0 | 0 | 1 | 0 | 1 |
I tried this df = df.groupby(["stay","ratec"])['status'].aggregate(lambda x: x.nunique()).unstack()
but did not work..
Would you be able to help me? Thanks in advance!