使用Python和Pandas实现Excel数据智能追加：避免重复记录

本教程详细介绍了如何使用python的pandas和openpyxl库，将dataframe中的数据追加到excel工作表中，同时智能识别并跳过已存在的重复记录。通过比较关键列（如“alias”）的值，确保只有全新的数据行被写入，从而有效管理数据一致性并避免冗余。

引言

在日常数据处理中，我们经常需要将新的数据追加到现有的Excel文件中。然而，一个常见的挑战是如何避免追加重复的数据行，尤其当数据量较大时，手动检查和筛选变得不切实际。本教程将提供一个高效且健壮的Python解决方案，利用Pandas进行数据比对，并结合openpyxl库实现非重复数据的智能追加。

核心策略

实现智能追加并避免重复记录的核心策略包括以下几个步骤：

1. 读取现有Excel数据： 首先，将目标Excel工作表中的所有现有数据读取到一个Pandas DataFrame中。
2. 识别并过滤新数据中的重复项： 将待追加的新数据也转换为Pandas DataFrame。然后，通过比较新数据DataFrame的某个关键列（作为唯一标识符）与现有数据DataFrame中相同列的值，筛选出那些在Excel中尚不存在的新行。
3. 追加非重复的新数据到Excel： 最后，使用openpyxl库将筛选出的非重复新数据行追加到Excel工作表的末尾。

环境准备

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

pip install pandas openpyxl

然后，在您的Python脚本中导入所需的模块：

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

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

实现步骤

下面将详细介绍如何一步步实现数据的智能追加。

1. 定义Excel文件路径

首先，指定您要操作的Excel文件的路径。

excelpath = 'contact.xlsx'

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

在追加数据之前，我们需要获取Excel文件中已有的数据，以便进行重复性检查。考虑到文件可能不存在（例如，第一次运行脚本），我们需要一个健壮的机制来处理这种情况。

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

这段代码尝试从contact.xlsx的Sheet1中读取数据。如果文件不存在，它会捕获FileNotFoundError异常，并创建一个包含alias和fullname列的空DataFrame，这确保了后续的数据比较操作能够顺利进行。

BibiGPT-哔哔终结者

B站视频总结器-一键总结 音视频内容

下载

3. 准备待追加的新数据DataFrame

接下来，准备您要追加到Excel中的新数据。这些数据通常来自数据库查询、API接口或其他数据源，这里我们用一个示例DataFrame来表示。

df1 = pd.DataFrame([
    {"alias": "xyz", "fullname": "abc"},
    {"alias": "def", "fullname": "ghi"},
    {"alias": "uvw", "fullname": "xyz"},
    {"alias": "new_alias", "fullname": "new_full_name"}, # 示例新数据
])

在这个例子中，df1包含了多行数据，其中一些可能已经在existing_df中存在（例如xyz, abc）。

4. 识别并过滤重复数据

这是整个解决方案的核心步骤。我们利用Pandas的isin()方法来检查df1中alias列的值是否已存在于existing_df的alias列中。~运算符用于取反，从而筛选出那些不在现有数据中的新行。

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

执行此操作后，new_data DataFrame将只包含那些在Excel中尚无对应alias值的数据行。

5. 将非重复数据追加到Excel

在确认存在非重复的新数据后，我们使用openpyxl库将这些数据追加到Excel文件中。

if not new_data.empty:
    # 如果有新数据需要追加
    wb = load_workbook(excelpath) # 加载工作簿
    ws = wb['Sheet1']             # 获取目标工作表

    # 遍历new_data的每一行并追加到Excel
    # index=False 避免将DataFrame索引写入Excel
    # header=False 避免重复写入列头
    for r in dataframe_to_rows(new_data, index=False, header=False):
        ws.append(r)
    wb.save(excelpath) # 保存工作簿

    print("数据追加成功，已跳过重复项。")
else:
    print("没有新数据需要追加。")

这段代码首先检查new_data是否为空。如果不为空，它会加载Excel文件，获取Sheet1工作表，然后遍历new_data的每一行，使用ws.append(r)将其追加到工作表的末尾。dataframe_to_rows函数在转换为行时，index=False确保不写入DataFrame的索引，header=False确保不写入列头（因为Excel中通常已经有列头）。最后，wb.save(excelpath)将更改保存到文件中。

完整示例代码

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

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

# 1. 定义Excel文件路径
excelpath = 'contact.xlsx'

# 2. 加载现有Excel数据或初始化
try:
    existing_df = pd.read_excel(excelpath, sheet_name='Sheet1')
except FileNotFoundError:
    # 如果文件不存在，则创建一个空的DataFrame，列名与待追加数据保持一致
    # 注意：如果文件不存在，openpyxl在第一次写入时会自动创建文件和表头
    # 但为了Pandas的isin()操作，我们仍需一个结构一致的existing_df
    existing_df = pd.DataFrame(columns=['alias', 'fullname'])
    # 如果是新文件，可能需要先写入一次带表头的空数据，以便后续append操作
    # 这一步可以省略，因为如果existing_df是空的，new_data会是df1的全部内容
    # 并且openpyxl在append时，如果是空文件，会自动从第一行开始写入。
    # 真正的表头处理将在下面讨论。

# 3. 准备待追加的新数据DataFrame
df1 = pd.DataFrame([
    {"alias": "xyz", "fullname": "abc"},
    {"alias": "def", "fullname": "ghi"},
    {"alias": "uvw", "fullname": "xyz"},
    {"alias": "new_alias_1", "fullname": "New Full Name 1"},
    {"alias": "xyz", "fullname": "abc_duplicate"}, # 这条会被跳过
    {"alias": "new_alias_2", "fullname": "New Full Name 2"},
])

# 4. 识别并过滤重复数据
# 筛选出df1中'alias'列的值不在existing_df的'alias'列中的行
new_data = df1[~df1['alias'].isin(existing_df['alias'])]

# 5. 将非重复数据追加到Excel
if not new_data.empty:
    # 如果Excel文件不存在，openpyxl会创建它。
    # 对于新创建的文件，我们可能需要先写入列头。
    # 这里假设文件存在且有表头，或者existing_df已经处理了表头。
    # 如果是全新文件，可以考虑在第一次写入时，先写入表头。
    # 简单起见，这里直接加载。

    # 检查文件是否存在，如果不存在则创建并写入表头
    try:
        wb = load_workbook(excelpath)
        ws = wb['Sheet1']
    except FileNotFoundError:
        # 如果文件不存在，创建一个新的工作簿和工作表
        from openpyxl import Workbook
        wb = Workbook()
        ws = wb.active # 获取默认激活的工作表
        ws.title = 'Sheet1'
        # 写入表头
        ws.append(list(df1.columns)) # 使用df1的列名作为表头

    # 再次确保获取的是正确的工作表
    ws = wb['Sheet1']

    for r in dataframe_to_rows(new_data, index=False, header=False):
        ws.append(r)
    wb.save(excelpath)
    print("数据追加成功，已跳过重复项。")
else:
    print("没有新数据需要追加。")

注意事项与最佳实践

1. 唯一性判断列的选择： 教程中以alias列作为唯一标识符。在实际应用中，请根据您的业务逻辑选择一个或多个列的组合来确保数据的唯一性。
2. 性能优化：
   • 对于非常大的Excel文件（例如几十万行以上），pd.read_excel读取整个文件到内存可能会消耗大量时间和内存。可以考虑使用chunksize参数分块读取，或者直接使用数据库进行数据管理。
   • openpyxl在追加大量行时效率较高，但频繁地加载和保存工作簿会降低性能。如果需要追加的数据量非常大，可以考虑一次性构建所有数据DataFrame，再进行一次性写入。
3. 错误处理：
   • 工作表不存在： 如果wb['Sheet1']中的Sheet1不存在，会引发KeyError。在实际应用中，您可能需要检查工作表是否存在，或在不存在时创建它。
   • 文件权限： 确保Python脚本对Excel文件有读写权限。
4. header参数： 在dataframe_to_rows中设置header=False至关重要，它能防止每次追加时都将DataFrame的列头写入Excel，导致重复的表头行。
5. 首次写入时的表头： 如果Excel文件是全新的，第一次运行时文件不存在，pd.read_excel会抛出FileNotFoundError。此时existing_df为空，new_data将包含df1的所有内容。在将这些数据写入全新的Excel文件时，通常需要先写入列头。上述完整示例代码中已加入了对新文件创建和写入表头的处理逻辑。
6. 内存管理： 当处理非常大的DataFrame时，注意内存消耗。Pandas DataFrame在内存中存储数据，如果数据量超过可用内存，可能会导致程序崩溃。

总结

本教程提供了一种使用Python、Pandas和openpyxl库向Excel文件智能追加数据并避免重复记录的有效方法。通过将现有数据与新数据进行比对，并只追加唯一的记录，我们能够维护数据的一致性，提高数据管理的效率和准确性。此方法结构清晰、易于理解和扩展，适用于各种需要自动化Excel数据追加的场景。