I have an Excel file containing credit spread calculations for different credit ratings. The structure includes merged cells which create multi-level column headers. Here is an example of the structure:

USD All Sectors A  USD All Sectors A  USD All Sectors A
Level  Date        1D                7D                14D
0      05/30/2023  66.42             66.42             66.42
0      05/31/2023  65.95             65.95             65.95

I need to transform this data into a long format using pandas so that it looks like this:

DATO        INDEKS            TENOR  NIVEAU
05/30/2023  USD All Sectors A  1D     66.42
05/31/2023  USD All Sectors A  1D     65.95
05/30/2023  USD All Sectors A  7D     66.42
05/31/2023  USD All Sectors A  7D     65.95
05/30/2023  USD All Sectors A  14D    66.42
05/31/2023  USD All Sectors A  14D    65.95```

import pandas as pd

Load the data from the Excel file

data = pd.read_excel(‘credit_spreads.xlsx’, header=[0, 1], skiprows=12)

df_stacked = df.stack(level=1).reset_index()
print(df_stacked.head(10))

Which gives the following output (a small part of the output):

  level_0 level_1  USD All Sectors A  USD All Sectors B  USD All Sectors BB  
0        0    108M           129.6630           585.9322            335.4587   
1        0    120M           133.1738           585.9688            335.4797   
2        0     12M            66.4248           581.6899            333.0146   

However, I am having trouble getting the multi-index headers to work correctly, and the reshaped data is not in the desired format. The main issue seems to be handling the correct multiindex headers.

Is the stack function the corret function to use here? Or should i go with melt or pivot isntead?