如何基于时间差删除DataFrame中的重复行并保留最新记录？

Problem Description #

I have a Pandas DataFrame with ID, DATE_ENCOUNTER, and LOAD fields (plus more fields and IDs in real scenarios). Records with dates within 4 days of each other are considered near-duplicates, and I need to remove these duplicates while keeping only the most recent record in each group of close dates.

Sample Data #

Original DataFrame:

Desired Output:

Code to Generate the DataFrame #

import pandas as pd
details = {
    'ID':[151336,151336,151336,151336,151336,151336,151336,151336,151336,151336,151336,151336,151336,151336,151336],
    'DATE_ENCOUNTER':['2017-08-22','2017-08-23','2017-08-24','2017-08-25','2017-09-05','2017-09-06','2017-10-16','2017-10-17','2017-10-18','2017-10-30','2017-10-31','2017-11-01','2017-12-13','2018-01-03','2018-02-09'],
    'LOAD':[40,40,40,40,50,50,51,51,51,50,50,50,62,65,60]
}
df = pd.DataFrame(details)

Attempted Code (Not Working as Expected) #

m = df.groupby('ID').DATE_ENCOUNTER.apply(lambda x: x.diff().dt.days < 4)
m2 = df.ID.duplicated(keep=False) & (m | m.shift(-1))
df_dedup2 = df[~m2]

Solution #

The core idea is to group consecutive records with dates within 4 days, then retain only the most recent entry in each group. Here's a scalable approach that works for multiple IDs and additional fields:

# Step 1: Convert date column to datetime (required for date difference calculations)
df['DATE_ENCOUNTER'] = pd.to_datetime(df['DATE_ENCOUNTER'])

# Step 2: Sort by ID and date to ensure chronological order
df_sorted = df.sort_values(['ID', 'DATE_ENCOUNTER'])

# Step 3: Create group IDs for records within 4 days of each other
# Increment group number whenever the date gap from the previous record is >=4 days
df_sorted['group'] = df_sorted.groupby('ID')['DATE_ENCOUNTER'].apply(
    lambda x: (x.diff().dt.days >= 4).cumsum()
)

# Step 4: Keep only the last (most recent) record per ID and group
df_dedup = df_sorted.groupby(['ID', 'group']).last().reset_index(drop=True)

# Check the result
print(df_dedup)

Explanation #

1. Datetime Conversion: The original date strings can't be used for calculations, so converting to datetime is essential.
2. Sorting: Ensures we process records in the correct chronological order, which is critical for grouping close dates.
3. Group Creation: Using cumsum() on the date gap condition creates unique groups for each set of records that are more than 4 days apart. All records within a 4-day window get the same group ID.
4. Retain Most Recent: groupby().last() picks the latest entry in each group, exactly matching your desired output.

This method handles multiple IDs and extra fields seamlessly, preserving all columns while removing near-duplicates.

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