r - 按组检测序列并为子集计算新变量

Question

我需要在data.frame中按组检测序列并计算新变量。

考虑我有以下data.frame：

df1 <- data.frame(ID = c(1,1,1,1,1,1,1,2,2,2,3,3,3,3),
seqs = c(1,2,3,4,5,6,7,1,2,3,1,2,3,4),
count = c(2,1,3,1,1,2,3,1,2,1,3,1,4,1),
product = c("A", "B", "C", "C", "A,B", "A,B,C", "D", "A", "B", "A", "A", "A,B,C", "D", "D"),
stock = c("A", "A,B", "A,B,C", "A,B,C", "A,B,C", "A,B,C", "A,B,C,D", "A", "A,B", "A,B", "A", "A,B,C", "A,B,C,D", "A,B,C,D"))
df1
> df1
ID seqs count product   stock
1   1    1     2       A       A
2   1    2     1       B     A,B
3   1    3     3       C   A,B,C
4   1    4     1       C   A,B,C
5   1    5     1     A,B   A,B,C
6   1    6     2   A,B,C   A,B,C
7   1    7     3       D A,B,C,D
8   2    1     1       A       A
9   2    2     2       B     A,B
10  2    3     1       A     A,B
11  3    1     3       A       A
12  3    2     1   A,B,C   A,B,C
13  3    3     4       D A,B,C,D
14  3    4     1       D A,B,C,D

我有兴趣计算遵循以下顺序的ID度量：

- Count == 1
- Count > 1
- Count == 1

在示例中，这适用于：

- rows 2, 3, 4 for `ID==1`
- rows 8, 9, 10 for `ID==2`
- rows 12, 13, 14 for `ID==3`

对于这些 ID 和行，我需要计算一个名为new的度量值，该度量值采用序列最后一行product的值，if它位于序列的第二行中，而不是在第一个序列的stock中。

期望的结果如下所示：

> output
ID seq1 seq2 seq3 new
1  1    2    3    4   C
2  2    1    2    3    
3  3    2    3    4   D

注意：

1. 在检测到 ID 的序列中，没有新产品添加到库存中。
2. 在原始数据中，有很多 ID 没有任何序列。
3. 有些ID有多个合格序列。所有这些都应该被记录下来。
4. 计数始终为 1 或更大。
5. 原始数据包含数百万个ID，最多包含 1500 个序列。

您将如何编写一段有效的代码来获得此输出？

Answer 1

这是一个data.table选项：

library(data.table)
char_cols <- c("product", "stock")
setDT(df1)[, 
(char_cols) := lapply(.SD, as.character), 
.SDcols = char_cols] # in case they're factors
df1[, c1 := (count == 1) & 
(shift(count) > 1) & 
(shift(count, 2L) == 1), 
by = ID] #condition1
df1[, pat := paste0("(", gsub(",", "|", product), ")")] # pattern
df1[, c2 := mapply(grepl, pat, shift(product)) & 
!mapply(grepl, pat, shift(stock, 2L)), 
by = ID] # condition2
df1[(c1), new := ifelse(c2, product, "")] # create new column
df1[, paste0("seq", 1:3) := shift(seqs, 2:0)] # create seq columns
df1[(c1), .(ID, seq1, seq2, seq3, new)] # result

Answer 2

这是使用tidyverse的另一种方法;但是，我认为lag和lead使这个解决方案有点耗时。我在代码中包含注释以使其更清晰。

^{但我花了足够的时间在上面，无论如何都要发布它。}

library(tidyverse)
df1 %>% group_by(ID) %>%  
# this finds the row with count > 1 which ...
#... the counts of the row before and the one of after it equals to 1
mutate(test = (count > 1 & c(F, lag(count==1)[-1]) & c(lead(count==1)[-n()],F))) %>% 
# this makes a column which has value of True for each chunk...      
#that meets desired condition to later filter based on it
mutate(test2 = test | c(F,lag(test)[-1]) | c(lead(test)[-n()], F))  %>% 
filter(test2) %>% ungroup() %>% 
# group each three occurrences in case of having multiple ones within each ID
group_by(G=trunc(3:(n()+2)/3)) %>% group_by(ID,G) %>% 
# creating new column with string extracting techniques ...
#... (assuming those columns are characters) 
mutate(new=
str_remove_all(
as.character(regmatches(stock[2], gregexpr(product[3], stock[2]))),
stock[1])) %>% 
# selecting desired columns and adding times for long to wide conversion
select(ID,G,seqs,new) %>% mutate(times = 1:n()) %>% ungroup() %>% 
# long to wide conversion using tidyr (part of tidyverse)
gather(key, value, -ID, -G, -new, -times) %>%
unite(col, key, times) %>% spread(col, value) %>% 
# making the desired order of columns
select(-G,-new,new) %>% as.data.frame()
#   ID seqs_1 seqs_2 seqs_3 new
# 1  1      2      3      4   C
# 2  2      1      2      3    
# 3  3      2      3      4   D