我问了类似这样的问题 这里 并且那里提到的解决方案可以很好地解决那里提出的问题，但是这个问题是一个更棘手和更难的版本。
我有这样的数据表。
ID1 member
1 a parent
2 a child
3 a parent
4 a child
5 a child
6 b parent
7 b parent
8 b child
9 c child
10 c child
11 c parent
12 c child
我想分配一个如下所示的序列 ID1 和 会员 柱。
ID1 member sequence
1 a parent 1
2 a child 2
3 a parent 1
4 a child 2
5 a child 3
6 b parent 1
7 b parent 1
8 b child 2
9 c child 2 *
10 c child 3
11 c parent 1
12 c child 2
即
> dt$sequence = 1, wherever dt$member == "parent"
> dt$sequence = previous_row_value + 1, wherever dt$member=="child"
但有时可能会发生新的ID1可能无法以member =“parent”开头。如果它以“child”开头（例如星号标记的行），我们必须以2开始排序。
截至目前，我一直在使用循环，如下所示。
dt_sequence <- dt[ ,sequencing(.SD), by="ID1"]
sequencing <- function(dt){
for(i in 1:nrow(dt)){
if(i == 1){
if(dt[i,member] %in% "child")
dt$sequence[i] = 2
else
dt$sequence[i] = 1
}
else{
if(dt[i,member] %in% "child")
dt$sequence[i] = as.numeric(dt$sequence[i-1]) + 1
else
dt$sequence[i] = 1
}
}
return(dt)
}
我在4e5行的数据表上运行此代码，需要花费大量时间才能完成（大约20分钟）。
任何人都可以建议更快的方式来做到这一点。
DF <- read.table(text=" ID1 member
1 a parent
2 a child
3 a parent
4 a child
5 a child
6 b parent
7 b parent
8 b child
9 c child
10 c child
11 c parent
12 c child", header=TRUE, stringsAsFactors=FALSE)
library(data.table)
setDT(DF)
DF[, sequence := seq_along(member) + (member[1] == "child"),
by = list(ID1, cumsum(member == "parent"))]
# ID1 member sequence
# 1: a parent 1
# 2: a child 2
# 3: a parent 1
# 4: a child 2
# 5: a child 3
# 6: b parent 1
# 7: b parent 1
# 8: b child 2
# 9: c child 2
#10: c child 3
#11: c parent 1
#12: c child 2
尝试这个，
dt$sequence <- rep(NA, length(dt$member))
for (i in seq_along(dt$member)){
dt$sequence[i] <- ifelse(dt$member[i]=="parent", 1,
ifelse(dt$ID1[i]==dt$ID1[i-1], dt$sequence[i-1] + 1, 2)
)
}
更简单的dplyr解决方案
data <- dt %>%
group_by(ID1) %>%
mutate(
seq = ifelse(member=="parent", 1, 2),
sequence = ifelse(seq==1, 1, lag(seq, default = 1) + 1)
)
如果每组 ID1 至少包含一个 parent，更简单的解决方案是在group = ID1中安排数据，以便 parent 总是排在最前面：
dt %>%
group_by(ID1) %>%
arrange(desc(member))
DF <- read.table(text=" ID1 member
1 a parent
2 a child
3 a parent
4 a child
5 a child
6 b parent
7 b parent
8 b child
9 c child
10 c child
11 c parent
12 c child", header=TRUE, stringsAsFactors=FALSE)
library(data.table)
setDT(DF)
DF[, sequence := seq_along(member) + (member[1] == "child"),
by = list(ID1, cumsum(member == "parent"))]
# ID1 member sequence
# 1: a parent 1
# 2: a child 2
# 3: a parent 1
# 4: a child 2
# 5: a child 3
# 6: b parent 1
# 7: b parent 1
# 8: b child 2
# 9: c child 2
#10: c child 3
#11: c parent 1
#12: c child 2
尝试这个，
dt$sequence <- rep(NA, length(dt$member))
for (i in seq_along(dt$member)){
dt$sequence[i] <- ifelse(dt$member[i]=="parent", 1,
ifelse(dt$ID1[i]==dt$ID1[i-1], dt$sequence[i-1] + 1, 2)
)
}
更简单的dplyr解决方案
data <- dt %>%
group_by(ID1) %>%
mutate(
seq = ifelse(member=="parent", 1, 2),
sequence = ifelse(seq==1, 1, lag(seq, default = 1) + 1)
)
如果每组 ID1 至少包含一个 parent，更简单的解决方案是在group = ID1中安排数据，以便 parent 总是排在最前面：
dt %>%
group_by(ID1) %>%
arrange(desc(member))
确实是个好问题。所以这是我的解决方案：
数据
dd <- structure(list(ID1 = structure(c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 3L),
.Label = c("a", "b", "c"), class = "factor"),
member = structure(c(2L, 1L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 1L, 2L, 1L),
.Label = c("child", "parent"),
class = "factor")),
.Names = c("ID1", "member"),
row.names = c("1", "2", "3", "4", "5", "6", "7", "8",
"9", "10", "11", "12"), class = "data.frame")
码
首先，设置所有元素 parent 到1：
parent <- dd$member == "parent"
dd$sequence <- 0
dd$sequence[parent] <- 1
现在，设置全部 child 没有父母的elemetns 2：
dd$sequence <- ave(dd$sequence, dd$ID1,
FUN = function(.) {
ret <- .
ret[1] <- if (ret[1] == 0) 2 else ret[1]
ret}
)
现在，我们想得到每个序列的长度 0's 以及每个人的位置 0：
rl <- rle(dd$sequence)
rl.wh <- which(rl$values == 0)
最后，我们可以生成序列：
dd$sequence[dd$sequence == 0] <- unlist(mapply(function(x, r)
seq(x + 1, length.out = r, by = 1), rl$values[rl.wh - 1], rl$length[rl.wh]))