Grouping and partially transposing a dataframe with pandas

Question

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

@mozway, should be reopened? – jezrael Dec 16 '22 at 15:04 — jezrael, Dec 16 '22 at 15:04

jezrael · Accepted Answer · 2022-12-16T14:26:18.043

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

Grouping and partially transposing a dataframe with pandas

1 Answers1