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
– Zaraki Kenpachi, Commented Feb 9, 2020 at 12:54
"loop will overwrite the existing sheet each time.": First aggregate in a df then write once — stovfl
– stovfl, Commented Feb 9, 2020 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
– Jon Clements, Commented Feb 9, 2020 at 12:59

nima · Accepted Answer · 2020-02-09 12:59:42Z

1

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)}')

answered Feb 9, 2020 at 12:59

nima

1,64510 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

create multiple dataframes in loop and write to excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related