Is there any way to get transpose of selected columns only in pandas?

Question

I have a large dataset where I have about more than 350 columns and I want to convert those 350 columns into rows and rows values as columns. The users provide the ranking "1-10" to brands and I want to show ranking (1-10) as columns.

Here is how my df looks:

id          Gender       Age            BMW         Audi         VW         Mercedes
1             M          20              3           1           4            2
2             F          50              1           2           3            4
3             F          25              2           1           4            3   

   

And i would like to change it in this way as desired column.

id          Gender       Age            Rank1         Rank2           Rank3          Rank4      
1             M          20              Audi        Mercedes          BMW            VW      
2             F          50              BMW          Audi             VW             Mercedes
3             F          25              Audi         BMW              Mercedes       VW        

In this way I can get rid of so many columns... Any solution?

Answer 1

you can melt+pivot:

idx = ['id','Gender','Age']
m = df.melt(idx).pivot_table(index=idx,columns='value',values='variable',aggfunc='first')
out = m.add_prefix('Rank').reset_index(idx).rename_axis(index=None,columns=None)

---

print(out)

   id Gender  Age Rank1     Rank2     Rank3     Rank4
0   1      M   20  Audi  Mercedes       BMW        VW
1   2      F   50   BMW      Audi        VW  Mercedes
2   3      F   25  Audi       BMW  Mercedes        VW