0

I have a Pandas DataFrame in Python such as this:

       Group     Pre/post    Value
0         A        Pre         3
1         A        Pre         5
2         A        Post       13
3         A        Post       15
4         B        Pre         7
5         B        Pre         8  
6         B        Post       17
7         B        Post       18

And I'd like to turn it into a different table such as:

      Group       Pre      Post
0       A       3         13
1       A       5         15
2       B       7         17
3       B       8         18

I tried pivoting with df.pivot(index='Group', columns='Pre/post', values='Value') but since I have repeated values and order is important, it went traceback

PAULO MARTINS
  • 19
  • 2

1 Answers1

1

Here is one way to do it, use list as an aggfunc in pivot_table, to collect the duplicate values for index and column as a list, then using explode split the list into multiple rows.

df.pivot_table(index='Group', columns='Pre/post', values='Value', aggfunc=list
              ).reset_index().explode(['Post','Pre'], ignore_index=True)

Pre/post    Group   Post    Pre
      0         A     13    3
      1         A     15    5
      2         B     17    7
      3         B     18    8
Naveed
  • 11,495
  • 2
  • 14
  • 21
  • 1
    This seems like an inefficient way to handle the duplicates ;) The canonical way would be `df.assign(idx=df.groupby('Pre/post').cumcount()).pivot(index=['idx', 'Group'], columns='Pre/post', values='Value')` – mozway Aug 20 '22 at 15:34
  • Yes @mozway, I agree – Naveed Aug 20 '22 at 15:40