Pandas - how to pivot type and value columns into new columns for each type-CodePudding

I have a pandas dataframe storing the value of different metrics for different parameter configuration, such as:

index | param1 | params2 | metric | score | 
  0   |   xx   |   yy    |  XXX   |  100  |
  1   |   xx   |   yy    |  YYY   |   50  |
  2   |   xx   |   yy    |  ZZZ   |   20  |
  3   |   aa   |   bb    |  XXX   |  200  |
  4   |   aa   |   bb    |  YYY   |   50  |
  5   |   aa   |   bb    |  ZZZ   |   10  |

I would like to convert it into:

index | param1 | params2 | XXX  | YYY  | ZZZ | 
  0   |   xx   |   yy    |  100 |  50  |  20 |
  1   |   aa   |   bb    |  200 |  50  |  10 |

I m trying to do it with pivot, that is

df.pivot(index='index',columns=['metric','param1','param2'],values='score')

but the results is not what I am searching for. Moreover, I am not interested into aggregating the values, just reshaping them.

CodePudding user response：

You can use

df = pd.DataFrame({
    'index': [0, 1, 2, 3, 4, 5],
    'param1': ['xx', 'xx', 'xx', 'aa', 'aa', 'aa'],
    'param2': ['yy', 'yy', 'yy', 'bb', 'bb', 'bb'],
    'metric': ['XXX', 'YYY', 'ZZZ', 'XXX', 'YYY', 'ZZZ'],
    'score': [100, 50, 20, 200, 50, 10]
})

df.pivot_table(index=['param1', 'param2'],columns='metric',values='score').reset_index()

CodePudding user response：

You can use pd.pivot_table():

df.set_index('index',inplace=True)
pd.pivot_table(df,index=['param1','params2'],columns='metric',values='score').reset_index()

Prints:

metric param1 params2  XXX  YYY  ZZZ
0          aa      bb  200   50   10
1          xx      yy  100   50   20