Pivoting and transposing using pandas dataframe-CodePudding

Suppose that I have a pandas dataframe like the one below:

 import pandas as pd
    df = pd.DataFrame({'fk ID': [1,1,2,2], 
                       'value': [3, 3, 4,5],
                       "valID": [1,2,1,2]})

The above would give me the following output:

print(df)
   fk ID  value  valID
0      1      3      1
1      1      3      2
2      2      4      1
3      2      5      2

 |fk ID| value | valId |
 |  1  |  3    |   1   |
 |  1  |  3    |   2   |
 |  2  |  4    |   1   |
 |  2  |  5    |   2   |

and I would like to transpose and pivot it in such a way that I get the following table:

  fk ID  value  valID  fkID  value   valID
 |  1  |   3  |  1   |  1  |   3   |  2   | 
 |  2  |   4  |   1  |  2  |   5   |  2   |

CodePudding user response：

Try:

df_out = df.set_index([df.groupby('fk ID').cumcount(), 
                       df.groupby('valID').cumcount()]).unstack(0)
df_out = df_out.sort_index(level=1, axis=1)
df_out.columns = [f'{i}_{j}' for i, j in df_out.columns]
df_out

Output:

   fk ID_0  valID_0  value_0  fk ID_1  valID_1  value_1
0        1        1        3        1        2        3
1        2        1        4        2        2        5