如何从包含小DataFrame的大DataFrame中移除共同行，保留差异行？

Got it, let's solve this problem step by step. The goal is to filter rows in df_large where the combination of columns a, b, c doesn't exist in df_small at all. Here are a couple of straightforward ways to get your expected result:

Method 1: Use merge with indicator parameter #

This approach is intuitive and easy to follow, especially if you're familiar with pandas joins. We'll do a left join between df_large and df_small on the matching columns, then keep only the rows that exist only in df_large.

import pandas as pd
import numpy as np

# Define your original DataFrames
df_small = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
df_large = pd.DataFrame(np.array([[22, 1, 2, 3, 99], [31, 4, 5, 6, 75], [73, 7, 8, 9, 23], [16, 2, 1, 2, 13], [17, 1, 4, 3, 25], [93, 3, 2, 8, 18]]), columns=['k', 'a', 'b', 'c', 'd'])

# Perform left join with indicator to track row origin
merged_df = df_large.merge(df_small, on=['a', 'b', 'c'], how='left', indicator=True)

# Filter rows that only exist in df_large, then drop the indicator column
df_result = merged_df[merged_df['_merge'] == 'left_only'].drop('_merge', axis=1)

print(df_result)

Method 2: Use Tuple Sets for Fast Lookup #

If you're working with larger datasets, this method can be more efficient because set lookups are O(1). We'll convert the matching column combinations from df_small into a set of tuples, then check which rows in df_large don't match any of these tuples.

import pandas as pd
import numpy as np

# Define your original DataFrames
df_small = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]), columns=['a', 'b', 'c'])
df_large = pd.DataFrame(np.array([[22, 1, 2, 3, 99], [31, 4, 5, 6, 75], [73, 7, 8, 9, 23], [16, 2, 1, 2, 13], [17, 1, 4, 3, 25], [93, 3, 2, 8, 18]]), columns=['k', 'a', 'b', 'c', 'd'])

# Convert df_small's a/b/c combinations to a set of tuples
small_matches = set(df_small.apply(tuple, axis=1))

# Filter df_large to keep rows where a/b/c isn't in the matches set
df_result = df_large[~df_large[['a', 'b', 'c']].apply(tuple, axis=1).isin(small_matches)]

print(df_result)

Both methods will output exactly the result you're expecting:

k  a  b  c   d
3  16  2  1  2  13
4  17  1  4  3  25
5  93  3  2  8  18

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