Automate Multi‑Sheet Excel Scoring with Python & Pandas: Step‑by‑Step Guide

Hello, I'm Pi Pi. Recently a member asked how to batch calculate average total scores from seven evaluation Excel sheets using Python.

1. Introduction

The task involves reading multiple Excel files, skipping header rows, cleaning nulls, computing total and average scores per person, and aggregating the results.

2. Implementation

The following Python script uses pandas and pathlib to read all .xls files in a folder, drop empty rows, add total and average columns, concatenate the dataframes, and group by name to obtain summed totals and mean averages.

import pandas as pd
import pathlib
# 获取文件夹中每个Excel文件的路径
folder = r"C:\Users\Desktop\民主评议表"
excel_files = pathlib.Path(folder).glob('*.xls')
header = ['姓名','以学铸魂','以学增智','以学正风','以学促干']
data = []
for i in excel_files:
    # 读取Excel文件，并跳过前4行，使用前5列数据
    df = pd.read_excel(i, skiprows=4, header=None, index_col=0, usecols="A:F")
    df.dropna(inplace=True)
    df.columns = header
    df['总分'] = df[header[1:]].sum(axis=1)
    df['平均分'] = df[header[1:]].mean(axis=1)*4
    data.append(df)

# 纵向拼接data中的df数据
df = pd.concat(data, axis=0).reset_index(drop=True)
# 按照'姓名'列进行分组，并计算每个姓名的总分和平均分
result = df.groupby('姓名', as_index=False).agg(总分=('总分','sum'), 平均分=('总分','mean')).sort_values(by='平均分', ascending=False)
print(result)
# 将结果保存到新的Excel文件中
# result.to_excel('output.xlsx', index=True)

Running the script produces the expected aggregated scores, matching manual calculations.

The solution demonstrates how Python can automate repetitive Excel statistics tasks efficiently.

3. Conclusion

This example shows a practical Python automation workflow for batch processing Excel files, calculating totals and averages per individual, and saving the results, helping users avoid tedious manual work.