Selectively converting columns to rows

Asked Jun 19 '19 at 14:51

Active Jun 19 '19 at 15:02

Viewed 34 times

I am trying to transpose the following data in such a way that time is left as the index column and col A to col D are returned as rows in col B while the values under col A to col D become their own column

To give you a visual example. This is an example of what I currently have

time colA colB colC colD colE colF colG
12     1    4   5     9    4   3    3
13     2    7   6     8    3   4    5

This is my desired output:

time  cols   vals
 12   colA    1
 12   colB    4
 12   colC    5
 12   colD    9 
 12   colE    4
 12   colF    3
 12   colG    3 
 13   colA    2
 13   colB    7
 13   colC    6
 13   colD    8
  etc.....

What I have tried. I tried literally transposing the data frame, but obviously changes columns to rows, which is only half correct

df = df.transpose()

I gave it a bit more thought and currently researching using the pivot table function for this, but I am not sure if this even makes sense

I don't think this is a duplicate of pandas convert some columns into rows

Trying this

df.melt(id_vars='time_period',var_name='cols',value_name='value')

Returns the time as one column, the second column as all the unique values from the second column

edited Jun 19 '19 at 15:02

asked Jun 19 '19 at 14:51

Emm

2,367
3
24
50

This is either `stack` or `melt`, or `wide_to_long`. – Quang Hoang Jun 19 '19 at 14:52
1

`df.melt('time',var_name='cols')` – anky Jun 19 '19 at 14:52
@anky_91 that doesn't seem to produce the desired output – Emm Jun 19 '19 at 15:03
@Emm do you mean the order? value wise it does according to the question: `df.set_index('time').stack().reset_index(name='vals').rename(columns={'level_1':'cols'})` – anky Jun 19 '19 at 15:05
After melting, you can use `ddf.sort_values('time').reset_index(drop=True)` to sort on `time` column. – Valentino Jun 19 '19 at 17:16

Selectively converting columns to rows

0 Answers0