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 enter image description here

• jpp: This is unclear. In pandas itself, you don't get these "combined cells" as you visualize them. You can't, as far as I know, get your output into Excel with combined rows from pandas in the way you want. So what exactly are you looking for?
• Phuong Duyen Huynh Ngoc: I just wonder is there anyway to display the pandas dataframe like the second table (the below table). I do not want to extract the table to excel or load the table from excel either. I just wonder whether we can display "combined cells" like the below table simply with pandas command
• Dan: @PhuongDuyenHuynhNgoc No, pandas isn't a visualisation library so it doesn't make sense to ask to to display a table in a non standard way. In what context do you want to do this?

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

• Phuong Duyen Huynh Ngoc: thank you very much for your answer, it is what I am looking for.
• ollydbg23: What does the dummy column in the option 2 used for? I don't see much difference compared with option 1, except that a new dummy column is added in option 2.
• jpp: @ollydbg23, OP is interested in visual representation (as misguided as that is for Pandas). Visually, the BBB is repeated in option 1, not in option 2.
• ollydbg23: @jpp, Thanks for the reply, indeed, I did not notice the difference of column "PM" in the two options.

Answer 2

The dataframe you have looks fine. It really depends on what you are trying to do.

If you are trying to return a dataframe where Name is AAA and PM is BBB you should use the pandas lookup

dfnew = df[(df.Name == 'AAA') & (df.PM == 'BBB')]

• Phuong Duyen Huynh Ngoc: Hi, Ithank you for your answer, but I think you have misunderstood what I mean, I mean how can I turn from the table above to the table below. Because the column name and PM is the same, so I want to merge them into one single value