python - 如何使用 Pandas 在两个日期之间生成观察结果

Question

我有以下格式的数据集:

User ID   Start Date   End Date
1         '2000-01-01' '2000-03-01'
2         '2002-01-01' '2002-08-01'
...         ....         ....
10        '2003-03-01' '2004-01-01'

如何为每个用户生成一个包含开始日期和结束日期之间的每个日期的数据集，如下所示:

User ID   Start Date  End Date      Activity Date
1         '2000-01-01' '2000-03-01' '2000-01-01'
1         '2000-01-01' '2000-03-01' '2000-02-01' 
1         '2000-01-01' '2000-03-01' '2000-03-01' 
2         '2002-01-01' '2002-08-01'  '2002-01-01'
...         ....           ...           ...
2         '2002-01-01' '2002-08-01'  '2002-07-01'
2         '2002-01-01' '2002-08-01'  '2002-08-01'
...         ....           ...           ...
...         ....           ...           ...
10        '2003-03-01' '2004-01-01'  '2003-03-01'
10        '2003-03-01' '2004-01-01'  '2003-04-01'
...         ....           ...           ...
...         ....           ...           ...
10        '2003-03-01' '2004-01-01'  '2003-12-01'
10        '2003-03-01' '2004-01-01'  '2004-01-01'

Answer 1

使用 pd.date_range 生成从开始日期到结束日期的日期。我通过执行 freq=30D 将频率设置为 30 天 - 选择您方便的时间。

df['Activity Date'] = df.apply(lambda s: pd.date_range(s['Start Date'], s['End Date'], freq='30D').tolist(), 1)
df = df.explode('Activity Date')

---

   User ID Start Date   End Date Activity Date
0        1 2000-01-01 2000-03-01    2000-01-01
0        1 2000-01-01 2000-03-01    2000-01-31
0        1 2000-01-01 2000-03-01    2000-03-01
1        2 2002-01-01 2002-08-01    2002-01-01
1        2 2002-01-01 2002-08-01    2002-01-31
1        2 2002-01-01 2002-08-01    2002-03-02
1        2 2002-01-01 2002-08-01    2002-04-01
1        2 2002-01-01 2002-08-01    2002-05-01
1        2 2002-01-01 2002-08-01    2002-05-31
1        2 2002-01-01 2002-08-01    2002-06-30
1        2 2002-01-01 2002-08-01    2002-07-30
2       10 2003-03-01 2004-01-01    2003-03-01
2       10 2003-03-01 2004-01-01    2003-03-31
2       10 2003-03-01 2004-01-01    2003-04-30
2       10 2003-03-01 2004-01-01    2003-05-30
2       10 2003-03-01 2004-01-01    2003-06-29
2       10 2003-03-01 2004-01-01    2003-07-29
2       10 2003-03-01 2004-01-01    2003-08-28
2       10 2003-03-01 2004-01-01    2003-09-27
2       10 2003-03-01 2004-01-01    2003-10-27
2       10 2003-03-01 2004-01-01    2003-11-26
2       10 2003-03-01 2004-01-01    2003-12-26