I have the following pandas dataframe:

    0
0                      
A   0
B   0
C   0
C   4
A   1
A   7

Now there are some index letter (A and C) that appear multiple times. I want the values of these index letters on a extra column beside instead of a extra row. The desired pandas dataframe looks like:

    0   1       3
0                      
A   0   1       7
B   0   np.nan  np.nan
C   0   4       np.nan

Anything would help!

CodePudding user response：

IIUC, you need to add a helper column:

(df.assign(group=df.groupby(level=0).cumcount())
   .set_index('group', append=True)[0] # 0 is the name of the column here
   .unstack('group')
)

or:

(df.reset_index()
   .assign(group=lambda d: d.groupby('index').cumcount())
   .pivot('index', 'group', 0) # col name here again
)

output:

group    0    1    2
A      0.0  1.0  7.0
B      0.0  NaN  NaN
C      0.0  4.0  NaN