如何在一行中计算数据框中的并发事件?

本文介绍了如何在一行中计算数据框中的并发事件?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个电话数据集.我想计算每条记录有多少活动呼叫.我发现了这个问题，但我想避免循环和功能.

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