Better way to split column by value into new columns?

Question

I have the following dataframe:

data = {'ID': [1, 1, 2, 2, 3, 3, 4, 4], 'User': ['type 1', 'type 2', 'type 1', 'type 2', 'type 1', 'type 2', 'type 1', 'type 2',], 'Rating': [4,5,3,2,1,5,4,3]} 
df = pd.DataFrame(data)
print(df)

I want to create two new columns based on "User", one for type 1 and another for type 2. I suspect I have to create a new dataframe:

Type_1 = df[df.User == 'type 1']
Type_2 = df[df.User == 'type 2']
df1 = pd.merge(Type_1, Type_2, how="left", on=['ID'])
print(df1)

Is there a quicker way of accomplishing this?

score 1 · Accepted Answer · answered Jan 23 '21 at 00:45

IIUC,

df.set_index(['ID','User'])['Rating'].unstack('User').reset_index()

OR

df.pivot('ID','User','Rating').reset_index()

Output:

User  ID  type 1  type 2
0      1       4       5
1      2       3       2
2      3       1       5
3      4       4       3

Better way to split column by value into new columns?

1 Answers1