0

Could you please help how to transpose the dataframe:

From this:

data = {'ID' : [1,1,1,2,2,2,2],
        'Name' : ['OO', 'XX', 'YY','ZZ', 'MM', 'VV', 'RR'],
         'Rank' : [5,6,7,8,9,10,11],
         'Price' : [20,30,40,50,60,70,80]}

df = pd.DataFrame(data)

   ID Name Rank Price
0   1   OO  5   20
1   1   XX  6   30
2   1   YY  7   40
3   2   ZZ  8   50
4   2   MM  9   60
5   2   VV  10  70
6   2   RR  11  80

To this

enter image description here

Note, I have a huge excel file for this, this is just a sample.

Skittles
  • 121
  • 1
  • 11
  • Do you really want to have repeated column names? – Shubham Sharma Sep 14 '21 at 16:02
  • @Shubham Sharma yes, if you know how to do it, what would be the other option 'Rank1',Name1','Price1',Rank2'...? I prefer with the columns repeated – Skittles Sep 14 '21 at 16:04
  • IMO repeated column names will be of no use(unless there is a good reason). I prefer having the unique identifier on each column names. – Shubham Sharma Sep 14 '21 at 16:07
  • @Shubham Sharma alright, then adding numbers like I wrote : 'Rank_1',Name_1','Price_1',Rank_2','Name_2',... would also be fine – Skittles Sep 14 '21 at 16:18
  • Check Q10. of https://stackoverflow.com/questions/47152691/how-can-i-pivot-a-dataframe – Shubham Sharma Sep 14 '21 at 16:28
  • thank you, but unfortunately, it's not what I need, he put column A into the header, I wnt the headers to stay as they are or add some numbers to it. – Skittles Sep 14 '21 at 16:40

1 Answers1

0

Using groupby + cumcount add a sequential counter cols to the dataframe, then reshape using pivot keeping ID as index and cols as the new columns. After reshaping sort the columns on level=1 to maintain the order, finally flatten the columns using map with format

df['cols'] = df.groupby('ID').cumcount() + 1
s = df.pivot('ID', 'cols').sort_index(level=1, axis=1)
s.columns = s.columns.map('{0[0]}_{0[1]}'.format)

   Name_1  Price_1  Rank_1 Name_2  Price_2  Rank_2 Name_3  Price_3  Rank_3 Name_4  Price_4  Rank_4
ID                                                                                                
1      OO     20.0     5.0     XX     30.0     6.0     YY     40.0     7.0    NaN      NaN     NaN
2      ZZ     50.0     8.0     MM     60.0     9.0     VV     70.0    10.0     RR     80.0    11.0
Shubham Sharma
  • 68,127
  • 6
  • 24
  • 53