3

I have a dataframe like below. I want to convert it to pivot table format, where there is each row for unique ID, new column for each Score with Type prefix.

I have about 15 different Types in the actual dataframe.

df = pd.DataFrame({'ID' : [1,1,2,2,3,3,4,4],
                   'Type':['A','B','A','B','A','B','A','B'],
                   'Score':[0.3,np.nan, 0.2, 0.1, 1.1,np.nan, 2, np.nan]})

Desired output

ID A_Score B_Score
1 0.3
2 0.2 0.1
3 1.1
4 2

I tried below and it almost does what I need but I need the column renames and need it in pandas dataframe

df2 = df.pivot_table(index=['ID'], columns='Type')

enter image description here

Daven1
  • 135
  • 8

3 Answers3

3

You can do

out = df.pivot_table(index='ID', columns='Type',values='Score').add_prefix('Score_').reset_index()
Out[355]: 
Type  ID  Score_A  Score_B
0      1      0.3      NaN
1      2      0.2      0.1
2      3      1.1      NaN
3      4      2.0      NaN
BENY
  • 317,841
  • 20
  • 164
  • 234
2

Another version:

df = df.set_index(["ID", "Type"]).unstack(1)
df.columns = [f"{b}_{a}" for a, b in df.columns]
print(df.reset_index().fillna(""))

Prints:

   ID  A_Score B_Score
0   1      0.3        
1   2      0.2     0.1
2   3      1.1        
3   4      2.0
Andrej Kesely
  • 168,389
  • 15
  • 48
  • 91
2

You can flatten your multiIndex header using map-join

df2.columns = df2.columns.map('_'.join)
print(df2)

Output:

    Score_A  Score_B
ID                  
1       0.3      NaN
2       0.2      0.1
3       1.1      NaN
4       2.0      NaN
Scott Boston
  • 147,308
  • 15
  • 139
  • 187