CODEWITHSUNDEEP
pythonexcelpandasdataframeappend
Perry Hall
Perry Hall

Reputation: 11

create multiple dataframes in loop and write to excel

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.

Upvotes: 0

Views: 968

Answers (1)

nima
nima

Reputation: 1645

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

Upvotes: 1

Related Questions