Create df that holds column name and corresponding value of another dataframe

Question

I have created the following dataframe called df

       col1  col2  col3
    0     4     5     2
    1     5     2     4
    2     3    10     3
    3     6     2     2
    4     3     2     4

What I would like now is to flip the rows so that the df looks like this:

         column_name  value
    0        col1      4
    1        col2      5
    2        col3      2
    3        col1      5
    4        col2      2
    5        col3      4
   ...       ...      ...

I think I need to use stack(), but I'm not sure how. I've tried the following

df = df.stack().rename_axis(['column_name']).reset_index(name = 'value')

but that returns the following error

raise ValueError('Length of names must match number of levels in '
ValueError: Length of names must match number of levels in MultiIndex.

Question: how do I stack the values so that I get the desired dataframe?

Related: [pandas convert some columns into rows](https://stackoverflow.com/questions/28654047/pandas-convert-some-columns-into-rows) — jpp, Oct 02 '18 at 14:18

score 6 · Accepted Answer · edited Oct 02 '18 at 14:32

Here it is necessary to remove the first level of the MultiIndex using reset_index with drop=True:

df = (df.stack()
        .reset_index(level=0, drop=True)
        .rename_axis(['column_name'])
        .reset_index(name = 'value'))
print (df)
   column_name  value
0         col1      4
1         col2      5
2         col3      2
3         col1      5
4         col2      2
5         col3      4
6         col1      3
7         col2     10
8         col3      3
9         col1      6
10        col2      2
11        col3      2
12        col1      3
13        col2      2
14        col3      4

Another solution is melt, there are changed order of values:

df = df.melt(var_name='column_name')
print (df)
   column_name  value
0         col1      4
1         col1      5
2         col1      3
3         col1      6
4         col1      3
5         col2      5
6         col2      2
7         col2     10
8         col2      2
9         col2      2
10        col3      2
11        col3      4
12        col3      3
13        col3      2
14        col3      4

Thanks for your reply, but I didn't mean to have two columns called 'column' and 'name'. I just want one column called 'column_name' — titusAdam, Oct 02 '18 at 13:59

score 1 · Answer 2 · answered Oct 02 '18 at 14:44

If the order of rows is unimportant you can use pd.melt directly:

res = pd.melt(df, var_name='column_name')

If you wish to order by input rows, you can use pd.melt with reset_index to elevate the index to a series and then use sort_values:

res = pd.melt(df.reset_index(), id_vars='index', var_name='column_name')\
        .sort_values('index').drop('index', 1).reset_index(drop=True)

print(res)

   column_name  value
0         col1      4
1         col2      5
2         col3      2
3         col1      5
4         col2      2
5         col3      4
6         col1      3
7         col2     10
8         col3      3
9         col1      6
10        col2      2
11        col3      2
12        col1      3
13        col2      2
14        col3      4

Create df that holds column name and corresponding value of another dataframe

2 Answers2