-1

I am trying to re figure a DataFrame with the following components:

  Name   Date      X  Y
1 James  01/01/10  A  10
2 James  01/01/10  B  20
3 Sarah  02/01/10  A  30
4 Sarah  02/01/10  B  40
5 Sarah  03/01/10  A  50

Column X needs to be transposed into new columns with Name and Date grouped together. I have tried using pd.pivot and pd.pivot_table to get the resulting table below but have had no luck ie. pd.pivot_table(df, index=['Name','Date'], columns = 'X').reset_index()

  Name   Date      A  B  
1 James  01/01/10  10 20
3 Sarah  02/01/10  30 40
5 Sarah  03/01/10  50
WillacyMe
  • 562
  • 1
  • 6
  • 25

1 Answers1

1

also specify values = 'Y'.

try this:

import pandas as pd
from io import StringIO

df = pd.read_csv(StringIO("""
  Name   Date      X  Y
1 James  01/01/10  A  hi
2 James  01/01/10  B  there
3 Sarah  02/01/10  A  textual
4 Sarah  02/01/10  B  data
5 Sarah  03/01/10  A  column"""), sep="\s+")

print(pd.pivot_table(df, index=['Name','Date'], columns = 'X', values='Y', aggfunc=lambda x: ' '.join(str(v) for v in x)).reset_index())

Output:

X   Name      Date        A      B
0  James  01/01/10       hi  there
1  Sarah  02/01/10  textual   data
2  Sarah  03/01/10   column    NaN
Adam.Er8
  • 12,675
  • 3
  • 26
  • 38
  • This looks great. I'm trying it on one DataFrame though where column Y is non numeric and get the error No numeric types to aggregate. Can this be done the same way? There shouldn't be any duplicate values when grouped by name and date. – WillacyMe Jul 22 '19 at 14:42
  • @WillacyMe, yes, you can specify your own agg func. (e.g. `aggfunc=lambda x: ‘ ‘.join(str(v) for v in x)` – Adam.Er8 Jul 22 '19 at 14:44
  • @WillacyMe I've edited the answer to use a textual `Y` column – Adam.Er8 Jul 22 '19 at 14:49
  • Perfect, thank you! I just need to drop the level of those new columns now as I have a second column title called – WillacyMe Jul 22 '19 at 15:05