python - 如何从事件持续时间的数据帧创建时间序列？

Question

我有一个数据框，其中包含一个房间的预订(行:booking_id、入住日期和退房日期，我想将其转换为按全年日期索引的时间序列(索引:一年中的天数，特征:已预订或不)。

我计算了预订的持续时间，并每天重新索引数据框。现在我需要前向填充数据框，但次数有限:每次预订的持续时间。

尝试使用 ffill 遍历每一行，但它适用于整个数据框，而不是选定的行。知道我该怎么做吗？

这是我的代码:

import numpy as np
import pandas as pd
#create dataframe
data=[[1, '2019-01-01', '2019-01-02', 1],
      [2, '2019-01-03', '2019-01-07', 4], 
      [3, '2019-01-10','2019-01-13', 3]]
df = pd.DataFrame(data, columns=['booking_id', 'check-in', 'check-out', 'duration'])

#cast dates to datetime formats
df['check-in'] = pd.to_datetime(df['check-in'])
df['check-out'] = pd.to_datetime(df['check-out'])

#create timeseries indexed on check-in date
df2 = df.set_index('check-in')

#create new index and reindex timeseries
idx = pd.date_range(min(df['check-in']), max(df['check-out']), freq='D')
ts = df2.reindex(idx)

我有这个:

    booking_id  check-out   duration
2019-01-01  1.0     2019-01-02  1.0
2019-01-02  NaN     NaT     NaN
2019-01-03  2.0     2019-01-07  4.0
2019-01-04  NaN     NaT     NaN
2019-01-05  NaN     NaT     NaN
2019-01-06  NaN     NaT     NaN
2019-01-07  NaN     NaT     NaN
2019-01-08  NaN     NaT     NaN
2019-01-09  NaN     NaT     NaN
2019-01-10  3.0     2019-01-13  3.0
2019-01-11  NaN     NaT     NaN
2019-01-12  NaN     NaT     NaN
2019-01-13  NaN     NaT     NaN

我希望有:

    booking_id  check-out   duration
2019-01-01  1.0     2019-01-02  1.0
2019-01-02  1.0     2019-01-02      1.0
2019-01-03  2.0     2019-01-07  4.0
2019-01-04  2.0     2019-01-07  4.0
2019-01-05  2.0     2019-01-07  4.0
2019-01-06  2.0     2019-01-07  4.0
2019-01-07  NaN     NaT     NaN
2019-01-08  NaN     NaT     NaN
2019-01-09  NaN     NaT     NaN
2019-01-10  3.0     2019-01-13  3.0
2019-01-11  3.0     2019-01-13  3.0
2019-01-12  3.0     2019-01-13  3.0
2019-01-13  NaN     NaT     NaN

Answer 1

filluntil = ts['check-out'].ffill()
m = ts.index < filluntil.values

#reshaping the mask to be shame shape as ts
m = np.repeat(m, ts.shape[1]).reshape(ts.shape)

ts = ts.ffill().where(m)

首先，我们创建一个系列，其中包含日期。然后我们创建一个掩码，其中索引小于填充值。然后我们根据掩码进行填充。

如果你想包含退房日期的行，将 m 从 < 更改为 <=