How to multi index rows using pandas

Question

I am new to pandas, whenever I implement my code, first index is repeating for each row.

What I have tried is:

pg, ag, inc are arrays

cases=['a1','a2','a3']
data={'RED':rg,'GREEN':gg,'BLUE':bb}
stat_index=['HELO','HERE' ]
df=pd.DataFrame(data,pd.MultiIndex.from_product([cases,stat_index]),['RED','GREEN','BLUE'])
df.to_csv("OUT.CSV")

What I get is :

             RED         GREEN       BLUE
a1  HELO    304.907     286.074     12.498
a1  HERE    508.670     509.784     94.550
a2  HELO    448.974     509.406     56.466
a2  HERE    764.727     432.084     43.462
a3  HELO    412.539     602.001     10.849
a3  HERE    321.9       603.888     78.847

What I actually want is:

             RED         GREEN       BLUE
a1  HELO    304.907     286.074     12.498
    HERE    508.670     509.784     94.550
a2  HELO    448.974     509.406     56.466
    HERE    764.727     432.084     43.462
a3  HELO    412.539     602.001     10.849
    HERE    321.9       603.888     78.847

jezrael · Accepted Answer · 2019-08-20 08:58:02Z

1

Don't do it, only if really need it.

It is expected, because if write MulitIndex to file is repeated first levels. If display MultiIndex with DataFrame, it is not shown by default only. But if change multi_sparse to False you can check real data:

with pd.option_context('display.multi_sparse', False):
    print (df)
             RED    GREEN    BLUE
a1 HELO  304.907  286.074  12.498
a1 HERE  508.670  509.784  94.550
a2 HELO  448.974  509.406  56.466
a2 HERE  764.727  432.084  43.462
a3 HELO  412.539  602.001  10.849
a3 HERE  321.900  603.888  78.847

Main reason is compatibility, if use read_csv with empty spaces in MulitIndex, need preprocessing.

But it is possible:

a = df.index.get_level_values(0)
df.index = pd.MultiIndex.from_arrays([a.where(a.duplicated(), ''),
                                      df.index.get_level_values(1)])

with pd.option_context('display.multi_sparse', False):
    print (df)
             RED    GREEN    BLUE
   HELO  304.907  286.074  12.498
a1 HERE  508.670  509.784  94.550
   HELO  448.974  509.406  56.466
a2 HERE  764.727  432.084  43.462
   HELO  412.539  602.001  10.849
a3 HERE  321.900  603.888  78.847

edited Aug 20, 2019 at 8:58

answered Aug 20, 2019 at 8:53

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

IMCoins Over a year ago

I think he wants the output to a csv. Like, the df representation as csv.

codeuser Over a year ago

@IMCoins Yes I want to write to csv exaclty as pandas output

Collectives™ on Stack Overflow

How to multi index rows using pandas

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related