How to Filter Grouped Rows by 20‑Second Intervals Using Pandas

1. Introduction

The author received a question about processing a table that contains columns 编号, 环节, 审核人, 金额, and 结束时间. The task is to group by the first four columns, sort each group by 结束时间 in ascending order, and within each group keep only the first record when the time difference between consecutive rows is less than 20 seconds.

2. Solution Overview

The solution reads the Excel file with Pandas, performs the required grouping and sorting, converts the 结束时间 column to datetime, and then applies a custom filter that removes rows whose time difference is under 20 seconds, keeping only the first occurrence.

import pandas as pd
# Read Excel file
df = pd.read_excel('工作量计算.xlsx', index_col=None)

# Group by the four key columns and sort by "结束时间"
df = df.groupby(['编号', '环节', '审核人', '金额']).apply(lambda x: x.sort_values('结束时间', ascending=True))
df.reset_index(drop=True, inplace=True)
df['结束时间'] = pd.to_datetime(df['结束时间'])

# Function to filter rows within each group
def filter_rows(group):
    diff = group.groupby('编号')['结束时间'].diff()
    mask = (diff.dt.total_seconds() < 20)
    group = group[~mask].drop_duplicates(keep='first')
    return group

# Apply filter to each group
result = df.groupby(['编号', '环节', '审核人', '金额']).apply(filter_rows)
result.reset_index(drop=True, inplace=True)
print(result)

3. Result

After running the script, the filtered dataset contains 3,394 rows, satisfying the requirement that any two timestamps within the same group are at least 20 seconds apart.

4. Summary

The provided Pandas code demonstrates a practical approach to automate data cleaning tasks that involve grouping, sorting, and time‑based deduplication, which can be directly applied to similar real‑world problems.