[python]使用Pandas处理多个Excel文件并汇总数据

在数据分析和处理过程中，经常需要处理多个Excel文件，并将其中的数据进行汇总和分析。本文介绍使用Python的Pandas库来读取多个Excel文件，并汇总不同类型的数据，例如员工工资、工件数量等。

代码示例

以下是一个完整的代码示例，展示了如何读取指定目录下的所有Excel文件，并对其中的数据进行处理和汇总：

import pandas as pd
import os

# 设定存放Excel文件的目录路径
directory = r'C:\Users\Desktop\py计算'

# 创建一个空的列表用于存储所有记录DataFrame
all_data_frames = []

# 遍历目录中的所有Excel文件
for filename in os.listdir(directory):
    if filename.endswith(".xls"):
        file_path = os.path.join(directory, filename)
        # 读取Excel文件，从第六行开始，忽略前五行
        data = pd.read_excel(file_path, skiprows=4, engine='xlrd')
        # 去除特定列中的逗号并转换为数值格式，无法转换的设置为NaN
        data['存额'] = data['存额'].str.replace(',', '').astype(float)
        print(data)
        # 将读取到的DataFrame添加到列表中
        all_data_frames.append(data)

# 使用pd.concat将所有DataFrame合并成一个
all_data = pd.concat(all_data_frames, ignore_index=True)

# 计算每个员工的工资之和
salary_sum = all_data.groupby('证件号码')['存额'].sum().round(2).reset_index()

# 获取每个员工的其他信息（例如部门和职位）
employee_info = all_data[['姓名', '证件\n类型', '证件号码']].drop_duplicates(subset=['证件号码'])

# 合并工资之和和其他信息
result = pd.merge(salary_sum, employee_info, on='证件号码', how='left')

# 如果需要保存结果到新的Excel文件
result.to_excel('汇总带信息.xlsx', sheet_name='之和', index=False)

代码解析

1. 导入必要的库：首先，我们需要导入Pandas库和os库。
2. 设定目录路径：指定存放Excel文件的目录路径。
3. 创建空列表：用于存储所有记录的DataFrame。
4. 遍历目录中的Excel文件：使用os.listdir遍历目录中的所有文件，并筛选出以.xls结尾的Excel文件。
5. 读取Excel文件：使用pd.read_excel读取Excel文件，从第六行开始，忽略前五行。
6. 数据清洗：去除特定列中的逗号并转换为数值格式，无法转换的设置为NaN。
7. 合并所有DataFrame：使用pd.concat将所有DataFrame合并成一个。

关键函数

1. groupby：groupby函数用于将数据分组，以便对每个组进行聚合操作。在本例中，按证件号码分组，并计算每个员工的工资之和。

   salary_sum = all_data.groupby('证件号码')['存额'].sum().round(2).reset_index()
   

2. reset_index：reset_index函数用于重置索引，将分组后的结果转换为DataFrame。在本例中，我们在计算工资之和后使用reset_index将结果转换为DataFrame。

   salary_sum = all_data.groupby('证件号码')['存额'].sum().round(2).reset_index()
   

3. merge：merge函数用于合并两个DataFrame。在本例中，我们将工资之和与员工的其他信息合并。

   result = pd.merge(salary_sum, employee_info, on='证件号码', how='left')
   

4. drop_duplicates：drop_duplicates函数用于删除重复的行。在本例中，我们获取每个员工的其他信息，并删除重复的记录。

   employee_info = all_data[['姓名', '证件\n类型', '证件号码']].drop_duplicates(subset=['证件号码'])
   

通过以上步骤读取多个Excel文件，并对其中的数据进行处理和汇总。这种方法不仅适用于工资数据，还可以应用于其他类型的数据，例如工件数量、销售额等。这样可以提高数据处理的效率，适用于各种数据分析和处理场景。