Multi level header issue in pandas

user_13615401 0
Viewed 74

I am reading an excel file with following structure -

Input excel file

and using the following code - 

df = pd.read_excel("Myfile.xlsx", header=[0,1]) 
print(df.columns.ravel())

I expect the output should be like

[('Unnamed: 0_level_0', 'Id') ('Name', 'First Name')
 ('Name', 'Middle name') ('Name', 'Last name') ('Unnamed: 1_level_0', 'Age')
 ('Unnamed: 2_level_0', 'Email') ('Unnamed: 3_level_0', 'Phone') ('Address', 'House NO')
 ('Address', 'Street') ('Address', 'City') ('Address', 'State')
 ('Address', 'PIN')]

But what I get is - 

[('Unnamed: 0_level_0', 'Id') ('Name', 'First Name')
 ('Name', 'Middle name') ('Name', 'Last name') ('Name', 'Age')
 ('Name', 'Email') ('Name', 'Phone') ('Address', 'House NO')
 ('Address', 'Street') ('Address', 'City') ('Address', 'State')
 ('Address', 'PIN')]

Anyone has any clue why am I not getting the output as desired or is it what is desired? As the middle columns (Age, Email & Phone) are not part of Name (level 0 column), so what is being shown is incorrect. Any clue to resolve this?

pandas python
1 Answers

I was able to repro this. My guess is pandas is not considering whether the columns (Name, Address) are merged and assumes the columns represent the cells below, AND to the right.

I didn't see any reason provided in the docs (see index_label).

And this makes sense, say you have a sheet that looks like this:

HEADER1        HEADER2
Val1,Val2,Val3,Val4,Val5,Val6

The first 3 Vals look like they fall under HEADER1. In your screenshot, they clearly do not. Since Unnamed: 0_level_0 is sort of 'undefined,' they can get away with saying the labels are somewhat indeterminant, but they SHOULD handle the case of Merged cells, as in your screenshot, since that looks like a bug.

One other thing you could try is to put a blank ' ' space in the 'Unnamed' columns, to force them as being unlabeled. There may be other workarounds.

user_19142930
Related