如何基于指定列比较两个DataFrame并在df1中添加标记列？

Hey there! I see the issue with your current code—it only checks if values in column A of df1 exist in df2's A column, but doesn't account for matching values in column C at the same time. Let's fix that.

Why Your Original Code Fails #

The line result = df1[df1["A"].isin(df2["A"].tolist())] filters rows where A matches, but ignores the requirement that C must also match. For example, if df1 had a row with A=12 but C=20, your code would still include it, even though we only want rows where both A and C match between the two DataFrames.

Method 1: Use merge (Efficient for Large Datasets) #

This approach uses pandas' merge to identify rows where both A and C match, then maps the result to your desired 'X' marker:

import pandas as pd

# Your sample data
df1 = pd.DataFrame({'A': [12, 19], 'B': [52, 32], 'C': [16, 30], 'D': [23, 9]})
df2 = pd.DataFrame({'A': [12], 'G': [13], 'C': [16], 'D': [4], 'E': [100]})

# Extract unique (A, C) pairs from df2
match_pairs = df2[['A', 'C']].drop_duplicates()

# Merge df1 with match_pairs to flag matches
df1 = df1.merge(match_pairs, on=['A', 'C'], how='left', indicator='Compare')

# Convert merge indicator to 'X' for matches, empty string otherwise
df1['Compare'] = df1['Compare'].map({'both': 'X', 'left_only': ''})

print(df1)

Method 2: Use apply (Simple for Small Datasets) #

If you're working with a small dataset, you can use apply to check each row against a set of valid (A, C) pairs:

import pandas as pd

# Your sample data
df1 = pd.DataFrame({'A': [12, 19], 'B': [52, 32], 'C': [16, 30], 'D': [23, 9]})
df2 = pd.DataFrame({'A': [12], 'G': [13], 'C': [16], 'D': [4], 'E': [100]})

# Create a set of tuples for quick lookup of valid (A, C) pairs
valid_pairs = set(zip(df2['A'], df2['C']))

# Add the Compare column
df1['Compare'] = df1.apply(lambda row: 'X' if (row['A'], row['C']) in valid_pairs else '', axis=1)

print(df1)

Output for Both Methods #

Running either code will give you this result:

A   B   C   D Compare
0  12  52  16  23       X
1  19  32  30   9        

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