如何将日期范围格式的数据转换为按区域聚合的时间序列？

Got it, let's work through this problem step by step to transform your date range records into a daily time series that counts active records per region. We'll use Pandas here—it's perfect for time series operations and range-based aggregations.

Step 1: Load and Prepare the Data #

First, let's turn your raw data into a Pandas DataFrame with proper datetime types (critical for accurate date range checks).

import pandas as pd

# Raw input data converted to a list of lists
data = [
    [0, "2020-01-01", "2021-06-09", "region_a"],
    [1, "2020-06-29", "2021-06-09", "region_a"],
    [2, "2020-01-01", "2020-06-29", "region_a"],
    [3, "2020-01-01", "2021-06-09", "region_b"],
    [4, "2020-01-01", "2021-06-09", "region_b"],
    [5, "2020-01-01", "2021-06-09", "region_a"],
    [6, "2020-01-01", "2021-06-09", "region_a"],
    [7, "2020-07-08", "2021-06-09", "region_a"],
    [8, "2020-01-01", "2020-07-08", "region_a"],
    [9, "2021-05-10", "2021-06-09", "region_a"],
    [10, "2020-01-01", "2021-05-10", "region_a"],
    [11, "2020-01-01", "2021-06-09", "region_a"],
    [12, "2020-01-01", "2021-06-09", "region_a"],
    [13, "2020-01-01", "2021-06-09", "region_a"],
    [14, "2020-01-01", "2021-06-09", "region_a"],
    [15, "2020-01-01", "2021-06-09", "region_a"],
    [16, "2020-01-01", "2021-06-09", "region_a"],
    [17, "2020-01-01", "2021-06-09", "region_b"],
    [18, "2020-01-01", "2021-06-09", "region_a"],
    [19, "2020-02-10", "2021-06-09", "region_a"],
    [20, "2020-01-01", "2020-02-10", "region_a"],
    [21, "2020-01-01", "2021-06-09", "region_a"],
    [22, "2020-01-01", "2021-06-09", "region_b"],
    [23, "2020-01-01", "2021-06-09", "region_a"],
    [24, "2020-05-31", "2021-06-09", "region_b"],
    [25, "2020-01-01", "2020-05-31", "region_b"],
    [26, "2020-07-31", "2021-06-09", "region_a"],
    [27, "2020-03-01", "2020-07-31", "region_a"],
    [28, "2020-01-01", "2020-03-01", "region_a"],
    [29, "2021-03-08", "2021-06-09", "region_a"],
    [30, "2020-03-31", "2021-03-08", "region_a"],
    [31, "2020-01-01", "2020-03-31", "region_a"],
    [32, "2020-01-01", "2021-06-09", "region_a"],
    [33, "2020-01-01", "2021-06-09", "region_a"],
    [34, "2020-12-31", "2021-06-09", "region_a"],
    [35, "2020-01-01", "2020-12-31", "region_a"],
    [36, "2020-01-01", "2021-06-09", "region_a"],
    [37, "2021-03-17", "2021-06-09", "region_a"],
    [38, "2020-01-01", "2021-03-17", "region_a"],
    [39, "2020-01-01", "2021-06-09", "region_a"],
    [40, "2021-03-31", "2021-06-09", "region_b"],
    [41, "2020-01-01", "2021-03-31", "region_b"],
    [42, "2020-01-01", "2021-06-09", "region_a"],
    [43, "2020-05-31", "2021-06-09", "region_b"],
    [44, "2020-01-01", "2020-05-31", "region_b"],
    [45, "2021-05-08", "2021-06-09", "region_c"],
    [46, "2021-03-31", "2021-05-08", "region_c"],
    [47, "2020-12-31", "2021-03-31", "region_c"],
    [48, "2020-01-01", "2020-12-31", "region_a"],
    [49, "2020-01-01", "2021-06-09", "region_a"]
]

# Create DataFrame and convert date columns to datetime objects
df = pd.DataFrame(data, columns=["ID", "BEG_DT", "END_DT", "REGION"])
df["BEG_DT"] = pd.to_datetime(df["BEG_DT"])
df["END_DT"] = pd.to_datetime(df["END_DT"])

Step 2: Generate the Full Daily Time Series Index #

We'll create a continuous daily date range from the earliest start date to the latest end date in your dataset—this will be the index for our final output.

# Define the bounds of our time series
min_date = df["BEG_DT"].min()
max_date = df["END_DT"].max()

# Create a daily datetime index
date_index = pd.date_range(start=min_date, end=max_date, freq="D")

Step 3: Calculate Active Record Counts per Region #

For each region, we'll count how many records are active on each date (i.e., the date falls between the record's BEG_DT and END_DT). We'll use Pandas' IntervalIndex for efficient range checks.

# Initialize empty DataFrame to store results
result = pd.DataFrame(index=date_index)

# Process each region individually
for region in ["region_a", "region_b", "region_c"]:
    # Filter records for the current region
    region_data = df[df["REGION"] == region]
    
    # Create an IntervalIndex to represent all date ranges for the region
    intervals = pd.IntervalIndex.from_arrays(region_data["BEG_DT"], region_data["END_DT"], closed="both")
    
    # For each date in our index, count how many intervals include it
    result[region] = date_index.to_series().apply(lambda x: intervals.contains(x).sum())

Step 4: View the Final Output #

The resulting DataFrame has daily dates as the index, with columns for each region showing the number of active records on that day. Here's a quick preview:

# Print the first 5 rows
print(result.head())

Sample output snippet:

region_a  region_b  region_c
2020-01-01        14         4         0
2020-01-02        14         4         0
2020-01-03        14         4         0
2020-01-04        14         4         0
2020-01-05        14         4         0

To check a date where region_c becomes active:

print(result.loc["2020-12-31"])

Output:

region_a    28
region_b     6
region_c     1
Name: 2020-12-31 00:00:00, dtype: int64

Key Notes #

• The closed="both" parameter ensures we include both the start and end dates in our range checks (matches your requirement that dates between BEG_DT and END_DT are counted).
• This method is efficient even for larger datasets—IntervalIndex is optimized for these kinds of range queries.
• If you need to resample to a different frequency (e.g., weekly, monthly), you can use result.resample("W").sum() or similar after generating the daily counts.

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