Transpose dataframe at certain number of rows

Question

I have this data like given below .

   Col1 Col2  Col3       Col4
0   A01  CY5     1  -1.091990
1   A01  CY5     2   7.402931
2   A01  CY5     3   0.089915
3   A02  CY5     1  -1.526202
4   A02  CY5     2   2.229630
5   A02  CY5     3  -0.604621
6   A01  TEX     1 -10.525215
7   A01  TEX     2 -38.686164
8   A01  TEX     3   1.337128
9   A06  CY5     1   8.216859
10  A06  CY5     2   6.890880
11  A06  CY5     3   0.466027

I need to transform the data frame like mentioned below.

A01 CY5 -1.09199006470146 7.40293083315009  -1.52620195542602
A02 CY5 -1.52620195542602  2.2963003873349 -0.604621233162902
A01 TEX -10.5252148197815 -3.6861635883042   1.33712824456416
A06 CY5  8.21685850315987 6.89088046827999   0.46602727589584

I have tried pivot tables but couldn't achieved the desired output. I was hoping if someone can help.

Answer 1

You could use pivot to reshape. Then use rename_axis + reset_index to get it in the desired form:

out = df.pivot(['Col1', 'Col2'], 'Col3', 'Col4').add_prefix('Col4_').rename_axis(columns=[None]).reset_index()

Output:

  Col1 Col2     Col4_1     Col4_2    Col4_3
0  A01  CY5  -1.091990   7.402931  0.089915
1  A01  TEX -10.525215 -38.686164  1.337128
2  A02  CY5  -1.526202   2.229630 -0.604621
3  A06  CY5   8.216859   6.890880  0.466027