pivoting two column in pandas

Question

How can I transfer two column features into pivot table on the following dataset

I have tried the aggfunc function but this fill the value either 0 or 1. I want to transfer the row as cell value.

Here is the dataset

content  Users  
22       1196
23       1196
23       1216
16       880
20       880
20       1224
22       1245
23       1122
22       872

I want to transfer this dataset into this

Users   1    2
1196    22  23  
1216    23  NaN
880     16  20
1224    20  NaN
1245    22  NaN
1122    23  NaN
872     22  NaN

I have tried it by using

df.pivot_table(index=["city"], columns="cuisine", aggfunc=lambda x: 1, fill_value=0)

df.pivot_table(index=["users"], columns="content", aggfunc=lambda x: 1, fill_value=0)

But this fills the values either 0 or 1

Answer 1

You can accomplish what you want by doing:

df.groupby('Users')['content'].unique().apply(pd.Series)

First, what you want is to groupby your values by users, to get all content values. Since you want unique ones, you can apply unique() built-in pandas function.

Then, since you will have a columns with a list of values, you can apply pd.Series to each row, to expand it into columns, by default, they are named from 0 to n, where n is the maximum length of a list, in this case, two.

Answer 2

This would be a bit faster than using apply:

(df.assign(col=df.groupby('Users').cumcount())
  .set_index(['Users','col'])['content'].unstack())

Output:

col       0     1
Users            
872    22.0   NaN
880    16.0  20.0
1122   23.0   NaN
1196   22.0  23.0
1216   23.0   NaN
1224   20.0   NaN
1245   22.0   NaN