Use multiple rows as column header for pandas

Question

I have a dataframe that I've imported as follows.

df = pd.read_excel("./Data.xlsx", sheet_name="Customer Care", header=None)

I would like to set the first three rows as column headers but can't figure out how to do this. I gave the following a try:

df.columns = df.iloc[0:3,:]

but that doesn't seem to work.

I saw something similar in this answer. But it only applies if all sub columns are going to be named the same way, which is not necessarily the case.

Any recommendations would be appreciated.

iamchoosinganame · Accepted Answer · 2019-05-30 17:23:02Z

11

df = pd.read_excel(
    "./Data.xlsx", 
    sheet_name="Customer Care", 
    header=[0,1,2]
)

This will tell pandas to read the first three rows of the excel file as multiindex column labels.

If you want to modify the rows after you load them then set them as columns

#set the first three rows as columns
df.columns=pd.MultiIndex.from_arrays(df.iloc[0:3].values)
#delete the first three rows (because they are also the columns
df=df.iloc[3:]

edited May 30, 2019 at 17:23

answered May 30, 2019 at 16:36

iamchoosinganame

1,1201 gold badge8 silver badges17 bronze badges

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

3 Comments

piRSquared Over a year ago

And a great name you have chosen.

neanderslob Over a year ago

thanks for the response. Is there any way to set the header after I import the excel sheet? I should have specified this in the question but I need to modify those rows before they're ready to be headers.

iamchoosinganame Over a year ago

@neanderslob I've edited the answer to address this.

Collectives™ on Stack Overflow

Use multiple rows as column header for pandas

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related