CODEWITHSUNDEEP
python-3.xpandas
Christopher Mitchell
Christopher Mitchell

Reputation: 105

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

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

Upvotes: 10

Views: 5276

Answers (1)

piRSquared
piRSquared

Reputation: 294488

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)

Upvotes: 8

Related Questions