I have data like below.
col1 col2
23 101
23 102
24 101
25 102
25 103
I want to pivot col2 based on col1. Desired output is like below.
col1 pro_1 pro_2 pro_3
23 101 102 NA
24 101 NA NA
25 NA 102 103
Tried like below:
data.pivot(data,columns=['col_1'],values=['col_2'])
I got an error like below:
ValueError: The name col_1 occurs multiple times, use a level number
You need to provide information on the columns you want to put the values in 'col2' into. I think this is what you want:
mapping = {101: 'pro1', 102: 'pro2', 103: 'pro3'}
df['cols'] = df.col2.map(mapping)
df.pivot(index='col1', values='col2', columns='cols')
Edit: You can create the mapping automatically like so:
df['cols'] = 'pro' + df.col2.astype(str)
Edi2: You can check if your data has duplicate rows like so:
df.duplicated()
If you simply want to get rid of these you can do
df.loc[~df.duplicated()].pivot(index='col1', values='col2', columns='cols')