Append data frame and text to existing excel file sheets in a for loop using python

Question

I'm trying to write a functional code that will read data from multiple excel sheets, carry out some calculations, and then append a summary to the bottom of data in the excel sheet where the data was read from.

An example excel sheet data or data frame:

Required output: summary output starts 2 rows below the original data as below

ID  APP
1   20
2   50
3   79
4   34
5   7
6   5
7   3
8   78

Sumary:
Total=276

My attempt:

import pandas as pd
from excel import append_df_to_excel

path = 'data.xlsx'
Exls = pd.ExcelFile(path, engine='openpyxl')

for sheet in Exls.sheet_names:

    try:
   
        df = pd.read_excel(Exls,sheet_name=sheet)
        res=df.groupby['App'].sum
    
        writer = pd.ExcelWriter('data.xlsx', engine='xlsxwriter')
        df.to_excel(writer, res, sheet_name='Sheet1', startrow = 1, index=False)
        workbook  = writer.book
        worksheet = writer.sheets[data']

        text = 'summary'
        worksheet.write(0, 0, text)
        writer.save()

    except Exception:  
        continue

This code does not append any result to the excel file. Has anyone got better ideas?

Corey Flynn · Accepted Answer · 2022-07-03 03:28:34Z

2

The try..catch is the reason your sheet isn't updating. You have a few bugs in your code that are silently passing (the casing in the APP column does not match the spreadsheet, the groupby statement is malformed, you're missing an open quote on 'data', you're trying to write the summary to the top of the sheet instead of the bottom). Those are easy enough to patch up, though, your biggest issue is that you're trying to open an already-open file (actually, you try to open and close it for each sheet).

So what you're going to want to do is move the file operations outside the for-loop, and try to just have one file open and one file save. You can get a lot more concise that way like this:

import pandas as pd

path = 'data.xlsx'
xl = pd.ExcelFile(path)
writer = pd.ExcelWriter(xl, engine='openpyxl', mode='a')
workbook = writer.book

for worksheet in workbook.worksheets:
    sheet_name = worksheet.title
    df = xl.parse(sheet_name=sheet_name)
    sheet_end = len(df) + 2
    total = df.APP.sum()

    worksheet[f'A{sheet_end}'] = 'Summary:'
    worksheet[f'A{sheet_end+1}'] = f'total={total}'

writer.save()

edited Jul 3, 2022 at 3:28

answered Jul 3, 2022 at 3:25

Corey Flynn

1013 bronze badges

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

2 Comments

Rob John Over a year ago

thanks for your solution. It works perfectly! Just out of curiosity, if I wanted to append a data frame intead intead of "summary" text and "total" output as above, would this be fine for appending a datframe starting at a specific row and column: df.to_excel(writer, sheet_name=sheet, startrow=50, startcol=15) for example?

Corey Flynn Over a year ago

@RobJohn It will work, though you'll need to set the writer to overlay (ie: writer = pd.ExcelWriter(xl, engine='openpyxl', mode='a', if_sheet_exists='overlay')). This option was only added in pandas 1.4.0, so upgrade if you need to. I think the formatting pandas includes is very ugly, so you may need to update that a bit too.

keramat · Accepted Answer · 2022-07-03 03:52:16Z

1

Use:

import pandas as pd
xl = pd.ExcelFile('01.xlsx')
sheets = xl.sheet_names

writer = pd.ExcelWriter('output1.xlsx')
for sheet in sheets:
    df = pd.read_excel('01.xlsx', sheet_name=sheet)
    sum_ = pd.DataFrame({'ID': ['Summary:'], 'App': [df['App'].sum()]})
    df = df.append(sum_, ignore_index=True)
    df.to_excel(writer, sheet_name=sheet, index=False)
writer.save()
writer.close()

Note that 01.xlsx is a file with some sheets similar to the following image, and the output1.xlsx is the output file.

answered Jul 3, 2022 at 3:52

keramat

4,6138 gold badges29 silver badges42 bronze badges

Collectives™ on Stack Overflow

Append data frame and text to existing excel file sheets in a for loop using python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related