0

Although I found a similar question here (How to move duplicate rows into columns with python), I have an error with the column parameter.

In my case, I have a dataframe df and want to change it to df1:

df = pd.DataFrame(data=[['A',1],['A',2],['B',3],['C',4],['C',5],['C',6]],columns=['Name','Value'])
df
#   Name Value
#0     A     1
#1     A     2
#2     B     3
#3     C     4
#4     C     5
#5     C     6

df1
#   Name     0    1    2
#0     A     1    2  Nan
#1     B     3  Nan  Nan
#2     C     4    5    6

When I tried to run the code, it returned an error as below:

df.pivot(index='Name', columns=range(max(df.pivot_table(columns=['Name'], aggfunc='size'))), values='Value')
#KeyError: 3

I don't know why it couldn't allocate the column name automatically. Can anyone tell me where should I fix the problem in the above code?

Ssong
  • 184
  • 1
  • 10

1 Answers1

1
grouper = df.groupby('Name').cumcount()

grouper

0    0
1    1
2    0
3    0
4    1
5    2
dtype: int64


df.pivot_table('Value', index='Name', columns=grouper)

output:

    0   1   2
Name            
A   1.0 2.0 NaN
B   3.0 NaN NaN
C   4.0 5.0 6.0

It is also possible to use the following code

df.groupby('Name')['Value'].agg(list).apply(pd.Series)
Panda Kim
  • 6,246
  • 2
  • 12