如何对比同一Excel文件的两个Sheet，标记df1中与df2重复的行？

Hey there! Let's walk through how to solve this problem using pandas—it's straightforward once you know the right tricks. Here's a step-by-step breakdown:

1. 读取Excel中的两个Sheet #

First, we'll use pandas to load both sheets from your Excel file into separate DataFrames. Make sure to replace the sheet names with your actual ones (like "Sheet1" and "Sheet2" if you haven't renamed them).

import pandas as pd

# 加载Excel文件
excel_file = pd.ExcelFile('your_excel_file.xlsx')

# 解析两个Sheet到DataFrame
df1 = excel_file.parse('Sheet_with_df1')  # 替换为存储df1的Sheet名称
df2 = excel_file.parse('Sheet_with_df2')  # 替换为存储df2的Sheet名称

2. 匹配行并添加标记列 #

We have two reliable ways to check if rows from df1 exist in df2, then add the double column with "X" for matches.

方法一：使用merge（推荐，适合明确指定匹配列） #

This method is great if you want to explicitly define which columns to match (in case you ever need to adjust the matching criteria later). We'll use merge with an indicator to flag rows that exist in both DataFrames.

# 合并两个DataFrame，保留df1的所有行，添加匹配标记列
merged_result = df1.merge(
    df2,
    on=['Country', 'City', 'Population', 'Planet'],  # 指定所有需要匹配的列
    how='left',
    indicator=True
)

# 根据匹配标记添加double列：匹配到的填"X"，否则留空
df1['double'] = merged_result['_merge'].apply(lambda x: 'X' if x == 'both' else '')

方法二：使用元组集合（简洁，适合整行完全匹配） #

If you're sure you need to match every single column exactly, this approach converts each row into a tuple and checks membership in a set made from df2's rows—it's fast and concise.

# 将df2的所有行转换为元组，存入集合以便快速查找
df2_row_set = set(df2.apply(tuple, axis=1))

# 检查df1的每行是否在df2的行集合中
is_duplicate = df1.apply(tuple, axis=1).isin(df2_row_set)

# 映射布尔值到"X"或空字符串，添加到df1
df1['double'] = is_duplicate.map({True: 'X', False: ''})

3. 验证结果 #

After running either method, you can check the updated df1 with:

print(df1)

You'll see the new double column with "X" in every row that exists in both df1 and df2.

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