writing multiple excel sheets into one for loop code

Question

sheet1=[FC_filter1,FC_filter2,FC_filter3]

with pd.ExcelWriter(path, mode='a', if_sheet_exists='replace', engine="openpyxl") as writer:
    FC_filter1.to_excel(writer, sheet_name='FC_filter1',index='False')
    FC_filter2.to_excel(writer, sheet_name='FC_filter2',index='False')
    FC_filter3.to_excel(writer, sheet_name='FC_filter3',index='False')

Can we make this code into one line using for loop instead of mentioning FC_filter1,2,3 each time ?

I'm not an expert in PANDAS, but since you are opening the Excel document in 'a' mode, there should be no problem doing something like for sheet_filter in sheet1: with pd.ExcelWriter.... The only problem is that you don't have an array of sheet titles. There is no clean way in python to convert variable identifier to string. Instead, put the strings into another array. ["FC_filter1","FC_filter2","FC_filter3"]. — Michael Sohnen
– Michael Sohnen, Commented Aug 5, 2022 at 20:24

Rabinzel · Accepted Answer · 2022-08-05 20:28:47Z

1

Use a loop through sheet1 with enumerate to name the sheets counting up, starting by 1. This assumes that the names of your filter going like 1,2,3,... If not, you could save the data as dictionary with the name as key (string) and the df as value and access them in a for loop.

sheet1=[FC_filter1,FC_filter2,FC_filter3]

with pd.ExcelWriter(path, mode='a', if_sheet_exists='replace', engine="openpyxl") as writer:
    for i, fc in enumerate(sheet1, 1):
        fc.to_excel(writer, sheet_name=f'FC_filter{i}',index='False')

answered Aug 5, 2022 at 20:28

Rabinzel

7,9533 gold badges12 silver badges31 bronze badges

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

5 Comments

dhananya s Over a year ago

It is taking fc as string,I'm getting this error, AttributeError: 'str' object has no attribute 'to_excel'

Rabinzel Over a year ago

do you have a df which is stored as variable FC_Filter1?

dhananya s Over a year ago

final_dict = dict(zip(sheet1, list(new.values()))) locals().update(final_dict) I'm storing keys of dic as variable- FC_filter 1,2,3

Rabinzel Over a year ago

this is different from the list sheet1 your provided in your question... please edit your code so we have all information we need.

dhananya s Over a year ago

with pd.ExcelWriter(path, mode='a', if_sheet_exists='replace', engine="openpyxl") as writer: for m, fc in enumerate(sheet1, 1): pd.DataFrame(result[0]).to_excel(writer, sheet_name=f'FC_filter{m}',index='False') Insteading of giving result[0], can we automate this with len()?

Collectives™ on Stack Overflow

writing multiple excel sheets into one for loop code

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related