日期差小于一个值的总和 [英] Sum if the date difference is smaller than a value

问题描述

我有一个数据库，其中包含一系列机器的错误记录及其对应的日期。有几种错误。即：

初始数据表

  fechayhora id tipo 
 1：2017-03-21 11:03:00 A2_LR1_Z1 APF 
 2：2017-05-03 10:34:00 A2_LR1_Z1 APF 
 3：2017-05-17 08:52:00 A2_LR1_Z1 APF 
 4：2017-05-17 10:46:00 A2_LR1_Z1 APF 
 5：2017-05-17 14:23:00 A2_LR1_Z1 APF 
 6：2017-05-17 17:29:00 A2_LR1_Z1 APF 
  

我要添加一列，该列包含先前发生的尖锐 APF事件的总和，可以说12个小时（实际上，可能会有所不同。）

预期结果：

  fechayhora id tipo number_of_APF_12h 
 1：2017-03-21 11:03:00 A2_LR1_Z1 APF 0 
 2：2017-05-03 10:34:00 A2_LR1_Z1 APF 0 
 3：2017-05-17 08 ：52：00 A2_LR1_Z1 APF 0 
 4：2017-05-17 10:46:00 A2_LR1_Z1 APF 1 
 5：2017-05-17 14:23:00 A2_LR1_Z1 APF 2 
 6 ：2017 -05-17 17:29:00 A2_LR1_Z1 APF 3 
  

解决方案

这里是一种利用 purrr :: map2_dbl（）的解决方案。您可以将小时数更改为所需的小时数。

< pre $ class $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$创建了一个压缩包。 b preventPackageStartupMessages（library（lubridate））

＃示例数据
df<-tribble（
〜fechayhora，〜id，〜tipo，
2017-03 -21 11:03:00， A2_LR1_Z1， APF，
2017-05-03 10:34:00， A2_LR1_Z1， APF，
2017-05 -17 08:52:00， A2_LR1_Z1， APF，
2017-05-17 10:46:00， A2_LR1_Z1， APF，
2017-05 -17 14:23:00， A2_LR1_Z1， APF，
2017-05-17 17:29:00， A2_LR1_Z1， APF
）

＃将fechayhora转换为日期并添加一列时差
df<-df％&％;％
mutate（fechayhora = as.POSIXct（fechayhora），
minus_12 = fechayhora-小时（12））

＃映射fechayh ora和minus_12
＃对于每个（fechayhora，minus_12）对，找到它们之间的所有日期
＃并对返回的逻辑向量求和
df<-df％>％mutate （
number_of_APF_12h = map2_dbl（.x = fechayhora，
.y = minus_12，
.f =〜sum（between（df $ fechayhora，.y，.x））-1））

df％>％
select（fechayhora，number_of_APF_12h）
＃> ＃小动作：6 x 2
＃> fechayhora number_of_APF_12h
＃> < dttm> < dbl>
＃> 1 2017-03-21 11:03:00 0
＃> 2 2017-05-03 10:34:00 0
＃> 3 2017-05-17 08:52:00 0
＃> 4 2017-05-17 10:46:00 1
＃> 5 2017-05-17 14:23:00 2
＃> 6 2017-05-17 17:29:00 3

I have a database that contains the registers of errors of a series of machines, with their correspondent date. There are several kind of errors. Ie:

initial data table

          fechayhora        id tipo
1: 2017-03-21 11:03:00 A2_LR1_Z1  APF
2: 2017-05-03 10:34:00 A2_LR1_Z1  APF
3: 2017-05-17 08:52:00 A2_LR1_Z1  APF
4: 2017-05-17 10:46:00 A2_LR1_Z1  APF
5: 2017-05-17 14:23:00 A2_LR1_Z1  APF
6: 2017-05-17 17:29:00 A2_LR1_Z1  APF

I would to add a column that contains the sum of the events tipye "APF" that have occured in the previous, lets say 12 hours (a parameter actually that I could vary).

Result expected:

          fechayhora        id tipo    number_of_APF_12h
1: 2017-03-21 11:03:00 A2_LR1_Z1  APF  0
2: 2017-05-03 10:34:00 A2_LR1_Z1  APF  0
3: 2017-05-17 08:52:00 A2_LR1_Z1  APF  0
4: 2017-05-17 10:46:00 A2_LR1_Z1  APF  1
5: 2017-05-17 14:23:00 A2_LR1_Z1  APF  2
6: 2017-05-17 17:29:00 A2_LR1_Z1  APF  3 

解决方案

Here is a solution that utilizes purrr::map2_dbl(). You can change the number of hours to whatever you'd like.

suppressPackageStartupMessages(library(tibble))
suppressPackageStartupMessages(library(dplyr))
suppressPackageStartupMessages(library(purrr))
suppressPackageStartupMessages(library(lubridate))

# Example data
df <- tribble(
  ~fechayhora,        ~id,       ~tipo,
  "2017-03-21 11:03:00", "A2_LR1_Z1",  "APF",
  "2017-05-03 10:34:00", "A2_LR1_Z1",  "APF",
  "2017-05-17 08:52:00", "A2_LR1_Z1",  "APF",
  "2017-05-17 10:46:00", "A2_LR1_Z1",  "APF",
  "2017-05-17 14:23:00", "A2_LR1_Z1",  "APF",
  "2017-05-17 17:29:00", "A2_LR1_Z1",  "APF"
)

# Convert fechayhora to date and add a column of the time difference
df <- df %>%
  mutate(fechayhora = as.POSIXct(fechayhora),
         minus_12   = fechayhora - hours(12))

# Map over fechayhora and minus_12
# For each (fechayhora, minus_12) pair, find all the dates between them
# and sum the logical vector that is returned
df <- df %>% mutate(
  number_of_APF_12h = map2_dbl(.x = fechayhora, 
                               .y = minus_12, 
                               .f = ~sum(between(df$fechayhora, .y, .x)) - 1))

df %>%
  select(fechayhora, number_of_APF_12h)
#> # A tibble: 6 x 2
#>            fechayhora number_of_APF_12h
#>                <dttm>             <dbl>
#> 1 2017-03-21 11:03:00                 0
#> 2 2017-05-03 10:34:00                 0
#> 3 2017-05-17 08:52:00                 0
#> 4 2017-05-17 10:46:00                 1
#> 5 2017-05-17 14:23:00                 2
#> 6 2017-05-17 17:29:00                 3

这篇关于日期差小于一个值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！