Pandas groupby split repeated rows into columns

user_5439546 0
Viewed 86

I have a dataframe like below and I'm trying to split the repeated unique_id rows into new columns.

unique_id Name     Description_col
001       Ram      description1
002       Kumar    description2
002       Gopal    description3
003       Dinesh   description4
004       Avi      description5
004       Sam      description6
004       Guru     description7

I am trying to split the dataframe by grouping by unique_id. If the unique id is repeated then the Name, Description columns should be added as new columns.

My expected output is:

unique_id Name     Description_col     Name2    Description_col2   Name3     Description_col3
001       Ram      description1
002       Kumar    description2        Gopal    description3    
003       Dinesh   description4
004       Avi      description5        Sam      description6        Guru     description7
pandas group-by python
1 Answers

We need use cumcount create the additional key for pivot

df['key'] = df.groupby('unique_id').cumcount().astype(str)
s=df.pivot_table(index='unique_id',columns='key',values=['Name','Description_col'],aggfunc='first').sort_index(level=1,axis=1)
s.columns=s.columns.map('_'.join)
s
Out[97]: 
          Description_col_0  Name_0  ... Description_col_2 Name_2
unique_id                            ...                         
1              description1     Ram  ...               NaN    NaN
2              description2   Kumar  ...               NaN    NaN
3              description4  Dinesh  ...               NaN    NaN
4              description5     Avi  ...      description7   Guru
[4 rows x 6 columns]
user_79645270
Related