CODEWITHSUNDEEP
pythonexcelpandasdataframe
Mithril
Mithril

Reputation: 13788

How do I write multiple dataframes to one excel sheet?

I know how to write multiple dataframes to different sheets in one excel.

But now I want to write two different dataframes in one excel horizonally.

For example:

enter image description here

The only way I know is 

pd.concat((df1.reset_index() , df2.reset_index(), axis=1).to_excel('1.xlsx')

But this is not convenient.

Reason:

  1. concat two df would mess the order of columns , have to do re order maually.
  2. have to reset index(df1 is datetime index, df2 is custom string index), which would result ugly presentation .
  3. adjusting the postion is diffcult, what if I need one dataframe in left and two dataframes placed vertically in right ?

PS: it is just inconvenient to use pd.concat , because need much effort to make that excel look beautiful.

I also tried to use pd.ExcelWriter which can assign startcol :

  1. job 1: 

    file_name = 'xxx.xlsx'
writer = pd.ExcelWriter(file_name, engine='xlsxwriter', 
                    datetime_format='yyyy-mm-dd',
                    date_format='yyyy-mm-dd')
df1.to_excel(writer, sheet_name=sheet_name, startrow=0 , startcol=0)

  2. job 2 

    file_name = 'xxx.xlsx'
writer = pd.ExcelWriter(file_name, engine='xlsxwriter', 
                    datetime_format='yyyy-mm-dd',
                    date_format='yyyy-mm-dd')
df2.to_excel(writer, sheet_name=sheet_name, startrow=0 , startcol=df1.shape[1]+2)

But this overwrite the excel ....

I hope there is a easy way to write a dataframe in position startrow, startcol without overwirting the file ?

Upvotes: 1

Views: 2183

Answers (2)

mouwsy
mouwsy

Reputation: 1933

You can use xlwings: Get the last column of df1 then go two columns further right with .offset(column_offset=2) and then insert df2. It's flexible and easy to use (no need to use pd.concat):

import xlwings as xw
import pandas as pd

df1 = pd._testing.makeDataFrame()
df2 = pd._testing.makeDataFrame()

with xw.App(visible=False) as app:
    wb = xw.Book()
    wb.sheets[0].range("A1").value = df1
    wb.sheets[0].range("A1").expand("right")[-1].offset(column_offset=2).value = df2
    wb.save()
    wb.close()

Upvotes: 0

Rauf Bhat
Rauf Bhat

Reputation: 61

Create columns in df1 and df2 with values as row numbers. This way you will retain the order of the two dataframes.

2) Merge the two dataframes on the the columns created in step one

3) Push this to excel

df1['key']= df1.reset_index().index

df2['key'] = df2.reset_index().index

pd.merge(df1, df2, how='outer',on='key').to_excel('1.xlsx')

Upvotes: 1

Related Questions