Reading Multi-index data frames in python to retain format-CodePudding

I have a multiindex dataframe.

When I load the data

df = pd.read_excel(path   'doc.xlsx', sheet_name='sheet1', index_col=[0,1])

It looks like this

Index    Index    A    B    C    D
0        AA       0.1  0.2  0.4  0.4
0        AB       0.0 -0.5  0.3  0.9
1        BB       0.4  0.6  1.2  0.8
1        BC       0.4  0.4  0.1  0.7

I hoped it would instead look like this

Index    Index    A    B    C    D
0        AA       0.1  0.2  0.4  0.4
         AB       0.0 -0.5  0.3  0.9
1        BB       0.4  0.6  1.2  0.8
         BC       0.4  0.4  0.1  0.7

How can i get it to look the above table by reading this file differently or merging the 1st index column.

CodePudding user response：

I would try:

df = pd.read_excel(path   'doc.xlsx', sheet_name='sheet1', index_col=[1,2])

And if that still fails, I'd just do:

df = pd.read_excel(path   'doc.xlsx', sheet_name='sheet1')
# rename columns beforehand if desired
df.set_index(['Index', 'Index.1'], inplace=True)

CodePudding user response：

So some of my packages were outdated, as soon and I did an update this started to work correctly. Thanks everyone for input.