Export many small DataFrames to a single Excel worksheet

Question

With this code, it is possible to export every data frame in a new worksheet iterating data frames list:

def save_xls(list_dfs, xls_path):
    writer = ExcelWriter(xls_path)
    for n, df in enumerate(list_dfs):
        df.to_excel(writer,'sheet_dati%s' % n)
    writer.save()
save_xls(list_dfs, xls_path)

But its possible to export two or more data frames in a single worksheet?

score 12 · Answer 1 · answered Feb 17 '14 at 17:59

Something like this: ?

from pandas import ExcelWriter
def dfs2xlsx(list_dfs,xls_path = None):
    #save_xls([df1,df2],'output1.xlsx')
    if xls_path == None :
        xls_path = '~tmp.xlsx'
    writer = ExcelWriter(xls_path)
    i=0
    for n, df in enumerate(list_dfs):
        df.to_excel(writer,'Sheet1',startcol=i,startrow =2)
        i+= len(df.columns) + 2
    writer.save()
    os.system('start excel.exe %s' %(writer.path ))

Great answer. Thanks very much. – kawing-chiu Feb 20 '16 at 15:17 — kawing-chiu, Feb 20 '16 at 15:17

root · Accepted Answer · 2013-01-09T13:31:43.530

5

If you want to have multiple DataFrames on the same sheet how would you combine them? Instead merge, join, or concatenate them into a single DataFrame beforehand as pandas gives you multiple ways of doing so. And then do your export.

edited Jan 09 '13 at 13:31

answered Jan 09 '13 at 13:25

root

76,608
25
108
120

score 3 · Answer 3 · answered Jan 09 '13 at 13:25

3

I think you are going to be better doing a concat of these DataFrames before exporting (via to_excel). That is:

pd.concat(list_dfs).to_excel(xls_path)

answered Jan 09 '13 at 13:25

Andy Hayden

359,921
101
625
535

Thanks, but the integration of data frames is messy (they are merged together and not in sequence) when exported on the sheet. Unfortunately I think I should have to study a lot merge, join, concatenate functions in order to have usable and correctly formatted data on the sheet. Anyway this command is useful too. Thanks. M – Manuel Zompetta Jan 09 '13 at 15:49
@ManuelZompetta merging in pandas itself isn't too tricky, best of luck :) – Andy Hayden Jan 09 '13 at 15:53
+1 for Manuel, before the pandas.ExcelWriter has a write-rows method, which could be used for this purpose. But I noticed that recent version removes this method. I would argue sometimes people have some dataframs that have good reason to be separated. – wiswit Jan 31 '13 at 22:27
1

more comments: I just notice now you have startrow and startcol in the pa.DataFrame.to_excel method, so one probale workaround is to calculate the length that needed. – wiswit Jan 31 '13 at 22:35

score 3 · Answer 4 · answered Dec 28 '15 at 21:46

3

+1 for ashkan's answer. This method allows you to write the headers multiple times, and do things like leaving space between the individual dataframes much more easily than inserting blank rows into one larger concat'd dataframe. For instance:

writer = pd.ExcelWriter(os.path.join(filepath, filename))
for item in list_of_dataframes:
    item.to_excel(writer, "sheetname", startcol=0, startrow=n)
    n += len(item.index) + 2
writer.save()

This allows you to write multiple (possibly different or unrelated) dataframes into one excel sheet, one after another, leaving a blank line between each as you go down the page.

answered Dec 28 '15 at 21:46

AndrewH

234
1
6

5

This post is _awfully_ close to being commentary on another answer, rather than it's own stand-alone post. If you've got additional information to add to an answer, that's fine. But just commenting on a post is what comments are for. – gunr2171 Dec 28 '15 at 21:54
1

As a new member I was unable to comment, I tried that first. Noted for next time :) – AndrewH Dec 28 '15 at 22:23

Export many small DataFrames to a single Excel worksheet

4 Answers4