如何在Python中用Left Join实现A∩B'？Pandas df1∩df2'求解

General Concept: What is A∩B'? #

A∩B' refers to all records that exist in dataset A but do not exist in dataset B. To get this using a left join, you'll:

• Do a left join between A and B (keeping all rows from A)
• Filter out rows where there's a matching record in B (identified by null values in B's columns after the join)

1. Implementing A∩B' with Left Join in Python (General Case) #

Assuming you're working with Pandas DataFrames (the most common scenario in Python), the core logic is straightforward:

• Join your two DataFrames on shared key columns using a left join
• Use Pandas' built-in merge indicator to easily spot rows that only exist in the left DataFrame (A)

Here's the reusable pattern:

# Replace ['key1', 'key2'] with your actual shared key columns
joined_df = df_a.merge(df_b, on=['key1', 'key2'], how='left', indicator=True)
# Filter to keep only rows present in df_a but not df_b
result_df = joined_df[joined_df['_merge'] == 'left_only'].drop(columns='_merge')

The indicator=True parameter adds a _merge column that labels each row as left_only, right_only, or both—making it trivial to filter for exactly what you need.

2. Your Specific Pandas Scenario #

Given your datasets:

• df1 (100 rows) with columns: a, b, c, d, x, y, v
• df2 (100 rows) with columns: a, b, e, f, p, o, i
• Desired output: df1∩df2' with columns: c, d, x, y, v

Step-by-Step Solution:

1. Identify shared keys: a and b are the only columns present in both DataFrames—these are our join keys.
2. Left join with match tracking: Use merge with indicator=True to flag where each row originates.
3. Filter non-matching rows: Keep only rows that exist exclusively in df1.
4. Select target columns: Drop the extra join columns and retain only the desired columns from df1.

Here's the complete code:

# Perform left join and add merge indicator
merged = df1.merge(df2, on=['a', 'b'], how='left', indicator=True)

# Keep only rows that are unique to df1
df1_excl_df2 = merged[merged['_merge'] == 'left_only']

# Extract the required columns from df1
final_result = df1_excl_df2[['c', 'd', 'x', 'y', 'v']]

Alternative Approach (Without Indicator):

If you prefer not to use the indicator parameter, you can check for nulls in any column unique to df2 (since non-matching rows will have NaN values in all df2 columns):

merged = df1.merge(df2, on=['a', 'b'], how='left')
# 'e' is a column only present in df2—check for nulls here
df1_excl_df2 = merged[merged['e'].isna()]
final_result = df1_excl_df2[['c', 'd', 'x', 'y', 'v']]

This works, but using indicator is more robust (especially if df2 has columns that might naturally contain null values).

内容的提问来源于stack exchange，提问作者Chris D'mello