I have a dataframe
cycle_end_date | mid_cycle | Total | 0 | 1 | 2 | 3 |
---|---|---|---|---|---|---|
15th Sept | 230 | 600 | 100 | 200 | 50 | 10 |
I want to transpose this dataframe such as:
cycle_end_date | mid_cycle | Total | Delay | new_col |
---|---|---|---|---|
15th Sept | 230 | 600 | 0 | 100 |
15th Sept | 230 | 600 | 1 | 200 |
15th Sept | 230 | 600 | 2 | 50 |
15th Sept | 230 | 600 | 3 | 10 |
Basically, the columns day0 , day1, day2 will become rows which will come under mid_cycle
and total_amt, pending_amt
will get distributed accordingly.
How do we achieve this? .transpose()
is not giving me the desired results.
Use melt
:
out = df.melt(id_vars=['cycle_end_date', 'mid_cycle', 'Total'],
var_name='Delay', value_name='new_col')
print(out)
# Output
cycle_end_date mid_cycle Total Delay new_col
0 15th Sept 230 600 0 100
1 15th Sept 230 600 1 200
2 15th Sept 230 600 2 50
3 15th Sept 230 600 3 10