create multiple dataframes in loop and write to excel

Question

I am running a loop over a function that produces a dataframe, that looks like this:

        Prd1    Prd2     Prd3    Prd4    Prd5    Prd6
Loc1    0        0        0        0       0       0
Loc2    0        0        0        0       0       0
Loc3    0      30.61      0        0   319.58   430.87  
Loc4    0        0        0        0       0    120.73
Loc5    0        0        0        0       0       0
Loc6    78.21  822.36     0        0       0       0

I want to run the function multiple times, to produce a series of dataframes to then analyse or visualise evolution over time.For this it would be ideal to have them all in one excel workbook. However I am struggling with this. I have tried

for case in CASES:
    B= A[case]
    result = function(B)
    print(result)
    with pd.ExcelWriter('results.xlsx') as writer:  # doctest: +SKIP
        result.to_excel(writer, sheet_name='case_'+str(case))

my hope was that this would write the different results into the same spreadsheet. However as a result the spreadsheet only contains a tab with the last case. I can see how the above being within the CASES loop will overwrite the existing sheet each time. Is there a function to preserve the sheets that already exist and "append" any new sheet to the existing workbook? Thanks in advance. regards P.

you need all you created data frames save into single csv file? — Zaraki Kenpachi, Feb 09 '20 at 12:54
***"loop will overwrite the existing sheet each time."***: First aggregate in a `df` then **write once** — stovfl, Feb 09 '20 at 12:55
Move the opening of the `pd.ExcelWriter` outside the for loop so it gets opened and closed once with sheets in it, rather than overwriting it every iteration — Jon Clements, Feb 09 '20 at 12:59

score 1 · Answer 1 · answered Feb 09 '20 at 12:59

According to this SO thread, you can retrieve list of sheet names before starting your loop and check for existing sheets and skip them.

xl = pd.ExcelFile('results.xlsx')
existing_sheets = xl.sheet_names

for case in CASES:
    if f'cast_{str(case)}' in existing_sheets:
        continue
    B= A[case]
    result = function(B)
    print(result)
    with pd.ExcelWriter('results.xlsx') as writer:  # doctest: +SKIP
        result.to_excel(writer, sheet_name=f'case_{str(case)}')

create multiple dataframes in loop and write to excel

1 Answers1