XlsxWriter python to write a dataframe in a specific cell

Question

One can write data to a specific cell, using:

xlsworksheet.write('B5', 'Hello')

But if you try to write a whole dataframe, df2, starting in cell 'B5':

xlsworksheet.write('B5', df2)

TypeError: Unsupported type <class 'pandas.core.frame.DataFrame'> in write()

What should be the way to write a whole dataframe starting in a specific cell?

The reason I ask this is because I need to paste 2 different pandas dataframes in the same sheet in excel.

Particulate

Answer 1

XlsxWriter doesn't write Pandas dataframes directly. However, it is integrated with Pandas so you can do it the other way around.

Here is a small example of writing 2 dataframes to the same worksheet using the startrow parameter of Pandas to_excel:

import pandas as pd

df1 = pd.DataFrame({'Data': [10, 20, 30, 40]})
df2 = pd.DataFrame({'Data': [13, 24, 35, 46]})

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

df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet1', startrow=6)

Output:

You can turn off the column and row indexes using other to_excel options.

Pathology

Thanks @Pathology Just what I was looking for. – Particulate

You may want to add writer.close() at the end to unlock the created file. A new pythoner may think the code failed. – Electrodynamometer

This code remove the all other sheets present in workbook. How can we write to only Sheet1 while keeping Sheet2,3 etc as is. – Psalm

Answer 2

You write strings to Excel. To convert DataFrames to strings, there you have several options of which it looks like to_csv is your best bet:

>>> string1 = df1.to_csv(writer)
>>> xlsworksheet.write('B5',string1)
>>> string2 = df2.to_csv(writer,'Sheet2')
>>> xlsworksheet.write('C5', string2)

Note this will write your entire dataframe to one cell. The only way I know of to write a frame to individual cells in an Excel sheet is to combine them and then use to_excel:

>>> writer = ExcelWriter('output.xlsx')
>>> frame = pd.concat(df1, df2)
>>> frame.to_excel(writer,'Sheet1')

Hope this helps...

Internment

To_excel(writer, 'Sheet1') remove the all other sheets present in workbook. How can we write to only Sheet1 while keeping existing worksheets ? – Psalm