python - Pandas 日期间隔的滚动窗口计数

Question

我有项目的历史记录及其相关的计划开始和结束时间:

id   planned_start planned_end
1    2017-09-12    2017-09-13
2    2017-09-12    2017-09-14
3    2017-09-12    2017-09-13
4    2017-09-13    2017-09-13
5    2017-09-12    2017-09-12
6    2017-09-12    2017-09-20
7    2017-09-14    2017-09-15
8    2017-09-14    2017-09-20

我想统计上述项目每个开始日期的并发项目数。这是我的逻辑:

for project_id in df['id']:
    start_date = df[df['id'] == project_id]['planned_start'].values[0]
    concurrent_projects = df[(df['planned_start'] <= start_date) & (df['planned_end'] >= start_date)]
    df.ix[df['id'] == project_id, 'concurrent_projects'] = concurrent_projects.shape[0]

产生这个:

   id planned_start planned_end  concurrent_projects
0   1    2017-09-12  2017-09-13                  5.0
1   2    2017-09-12  2017-09-14                  5.0
2   3    2017-09-12  2017-09-13                  5.0
3   4    2017-09-13  2017-09-13                  5.0
4   5    2017-09-12  2017-09-12                  5.0
5   6    2017-09-12  2017-09-20                  5.0
6   7    2017-09-14  2017-09-15                  4.0
7   8    2017-09-14  2017-09-20                  4.0

但是，我知道上面的 for 循环在时间方面是多么次优。实际上，我有超过 500,000 个项目需要计算。有人可以就如何加快速度提供一些建议吗？我知道必须有一个 pure-pandas 甚至 numpy 的解决方案来杀死我上面的内容。

Answer 1

矢量化方式...但会耗尽内存。仍在研究更好的矢量化方式。我有概念，只是在吃晚饭时处理细节。

s = df.planned_start.values
e = df.planned_end.values

s_ = s >= s[:, None]
e_ = s <= e[:, None]

df.assign(concurrent_projects=(e_ & s_).sum(0))

   id planned_start planned_end  concurrent_projects
0   1    2017-09-12  2017-09-13                    5
1   2    2017-09-12  2017-09-14                    5
2   3    2017-09-12  2017-09-13                    5
3   4    2017-09-13  2017-09-13                    5
4   5    2017-09-12  2017-09-12                    5
5   6    2017-09-12  2017-09-20                    5
6   7    2017-09-14  2017-09-15                    4
7   8    2017-09-14  2017-09-20                    4

---

抱歉，我没有时间解释。但我不想让你悬着

k = len(df)

d = np.column_stack([df.planned_start.values, df.planned_end.values + 1]).ravel()

i = np.tile([1, -1], k)
a = d.argsort()

f = np.arange(k).repeat(2)
r = np.zeros(k, int)
z = np.zeros(k, int)
m = np.zeros(k, int)

cumsum = 0

for j in range(f.size):
    x = f[a[j]]
    y = i[a[j]]
    r[x] = cumsum
    z[x] = (y + 1) // 2
    r += y * z
    m = np.column_stack([m, r]).max(1)
    cumsum += y

m

array([5, 5, 5, 5, 5, 5, 4, 4])