-2

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 ...
max
  • 41
  • 7
  • 1
    Given that you apparently already know the term "pivot" then there really is nothing stopping you researching the huge number of answers that already exist for this. What is your research? – roganjosh Aug 02 '23 at 15:15
  • This is not really helpful at all tbh. I already tried several things with pivoting and looked through the forum. – max Aug 02 '23 at 15:17
  • 2
    You might want to update the question with the exact matching output – mozway Aug 02 '23 at 15:26
  • 2
    Related: [How can I pivot a dataframe?](/q/47152691/4518341) (though it's really broad) – wjandrea Aug 02 '23 at 15:32
  • Thanks! I'll look into it. Also the output has been updated now. – max Aug 02 '23 at 15:33

2 Answers2

1

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
mozway
  • 194,879
  • 13
  • 39
  • 75
1

You can use pivot_table.

df.pivot_table(columns=['Week'], values=['A', 'B']).rename_axis('Type')

Week    1    2
Type          
A     1.0  2.0
B     1.5  2.0

The default aggfunc is mean, so It Just Works.

wjandrea
  • 28,235
  • 9
  • 60
  • 81