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

Question

Reproduced code :

import numpy as np 
import pandas as pd

# Create a Dataframe
df1 = pd.DataFrame(np.random.rand(8).reshape(4,2),columns=['X','Y']);print(df1.shape)
df2 = pd.DataFrame(np.random.rand(6).reshape(3,2),columns=['X','Y']);print(df2.shape)
df3 = pd.DataFrame(np.random.rand(10).reshape(5,2),columns=['X','Y']);print(df3.shape)

These above three data frame which has same columns but different rows , need to written in same excel sheet one below other . Expected Output as shown below.

Note: No of dataframe may vary

stackoverflow.com/q/32957441/6361531

Scott Boston
– Scott Boston

2021-05-13 13:44:34 +00:00
Commented May 13, 2021 at 13:44 — Scott Boston
– Scott Boston, Commented May 13, 2021 at 13:44

Laurent · Accepted Answer · 2022-10-18 18:25:22Z

7

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)

edited Oct 18, 2022 at 18:25

answered May 13, 2021 at 13:41

Laurent

13.7k7 gold badges30 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aragorn64 Over a year ago

Is it possible to add a single header at the top of the generated sheet? I'm getting a header between every DataFrame. I have tried to set the option 'header=False', but it remove every header.

Laurent Over a year ago

@Aragon64 Hi, yes, see my extended my answer. Cheers.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related