我有项目的历史记录及其相关的计划开始和结束时间:
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 的解决方案来杀死我上面的内容。
请您参考如下方法:
矢量化方式...但会耗尽内存。仍在研究更好的矢量化方式。我有概念，只是在吃晚饭时处理细节。
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])
