Pandas Data Frame how to merge columns

Question

I have a pandas dataframe like in the picture. How can I turn it into the table like below. (the demonstration is in excel but I just want to illustrate to you how the table look like- this question does not related to importing and exporting dataframe from/to excel)

Thank you

Answer 1

This is not possible.

Underlying pandas.DataFrame objects are numpy arrays, which do not group data in the way you suggest. Therefore, an arbitrary column cannot be displayed as grouped data.

Option 1

It is possible to partially replicate your desired output by using MultiIndex:

import pandas as pd

df = pd.DataFrame([['AAA', 8, 2, 'BBB'],
                   ['AAA', 9, 5, 'BBB'],
                   ['AAA', 10, 6, 'BBB']],
                  columns=['Name', 'Score1', 'Score2', 'PM'])

res = df.set_index(['Name', 'PM'])

Result:

          Score1  Score2
Name PM                 
AAA  BBB       8       2
     BBB       9       5
     BBB      10       6

Option 2

Or you can add a dummy column and set_index on 3 columns:

df['dummy'] = 0
res = df.set_index(['Name', 'PM', 'dummy'])

Result:

                Score1  Score2
Name PM  dummy                
AAA  BBB 0           8       2
         0           9       5
         0          10       6