Pandas多分组筛选：提取同一ID下不同TITLE的去重行

Got it, let's break down your requirement and build the solution step by step:
You want to:

1. Keep only rows where the same ID has multiple distinct TITLE values
2. For those qualified IDs, deduplicate rows so each ID-TITLE pair appears only once

Here's the code that does exactly this, with explanations:

import pandas as pd
import numpy as np

# Your original DataFrame
df = pd.DataFrame(np.array([
 [1, 'Black', 233, 342],
 [1, 'White', 342, 1222],
 [1, 'Orange', 432, 1234],
 [1, 'Orange', 2234, 1345],
 [1, 'Orange', 2234, 1345],
 [2, 'Pink', 444, 222],
 [2, 'White', 35345, 2345434],
 [3, 'Black', 4544, 2222],
 [4, 'White', 4433, 123],
 [4, 'White', 4433, 123],
 [4, 'White', 4433, 123],
 [4, 'White', 4433, 123],
 [4, 'White', 4433, 123],
 [4, 'White', 4433, 123],
]), columns=['ID', 'TITLE', 'ITEM_ID', 'CARD_ID'])

# Step 1: Identify IDs that have more than one unique TITLE
valid_ids = df.groupby('ID')['TITLE'].nunique()[lambda x: x > 1].index

# Step 2: Filter the original DataFrame to keep only these IDs
filtered_df = df[df['ID'].isin(valid_ids)]

# Step 3: Deduplicate to keep only one row per ID-TITLE pair
final_df = filtered_df.drop_duplicates(subset=['ID', 'TITLE']).reset_index(drop=True)

print(final_df)

Output: #

ID   TITLE  ITEM_ID  CARD_ID
0   1   Black      233      342
1   1   White      342     1222
2   1  Orange      432     1234
3   2    Pink      444      222
4   2   White    35345  2345434

Let's walk through each step: #

• Step 1: We group the DataFrame by ID and count the number of unique TITLE values per group. Then we keep only the IDs where this count is greater than 1 (meaning the ID has multiple distinct titles).
• Step 2: We use this list of valid IDs to filter out rows from the original DataFrame that belong to IDs with only one unique title (like ID 3 and 4 in your data).
• Step 3: Finally, we use drop_duplicates with subset=['ID', 'TITLE'] to ensure each combination of ID and TITLE appears exactly once. The reset_index(drop=True) just cleans up the index for readability.

If you want to keep a specific row from each duplicate group (e.g., the one with the smallest ITEM_ID), you could replace Step 3 with a groupby and aggregation instead:

final_df = filtered_df.groupby(['ID', 'TITLE']).agg({
    'ITEM_ID': 'first',  # or 'min', 'max' depending on your need
    'CARD_ID': 'first'
}).reset_index()

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