如何在一行中计算数据框中的并发事件?
问题描述
我有一个电话数据集.我想计算每条记录有多少活动呼叫.我发现了这个问题，但我想避免循环和功能.
I have a dataset with phone calls. I want to count how many active calls there are for each record. I found this question but I'd like to avoid loops and functions.
每个调用都有一个日期、一个开始时间和一个结束时间.
Each call has a date, a start time and a end time.
数据框:
start end date
0 09:17:12 09:18:20 2016-08-10
1 09:15:58 09:17:42 2016-08-11
2 09:16:40 09:17:49 2016-08-11
3 09:17:05 09:18:03 2016-08-11
4 09:18:22 09:18:30 2016-08-11
我想要什么:
start end date activecalls
0 09:17:12 09:18:20 2016-08-10 1
1 09:15:58 09:17:42 2016-08-11 1
2 09:16:40 09:17:49 2016-08-11 2
3 09:17:05 09:18:03 2016-08-11 3
4 09:18:22 09:18:30 2016-08-11 1
我的代码:
import pandas as pd
df = pd.read_clipboard(sep='ss+')
df['activecalls'] = df[(df['start'] <= df.loc[df.index]['start']) &
(df['end'] > df.loc[df.index]['start']) &
(df['date'] == df.loc[df.index]['date'])].count()
print(df)
我得到了什么:
start end date activecalls
0 09:17:12 09:18:20 2016-08-10 NaN
1 09:15:58 09:17:42 2016-08-11 NaN
2 09:16:40 09:17:49 2016-08-11 NaN
3 09:17:05 09:18:03 2016-08-11 NaN
4 09:18:22 09:18:30 2016-08-11 NaN
解决方案
你可以使用:
#convert time and date to datetime
df['date_start'] = pd.to_datetime(df.start + ' ' + df.date)
df['date_end'] = pd.to_datetime(df.end + ' ' + df.date)
#remove columns
df = df.drop(['start','end','date'], axis=1)
带循环的解决方案:
active_events= []
for i in df.index:
active_events.append(len(df[(df["date_start"]<=df.loc[i,"date_start"]) &
(df["date_end"]> df.loc[i,"date_start"])]))
df['activecalls'] = pd.Series(active_events)
print (df)
date_start date_end activecalls
0 2016-08-10 09:17:12 2016-08-10 09:18:20 1
1 2016-08-11 09:15:58 2016-08-11 09:17:42 1
2 2016-08-11 09:16:40 2016-08-11 09:17:49 2
3 2016-08-11 09:17:05 2016-08-11 09:18:03 3
4 2016-08-11 09:18:22 2016-08-11 09:18:30 1
merge
#cross join
df['tmp'] = 1
df1 = pd.merge(df,df.reset_index(),on=['tmp'])
df = df.drop('tmp', axis=1)
#print (df1)
#filtering by conditions
df1 = df1[(df1["date_start_x"]<=df1["date_start_y"])
(df1["date_end_x"]> df1["date_start_y"])]
print (df1)
date_start_x date_end_x activecalls_x tmp index
0 2016-08-10 09:17:12 2016-08-10 09:18:20 1 1 0
6 2016-08-11 09:15:58 2016-08-11 09:17:42 1 1 1
7 2016-08-11 09:15:58 2016-08-11 09:17:42 1 1 2
8 2016-08-11 09:15:58 2016-08-11 09:17:42 1 1 3
12 2016-08-11 09:16:40 2016-08-11 09:17:49 2 1 2
13 2016-08-11 09:16:40 2016-08-11 09:17:49 2 1 3
18 2016-08-11 09:17:05 2016-08-11 09:18:03 3 1 3
24 2016-08-11 09:18:22 2016-08-11 09:18:30 1 1 4
date_start_y date_end_y activecalls_y
0 2016-08-10 09:17:12 2016-08-10 09:18:20 1
6 2016-08-11 09:15:58 2016-08-11 09:17:42 1
7 2016-08-11 09:16:40 2016-08-11 09:17:49 2
8 2016-08-11 09:17:05 2016-08-11 09:18:03 3
12 2016-08-11 09:16:40 2016-08-11 09:17:49 2
13 2016-08-11 09:17:05 2016-08-11 09:18:03 3
18 2016-08-11 09:17:05 2016-08-11 09:18:03 3
24 2016-08-11 09:18:22 2016-08-11 09:18:30 1
#get size - active calls
print (df1.groupby(['index'], sort=False).size())
index
0 1
1 1
2 2
3 3
4 1
dtype: int64
df['activecalls'] = df1.groupby('index').size()
print (df)
date_start date_end activecalls
0 2016-08-10 09:17:12 2016-08-10 09:18:20 1
1 2016-08-11 09:15:58 2016-08-11 09:17:42 1
2 2016-08-11 09:16:40 2016-08-11 09:17:49 2
3 2016-08-11 09:17:05 2016-08-11 09:18:03 3
4 2016-08-11 09:18:22 2016-08-11 09:18:30 1
时间安排:
def a(df):
active_events= []
for i in df.index:
active_events.append(len(df[(df["date_start"]<=df.loc[i,"date_start"]) & (df["date_end"]> df.loc[i,"date_start"])]))
df['activecalls'] = pd.Series(active_events)
return (df)
def b(df):
df['tmp'] = 1
df1 = pd.merge(df,df.reset_index(),on=['tmp'])
df = df.drop('tmp', axis=1)
df1 = df1[(df1["date_start_x"]<=df1["date_start_y"]) & (df1["date_end_x"]> df1["date_start_y"])]
df['activecalls'] = df1.groupby('index').size()
return (df)
print (a(df))
print (b(df))
In [160]: %timeit (a(df))
100 loops, best of 3: 6.76 ms per loop
In [161]: %timeit (b(df))
The slowest run took 4.42 times longer than the fastest. This could mean that an intermediate result is being cached.
100 loops, best of 3: 4.61 ms per loop
相关文章