Python Pandas: How to make additional columns in excell sheet through dataframes + formatting excel sheet?

The main thing I wanted to see was how I can add additional columns into the excel sheet through use of dataframes. However, I also wanted to include some formatting options such as merging the cells in the top left and aligning the data points. This is what I mean:

Picture 1 (What my code currently outputs):

Picture 2 (What I want it to output):

Mainly, I want to be able to dynamically change the number of columns per category. So if I have additional data, the code should be able to create any amount of columns needed as to only 2. Also just formatting so the table will look clean (like the Type of Data Merged Cell and centering the data).

Here's the code for picture 1:

import pandas as pd

# Create a Pandas dataframe from the data.
df1 = pd.DataFrame([['a', 'b'], ['c', 'd']],
                    index=['row 1', 'row 2'],
                    columns=['col 1', 'col 2'])

df2 = pd.DataFrame([['a', 'b'], ['c', 'd']],
                    index=['row 1', 'row 2'],
                    columns=['col 1', 'col 2'])

df3 = pd.DataFrame([['a', 'b'], ['c', 'd']],
                    index=['row 1', 'row 2'],
                    columns=['col 1', 'col 2'])

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Concatenate data frames for seperate data types
out1 = (
    pd.concat({
        'data type 1': df1,
        'data type 2': pd.DataFrame([[1, 2], [3, 4]], columns=df1.columns, index=df1.index)
    }).swaplevel().sort_index()
)

out2 = (
    pd.concat({
        'data type 1': df2,
        'data type 2': pd.DataFrame([[1, 2], [3, 4]], columns=df2.columns, index=df2.index)
    }).swaplevel().sort_index()
)

out3 = (
    pd.concat({
        'data type 1': df3,
        'data type 2': pd.DataFrame([[1, 2], [3, 4]], columns=df3.columns, index=df3.index)
    }).swaplevel().sort_index()
)

# Convert the dataframe to an XlsxWriter Excel object.
out1.to_excel(writer, sheet_name='Sheet1')
out2.to_excel(writer, sheet_name='Sheet1', startrow=6)
out3.to_excel(writer, sheet_name='Sheet1', startrow=12)

# Close the Pandas Excel writer and output the Excel file.
writer.close()

I'm assuming for extra columns I need to do something similar when I concatenate the index's for additional rows?

You can define a column as a variable then save it to the excel file, example:

df = pd.read_csv('sample_submission.csv')

df['Transported'] = Guess_list

df.to_csv('sample_submission.csv', index=False)

This saves a list to the column 'Transported'.

If you want to create a new column you could do

df = pd.read_csv('sample_submission.csv')

df.insert(1, 'Column 2', Guess_list)

df.to_csv('sample_submission.csv', index=False)