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

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? — jpp
– jpp, Commented Mar 28, 2018 at 11:56
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 — Phuong Duyen Huynh Ngoc
– Phuong Duyen Huynh Ngoc, Commented Mar 28, 2018 at 12:02
@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? — Dan
– Dan, Commented Mar 28, 2018 at 12:23

jpp · Accepted Answer · 2018-03-28 12:10:03Z

9

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

edited Mar 28, 2018 at 12:10

answered Mar 28, 2018 at 12:07

jpp

166k37 gold badges301 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Phuong Duyen Huynh Ngoc Over a year ago

thank you very much for your answer, it is what I am looking for.

ollydbg23 Over a year ago

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 Over a year ago

@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 Over a year ago

@jpp, Thanks for the reply, indeed, I did not notice the difference of column "PM" in the two options.

Johny Mudly · Accepted Answer · 2018-03-28 11:39:57Z

0

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')]

answered Mar 28, 2018 at 11:39

Johny Mudly

3242 gold badges4 silver badges21 bronze badges

1 Comment

Phuong Duyen Huynh Ngoc Over a year ago

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

Collectives™ on Stack Overflow

Pandas Data Frame how to merge columns

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related