Pandas：基于多条件使用drop_duplicates实现数据集去重

Hey there! Let's solve this duplicate removal task where we need to keep the row with group = NII when there are duplicate ID and date pairs. Here's a step-by-step approach using Python's Pandas library, which is perfect for this kind of data manipulation:

Step 1: Prepare the Sample Data #

First, let's recreate your sample dataset to work with:

import pandas as pd

# Your original dataset
data = {
    'ID': [3001, 3001, 3001],
    'date': [2010, 2012, 2012],
    'group': ['DCM', 'NII', 'DCM']
}
df = pd.DataFrame(data)

Original dataset output:

ID  date group
0  3001  2010   DCM
1  3001  2012   NII
2  3001  2012   DCM

Step 2: Remove Duplicates with Priority #

We need to prioritize rows where group is NII when ID and date are the same. Here are two simple ways to do this:

Method 1: Using Groupby with Priority Sorting #

1. Assign a priority value to the group column (lower number = higher priority)
2. Sort the dataframe by priority
3. Group by ID and date, then keep the first row of each group (which will be the highest priority row)

# Add a priority column: NII gets higher priority (1) than DCM (2)
df['priority'] = df['group'].map({'NII': 1, 'DCM': 2})

# Sort by priority, then group by ID and date to keep the top-priority row
result_df = df.sort_values('priority').groupby(['ID', 'date'], as_index=False).first()

# Remove the temporary priority column
result_df = result_df.drop('priority', axis=1)

# Print the final result
print(result_df)

Method 2: Concise Sort + Drop Duplicates #

A more streamlined way is to sort the dataframe so NII rows come first, then drop duplicates while keeping the first occurrence per ID and date:

# Sort rows so NII appears before DCM for duplicate ID/date pairs
sorted_df = df.sort_values('group', key=lambda x: x.map({'NII': 0, 'DCM': 1}))

# Drop duplicates, keeping the first row (which is NII if present)
result_df = sorted_df.drop_duplicates(subset=['ID', 'date'], keep='first')

Final Result #

Both methods will give you the desired output:

ID  date group
0  3001  2010   DCM
1  3001  2012   NII

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