Putting Many Python Pandas Dataframes to One Excel Worksheet

Putting many python pandas dataframes to one excel worksheet

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.

Writing multiple Data frame in same excel sheet one below other in python

Here is one way to do this:

dfs = [df1, df2, df3]
startrow = 0
with pd.ExcelWriter('output.xlsx') as writer:
    for df in dfs:
        df.to_excel(writer, engine="xlsxwriter", startrow=startrow)
        startrow += (df.shape[0] + 2)

Alternatively, if you want a single header at the top of the sheet:

dfs = [df1, df2, df3]
with pd.ExcelWriter('output.xlsx') as writer:
    dfs[0].to_excel(writer, engine="xlsxwriter", startrow=0)
    startrow = dfs[0].shape[0] + 2
    for df in dfs[1:]:
        df.to_excel(writer, engine="xlsxwriter", startrow=startrow, header=False)
        startrow += (df.shape[0] + 2)

multiple dataframes from pandas to one excel sheet without deleting other sheets with python

I am a big fan of xlwings which is part of the anaconda distribution. This allows you to interactively interact with EXCEL which is really handy for viewing dataframes ( eg xl.view(df) ). It will also allow you to position output exactly where you want it, and you could format cells (eg make titles bold and a different colour) should you want.

You could try something like this, where file should reference the existing workbook (including file path). Here I have assumed Sheet1 already exists in the excel workbook. If that is not the case, then add a sheet via wb.sheets.add().

import numpy as np
import pandas as pd
import xlwings as xl

df1 = pd.DataFrame({
    'date':pd.date_range(start='1/1/1990', periods=10, freq='Q'),
    'a': np.random.choice(range(100),10),
    'b': np.random.choice(range(100),10),
})

df2 = pd.DataFrame({
    'date':pd.date_range(start='1/1/2000', periods=10, freq='Q'),
    'c': np.random.choice(range(100),10),
    'd': np.random.choice(range(100),10),
})

df3 = pd.DataFrame({
    'date':pd.date_range(start='1/1/2010', periods=10, freq='Q'),
    'e': np.random.choice(range(100),10),
    'f': np.random.choice(range(100),10),
})

file = 'C:/Users/BRB/Book1.xlsx'

wb = xl.Book(file)
ws = wb.sheets('Sheet1')

ws.range('A1').value = df1
ws.range('E1').value = df2
ws.range('A14').value = df3

wb.save()
wb.close()

And if you want to suppress the index, just include the option options(index=False) as follows:

ws.range('A1').options(index=False).value = df1
ws.range('E1').options(index=False).value = df2
ws.range('A14').options(index=False).value = df3

Appending multiple dataframes to excel on different sheets

Could you try this code?

for key in df_dict:
    df = df_dict[key].copy()
    with pd.ExcelWriter('data-frames.xlsx', mode='a') as writer:  
           df.to_excel(writer, sheet_name=key)

Saving multiple pandas dataframes to Excel file with Styleframe, each one on a different sheet

Is there any particular reason you are using xlsxwriter as an engine? styleframe only supports openpyxl, so this will work:

writer = pd.ExcelWriter(f'{file_path}/output.xlsx', engine='openpyxl')
df1 = pd.DataFrame({'a':[1]})
df2 = pd.DataFrame({'b':[2]})
StyleFrame(df1).to_excel(writer, sheet_name="sheet1")
StyleFrame(df2).to_excel(writer, sheet_name="sheet2")

writer.save()

Note that you do have to explicitly call writer.save() (just like when working with pandas.DataFrame directly).

There is a shortcut for pd.ExcelWriter(f'{file_path}/output.xlsx', engine='openpyxl'):

writer = StyleFrame.ExcelWriter(f'{file_path}/output.xlsx')

Write multiple pandas dataframes to excel

You need to call writer.save() after the for construct.

Once this method is called, the writer object is effectively closed and you will not be able to use it to write more data.

writer = ExcelWriter('test_output.xlsx')
for n, df in enumerate(df_list):
    df.to_excel(writer, 'sheet%s' % str(n + 1))
writer.save()

Export multiple dataframes in one excel tab

You can loop through a list of your dfs and use pandas.ExcelWriter :

import pandas as pd

list_df = [df1, df2, df3, df4]
df1.name = 'df1'
df2.name = 'df2'
df3.name = 'df3'
df4.name = 'df4'

sr = 0

with pd.ExcelWriter('your_excel_name.xlsx') as writer:
    for df in list_df:
        df.to_excel(writer, startrow=sr, sheet_name='your_sheet_name', index=False)
        workbook  = writer.book
        worksheet = writer.sheets['Sheet1']
        title = df.name + ':'
        worksheet.write(sr-1, 0, title)
        sr += (df.shape[0] + 3)

Note : The dataframes will be put in the same sheet and separated by one single empty row. You can adjust this by changing the value of (df.shape[0] + 3).

Output (in Excel)

Edit :

If you wanna keep only one header for all the dataframes, use this :

sr = df1.shape[0]+4
with pd.ExcelWriter('output.xlsx') as writer:
    df1.to_excel(writer, startrow=1, index=False)
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']
    title = df1.name + ':'
    worksheet.write(0, 0, title)
    for df in list_df[1:]:
        df.to_excel(writer, startrow=sr, index=False, header=False)
        workbook  = writer.book
        worksheet = writer.sheets['Sheet1']
        title = df.name + ':'
        worksheet.write(sr-1, 0, title)
        sr += (df.shape[0] + 2)

---

Related Topics