添加符合特定条件的柜台 [英] Add a counter meeting specific conditions

问题描述

问题陈述

给出以下具有两个列的数据集 Column1 & 第2列，再添加另外两个列，分别为 Counter 和 Counting time 。初始化计数器和计数器时间的条件如下：

Given the below data set which has two columns Column1 & Column 2, add another two more column called Counter and Counting time. The conditions to Initialize the counter and Counter time is as follows:

1. 仅当 Column1>中的值增加时，计数器才应递增。 1 和 Column2 = 0


2. 条件满足行中的2个值之后，计数器必须开始递增


3. 计数时间必须包含序列发生的时间值（满足条件的数据点序列）

1. The counter should be incremented only when value in Column1 > 1 and Column2 = 0
2. The Counter must start to increment after 2 values from the condition satisfied row
3. The Counting time must contain the values of number of time the sequence has occurred (Sequence of data points that has satisfied the condition)

具有预期输出的数据框

Column1 Column2 Counter Counter_Time  
1.1254  2.784    0        0
4.678   7.985    0        0  
8.89      0      0        1
7.65      0      0        1  
3.54      0      1        1  
4.32      0      2        1  
9.83      0      3        1
3.86     4.3     0        1
5.63     9.8     0        1
4.53      0      0        2
6.83      0      0        2   
3.431     0      4        2
8.976     0      5        2
9.864     0      6        2
7.3      9.2     0        2
2.3      3.2     0        2
4.3       0      0        3
2.1       0      0        3
4.32      0      7        3  

我遇到了类似的问题，该问题得到了如何增加计数器的答案，但我无法满足上述条件。请注意，计数器应在满足条件的两行之后开始。

I came across similar kind of question got the answer on how to increment the counter but I wasn't able to satisfy the above mentioned conditions. Please note that the Counter should start after the two rows which satisfies the condition.

数据集中的观察

1. 第3行满足条件，计数器未初始化，但 Counter_Time 已递增


2. Counter 从第5行开始（根据条件满足条件的值的前两行不应触发计数器）


3. 第8行的计数器恢复为0， Counter_Time 保持不变


4. 再次， Counter 已经开始从第12行开始增加，而不考虑第10行和第11行。但是 Counter_time 在第10行增加了

1. The row number 3 satisfies the condition, The counter is not initialized but Counter_Time has been incremented
2. The Counter has started from the row number 5(According to condition first 2 row from the condition satisfied values should not trigger the counter)
3. Counter in Row number 8 comes back to 0 and Counter_Time remains same
4. Again, the Counter has started to increment from row number 12 by not considering the row 10 and 11. But Counter_time was incremented at row 10

我已经详细说明了问题陈述，以便专家清楚地提供准确的解决方案。

I have elaborated the problem statement so that it is clear to the experts to provide accurate solutions.

推荐答案

# Load packages
library(tidyverse)
library(data.table)

# Create example data frame
dt <- fread("Column1 Column2
1.1254  2.784
4.678   7.985 
8.89      0
7.65      0  
3.54      0
4.32      0  
9.83      0
3.86     4.3
5.63     9.8
4.53      0
6.83      0  
3.431     0
8.976     0
9.864     0
7.3      9.2
2.3      3.2
4.3       0
2.1       0
4.32      0  ")

### Create Counter_Time
dt2 <- dt %>%
  mutate(Merge_ID = 1:n()) %>%
  mutate(Condition = ifelse(Column1 > 1 & Column2 == 0, 1, 0)) %>%
  mutate(ID = rleid(Condition)) %>%
  mutate(Counter_Time = ifelse(Condition == 0, (ID - 1)/2, ID/2))

### Create Counter
dt3 <- dt2 %>%
  group_by(Counter_Time) %>%
  slice(3:n()) %>%
  filter(Condition == 1) %>%
  ungroup() %>%
  mutate(Counter = 1:n()) %>%
  select(Merge_ID, Counter)

### Merge dt2 and dt3 together, dt4 is the final output
dt4 <- dt2 %>%
  left_join(dt3, by = "Merge_ID") %>%
  mutate(Counter = ifelse(is.na(Counter), 0, Counter)) %>%
  select(Column1, Column2, Counter, Counter_Time)

更新

以下代码是创建 dt2 之后的更新。这样做的目的是确保没有行满足条件时，代码仍会生成 Counter 都等于0的输出。

Update

The following code is an update after dt2 is created. The idea is to make sure when no rows meet the condition, the code still generates an output with Counter all equals to 0.

### Set the index
begin_index <- 3

### Filter the right condition
dt3 <- dt2 %>%
  group_by(Counter_Time) %>%
  slice(begin_index:n()) %>%
  filter(Condition == 1) %>%
  ungroup() 


### Check if dt3 has any rows
if (nrow(dt3) > 0){

  dt3 <- dt3 %>%
    mutate(Counter = 1:n()) %>%
    select(Merge_ID, Counter)

  ### Merge dt2 and dt3 together, dt4 is the final output
  dt4 <- dt2 %>%
    left_join(dt3, by = "Merge_ID") %>%
    mutate(Counter = ifelse(is.na(Counter), 0, Counter)) %>%
    select(Column1, Column2, Counter, Counter_Time)

### If nrow(dt3) is 0, no rows meet the condition
} else {

  ### Create Counter column from dt2
  dt4 <- dt2 %>%
    mutate(Counter = 0) %>%
    select(Column1, Column2, Counter, Counter_Time)

}

这篇关于添加符合特定条件的柜台的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！