如何基于指定列删除DataFrame中与另一DataFrame匹配的行？

I have two DataFrames as follows:

import pandas as pd
df1 = pd.DataFrame(
    {
        'A': list('abcaewar'),
        'B': list('ghjglmgb'),
        'C': list('lkjlytle'),
        'ignore': ['stuff'] * 8
    }
)
df2 = pd.DataFrame(
    {
        'A': list('abfu'),
        'B': list('ghio'),
        'C': list('lkqw'),
        'stuff': ['ignore'] * 4
    }
)

I want to remove all rows from df1 where the values in columns A, B, C exactly match the corresponding columns in df2. The expected result is:

A  B  C ignore
0  c  j  j  stuff
1  e  l  y  stuff
2  w  m  t  stuff
3  r  b  e  stuff

My current implementation is:

comp_columns = ['A', 'B', 'C']
df1 = df1.set_index(comp_columns)
df2 = df2.set_index(comp_columns)
keep_ind = [ ind for ind in df1.index if ind not in df2.index ]
new_df1 = df1.loc[keep_ind].reset_index()

Is there a more concise way to do this? For example, using clever masking operations, avoiding reset_index() and loops for filtering non-overlapping indices. Ideally, I don't want to hardcode column names, and can specify them via a list (sometimes 2 columns, sometimes 3 or more).

Absolutely, there are a few more concise and Pandas-idiomatic ways to achieve this without loops or index resets. Here are my top recommendations:

Method 1: Use merge with indicator=True #

This approach leverages Pandas' merge functionality to flag rows that exist only in df1, then filters those rows. It keeps your original column structure intact and is intuitive for most Pandas users:

comp_columns = ['A', 'B', 'C']

# Merge df1 with df2's target columns, adding an indicator to track row origin
merged = df1.merge(df2[comp_columns], on=comp_columns, how='left', indicator=True)
# Keep only rows that exist exclusively in df1
new_df1 = merged[merged['_merge'] == 'left_only'].drop(columns='_merge')

The indicator=True parameter adds a _merge column that labels rows as left_only (from df1), right_only (from df2), or both. Filtering for left_only gives exactly the rows you want to retain.

Method 2: Use isin with tuples #

This method uses fast set lookups to create a mask for filtering. It avoids any index manipulation entirely:

comp_columns = ['A', 'B', 'C']

# Convert df2's comparison columns into a set of tuples for quick lookups
df2_match_set = set(df2[comp_columns].itertuples(index=False, name=None))
# Create a mask where df1's rows are NOT present in the df2 match set
filter_mask = ~df1[comp_columns].itertuples(index=False, name=None).isin(df2_match_set)
# Apply the mask to get the filtered DataFrame
new_df1 = df1[filter_mask]

The ~ operator inverts the mask, so we keep rows that don't have a full match in df2's target columns. This is particularly efficient for large datasets due to the speed of set lookups.

Method 3: Dynamic filtering with query #

If you prefer string-based filtering, you can dynamically build a query string from your column list:

comp_columns = ['A', 'B', 'C']

# Convert df2's target columns to a list of tuples
df2_match_list = list(df2[comp_columns].itertuples(index=False, name=None))
# Build a query string like "(A,B,C) not in @df2_match_list"
filter_query = f"({','.join(comp_columns)}) not in @df2_match_list"
# Run the query to get the filtered DataFrame
new_df1 = df1.query(filter_query)

This method is highly readable and makes it easy to adjust the comparison columns on the fly without rewriting core logic.

All three methods let you specify comparison columns via a list (no hardcoding required) and avoid loops or index resets. The merge method is the most beginner-friendly, while the tuple set approach is typically the fastest for large datasets.

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