pandas_excel_tips.py

import pandas as pd
import numpy as np

randn = np.random.randn
df = pd.DataFrame(randn(15, 20))
df1 = pd.DataFrame(randn(10, 5))
df2 = pd.DataFrame(randn(5, 10))

writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')


def multiple_dfs_to_sheet(df_list, sheets, spaces):
    row = 0
    for dataframe in df_list:
        dataframe.to_excel(writer, sheet_name=sheets, startrow=row, startcol=0)
        row = row + len(dataframe.index) + spaces + 1


def dfs_to_multiple_sheet(df_list, sheet_list):
    for dataframe, sheet in zip(df_list, sheet_list):
        dataframe.to_excel(writer, sheet_name=sheet, startrow=0, startcol=0)


dfs = [df, df1, df2]
sheets = ['df', 'df1', 'df2']

multiple_dfs_to_sheet(dfs, 'Validation', 1)
dfs_to_multiple_sheet(dfs, sheets)
writer.save()