如何为DataFrame各ID随机选取指定时长的日期区间数据

需要为DataFrame中的每个account_id，在其date字段的最小和最大值范围内，随机选取1周、2周或4周时长的时间区间，并提取该区间内的所有行数据。例如某account_id的日期范围是2022-09-01至2022-09-28，需随机选取如2022-09-06至2022-09-12的7天区间数据。

原思路是随机采样start_date，加上指定时长得到end_date，若end_date超出该ID的最大日期则重新采样，但这种方法会遗漏很多组合，希望找到更优的实现方案。

示例输入数据 #

import pandas as pd
from pandas import Timestamp

data = [{'account_id': 1, 'date': Timestamp('2022-09-01 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-02 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-03 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-04 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-05 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-06 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-07 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-08 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-09 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-10 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-11 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-12 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-13 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-14 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-15 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-16 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-17 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-18 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-19 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-20 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-21 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-22 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-23 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-24 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-25 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-26 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-27 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-28 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-01 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-02 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-03 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-04 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-05 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-06 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-07 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-08 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-09 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-10 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-11 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-12 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-13 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-14 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-15 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-16 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-17 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-18 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-19 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-20 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-21 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-22 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-23 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-24 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-25 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-26 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-27 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-28 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-29 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-30 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-10-01 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-10-02 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-10-03 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-10-04 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-10-05 00:00:00')}]

df = pd.DataFrame(data)

核心思路 #

直接计算每个account_id下对应所选时长的有效起始日期范围，在该范围内随机选取起始日期，无需重复采样，确保所有可能的有效区间都有被选中的机会：

• 按account_id分组，获取每组的日期边界
• 随机选择时长（7/14/28天）
• 计算该时长下允许的最晚起始日期（最大日期减去时长）
• 在最小日期到最晚起始日期之间随机选起始日期
• 筛选起始日期到起始日期+时长范围内的所有行

代码实现 #

import numpy as np

def random_time_window(group):
    # 获取当前组的最小、最大日期
    min_date = group['date'].min()
    max_date = group['date'].max()
    
    # 可选时长列表（单位：天）
    durations = [7, 14, 28]
    # 随机选一个时长
    duration = np.random.choice(durations)
    
    # 计算允许的最晚起始日期
    latest_start = max_date - pd.Timedelta(days=duration)
    
    # 处理日期范围不足时长的情况（默认取最小日期作为起始）
    if latest_start < min_date:
        start_date = min_date
    else:
        # 生成所有可能的起始日期，随机选一个
        possible_starts = pd.date_range(start=min_date, end=latest_start, freq='D')
        start_date = np.random.choice(possible_starts)
    
    # 计算结束日期
    end_date = start_date + pd.Timedelta(days=duration)
    
    # 筛选区间内的行
    return group[(group['date'] >= start_date) & (group['date'] < end_date)]

# 应用分组处理并重置索引
output = df.groupby('account_id').apply(random_time_window).reset_index(drop=True)

示例输出（随机结果） #

output_data = [{'account_id': 1, 'date': Timestamp('2022-09-06 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-07 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-08 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-09 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-10 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-11 00:00:00')},
 {'account_id': 1, 'date': Timestamp('2022-09-12 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-15 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-16 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-17 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-18 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-19 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-20 00:00:00')},
 {'account_id': 2, 'date': Timestamp('2022-09-21 00:00:00')}]

output = pd.DataFrame(output_data)

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