基于多列分组后识别并删除两列重复项的技术求助

Got it, let’s walk through this step by step—this is a super common task when cleaning grouped response/time data, and it’s totally manageable with pandas. Here’s how to approach identifying duplicate patterns within groups and then removing those duplicates:

First, let’s assume you’re working with a pandas DataFrame (this is the standard tool for this kind of task). We’ll break this into two core parts: spotting duplicate patterns within your groups and cleaning those duplicates out.

1. Mark Duplicates Within Groups (For Pattern Analysis) #

First, let’s flag which rows are duplicates only within their respective groups (based on your grouping columns) for the testtime and responsetime fields. This lets you inspect the duplicates before deleting them.

Let’s define your columns first (swap out the group columns with your actual ones):

import pandas as pd

# Replace these with your actual grouping columns (e.g., ['user_id', 'service_name'])
group_columns = ['group_col1', 'group_col2']
# Columns we're checking for duplicates
duplicate_check_cols = ['testtime', 'responsetime']

Add Duplicate Flag Columns #

We’ll use groupby() + transform() to add flags to your original DataFrame:

# Flag rows that are duplicates (excludes the first occurrence in the group)
df['is_duplicate_in_group'] = df.groupby(group_columns)[duplicate_check_cols].transform(
    lambda x: x.duplicated()
)

# If you want to flag ALL instances of duplicates (including the first occurrence)
df['has_duplicate_in_group'] = df.groupby(group_columns)[duplicate_check_cols].transform(
    lambda x: x.duplicated(keep=False)
)

Analyze Duplicate Patterns #

Now you can dig into the duplicates:

• Count duplicates per group:

  duplicate_summary = df.groupby(group_columns).agg(
      total_rows=('is_duplicate_in_group', 'count'),
      duplicate_rows=('is_duplicate_in_group', 'sum')
  ).reset_index()
  print(duplicate_summary)
  

• View all duplicate rows (including their original group context):

  # Grab all rows that have any duplicate in their group
  duplicate_rows = df[df['has_duplicate_in_group']]
  print(duplicate_rows)
  

• Pro tip: If testtime is stored as a string, convert it to datetime first to avoid false duplicates:

  df['testtime'] = pd.to_datetime(df['testtime'])
  

2. Remove Duplicates Within Groups #

Choose the method that fits your use case:

Option 1: Keep the First Occurrence of Each Duplicate Pair #

This deletes subsequent duplicates in each group, keeping the first instance:

# Using groupby + apply + drop_duplicates
df_cleaned = df.groupby(group_columns, group_keys=False).apply(
    lambda group: group.drop_duplicates(subset=duplicate_check_cols, keep='first')
)

Option 2: Delete All Duplicate Instances #

If you only want to keep rows where testtime + responsetime are unique within the group:

df_cleaned = df.groupby(group_columns, group_keys=False).apply(
    lambda group: group.drop_duplicates(subset=duplicate_check_cols, keep=False)
)

Option 3: Use the Flag Column for Simplicity #

If you already added the flag columns, you can filter directly:

# Keep first occurrence (remove subsequent duplicates)
df_cleaned = df[~df['is_duplicate_in_group']]

# Keep only unique rows (remove all duplicates)
df_cleaned = df[~df['has_duplicate_in_group']]

Bonus: Keep the Most Recent Duplicate #

If you want to retain the latest entry instead of the first, sort first:

df_cleaned = df.groupby(group_columns, group_keys=False).apply(
    lambda group: group.sort_values('testtime', ascending=False)
                      .drop_duplicates(subset=duplicate_check_cols, keep='first')
)

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