0

I can put multiple dataframes into one xlsx sheet and put multiple dataframes across separate tabs sheet as clearly explained here Putting many python pandas dataframes to one excel worksheet. However I cannot figure out how to do both in one go without generating the files first and then combine them together.

I have 4 dataframes and I world like to have 2 of them in one sheet and the other 2 in another sheet.

df1 = pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]})
df1a = pd.DataFrame({'col1a': [1, 2], 'col2a': [3, 4]})
df2 = pd.DataFrame({'col3': [1, 2], 'col4': [3, 4]})
df2a = pd.DataFrame({'col3a': [1, 2], 'col4a': [3, 4]})


dic = {'sheet_1': [df1, df1a], 
       'sheet_2': [df2, df2a]}

def multiple_dfs(df_list, sheets):
    writer = pd.ExcelWriter('test1.xlsx', engine='xlsxwriter')   
    row = 0
    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets)   
        row = row + len(dataframe.index) + 4
    writer.save()


for k, v in dic.items(): 
    multiple_dfs(v, k)
Caiotru
  • 335
  • 1
  • 10
  • Clearly not the answer you're expecting, but isn't it possible to just concatenate (in column axis) df1 and df1a, and df2 and df2a. So you now have 2 dataframes, for 2 sheets, and get back to a problem you know how to solve – Adept Nov 19 '21 at 10:38
  • Unfortunately I need to keep both tables in the sheet separated from each other – Caiotru Nov 19 '21 at 10:40
  • Again not a clean way to do it, but add one or a few empty columns at the end of the 1st dataframe before to concat. So in this way you'll have the concatenation of your 2 dataframes, the first one having "empty columns" in the end, showing a separation in Excel. (ofc when I say concatenate, that means having columns for df1 and next to them, columns for df1a, I don't mean to add df1a rows under df1) – Adept Nov 19 '21 at 10:43

0 Answers0