Pandas时间序列去重：时间列唯一时，如何按时间差检测指定列重复？

Got it, let's tackle this problem step by step! Since your time column is unique, the standard df.duplicated() method won't pick up the duplicates you care about—we need to combine checks on columns A/B/C and the time gap between rows. Here's how to do this with pandas:

Step 1: Ensure your time column is datetime-aware #

First, make sure pandas recognizes your time column as a datetime type (critical for calculating time differences). If it's not already, convert it:

import pandas as pd

# If your time is in a standard string format (e.g., "2024-05-20 14:30:00.123")
df['time'] = pd.to_datetime(df['time'])

# If your time is a Unix timestamp (in milliseconds), use this instead:
# df['time'] = pd.to_datetime(df['time'], unit='ms')

Step 2: Sort and calculate time differences within A/B/C groups #

We'll group rows by columns A, B, C, sort each group by time, then compute the time difference between consecutive rows in the group:

# Sort the dataframe to group identical A/B/C values together, ordered by time
df_sorted = df.sort_values(by=['A', 'B', 'C', 'time']).reset_index(drop=True)

# Calculate time difference (in milliseconds) between each row and the previous row in its A/B/C group
df_sorted['time_diff_ms'] = df_sorted.groupby(['A', 'B', 'C'])['time'].diff().dt.total_seconds() * 1000

Step 3: Flag rows to delete #

Mark any row where the time difference from the prior row in the same A/B/C group is less than 200ms (these are the duplicates you want to remove):

# Flag rows: time_diff_ms <200ms AND not the first row in the group (since diff() returns NaN for first row)
df_sorted['to_drop'] = (df_sorted['time_diff_ms'] < 200) & (~df_sorted['time_diff_ms'].isna())

Step 4: Filter out duplicates #

Finally, drop the flagged rows and clean up the helper columns:

# Keep only rows that aren't marked for deletion
df_deduped = df_sorted[~df_sorted['to_drop']].drop(columns=['time_diff_ms', 'to_drop'])

Quick Notes #

• This method keeps the earliest row in each duplicate cluster (since we sort by time ascending and drop subsequent rows). If you want to keep the latest row instead, sort by time in descending order (ascending=False) in Step 2.
• Double-check your time unit conversion! If your time column uses microseconds or seconds, adjust the total_seconds() multiplication accordingly (e.g., multiply by 1e6 for microseconds).

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