Putting many python pandas dataframes to one excel worksheet

Question

It is quite easy to add many pandas dataframes into excel work book as long as it is different worksheets. But, it is somewhat tricky to get many dataframes into one worksheet if you want to use pandas built-in df.to_excel functionality.

# Creating Excel Writer Object from Pandas  
writer = pd.ExcelWriter('test.xlsx',engine='xlsxwriter')   
workbook=writer.book
worksheet=workbook.add_worksheet('Validation') 
df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)   
another_df.to_excel(writer,sheet_name='Validation',startrow=20, startcol=0) 

The above code won't work. You will get the error of

 Sheetname 'Validation', with case ignored, is already in use.

Now, I have experimented enough that I found a way to make it work.

writer = pd.ExcelWriter('test.xlsx',engine='xlsxwriter')   # Creating Excel Writer Object from Pandas  
workbook=writer.book
df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)   
another_df.to_excel(writer,sheet_name='Validation',startrow=20, startcol=0) 

This will work. So, my purpose of posting this question on stackoverflow is twofold. Firstly, I hope this will help someone if he/she is trying to put many dataframes into a single work sheet at excel.

Secondly, Can someone help me understand the difference between those two blocks of code? It appears to me that they are pretty much the same except the first block of code created worksheet called "Validation" in advance while the second does not. I get that part.

What I don't understand is why should it be any different ? Even if I don't create the worksheet in advance, this line, the line right before the last one,

 df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)  

will create a worksheet anyway. Consequently, by the time we reached the last line of code the worksheet "Validation" is already created as well in the second block of code. So, my question basically, why should the second block of code work while the first doesn't?

Please also share if there is another way to put many dataframes into excel using the built-in df.to_excel functionality !!

Taunt

Answer 1

To create the Worksheet in advance, you need to add the created sheet to the sheets dict:

writer.sheets['Validation'] = worksheet

Using your original code:

# Creating Excel Writer Object from Pandas  
writer = pd.ExcelWriter('test.xlsx',engine='xlsxwriter')   
workbook=writer.book
worksheet=workbook.add_worksheet('Validation')
writer.sheets['Validation'] = worksheet
df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)   
another_df.to_excel(writer,sheet_name='Validation',startrow=20, startcol=0) 

---

Explanation

If we look at the pandas function to_excel, it uses the writer's write_cells function:

excel_writer.write_cells(formatted_cells, sheet_name, startrow=startrow, startcol=startcol)

So looking at the write_cells function for xlsxwriter:

def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
    # Write the frame cells using xlsxwriter.
    sheet_name = self._get_sheet_name(sheet_name)
    if sheet_name in self.sheets:
        wks = self.sheets[sheet_name]
    else:
        wks = self.book.add_worksheet(sheet_name)
        self.sheets[sheet_name] = wks

Here we can see that it checks for sheet_name in self.sheets, and so it needs to be added there as well.

Radman

Great! But typo: it should read writer.sheets['Validation'] = worksheet, not sheet. – Correlation

As per docs, there is no need for creating sheets. xlsxwriter.readthedocs.io/example_pandas_multiple.html – Writein

@AdarshMadrecha from my understanding that puts each dataframe on a different worksheet, whereas this question is about putting multiple dataframes in the same worksheet – Radman

agree with @Radman – Writein

@Radman Using the code snippet I get a empty, zero sized file. I have to call writer.close() afterwards. Might be dependt on Pandas version, but I think it should be added to the answer. I could not edit it myself due to "edit queue is full". – Rosser

Answer 2

user3817518: "Please also share if there is another way to put many dataframes into excel using the built-in df.to_excel functionality !!"

Here's my attempt:

Easy way to put together a lot of dataframes on just one sheet or across multiple tabs. Let me know if this works!

-- To test, just run the sample dataframes and the second and third portion of code.

Sample dataframes

import pandas as pd
import numpy as np

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

Put multiple dataframes into one xlsx sheet

# funtion
def multiple_dfs(df_list, sheets, file_name, spaces):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
    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
    writer.save()

# list of dataframes
dfs = [df,df1,df2]

# run function
multiple_dfs(dfs, 'Validation', 'test1.xlsx', 1)

Put multiple dataframes across separate tabs/sheets

# function
def dfs_tabs(df_list, sheet_list, file_name):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
    for dataframe, sheet in zip(df_list, sheet_list):
        dataframe.to_excel(writer, sheet_name=sheet, startrow=0 , startcol=0)   
    writer.save()

# list of dataframes and sheet names
dfs = [df, df1, df2]
sheets = ['df','df1','df2']    

# run function
dfs_tabs(dfs, sheets, 'multi-test.xlsx')

Commonly

Unfortunately using this code I receive this error: – Spancake

AttributeError: 'unicode' object has no attribute 'to_excel' – Spancake

Can you help me? Thanks – Spancake

Excellent post! Exactly what I was looking for and then some. : ) – Seaward

@ManuelZompetta do you have the most up to date version of Pandas? 'to_excel' should work - pandas.pydata.org/pandas-docs/stable/generated/… – Commonly

@Seaward happy to help! :) – Commonly

I can confirm the codes work in my VS Code, Python 3.9.6 M1 Mac – Roadside

Is there a way we can mix up both? Like I have few dataframes which should go into one sheet and few dataframes which should go into another sheet. Can we do this? – Lodged

Just as a reference to get rid of a FutureWarning message: https://mcmap.net/q/211893/-futurewarning-save-is-not-part-of-the-public-api-in-python – Rallentando

Answer 3

Use with - you don't have to call writer.save() or writer.close() explicitly.

Also, it automatically manages workbook.close(), if you use workbook=writer.book.
(Other answers forgot to do this, and this happens quite often because we are human ;)

import pandas as pd 

df = pd.DataFrame(data={'col1':[9,3,4,5,1,1,1,1], 'col2':[6,7,8,9,5,5,5,5]}) 
df2 = pd.DataFrame(data={'col1':[25,35,45,55,65,75], 'col2':[61,71,81,91,21,31]}) 

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='testSheetJ', startrow=1, startcol=0)
    df2.to_excel(writer, sheet_name='testSheetJ', startrow=1+len(df)+3, startcol=0) 

Result:

---

If, you want to write the dataframe in different sheets, just do this:

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2') 

Alar

Answer 4

The answer by Adrian can be simplified as follows

writer = pd.ExcelWriter('test.xlsx',engine='xlsxwriter')
df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)
another_df.to_excel(writer,sheet_name='Validation',startrow=20, startcol=0)

Works for pandas 0.25.3 with python 3.7.6

Syllable

don't forget writer.save() – Rose

Answer 5

I would be more inclined to concatenate the dataframes first and then turn that dataframe into an excel format. To put two dataframes together side-by-side (as opposed to one above the other) do this:

writer = pd.ExcelWriter('test.xlsx',engine='xlsxwriter')   # Creating Excel Writer Object from Pandas  
workbook=writer.book
df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)   
new_df = pd.concat([df, another_df], axis=1)
new_df.to_excel(writer,sheet_name='Validation',startrow=0 , startcol=0)   

Mullah

Answer 6

To write multiple dataframes to a single excel sheet

# Position the dataframes in the worksheet.
df1.to_excel(writer, sheet_name='Sheet1')  # Default position, cell A1.
df2.to_excel(writer, sheet_name='Sheet1', startcol=3)
df3.to_excel(writer, sheet_name='Sheet1', startrow=6)

# Write the dataframe without the header and index.
df4.to_excel(writer, sheet_name='Sheet1',startrow=7, startcol=4, header=False, index=False)

Kurtiskurtosis