How to transpose dataframe columns into rows in pandas

Question

I have below dataframe and want to transpose the columns aftr 3rd column into rows. Please help on this.

df:
country year    perc    data1   data2   data3
IN      2015    hjk     75      81      96
US      2015    KTM     100     289     632

Results:                
country year    perc    TransposedColumn    Value   
IN      2015    hjk     data1               75  
IN      2015    hjk     data2               81  
IN      2015    hjk     data3               96  
US      2015    KTM     data1               100 
US      2015    KTM     data2               289 
US      2015    KTM     data3               632

It would be nice to see what you currently have in your code and where you would like the transpose implementation to be. Could you add this? — Easton Bornemeier
– Easton Bornemeier, Commented Jun 21, 2017 at 14:25
@EastonBornemeier I have checked post: stackoverflow.com/questions/42292986/… , but its giving error for me... — Plinus
– Plinus, Commented Jun 21, 2017 at 14:27

Scott Boston · Accepted Answer · 2017-06-21 14:43:58Z

20

use melt:

df.melt(id_vars=['country','year','perc'])

older versions of Pandas:

pd.melt(df, id_vars=['country','year','perc'])

Output:

  country  year perc variable  value
0      IN  2015  hjk    data1     75
1      US  2015  KTM    data1    100
2      IN  2015  hjk    data2     81
3      US  2015  KTM    data2    289
4      IN  2015  hjk    data3     96
5      US  2015  KTM    data3    632

Option #2

df.set_index(['country','year','perc']).stack().reset_index()

Output:

  country  year perc level_3    0
0      IN  2015  hjk   data1   75
1      IN  2015  hjk   data2   81
2      IN  2015  hjk   data3   96
3      US  2015  KTM   data1  100
4      US  2015  KTM   data2  289
5      US  2015  KTM   data3  632

edited Jun 21, 2017 at 14:43

answered Jun 21, 2017 at 14:38

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

7 Comments

Plinus Over a year ago

thanks, but getting error: AttributeError: 'DataFrame' object has no attribute 'melt', using 2.7 python

Scott Boston Over a year ago

YOu are using a older version upgarde or pd.melt(df,id_vars=['country','year','perc'])

Plinus Over a year ago

thanks a lot... working fine... OK.. I will upgrade to latest

Plinus Over a year ago

how to insert these results to already created data frame 'resultsAll' with same structure, I want to transpose 400+ csv file and insrert to one dataframe, how to achieve this?

Scott Boston Over a year ago

df1 = then statement this will assign the results to the df1 variable creating a new dataframe.

|

Collectives™ on Stack Overflow

How to transpose dataframe columns into rows in pandas

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related