Python中基于指定列相等性筛选DataFrame：获取前两列相同但第三列不同的行

To solve this problem, we can use pandas' grouping and transformation capabilities to identify rows that belong to groups where the first two columns (A and B) are identical, but the third column (C) has multiple distinct values. Here's a step-by-step solution:

First, let's recreate your sample DataFrame for reference:

import pandas as pd

# Sample DataFrame
df = pd.DataFrame({
    'A': [1, 1, 2, 3, 4, 2],
    'B': [4, 5, 3, 1, 3, 3],
    'C': [2, 3, 3, 1, 2, 5]
})

Solution Code #

# Calculate the number of unique C values for each (A, B) group
unique_c_per_group = df.groupby(['A', 'B'])['C'].transform('nunique')

# Filter rows where the group has more than one unique C value
filtered_df = df[unique_c_per_group > 1]

print(filtered_df)

Output #

A  B  C
2  2  3  3
5  2  3  5

How It Works #

1. Grouping by A and B: We use groupby(['A', 'B']) to cluster rows where both columns A and B have matching values.
2. Transform with nunique: The transform('nunique') method calculates how many distinct values exist in column C for each group, and returns a series that aligns with the original DataFrame's row order (so every row gets the count of unique C values from its group).
3. Filtering: We keep only rows where their group's unique C count is greater than 1—this means there are at least two different C values for that (A, B) pair, which is exactly what you're looking for.

Alternative Approach #

If you prefer a more explicit method, you can first identify the (A, B) pairs with multiple C values, then filter the original DataFrame to those pairs:

# Get (A, B) pairs that have multiple unique C values
target_pairs = df.groupby(['A', 'B'])['C'].nunique()[lambda x: x > 1].index

# Filter the DataFrame to include only these pairs
filtered_df = df[df.set_index(['A', 'B']).index.isin(target_pairs)]

This achieves the same result, though the first method is more concise and efficient for most use cases.

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