距离到多个不规则采样的事件熊猫

问题描述：

     event 
Time       
2013-01-01 01:40:53.072  n 
2013-01-01 01:41:25.563  e 
2013-01-01 01:51:23.293  e 
2013-01-01 01:57:14.168  e 
2013-01-01 01:58:07.273  e 
2013-01-01 02:05:36.250  e 
2013-01-01 02:35:08.501  e 
2013-01-01 02:37:36.498  e 
2013-01-01 03:22:15.091  e 
2013-01-01 03:35:58.140  e 
2013-01-01 03:39:47.682  e 
2013-01-01 04:22:18.756  e 
2013-01-01 04:33:08.892  e 
2013-01-01 04:43:17.985  n 
2013-01-01 04:49:49.281  e 
2013-01-01 05:10:26.957  e 
2013-01-01 05:17:15.411  e 
2013-01-01 06:11:15.033  e 
2013-01-01 06:46:36.406  e 
2013-01-01 07:26:00.488  e

，我想计算每个事件n之间的累积经过的时间。还有一个类似的问题（Pandas time series time between events），但由于不规则的timeindex，我无法使解决方案适应我的问题。我尝试使用df1['diff']=df1.groupby('event_bool')['event_time'].diff() 获得这样的事情：

     event event_bool   diff 
Time              
2013-01-01 01:40:53.072  n  True    NaT 
2013-01-01 01:41:25.563  e  False    NaT 
2013-01-01 01:51:23.293  e  False 00:09:57.730000 
2013-01-01 01:57:14.168  e  False 00:05:50.875000 
2013-01-01 01:58:07.273  e  False 00:00:53.105000 
2013-01-01 02:05:36.250  e  False 00:07:28.977000 
2013-01-01 02:35:08.501  e  False 00:29:32.251000 
2013-01-01 02:37:36.498  e  False 00:02:27.997000 
2013-01-01 03:22:15.091  e  False 00:44:38.593000 
2013-01-01 03:35:58.140  e  False 00:13:43.049000 
2013-01-01 03:39:47.682  e  False 00:03:49.542000 
2013-01-01 04:22:18.756  e  False 00:42:31.074000 
2013-01-01 04:33:08.892  e  False 00:10:50.136000 
2013-01-01 04:43:17.985  n  True    NaT 
2013-01-01 04:49:49.281  e  False 00:16:40.389000 
2013-01-01 05:10:26.957  e  False 00:20:37.676000 
2013-01-01 05:17:15.411  e  False 00:06:48.454000 
2013-01-01 06:11:15.033  e  False 00:53:59.622000 
2013-01-01 06:46:36.406  e  False 00:35:21.373000 
2013-01-01 07:26:00.488  e  False 00:39:24.082000

但是我有以下的未解决问题：

没有为n后的第一个事件e在NAT。结果应该是'00：00：32.491000``
Ho会累计累计n事件之间的经过时间？

link to data file

答

让我们尝试以下操作：

df = df.reset_index()

~~df_out = pd.concat（[DF，df.where（DF [ '事件'] == 'E'）dropna（）[ '时间' ]为.diff（）重命名（ '差异'）]，轴= 1）~~

df_out = pd.concat([df,df['Time'].diff().rename('diff').mask(df['event'] == 'n')],axis=1) 

df_out['cum diff'] = df_out.groupby((df_out.event == 'n').cumsum())['diff'].transform(lambda x: x.fillna(0).cumsum()) 

df_out = df_out.set_index('Time')

更新输出：

      Time event   diff  cum diff 
0 2013-01-01 01:40:53.072  n    NaT  00:00:00 
1 2013-01-01 01:41:25.563  e 00:00:32.491000 00:00:32.491000 
2 2013-01-01 01:51:23.293  e 00:09:57.730000 00:10:30.221000 
3 2013-01-01 01:57:14.168  e 00:05:50.875000 00:16:21.096000 
4 2013-01-01 01:58:07.273  e 00:00:53.105000 00:17:14.201000 
5 2013-01-01 02:05:36.250  e 00:07:28.977000 00:24:43.178000 
6 2013-01-01 02:35:08.501  e 00:29:32.251000 00:54:15.429000 
7 2013-01-01 02:37:36.498  e 00:02:27.997000 00:56:43.426000 
8 2013-01-01 03:22:15.091  e 00:44:38.593000 01:41:22.019000 
9 2013-01-01 03:35:58.140  e 00:13:43.049000 01:55:05.068000 
10 2013-01-01 03:39:47.682  e 00:03:49.542000 01:58:54.610000 
11 2013-01-01 04:22:18.756  e 00:42:31.074000 02:41:25.684000 
12 2013-01-01 04:33:08.892  e 00:10:50.136000 02:52:15.820000 
13 2013-01-01 04:43:17.985  n    NaT  00:00:00 
14 2013-01-01 04:49:49.281  e 00:06:31.296000 00:06:31.296000 
15 2013-01-01 05:10:26.957  e 00:20:37.676000 00:27:08.972000 
16 2013-01-01 05:17:15.411  e 00:06:48.454000 00:33:57.426000 
17 2013-01-01 06:11:15.033  e 00:53:59.622000 01:27:57.048000 
18 2013-01-01 06:46:36.406  e 00:35:21.373000 02:03:18.421000 
19 2013-01-01 07:26:00.488  e 00:39:24.082000 02:42:42.503000

不错！但是，我无法解决'diff'列的转变。第一个'e'应该是'00：00：32.491000'的差异，而不是NaT。第二个'n'之后的第一个'e'应该显示'2013-01-01 04:49:49.281 - 2013-01-01 04：43：17.985 = 00：06：31.296' – lorenzo

@lorenzo Oh ...我在问题中匹配你的输出。这看起来更好吗？ –

工程就像一个魅力！谢谢@Scott – lorenzo

答

不知道什么是NAT转换，但你可以使用一个补法，以取代所有空值的差异列。然后使用.sum（）聚合方法。

答

首先我想到的解决方案使用厕所P，其变为如下所示：

times = [] 
for index, row in df.iterrows(): 
if(row['event'] == 'n'): 
    last = row['Time'] 
times.append(row['Time'] - last) 

df['TimeNew'] = times

但后来，我看到对方的回答，我试图运行一些测试，看看哪个更好的表现。我拼命地跑10次，每次方法，并采取了平均时间：

Lines | Loop method (s) | lambda method (s) | 
--------------------------------------------- 
21 | 0,006838305  | 0,013882545  | 
504 | 0,092648337  | 0,056006076  | 
1000 | 0,169315854  | 0,097687499  | 
10000 | 1,414376600  | 0,746927508  |

Execution time by method

这里发布的答案其实更多的数据速度更快。对于循环的正常表现，这并不令人惊奇。

距离到多个不规则采样的事件熊猫

相关推荐