10

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
  • 159,742
  • 34
  • 281
  • 339
Phuong Duyen Huynh Ngoc
  • 459
  • 2
  • 5
  • 19
  • 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 Mar 28 '18 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 Mar 28 '18 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 Mar 28 '18 at 12:23

2 Answers2

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
jpp
  • 159,742
  • 34
  • 281
  • 339
  • thank you very much for your answer, it is what I am looking for. – Phuong Duyen Huynh Ngoc Mar 28 '18 at 12:09
  • 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. – ollydbg23 Apr 25 '19 at 08:38
  • @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. – jpp Apr 25 '19 at 09:19
  • @jpp, Thanks for the reply, indeed, I did not notice the difference of column "PM" in the two options. – ollydbg23 Apr 25 '19 at 10:12
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')]
Johny Mudly
  • 314
  • 2
  • 3
  • 20
  • 1
    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 – Phuong Duyen Huynh Ngoc Mar 28 '18 at 11:59