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.

Answer 1

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:]