CODEWITHSUNDEEP
pythonpandasexceldataframe
JakeP
JakeP

Reputation: 117

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

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

Upvotes: 1

Views: 126

Answers (1)

Timeless
Timeless

Reputation: 37787

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 :

enter image description here

Upvotes: 2

Related Questions