Writing into a unique Excel Sheet while looping with python/pandas

Question

I need to read an Excel sheet named 'Input' make some operations and write the result into an new sheet 'Output'. I am creating a new Pandas dataframe that I need to write into my 'Output' Sheet. I need to concatenate all the results on this Sheet

Here is the structure of my code :

import pandas as pd
import math
import numpy
from openpyxl import load_workbook


df = pd.read_excel('Test.xlsx', sheet_name="Input")
for i in range(len(df)):
    if A:
    
        data = (...)
        # Create the pandas DataFrame 
        new_df = pd.DataFrame(data, columns = ['X','Y']) 
        print("New DF")
        print(new_df)

    elif B:

        data = (...)


         # Create the pandas DataFrame 
        new_df = pd.DataFrame(data, columns = ['X','Y']) 
        print("New DF")
        print(new_df)

    elif C:

        data = (...)

        # Create the pandas DataFrame 
        new_df = pd.DataFrame(data, columns = ['X','Y']) 
        print("New DF")
        print(new_df)

    else:
        print("WARNING")

    with pd.ExcelWriter("Test.xlsx", engine="openpyxl", mode="a") as writer:
        new_df.to_excel(writer, sheet_name="Output", index=False)

My code is working but it creates several Sheets Output1,Output2,Output3,...

How can I fix this to have only one Output Sheet that contains all my results ?

Thank you

Why don't you concatenate all the dataframes and write once? — Quang Hoang
– Quang Hoang, Commented Oct 23, 2020 at 13:14

Bill Huang · Accepted Answer · 2020-10-23 14:51:25Z

1

The ExcelWriter should be placed outside the for loop. Try this construct:

ls_new_df = []  # result container
for i in rng:
    # perform the task
    # ....
    # append new_df to list at last
    ls_new_df.append(new_df)

df_out = pd.concat(ls_new_df, axis=0)  # optional: .reset_index(drop=True)

# write to excel or csv at last
with pd.ExcelWriter("Test.xlsx", engine="openpyxl", mode="a") as writer:
    df_out.to_excel(writer, sheet_name="Output", index=False)

answered Oct 23, 2020 at 14:51

Bill Huang

4,6772 gold badges15 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Writing into a unique Excel Sheet while looping with python/pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related