如何从现有OrderedDict提取重复值生成新OrderedDict（Excel场景）

我想要提取Excel各工作表中基于ID列的重复行（保留每组重复的最后一行），但目前用pandas.DataFrame.duplicated只能得到标记重复的布尔列表，无法批量处理所有工作表并生成包含重复行的新Excel文件。

初始数据（读取后生成的OrderedDict） #

{'Sheet_1':     ID      Name  Surname  Grade
 0  104  Eleanor     Rigby      6
 1  104  Eleanor     Rigby      6
 2  168  Barbara       Ann      8
 3  450    Polly   Cracker      7
 4   90   Little       Joe     10
 5   90   Little       Joe     10,
 'Sheet_2':     ID       Name   Surname  Grade
 0  106       Lucy       Sky      8
 1  128    Delilah  Gonzalez      5
 2  100  Christina   Rodwell      3
 3  100  Christina   Rodwell      3
 4   40      Ziggy  Stardust      7,
 'Sheet_3':     ID   Name   Surname  Grade
 0   22   Lucy  Diamonds      9
 1   50  Grace     Kelly      7
 2   50  Grace     Kelly      7
 3  105    Uma   Thurman      7
 4  105    Uma   Thurman      7
 5   29   Lola      King      3}

期望结果（处理后生成的OrderedDict） #

{'Sheet_1':     ID      Name  Surname  Grade  
 1  104  Eleanor     Rigby      6
 5   90   Little       Joe     10,
 'Sheet_2':     ID       Name   Surname  Grade
 3  100  Christina   Rodwell      3,
 'Sheet_3':     ID   Name   Surname  Grade
 2   50  Grace     Kelly      7
 4  105    Uma   Thurman      7}

当前使用的代码 #

# Importing modules

import openpyxl as op
import pandas as pd
import numpy as np
import xlsxwriter
from openpyxl import Workbook, load_workbook

# Defining the file path

path_excel_file = r'C:\Users\machukovich\Desktop\stack.xlsx'

# Loading the files into a dictionary of Dataframes

dfs = pd.read_excel(path_excel_file, sheet_name=None, skiprows=2)

# Looping through the different sheets so to

for sheet_name, df in dfs.items():
    duplicated_values_df = df.duplicated(subset='ID', keep='last')
    
### 此时我仅获取到单个工作表的布尔列表，希望循环处理Excel的所有工作表

# Then, I would create a new excel file with the duplicated_values_df data

Path_new_file = r'C:\Users\machukovich\Desktop\new_file.xlsx'

# Create a Pandas Excel writer using XlsxWriter as the engine.

with pd.ExcelWriter(Path_new_file, engine='xlsxwriter') as writer:
    for sheet_name, df in duplicated_values_df.items():
        df.to_excel(writer, sheet_name=sheet_name, startrow=2, index=False)

解决方案 #

问题核心是：

1. 循环时未保存每个工作表的处理结果，每次循环都会覆盖变量
2. df.duplicated()返回的是布尔Series，需要用它筛选原DataFrame才能得到重复行数据

修正后的完整代码：

import pandas as pd
import xlsxwriter

# 定义文件路径
path_excel_file = r'C:\Users\machukovich\Desktop\stack.xlsx'
path_new_file = r'C:\Users\machukovich\Desktop\new_file.xlsx'

# 读取Excel所有工作表为DataFrame字典
dfs = pd.read_excel(path_excel_file, sheet_name=None, skiprows=2)

# 创建空字典存储处理后的重复行数据
processed_dfs = {}

# 遍历每个工作表，提取重复行
for sheet_name, df in dfs.items():
    # 生成布尔标记，keep='last'保留每组重复的最后一行
    duplicated_mask = df.duplicated(subset='ID', keep='last')
    # 筛选出重复行（标记为True的行）
    duplicated_rows = df[duplicated_mask]
    # 将结果存入字典
    processed_dfs[sheet_name] = duplicated_rows

# 将处理后的数据写入新Excel文件
with pd.ExcelWriter(path_new_file, engine='xlsxwriter') as writer:
    for sheet_name, df in processed_dfs.items():
        # 写入工作表，跳过前2行，不保留索引
        df.to_excel(writer, sheet_name=sheet_name, startrow=2, index=False)

关键说明： #

• df.duplicated(subset='ID', keep='last')：生成布尔Series，标记哪些行是重复的（保留每组重复的最后一行）
• df[duplicated_mask]：用布尔索引筛选出标记为True的行，也就是我们需要的重复行
• processed_dfs字典存储所有工作表的处理结果，确保后续能循环写入新Excel

内容的提问来源于stack exchange，提问作者machukovich