I want to group a dataframe by a column (Type) and then transpose the columns (Parameter 1-3) while keeping the group as shown in the exemplary tables. While the first step is clear, i’m struggling with the parameter transposition and don’t know how to solve this. Any help would be appreciated…
| Type | Parameter 1 | Parameter 2 | Parameter 3 |
|---|---|---|---|
| A | 1 | 2 | 4 |
| A | 3 | 10 | 1 |
| B | 2 | 5 | 9 |
| B | 4 | 8 | 2 |
| Type | Params | Value 1 | Value 2 |
|---|---|---|---|
| A | Parameter 1 | 1 | 3 |
| Parameter 2 | 2 | 10 | |
| Parameter 3 | 4 | 1 | |
| B | Parameter 1 | 2 | 4 |
| Parameter 2 | 5 | 8 | |
| Parameter 3 | 9 | 2 |
>Solution :
Use GroupBy.cumcount for counter, unpivot by DataFrame.melt, pivoting by DataFrame.pivot and last DataFrame.add_prefix:
df1 = (df.assign(g = df.groupby('Type').cumcount().add(1))
.melt(['Type','g'], var_name='Params')
.pivot(index=['Type','Params'], columns='g', values='value')
.add_prefix('Value'))
print (df1)
g Value1 Value2
Type Params
A Parameter 1 1 3
Parameter 2 2 10
Parameter 3 4 1
B Parameter 1 2 4
Parameter 2 5 8
Parameter 3 9 2
Or DataFrame.set_index with DataFrame.stack and Series.unstack:
df1 = (df.set_index(['Type', df.groupby('Type').cumcount().add(1)])
.stack()
.unstack(level=1)
.add_prefix('Value')
.rename_axis(['Type','Params'])
)
print (df1)
Value1 Value2
Type Params
A Parameter 1 1 3
Parameter 2 2 10
Parameter 3 4 1
B Parameter 1 2 4
Parameter 2 5 8
Parameter 3 9 2