Python create dataframes from a function using ExcelWriter

Question

I am trying to create 3 different dataframes to output in my excel file in 3 separate worksheet called df, df_OK, df_KO. However the code below only outputs df and is not creating the other 2 dataframes df_OK, df_KO to have in the same Excel file but in 2 separate worksheets.

Any suggestions? Thanks

class blah:
    def __init__(self, path, file_in, file_out):
        self.path = path
        self.file_in = file_in
        self.file_out = file_out
        
    def process_file(self):
        df = pd.read_excel(self.path + self.file_in)
        df_OK = df.loc[df['Status'] == 'OK']
        df_KO = df.loc[df['Status'] == 'KO']
        df_OK.loc['Total'] = df_OK[['Price']].sum(axis=0)

        writer = pd.ExcelWriter(self.path + self.file_out, engine='xlsxwriter')
        
        dfs = {
            'All': df, 
            'OK': df_OK, 
            'KO': df_KO
            }      
         
        for sheet_name in dfs.keys():
            dfs[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
            writer.save()
            
b = blah('C:/Users/......./',
         'path...',
         'file_in....', 
         'file_out...')
        
b.process_file()

Martin · Accepted Answer · 2020-10-02 10:13:40Z

1

It is because you overwrite the same Excel file in every iteration of your for sheet_name in dfs.keys() loop. So every time you write an Excel file with only a single sheet to the same filename, thus overwriting the previous document.

You should move the writer.save() outside your loop like so:

for sheet_name in dfs.keys():
    dfs[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
writer.save()

answered Oct 2, 2020 at 10:13

Martin

3833 silver badges8 bronze badges

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

1 Comment

Caiotru Over a year ago

Thanks @Martin you just made my day!

Collectives™ on Stack Overflow

Python create dataframes from a function using ExcelWriter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related