Filtering Close‑Timestamp Records in Pandas: Keep Only the First Entry

1. Introduction

In a work scenario a data table contains columns "ID", "Stage", "Reviewer", "Amount", and "EndTime". The requirement is to group rows by the first four columns, sort each group by EndTime, and keep only the first record when consecutive EndTime values differ by 20 seconds or less.

2. Implementation

The solution uses pandas. A helper function func iterates over the sorted EndTime series, yields True for the first record of a group and for records whose time gap exceeds 20 seconds, otherwise yields False. The DataFrame is sorted, grouped, and filtered with transform(func).

import pandas as pd

def func(date_s):
    """filter function"""
    min_date = date_s.iloc[0]
    for num, i in enumerate(date_s):
        if num and (i - min_date).seconds <= 20:
            yield False
        else:
            min_date = i
            yield True

df = pd.read_excel('工作量计算.xlsx')
df.sort_values(["编号", "环节", "审核人", "金额", "结束时间"], inplace=True)
df = df[df.groupby(["编号", "环节", "审核人", "金额"])['结束时间'].transform(func)]
print(df)

The resulting DataFrame contains only the first record of each group when the time difference is within 20 seconds, matching the expected behavior.

3. Conclusion

The article demonstrates a practical pandas approach to de‑duplicate rows based on a 20‑second time window within each group, providing a reusable pattern for similar data‑cleaning tasks.