Error when using pandas read_excel(header=[0,1])

Question

I'm trying to use pandas read_excel to work with a file. The file has two columns of headers so I'm trying to use the multiIndex feature apart of the header keyword argument.

import pandas as pd, os 

"""data in 2015 MOR Folder"""
filename = 'MOR-JANUARY 2015.xlsx'

print(os.path.isfile(filename))

df1 = pd.read_excel(filename, header=[0,1], sheetname='MOR')

print(df1)

the error I get is ValueError: Length of new names must be 1, got 2. The file is in this google drive folder https://drive.google.com/drive/folders/0B0ynKIVAlSgidFFySWJoeFByMDQ?usp=sharing I'm trying to follow the solution posted here Read excel sheet with multiple header using Pandas

The error may be version specific, and seems to be a work in progress - GitHub — arthuritus
– arthuritus, Commented May 11, 2017 at 13:58

piRSquared · Accepted Answer · 2016-12-20 22:09:50Z

8

I could be mistaken but I don't think pandas handles parsing excel rows where there are merged cells. So in that first row, the merged cells get parsed as mostly empty cells. You'd need them nicely repeated to act correctly. This is what motivates the ffill below. If you could control the Excel workbook ahead of time and you might be able to use the code you have.

my solution

It's not pretty, but it'll get it done.

filename = 'MOR-JANUARY 2015.xlsx'
df1 = pd.read_excel(filename, sheetname='MOR', header=None)

vals = df1.values

mux = pd.MultiIndex.from_arrays(df1.ffill(1).values[:2, 1:], names=[None, 'DATE'])

df1 = pd.DataFrame(df1.values[2:, 1:], df1.values[2:, 0], mux)

answered Dec 20, 2016 at 22:09

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

1 Comment

D A Wells Over a year ago

An update, pandas now does handle merged cells. This error is thrown when column names are not unique. (That's how I ended up here).

Collectives™ on Stack Overflow

Error when using pandas read_excel(header=[0,1])

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related