1

I have a dataframe like this:

A B C
One 12 13
One 10 9
One 3 6
Two 11 7
Two 14 12
Three 12 8
Three 1 9
Three 3 4

I need to transform the dataframe as this:

A B C B2 C2 B3 C3
One 12 13 10 9 3 6
Two 11 7 14 12
Three 12 8 1 9 3 4

I would be very grateful if you could help me.

Thank you

Quim Quadrada
  • 45
  • 6

2 Answers2

2

groupby with cumcount

df.set_index(['A', df.groupby('A').cumcount()]).unstack()

          B                C           
          0     1    2     0     1    2
A                                      
One    12.0  10.0  3.0  13.0   9.0  6.0
Three  12.0   1.0  3.0   8.0   9.0  4.0
Two    11.0  14.0  NaN   7.0  12.0  NaN

Nailing down actual desired output

d0 = df.set_index(['A', df.groupby('A').cumcount()]).unstack()
d0.columns = [f'{tup[0]}{"" if tup[1] == 0 else tup[1]}' for tup in d0.columns]
d0.reset_index()

       A     B    B1   B2     C    C1   C2
0    One  12.0  10.0  3.0  13.0   9.0  6.0
1  Three  12.0   1.0  3.0   8.0   9.0  4.0
2    Two  11.0  14.0  NaN   7.0  12.0  NaN
piRSquared
  • 285,575
  • 57
  • 475
  • 624
1

Try with stack and pivot:

stacked = df.set_index("A").stack().reset_index()
stacked["Column"] = stacked["level_1"].add(stacked.groupby(["A","level_1"]).cumcount().add(1).astype(str))
output = stacked.pivot("A","Column",0).rename_axis(None,axis=1)

>>> output
         B1    B2   B3    C1    C2   C3
A                                      
One    12.0  10.0  3.0  13.0   9.0  6.0
Three  12.0   1.0  3.0   8.0   9.0  4.0
Two    11.0  14.0  NaN   7.0  12.0  NaN
Input df:
df = pd.DataFrame({'A': ['One', 'One', 'One', 'Two', 'Two', 'Three', 'Three', 'Three'],
                   'B': [12, 10, 3, 11, 14, 12, 1, 3],
                   'C': [13, 9, 6, 7, 12, 8, 9, 4]})
not_speshal
  • 22,093
  • 2
  • 15
  • 30