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

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

Wow! Excellent. That was almost too easy :) Thank you! I will accept in 12 minutes :)
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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related