Parsing a Multi-Index Excel File in Pandas

Question

I have a time series excel file with a tri-level column MultiIndex that I would like to successfully parse if possible. There are some results on how to do this for an index on stack overflow but not the columns and the parse function has a header that does not seem to take a list of rows.

The ExcelFile looks like is like the following:

Column A is all the time series dates starting on A4
Column B has top_level1 (B1) mid_level1 (B2) low_level1 (B3) data (B4-B100+)
Column C has null (C1) null (C2) low_level2 (C3) data (C4-C100+)
Column D has null (D1) mid_level2 (D2) low_level1 (D3) data (D4-D100+)
Column E has null (E1) null (E2) low_level2 (E3) data (E4-E100+)
...

So there are two low_level values many mid_level values and a few top_level values but the trick is the top and mid level values are null and are assumed to be the values to the left. So, for instance all the columns above would have top_level1 as the top multi-index value.

My best idea so far is to use transpose, but the it fills Unnamed: # everywhere and doesn't seem to work. In Pandas 0.13 read_csv seems to have a header parameter that can take a list, but this doesn't seem to work with parse.

It would be useful to have a comment or two from the down voters so i know how to phrase the question differently next time. — rhaskett
– rhaskett, Commented Jun 12, 2014 at 18:41

Happy001 · Accepted Answer · 2014-06-11 03:22:26Z

7

You can fillna the null values. I don't have your file, but you can test

#Headers as rows for now
df = pd.read_excel(xls_file,0, header=None, index_col=0) 

#fill in Null values in "Headers"
df = df.fillna(method='ffill', axis=1) 

#create multiindex column names
df.columns=pd.MultiIndex.from_arrays(df[:3].values, names=['top','mid','low']) 

#Just name of index
df.index.name='Date' 

#remove 3 rows which are already used as column names
df = df[pd.notnull(df.index)]

answered Jun 11, 2014 at 3:22

Happy001

6,4032 gold badges26 silver badges17 bronze badges

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

1 Comment

rhaskett Over a year ago

Thanks. header=None was what I was missing here as this avoids the multiple Unnamed column names allowing the horizontal fill. Well done.

Collectives™ on Stack Overflow

Parsing a Multi-Index Excel File in Pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related