Write Dataframe outputs from a for loop to Excel without overwriting Pandas

Question

I have an extensive set of code that results in 6 DataFrames per for-loop run. The column names are tailored to what vehicle I have running through the loops so the column names are different but the sizes of the dataframes are the same. I want to print a couple dataframes to the same sheet but I have issues with data being overwritten.

Here is a simple example code:

df4.columns = ['Car', 'Amount', 'ColorCode', 'TireSize', 'AreaCode']

df = df4.copy()
df2 = df4.copy()

df2.columns = ['Truck', 'Amount', 'ColorCode', 'TireSize', 'AreaCode']


truck = df2.copy()
car = df.copy()

df_list = list()
df_list.append(car)
df_list.append(truck)

 with pd.ExcelWriter(opf) as writer:
        for i in len(df_list):
             df_list[i].to_excel(writer,sheet_name = 'CarComparison', index = False)

When I try to print to excel, the dataframe is overwritten by the last one. I want the sheet to look like this:

    Car Amount  ColorCode   TireSize    AreaCode
    2      4        2          4           2
    4      8        4          8           4
    6      12       6          12          6
    8      16       8          16          8
    10     20       10         20          10

    Truck   Amount  ColorCode   TireSize    AreaCode
    2          4         2         4          2
    4          8         4         8          4
    6          12        6         12         6
    8          16        8         16         8
    10         20       10         20         10

add mode='a' to indicate append. By default it is set to write: See the docs — It_is_Chris
– It_is_Chris, Commented Jan 12, 2023 at 20:53
This did not work. It added numerous worksheets of individual dataframes instead of looking like the intended results. — JakeP
– JakeP, Commented Jan 12, 2023 at 21:38
Did you also add the if_sheet_exists param? Please read the documentation. — It_is_Chris
– It_is_Chris, Commented Jan 12, 2023 at 21:43

Timeless · Accepted Answer · 2023-01-19 16:57:55Z

2

IIUC, you can use the startrow parameter of pandas.DataFrame.to_excel :

# Number of blank rows between dataframes
B = 1

df_list = [car, truck]

 with pd.ExcelWriter(opf) as writer:
    startrow=0
    for idx, df in enumerate(df_list):
        df.to_excel(writer, sheet_name="CarComparison", startrow=startrow, index=False)
        startrow += len(df)+1+B

# Output :

edited Jan 19, 2023 at 16:57

answered Jan 12, 2023 at 21:39

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

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

4 Comments

JakeP Over a year ago

A little tweaking, but this worked thanks a lot!

JakeP Over a year ago

So I have an issue when the dataframes are different length. If the first two dataframes are 9 rows and the third is 2 rows and then the fourth is 5 rows, the 2 rows from the third dataframe is written in place of 2 of the rows of the fourth dataframe. Basically I need it to look at the previous dataframe and start from there.

JakeP Over a year ago

So is there anyway for the next dataframe to look at the previous one and write to Excel with a row in between no matter length of dataframe?

Timeless Over a year ago

I updated my answer to handle that.

Collectives™ on Stack Overflow

Write Dataframe outputs from a for loop to Excel without overwriting Pandas

1 Answer 1

# Output :

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

# Output :

4 Comments

Related