how to read excel file with nested columns with pandas?

Question

I am trying to read an Excel file using pandas but my columns and index are changed:

df = pd.read_excel('Assignment.xlsx',sheet_name='Assignment',index_col=0)

Excel file:

excel file ss

Jupyter notebook:

jupyter notebook ss

LOrD_ARaGOrN · Accepted Answer · 2019-11-18 09:21:14Z

3

By default pandas consider first row as header. You need to tell that take 2 rows as header.

df = pd.read_excel("xyz.xlsx",  header=[0,1], usecols = "A:I", skiprows=[0])

print df

You can choose to mention skiprows depending on the requirement. If you remove skiprows, it will show first row header without any unnamed entries.

Refer this link

answered Nov 18, 2019 at 8:54

LOrD_ARaGOrN

4,6166 gold badges39 silver badges59 bronze badges

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

4 Comments

i tried it but it gives the error:Passed header=[0,1] are too many rows for this multi_index of columns

ok it worked somewhat I I got this ibb.co/7JDWbTJ .How can I combine power and temperature under the header of registration unit-1?

@sanjuatwal try to skiprows=[1]

super thumbs up.