使用Python将DataFrame数据追加到Excel并跳过重复项的教程

本教程旨在详细介绍如何使用Python高效地将Pandas DataFrame中的数据追加到现有的Excel文件中，同时智能地识别并跳过已经存在于Excel中的重复记录。我们将利用pandas库进行数据处理和重复项检测，并结合openpyxl库实现对Excel文件的精确读写操作，确保数据更新的准确性和完整性，避免不必要的数据冗余。

1. 引言与背景

在日常数据处理工作中，我们经常需要将新的数据集合并到现有的数据存储中。当目标存储是Excel文件时，一个常见且关键的需求是避免重复写入已有的记录。例如，如果有一个包含别名（alias）和全名（fullname）的DataFrame，需要将其内容追加到一个Excel联系人列表中。如果Excel中已经存在某个别名，我们就不希望再次添加该条记录，而只追加全新的别名及其对应的全名。

本教程将提供一个健壮的Python解决方案，利用pandas和openpyxl这两个强大的库来解决这一问题。

2. 核心思路

解决此问题的核心策略可以分为以下几个步骤：

立即学习“Python免费学习笔记（深入）”；

1. 加载现有数据： 从目标Excel文件中读取所有现有数据，并将其转换为Pandas DataFrame。
2. 准备新数据： 准备需要追加的Pandas DataFrame。
3. 识别重复项： 比较新数据与现有数据，找出新数据中哪些记录在现有数据中已经存在。这通常基于一个或多个唯一标识符列（例如，本例中的'alias'列）。
4. 过滤新数据： 从新数据中移除所有已识别的重复项，只保留真正需要追加的唯一新记录。
5. 追加到Excel： 将过滤后的唯一新记录追加到Excel文件的指定工作表中。

3. 环境准备

在开始之前，请确保已安装必要的Python库：

pip install pandas openpyxl

4. 实现步骤与代码示例

下面我们将通过一个完整的代码示例来演示如何实现上述核心思路。

Uni-CourseHelper

私人AI助教，高效学习工具

下载

4.1 定义Excel路径和示例数据

首先，定义Excel文件的路径，并创建两个示例DataFrame：一个模拟已存在的数据，另一个模拟需要追加的新数据。

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import os # 用于检查文件是否存在

excel_path = 'contact.xlsx'
sheet_name = 'Sheet1'

# 模拟需要追加的新数据
df_new_data = pd.DataFrame([
    {"alias": "xyz", "fullname": "Alice Smith"},
    {"alias": "def", "fullname": "Bob Johnson"},
    {"alias": "uvw", "fullname": "Charlie Brown"},
    {"alias": "xyz", "fullname": "Alice Smith"}, # 这是一个重复项，应被跳过
    {"alias": "mno", "fullname": "David Lee"}    # 这是一个新项
])

4.2 加载现有数据或创建新文件

我们需要首先尝试加载Excel文件中的现有数据。如果文件不存在，则创建一个空的DataFrame作为现有数据，并确保后续能正确创建Excel文件。

existing_df = pd.DataFrame(columns=['alias', 'fullname']) # 默认空DataFrame

if os.path.exists(excel_path):
    try:
        # 尝试从Excel读取现有数据
        existing_df = pd.read_excel(excel_path, sheet_name=sheet_name)
    except Exception as e:
        print(f"警告：无法读取现有Excel文件 '{excel_path}' 或工作表 '{sheet_name}'，错误：{e}。将视为空文件处理。")
        # 如果读取失败，existing_df 保持为空，后续会创建新文件或新工作表
else:
    print(f"Excel文件 '{excel_path}' 不存在，将创建新文件。")
    # 如果文件不存在，existing_df 保持为空，后续会创建带表头的新文件

4.3 识别并过滤重复项

这是解决方案的关键一步。我们使用Pandas的isin()方法来检查df_new_data中的'alias'列值是否已存在于existing_df的'alias'列中。~运算符用于取反，从而筛选出那些alias值不在existing_df中的新记录。

# 根据 'alias' 列识别并过滤掉重复项
# 只有当 df_new_data 中的 'alias' 不在 existing_df 的 'alias' 中时，才保留该行
unique_new_data = df_new_data[~df_new_data['alias'].isin(existing_df['alias'])]

if unique_new_data.empty:
    print("没有新的唯一数据需要追加。")
else:
    print(f"找到 {len(unique_new_data)} 条新的唯一数据，准备追加。")
    print("将追加的数据：")
    print(unique_new_data)

4.4 追加唯一新数据到Excel

现在，我们有了需要追加的唯一新数据。接下来使用openpyxl库将其写入Excel。这里需要注意处理文件不存在或工作表不存在的情况。

    if not os.path.exists(excel_path):
        # 如果文件不存在，则创建新的工作簿和工作表，并写入表头
        wb = pd.ExcelWriter(excel_path, engine='openpyxl')
        unique_new_data.to_excel(wb, sheet_name=sheet_name, index=False, header=True)
        wb.close()
    else:
        # 如果文件已存在，加载工作簿
        wb = load_workbook(excel_path)

        # 检查工作表是否存在，如果不存在则创建
        if sheet_name not in wb.sheetnames:
            ws = wb.create_sheet(sheet_name)
            # 如果是新创建的表，需要先写入表头
            for col_name in unique_new_data.columns:
                ws.append([col_name]) # 写入表头，openpyxl append是按行追加，所以需要包装成列表
            # 更好的方式是使用 to_excel 写入新表头
            # 或者在创建工作表后，先写入表头，再追加数据
            # 示例中，我们假设现有表已经有表头，或者我们手动处理
            # 简化起见，这里假设如果文件存在，sheet也存在且有表头，或者我们第一次写入时已经处理
            # 实际上，更严谨的做法是：如果sheet不存在，先创建并写入header，然后像下面一样append data
            # 为了教程简洁，我们假设 sheet_name 存在且有header
            print(f"工作表 '{sheet_name}' 不存在，已创建。")
            # 写入表头
            ws.append(list(unique_new_data.columns))
        else:
            ws = wb[sheet_name]

        # 将过滤后的唯一新数据逐行追加到工作表
        # dataframe_to_rows 用于将DataFrame转换为适合openpyxl的行迭代器
        # index=False 避免将DataFrame索引写入Excel
        # header=False 避免将DataFrame列名作为数据行再次写入
        for r in dataframe_to_rows(unique_new_data, index=False, header=False):
            ws.append(r)

        # 保存工作簿
        wb.save(excel_path)

    print("数据追加成功，已跳过重复项。")

4.5 完整代码示例

将上述所有片段整合，形成一个完整的、可执行的脚本：

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

excel_path = 'contact.xlsx'
sheet_name = 'Sheet1'

# 模拟需要追加的新数据
df_new_data = pd.DataFrame([
    {"alias": "xyz", "fullname": "Alice Smith"},
    {"alias": "def", "fullname": "Bob Johnson"},
    {"alias": "uvw", "fullname": "Charlie Brown"},
    {"alias": "xyz", "fullname": "Alice Smith"}, # 这是一个重复项，应被跳过
    {"alias": "mno", "fullname": "David Lee"}    # 这是一个新项
])

print(f"尝试将数据追加到 '{excel_path}' (工作表: '{sheet_name}')")

# --- 步骤 1: 加载现有数据或初始化空DataFrame ---
existing_df = pd.DataFrame(columns=['alias', 'fullname']) # 默认空DataFrame，定义列名以匹配

if os.path.exists(excel_path):
    try:
        # 尝试从Excel读取现有数据
        # 确保读取时指定 sheet_name
        existing_df = pd.read_excel(excel_path, sheet_name=sheet_name)
        print(f"成功加载现有Excel数据，共 {len(existing_df)} 条记录。")
    except Exception as e:
        print(f"警告：无法读取现有Excel文件 '{excel_path}' 或工作表 '{sheet_name}'，错误：{e}。将视为空文件处理。")
        # 如果读取失败，existing_df 保持为空，后续会创建新文件或新工作表
else:
    print(f"Excel文件 '{excel_path}' 不存在，将创建新文件。")
    # 如果文件不存在，existing_df 保持为空，后续会创建带表头的新文件

# --- 步骤 2: 识别并过滤重复项 ---
# 使用 'alias' 列作为唯一标识符
# 只有当 df_new_data 中的 'alias' 不在 existing_df 的 'alias' 中时，才保留该行
unique_new_data = df_new_data[~df_new_data['alias'].isin(existing_df['alias'])]

if unique_new_data.empty:
    print("没有新的唯一数据需要追加。")
else:
    print(f"找到 {len(unique_new_data)} 条新的唯一数据，准备追加。")
    print("将追加的数据：")
    print(unique_new_data)

    # --- 步骤 3: 追加唯一新数据到Excel ---
    try:
        if not os.path.exists(excel_path) or existing_df.empty:
            # 如果文件不存在，或者现有数据为空（意味着可能是新文件或空文件），
            # 则直接使用pandas的to_excel方法创建文件并写入数据和表头
            # 注意：这里需要创建一个新的ExcelWriter对象
            with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
                unique_new_data.to_excel(writer, sheet_name=sheet_name, index=False, header=True)
            print(f"Excel文件 '{excel_path}' 已创建，并写入了 {len(unique_new_data)} 条数据（含表头）。")
        else:
            # 文件已存在且有数据，加载工作簿并追加
            wb = load_workbook(excel_path)

            # 确保工作表存在
            if sheet_name not in wb.sheetnames:
                ws = wb.create_sheet(sheet_name)
                # 如果是新创建的表，需要写入表头
                ws.append(list(unique_new_data.columns))
                print(f"工作表 '{sheet_name}' 不存在，已创建并写入表头。")
            else:
                ws = wb[sheet_name]

            # 将过滤后的唯一新数据逐行追加到工作表
            # header=False 确保不重复写入表头
            for r in dataframe_to_rows(unique_new_data, index=False, header=False):
                ws.append(r)

            # 保存工作簿
            wb.save(excel_path)
            print(f"成功追加 {len(unique_new_data)} 条数据到 '{excel_path}'。")

    except Exception as e:
        print(f"追加数据到Excel时发生错误：{e}")

print("操作完成。")

5. 注意事项与最佳实践

• 唯一标识符选择： 确保用于判断重复项的列（例如本例中的'alias'）确实能够唯一标识一条记录。如果需要基于多列判断唯一性，可以在isin()之前组合这些列或使用更复杂的合并逻辑。
• 性能考量： 对于非常大的Excel文件和DataFrame，pd.read_excel()可能会消耗较多内存和时间。如果文件过大，可以考虑分块读取或使用数据库。然而，对于大多数常见场景，此方法效率良好。
• 错误处理： 代码中包含了try-except块来处理文件不存在或读取失败的情况，这增强了程序的健壮性。
• 工作表名称： 确保sheet_name与Excel文件中实际的工作表名称匹配。如果工作表不存在，代码会自动创建并写入表头。
• 表头处理： 在追加数据时，dataframe_to_rows的header=False参数至关重要，它确保了只有数据行被追加，避免了重复的表头。当文件或工作表是第一次创建时，我们使用pd.ExcelWriter或ws.append(list(unique_new_data.columns))来确保表头被正确写入。
• Pandas与Openpyxl的结合： pandas在数据处理和过滤方面非常强大，而openpyxl在直接操作Excel文件（尤其是追加行）方面更高效，避免了pandas.to_excel(mode='a')可能带来的性能问题或覆盖原有格式的问题。

6. 总结

通过本教程，我们学习了如何利用Python的pandas和openpyxl库，实现一个高效且智能的数据追加机制，将DataFrame中的新数据写入Excel文件，并自动跳过已存在的重复记录。这种方法不仅保证了数据的准确性，也避免了手动检查和清理重复项的繁琐工作，极大地提高了数据管理的效率。