Transform dataframe column headers to row entries in Python

Question

I have a dataframe like this

import pandas as pd

year = [2005, 2006, 2007]
A = [4, 5, 7]
B = [3, 3, 9]
C = [1, 7, 6]
df_old = pd.DataFrame({'year' : year, 'A' : A, 'B' : B, 'C' : C})

Out[25]: 
   A  B  C  year
0  4  3  1  2005
1  5  3  7  2006
2  7  9  6  2007

I want to transform this to a new dataframe where the column headers ´A´, ´B´ and ´C´ are in the rows. I have this hack, which sorta does the job

df_new = pd.DataFrame({'year' : list(df_old['year']) + list(df_old['year'])\
                       + list(df_old['year']),
                       'col' : ['A']*len(df_old['A']) + ['B']*len(df_old['B'])\
                       + ['C']*len(df_old['C']),
                       'val' : list(df_old['A']) + list(df_old['B'])\
                       + list(df_old['C'])})

Out[27]: 
  col  val  year
0   A    4  2005
1   A    5  2006
2   A    7  2007
3   B    3  2005
4   B    3  2006
5   B    9  2007
6   C    1  2005
7   C    7  2006
8   C    6  2007

Is there a better, more compressed way to do this? Needless to say, this becomes cumbersome when there are a lot of columns.

jezrael · Accepted Answer · 2017-12-13 10:09:03Z

2

Use melt:

print (df_old.melt('year', value_name='val', var_name='col'))
   year col  val
0  2005   A    4
1  2006   A    5
2  2007   A    7
3  2005   B    3
4  2006   B    3
5  2007   B    9
6  2005   C    1
7  2006   C    7
8  2007   C    6

and for reorder columns reindex:

df=df_old.melt('year',value_name='val', var_name='col').reindex(columns=['col','val','year'])
print (df)
  col  val  year
0   A    4  2005
1   A    5  2006
2   A    7  2007
3   B    3  2005
4   B    3  2006
5   B    9  2007
6   C    1  2005
7   C    7  2006
8   C    6  2007

edited Dec 13, 2017 at 10:09

answered Dec 13, 2017 at 10:04

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mortysporty Over a year ago

Wow! Excellent. That was almost too easy :) Thank you! I will accept in 12 minutes :)

jezrael Over a year ago

Is important ordering of columns?

mortysporty Over a year ago

Not really. But feel free to append your answer to show how I could rearrange the columns.

Collectives™ on Stack Overflow

Transform dataframe column headers to row entries in Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related