Pandas combining rows as header info-CodePudding

This is how I am reading and creating the dataframe with pandas

def get_sheet_data(sheet_name='SomeName'):
    df = pd.read_excel(f'{full_q_name}',
                       sheet_name=sheet_name,
                       header=[0,1],
                       index_col=0)#.fillna(method='ffill')
    df = df.swapaxes(axis1="index", axis2="columns")
    return df.set_index('Product Code')

printing this tabularized gives me(this potentially will have hundreds of columns):

I cant seem to add those first two rows into the header, I've tried:

python:pandas - How to combine first two rows of pandas dataframe to dataframe header?

https://stackoverflow.com/questions/59837241/combine-first-row-and-header-with-pandas

and I'm failing at each point. I think its because of the multiindex, not necessarily the axis swap? But using:

https://pandas.pydata.org/docs/reference/api/pandas.MultiIndex.html

is kind of going over my head right now. Please help me add those two rows into the header?

The output of df.columns is massive so Ive cut it down alot:

Index(['Product Code','Product Narrative\nHigh-level service description','Product Name','Huawei Product ID','Type','Bill Cycle Alignment',nan,'Stackable',nan,

and ends with:

nan], dtype='object')

CodePudding user response：

We Create new column names and set them to df.columns, the new column names are generated by joining the 3 Multindex headers and the 1st row of the DataFrame.

df.columns = ['_'.join(i) for i in zip(df.columns.get_level_values(0).tolist(), df.columns.get_level_values(1).tolist(), df.iloc[0,:].replace(np.nan,'').tolist())]