Python增量写入Excel避免重复数据方法

本教程详细介绍了如何使用Python和pandas库将DataFrame数据增量地写入Excel文件，同时有效避免重复记录。通过读取现有数据、比较新旧数据并筛选出唯一的新条目，确保只有尚未存在于Excel中的数据行被追加，从而实现高效且无冗余的数据管理。

在日常数据处理工作中，我们经常需要将新的数据追加到现有的Excel文件中。然而，一个常见的挑战是如何避免写入重复的记录，特别是当某些列（如“别名”或“ID”）应作为唯一标识符时。直接追加数据可能会导致Excel文件中出现大量冗余信息，降低数据质量和处理效率。

本教程将提供一个结构化的方法，利用 pandas 和 openpyxl 库来解决这个问题。我们将演示如何智能地识别并跳过已存在的数据，只将全新的记录追加到Excel工作表中。

核心概念：数据去重与增量写入

实现增量写入并避免重复的关键在于以下两步：

1. 读取现有数据： 首先，我们需要将Excel文件中已有的数据读取到Python的DataFrame中。
2. 比较与筛选： 接着，将待写入的新数据DataFrame与现有数据进行比较，找出那些在现有数据中不存在的唯一新记录。

准备工作

在开始之前，请确保您的Python环境中安装了 pandas 和 openpyxl 库。如果尚未安装，可以使用以下命令进行安装：

pip install pandas openpyxl

逐步实现

我们将通过一个具体的例子来演示如何实现这一功能。假设我们有一个包含“alias”和“fullname”两列的DataFrame，需要将其内容追加到名为 contact.xlsx 的Excel文件的“Sheet1”中，并以“alias”列作为去重依据。

1. 导入必要的库

首先，导入我们将要使用的库：

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

2. 定义Excel文件路径和准备新数据

指定目标Excel文件的路径，并创建一个示例DataFrame df1，它代表了我们希望追加到Excel中的新数据。

excelpath = 'contact.xlsx'

# 示例新数据
df1 = pd.DataFrame([
    {"alias": "xyz", "fullname": "abc"},
    {"alias": "def", "fullname": "ghi"},
    {"alias": "uvw", "fullname": "xyz"},
    {"alias": "new1", "fullname": "New User 1"}, # 新增数据
    {"alias": "new2", "fullname": "New User 2"}  # 新增数据
])

3. 加载或初始化现有Excel数据

在进行数据比较之前，我们需要获取Excel文件中已有的数据。这里使用 pd.read_excel。为了处理Excel文件可能不存在的首次运行情况，我们使用 try-except FileNotFoundError 块。如果文件不存在，就创建一个空的DataFrame作为基准。

try:
    # 尝试读取现有Excel数据到DataFrame
    existing_df = pd.read_excel(excelpath, sheet_name='Sheet1') 
except FileNotFoundError:
    # 如果文件不存在，则创建一个空的DataFrame，列名与待写入数据一致
    existing_df = pd.DataFrame(columns=['alias', 'fullname']) 

说明： sheet_name='Sheet1' 指定了要读取的工作表。如果您的工作表名称不同，请相应修改。

4. 筛选出非重复的新数据

这是实现去重的核心步骤。我们将 df1 中 alias 列的值与 existing_df 中 alias 列的值进行比较，只保留那些在 existing_df 中不存在的行。

# 筛选出df1中'alias'列的值不在existing_df中'alias'列的值的行
new_data = df1[~df1['alias'].isin(existing_df['alias'])]

说明：

• existing_df['alias'] 获取现有数据中所有别名。
• df1['alias'].isin(existing_df['alias']) 返回一个布尔Series，指示 df1 中的每个别名是否已存在于 existing_df 中。
• ~ 是逻辑非运算符，它将上述布尔Series取反，从而选择那些别名不存在于 existing_df 中的行。
• 最终 new_data DataFrame将只包含需要追加的、不重复的记录。

5. 条件性写入Excel

在筛选出 new_data 后，我们检查它是否为空。只有当有新的、不重复的数据需要写入时，才执行Excel写入操作。

if not new_data.empty:
    # 加载工作簿
    wb = load_workbook(excelpath)
    # 选择目标工作表
    ws = wb['Sheet1']

    # 将新的数据行追加到工作表
    # index=False 避免写入DataFrame索引
    # header=False 避免重复写入列标题，因为Excel中通常已有标题
    for r in dataframe_to_rows(new_data, index=False, header=False):
        ws.append(r)

    # 保存工作簿
    wb.save(excelpath)
    print("数据已成功追加，并跳过重复项。")
else:
    print("没有新的数据需要追加，所有记录均已存在。")

说明：

• load_workbook(excelpath) 以读写模式打开Excel文件。
• wb['Sheet1'] 访问指定的工作表。
• dataframe_to_rows(new_data, index=False, header=False) 将DataFrame转换为适合 openpyxl 追加的行迭代器。header=False 是非常关键的，它确保在追加数据时不会再次写入列标题。
• ws.append(r) 将每一行追加到工作表的末尾。
• wb.save(excelpath) 保存对Excel文件的修改。

完整示例代码

将以上所有步骤整合，形成一个完整的脚本：

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

excelpath = 'contact.xlsx'

# 示例新数据
df1 = pd.DataFrame([
    {"alias": "xyz", "fullname": "abc"},
    {"alias": "def", "fullname": "ghi"},
    {"alias": "uvw", "fullname": "xyz"},
    {"alias": "new1", "fullname": "New User 1"},
    {"alias": "new2", "fullname": "New User 2"}
])

try:
    # 尝试读取现有Excel数据
    existing_df = pd.read_excel(excelpath, sheet_name='Sheet1') 
    # 确保'alias'列存在，如果不存在则初始化为空列表，防止后续isin操作报错
    if 'alias' not in existing_df.columns:
        existing_df['alias'] = []
except FileNotFoundError:
    # 如果文件不存在，则创建一个空的DataFrame作为基准
    existing_df = pd.DataFrame(columns=['alias', 'fullname']) 

# 筛选出非重复的新数据
# 只有当df1中的'alias'值不在existing_df的'alias'列中时，才保留该行
new_data = df1[~df1['alias'].isin(existing_df['alias'])]

if not new_data.empty:
    # 如果有新的数据需要写入
    wb = load_workbook(excelpath)
    ws = wb['Sheet1']

    # 检查是否需要写入标题行
    # 如果工作表为空（行数为0或只有默认标题），则写入标题
    if ws.max_row == 0 or (ws.max_row == 1 and all(cell.value is None for cell in ws[1])):
        ws.append(list(new_data.columns)) # 写入列标题

    # 将新的数据行追加到工作表，不写入索引和重复标题
    for r in dataframe_to_rows(new_data, index=False, header=False):
        ws.append(r)

    wb.save(excelpath)
    print("数据已成功追加，并跳过重复项。")
else:
    print("没有新的数据需要追加，所有记录均已存在。")

注意事项

1. 唯一标识列的准确性： 确保您选择用于去重的列（例如本例中的“alias”）在逻辑上确实应该是唯一的。如果该列在Excel中可能存在重复，那么去重逻辑将按照该列进行，可能不符合您的预期。
2. 性能考量： 对于包含数百万行的大型Excel文件，pd.read_excel 可能会消耗较多的内存和时间。在这种情况下，可以考虑使用数据库来存储数据，或者采用更高级的分块读取和处理策略。
3. 数据类型匹配： 确保DataFrame和Excel中用于比较的列的数据类型一致，以避免因数据类型不匹配导致的比较错误。例如，如果Excel中的“alias”是数字而DataFrame中是字符串，可能会导致 isin 方法无法正确匹配。
4. 初始文件状态： 在首次运行脚本时，如果 contact.xlsx 文件不存在，脚本会创建一个新的Excel文件，并写入所有数据。后续运行则会进行去重判断。
5. Excel文件头处理： 在上面的完整代码中，我们增加了一个逻辑来判断工作表是否为空，如果为空则写入标题行。这使得脚本在首次创建文件时也能有正确的表头。

总结

通过本教程，您已经学会了如何使用Python和pandas库实现向Excel文件增量写入数据，并有效避免重复记录。这种方法提高了数据管理的效率和准确性，尤其适用于需要定期更新数据集的场景。掌握这一技巧，将使您在数据自动化处理方面更具优势。