How to transpose half of the dataframe using pandas

Question

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.

still not fixed; the days in the source df have five elements — sammywemmy, Oct 10 '21 at 18:31
your second example dataframe is not a valid dataframe either. — Chris, Oct 10 '21 at 18:35
hey @sammywemmy I have changed my example. Can you please look at it? — coder_bg, Oct 10 '21 at 19:00

score 3 · Accepted Answer · answered Oct 10 '21 at 19:04

3

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

answered Oct 10 '21 at 19:04

Corralien

70,617
7
16
36

Thank you! this works! Have upvoted your answer. – coder_bg Oct 11 '21 at 04:27

How to transpose half of the dataframe using pandas

1 Answers1