python - pandas read from excel with multilevel columnnames and concat

Question

I have an excel with the following format

Product      Product_type    
ID           ColumnA        ColumnB        Start Date
10              12              14         01/01/2020 

I need to be able to read the excel into a pandas dataframe and concat the multilevel columns so the output is

Product_ID     Product_type_ColumnA        ColumnB        Start Date
10              12                            14          01/01/2020 

in some excel the top level columns could be one or more. It only needs to concat with the column name directly below it.

for example input

Product          
ID           ColumnA        ColumnB        Start Date
10    

and required output

Product_ID      ColumnA        ColumnB        Start Date
10              12              14         01/01/2020 

question from:https://stackoverflow.com/questions/65887092/pandas-read-from-excel-with-multilevel-columnnames-and-concat

Answer 1

This isn't very elegant but you could just import it as normal, then append the first row to the column names, then delete the first row:

# Import df (this would be from excel for you)
df = pd.DataFrame([[1,2,3],[4,5,6]], columns = ['ID', '', ''])

# Pick off first row and make into a list
col_names = df.values.tolist()[0]

# Put together the new column names
new_cols = []
for i in range(len(df.columns)):
    new_cols.append(str(df.columns[i]) + '_' + str(col_names[i]))

# Replace the column names
df.columns = new_cols

# Remove the first row of the df
df = df[1:]