In pandas the inner most index must label each row.

df = df.set_index('Customer ID', append=True).swaplevel(0,1)

Output:

                    Category    VALUE
Customer ID                          
HETO90      0      Baby Sets   1000.0
            1  Girls Dresses   5356.0
            2  Girls Jumpers   2822.0
            3      Girls Top  13398.0
            4         Shorts   7590.0

You could use the Series.fillna method to forword-fill in the NaN values:

df.index = pd.Series(df.index).fillna(method='ffill')

For example,

In [42]: df
Out[42]: 
       Sample    CD4   CD8
Day 1    8311  17.30  6.44
NaN      8312  13.60  3.50
NaN      8321  19.80  5.88
NaN      8322  13.50  4.09
Day 2    8311  16.00  4.92
NaN      8312   5.67  2.28
NaN      8321  13.00  4.34
NaN      8322  10.60  1.95

[8 rows x 3 columns]

In [43]: df.index = pd.Series(df.index).fillna(method='ffill')

In [44]: df
Out[44]: 
       Sample    CD4   CD8
Day 1    8311  17.30  6.44
Day 1    8312  13.60  3.50
Day 1    8321  19.80  5.88
Day 1    8322  13.50  4.09
Day 2    8311  16.00  4.92
Day 2    8312   5.67  2.28
Day 2    8321  13.00  4.34
Day 2    8322  10.60  1.95

[8 rows x 3 columns]

You can use pandas.DataFrame.pivot_table to merge the duplicated values then Styler.applymap_index with the standard CSS styles to get the desired Excel spreadsheet format.

Try this :

import pandas as pd

d1 = {"URL": ["https://example.com/1540"], "data": [2.5, 12.0, 5.0]}
d2 = {"URL": ["https://example.com/4541"], "data": [1.0, 8.0, 0.0]}

def stylize_df(_):
    return "font-weight: normal; text-align: center; vertical-align: middle;"

(
    pd.DataFrame([d1, d2])
        .apply(pd.Series.explode)
        .pivot_table(index=["URL", "data"], sort=False)
        .style.applymap_index(stylize_df)
        .to_excel("output_file.xlsx", startrow=-1)
)

# Output (in Excel) :