I have the following DataFrame:
| ID | Week | A | B |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 2 |
| 1 | 2 | 2 | 1 |
| 2 | 2 | 2 | 3 |
I now want to pivot the mean values in the DataFrame with the weeks as columns and the former columns as row entries in Type, so it can get this structure:
| Type | Week 1 | Week 2 | … | Week N |
|---|---|---|---|---|
| A | 1 | 2 | … | |
| B | 1.5 | 2 | … |
>Solution :
You don’t need a pivot, use groupby.mean and a transpose:
out = (df
.drop(columns='ID')
.groupby('Week').mean().T
.add_prefix('Week')
.rename_axis(index='Type', columns=None).reset_index()
)
Alternative with melt and pivot_table:
out = (df
.melt(['ID', 'Week'], var_name='Type')
.pivot_table(index='Type', columns='Week', values='value')
.add_prefix('Week').reset_index().rename_axis(columns=None)
)
Output:
Type Week1 Week2
0 A 1.0 2.0
1 B 1.5 2.0