How do I convert some rows into a same index - Python

Question

I have a table that needs to be transform in the following way

    ID      Item_ID  Rank
0   12      1068     1.0
1   12      8914     2.0
2   12      7997     3.0
3   13      311      4.0
4   13      123      5.0
5   13      39       6.0

to

ID    Item_ID_1  Item_ID_2  Item_ID_3
12    1068       8914       7997      
13    311        123        39

I've tried pd.pivot but gives me the error ValueError: Index contains duplicate entries, cannot reshape but, of course that I have duplicated entries.

Please some kind of help!

Thanks

score 3 · Accepted Answer · answered Jul 22 '19 at 14:55

Use groupby().cumcount() with pivot_table():

final=(df.assign(k=(df.groupby('ID').cumcount()+1).astype(str))
.pivot_table(index='ID',columns='k',values='Item_ID').add_prefix('Item_ID_')
 .reset_index().rename_axis(None,axis=1))

   ID  Item_ID_1  Item_ID_2  Item_ID_3
0  12       1068       8914       7997
1  13        311        123         39

score 2 · Answer 2 · answered Jul 22 '19 at 14:54

If you always have consecutive groups of N (in your example, 3) items, then the simplest (and probably fastest) solution is to just reshape

N = 3
pd.DataFrame(df['Item_ID'].to_numpy().reshape(-1, N), 
             columns=['Item_ID_1','Item_ID_2','Item_ID_3'],
             index=df.ID.unique())

Outputs

    Item_ID_1   Item_ID_2   Item_ID_3
12  1068        8914        7997
13  311         123         39

How do I convert some rows into a same index - Python

2 Answers2