Python Pandas合并连续与重叠日期范围问题求助（SAS代码迁移场景）

Got it, let's tackle this date range merging problem you're facing. The core issue with your existing code is that it only compares each row's start date to the previous row's end date, not the maximum end date of all prior merged ranges—which misses cases where a new range is fully contained within an earlier, longer range.

Here's a robust solution that handles overlaps, contained ranges, and your configurable gap threshold:

Step-by-Step Explanation #

1. Sort within groups: First, ensure each customer/service tier group is sorted by start date to process ranges in chronological order.
2. Track cumulative max end date: Use an expanding window to keep track of the latest end date across all prior rows in the group. This ensures we don't lose sight of longer ranges that contain shorter ones.
3. Identify new groups: Mark a new group when the current start date falls more than your threshold days after the cumulative max end date of the previous merged group.
4. Aggregate groups: Finally, merge all rows in each group to get the earliest start and latest end date.

Full Working Code #

import pandas as pd

# Your test data (converted to datetime)
data = [
    ["CUST123", 1, "2020-06-13", "2020-06-27"],
    ["CUST123", 1, "2020-06-15", "2020-07-13"],
    ["CUST123", 1, "2020-06-22", "2020-07-23"],
    ["CUST123", 1, "2020-06-24", "2020-07-08"],
    ["CUST123", 1, "2020-07-13", "2020-07-27"],
    ["CUST123", 1, "2020-08-20", "2020-09-17"],
    ["CUST123", 1, "2020-08-21", "2020-09-04"],
    ["CUST123", 1, "2020-08-25", "2020-09-05"],
    ["CUST123", 1, "2020-08-27", "2020-09-21"],
    ["CUST123", 1, "2020-09-01", "2020-10-05"],
    ["CUST123", 1, "2020-09-10", "2020-09-17"],
    ["CUST123", 1, "2020-10-05", "2020-11-30"],
    ["CUST123", 1, "2020-12-01", "2021-05-31"],
    ["CUST123", 1, "2021-06-01", "2021-11-30"],
    ["CUST456", 1, "2021-01-05", "2021-01-15"],
    ["CUST456", 2, "2021-01-16", "2021-01-31"]
]

df = pd.DataFrame(data, columns=["cust", "service_tier", "start_date", "end_date"])
df["start_date"] = pd.to_datetime(df["start_date"])
df["end_date"] = pd.to_datetime(df["end_date"])

# Configure your gap threshold here
threshold_days = 3

def merge_ranges(group):
    # Sort the group by start date to process in chronological order
    sorted_group = group.sort_values("start_date").reset_index(drop=True)
    
    # Track the maximum end date across all prior rows (handles contained ranges)
    sorted_group["cumulative_max_end"] = sorted_group["end_date"].expanding().max()
    
    # Flag rows that start a new group (gap > threshold from cumulative max end)
    sorted_group["new_group"] = sorted_group["start_date"] > (sorted_group["cumulative_max_end"].shift(1) + pd.Timedelta(days=threshold_days))
    sorted_group["new_group"] = sorted_group["new_group"].fillna(True)  # First row is always a new group
    
    # Assign group IDs by counting new group flags
    sorted_group["group_id"] = sorted_group["new_group"].cumsum()
    
    # Aggregate each group to get merged start/end dates
    merged = sorted_group.groupby("group_id").agg(
        start_date=("start_date", "min"),
        end_date=("end_date", "max")
    ).reset_index(drop=True)
    
    # Add back the customer and service tier columns
    merged["cust"] = group["cust"].iloc[0]
    merged["service_tier"] = group["service_tier"].iloc[0]
    
    return merged[["cust", "service_tier", "start_date", "end_date"]]

# Apply the function to each customer/service tier group
final_result = df.groupby(["cust", "service_tier"], group_keys=False).apply(merge_ranges).reset_index(drop=True)
print(final_result)

Output (Matches Your Expected Result) #

Why This Works Better Than Your Original Code #

Your initial approach used d["end_date"].shift() which only looks at the immediately preceding row's end date. For example, the row 2020-07-13 to 2020-07-27 was compared to the prior row's end date of 2020-07-08 (instead of the earlier, longer range's end date of 2020-07-23). Using expanding().max() fixes this by always referencing the latest end date from all previous merged ranges.

You can adjust the threshold_days variable to match whatever gap you want to consider "contiguous"—just like your old SAS program!

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