Pandas - For Each Index, Put All Columns Into Rows

Question

I'm trying to avoid looping, but the title sort of explains the issue.

import pandas as pd

df = pd.DataFrame(columns=['Index',1,2,3,4,5])
df = df.append({'Index':333,1:'A',2:'C',3:'F',4:'B',5:'D'}, ignore_index=True)
df = df.append({'Index':234,1:'B',2:'D',3:'C',4:'A',5:'Z'}, ignore_index=True)
df.set_index('Index', inplace=True)

print(df)
       1  2  3  4  5
Index               
333    A  C  F  B  D
234    B  D  C  A  Z

I want to preserve the index, and for each column turn it into a row with the corresponding value like this:

       newcol value
Index
333    1      A
333    2      C
333    3      F
333    4      B
333    5      C
234    1      B
234    2      D
234    3      C
234    4      A
234    5      Z

It's somewhat of a transpose issue, but not exactly like that. Any ideas?

Answer 1

You need:

df.stack().reset_index(1, name='value').rename(columns={'level_1':'newcol'}) 
# OR df.reset_index().melt('Index',var_name='new_col',value_name='Value').set_index('Index') 
#(cc: @anky_91)

Output:

       newcol value
Index              
333         1     A
333         2     C
333         3     F
333         4     B
333         5     D
234         1     B
234         2     D
234         3     C
234         4     A
234         5     Z

Answer 2

Another solution using to_frame and rename_axis:

df.stack().to_frame('value').rename_axis(index=['','newcol']).reset_index(1)

 newcol value
333 1   A
333 2   C
333 3   F
333 4   B
333 5   D
234 1   B
234 2   D
234 3   C
234 4   A
234 5   Z