在组内对值变化前后进行计数,为每个唯一的移位生成新变量 [英] counting after and before change in value, within groups, generating new variables for each unique shift
问题描述
我正在计算组中唯一值 id
的出现次数。我正在查看 TF
。当 TF
更改时,我要从这一点开始同时计算前进和后退。此计数应存储在新变量 PM#
中,以便 PM#
同时包含正负到 TF
中的每个唯一班次。从我收集到的信息来看,我需要使用 rle
,但是我有点受阻。
我制作了这个工作示例来说明我的问题。
我有此数据
df<-结构(list(id = c(0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
0L,1L,1L,1L,1L,1L,1L,1L,1L,7L,7L,7L,7L,7L,7L,7L,
7L,7L,7L,7L),TF = c(NA,0L,NA,0L,0L,1L,1L,1L,NA,0L,
0L,NA,0L,0L,0L,1L,1L,1L,NA,NA,0L,0L ,1L,0L,0L,1L,
0L,1L,1L,1L)),.names = c( id, TF),class = data.frame,row.names = c (NA,
-30L))
这是我所看到的数据
df [c(1:12,19:30),]
#> id TF
#> 1 0 NA
#> 2 0 0
#> 3 0 NA
#> 4 0 0
#> 5 0 0
#> 6 0 1
#> 7 0 1
#> 8 0 1
#> 9 0 NA
#> 10 0 0
#> 11 0 0
#> 12 1 NA
#> 19 1 NA
#> 20 7 NA
#> 21 7 0
#> 22 7 0
#> 23 7 1
#> 24 7 0
#> 25 7 0
#> 26 7 1
#> 27 7 0
#> 28 7 1
#> 29 7 1
#> 30 7 1
我已经开始干预 ave
, cumsum
和 rle
,但尚未通过这种方式解决。
df $ PM01<-with(df,ifelse(is.na(TF),NA,1))
df $ PM01< ;-with(df,ave(PM01,TF,id,FUN = cumsum))
with(df,tapply(TF,rep(rle(id)[[2]],rle(id )[[1]]),计数))
这就是我想要获得的,
dfa<-结构(list(id = c(0L,0L,0L,0L,0L,0L,0L,0L ,0L,0L,
0L,1L,1L,1L,1L,1L,1L,1L,1L,7L,7L,7L,7L,7L,7L,7L,
7L,7L,7L ,7L),TF = c(NA,0L,NA,0L,0L,1L,1L,1L,NA,0L,
0L,NA,0L,0L,0L,1L,1L,1L,NA, NA,0L,0L,1L,0L,0L,1L,
0L,1L,1L,1L),PM1 = c(NA,-3L,NA,-2L,-1L,1L,2L,3L, NA,
NA,NA,NA,-3L,-2L,-1L,1L,2L,3L,NA,NA,-2L,-1L,1L,
NA,NA,NA,NA ,NA,NA,NA),PM2 = c(NA,NA,NA,NA,NA,-3L,
-2L,-1L,NA,1L,2L,NA,NA,NA ,NA,NA,NA,NA,NA,NA,NA,NA,
NA,-1L,1L,2L,NA,NA,NA,NA,NA,NA),PM3 = c(NA,NA,NA,NA ,
NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
NA,NA,NA,-2L, -1L,1L,NA,NA,NA,NA),PM4 = c(NA,NA,NA,
NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA, NA,NA,NA,NA,NA,
NA,NA,NA,NA,NA,NA,-1L,1L,NA,NA,NA),PM5 = c(NA,NA,
NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
NA,NA,NA,NA,NA,NA,NA, NA,-1L,1L,2L,3L)),.names = c( id,
TF, PM1, PM2, PM3, PM4, PM5) ,class = data.frame,row.names = c(NA,
-30L))
dfa [c(1:12,19:30),]
#> id TF PM1 PM2 PM3 PM4 PM5
#> 1 0不适用不适用不适用不适用
# 2 0 0 -3不适用不适用不适用
#> 3 0不适用不适用不适用不适用
#> 4 0 0 -2不适用不适用不适用
#> 5 0 0 -1不适用不适用不适用
#> 6 0 1 1 -3 NA NA NA
#> 7 0 1 2 -2 NA NA NA
#> 8 0 1 3 -1 NA NA NA
#> 9 0不适用不适用不适用不适用
# 10 0 0不适用1不适用不适用
#> 11 0 0不适用2不适用不适用不适用
#> 12 1不适用不适用不适用不适用
# 19 1不适用不适用不适用不适用
# 20 7不适用不适用不适用不适用
# 21 7 0 -2不适用不适用不适用
#> 22 7 0 -1 NA NA NA NA NA
#> 23 7 1 1 -1 NA NA NA
#> 24 7 0 NA 1 -2 NA NA
#> 25 7 0 NA 2 -1 NA NA
# 26 7 1 NA NA 1 -1 NA
# 27 7 0不适用不适用1 -1
#> 28 7 1 NA NA NA NA NA 1
#> 29 7 1 NA NA NA NA NA 2
#> 30 7 1不适用不适用不适用3
一个棘手的问题,我相信代码可以得到进一步的改进。但是,我能够重现您的预期结果。请使用您的生产数据尝试这种方法。如果可以,我稍后再添加解释。
library(data.table)
tmp<-setDT( df)[,rn:= .I] [!is.na(TF)] [,rl:= rleid(TF),由= id] [
,c( up, dn) ):=。(seq_len(.N),-rev(seq_len(.N))),by =。(id,rl)] []
res <-tmp [tmp [, seq_len(max(rl)-1L),= =。(id)],on =。(id),allow.cartesian = TRUE] [
rl == V1,PM:= dn] [rl == V1 + 1L,PM:=上] [
,dcast(.SD,id + TF + rn〜paste0( PM,V1),value.var = PM)] [
df,on =。(rn,id,TF)] [,- rn]
res
< blockquote>
id TF PM1 PM2 PM3 PM4 PM5
1:0不适用不适用不适用不适用
2:0 0 -3不适用不适用不适用
3:0不适用不适用不适用不适用不适用
4:0 0 -2不适用不适用不适用
5:0 0 -1不适用不适用不适用
6:0 1 1 -3不适用不适用不适用
7:0 1 2 -2不适用不适用不适用
8:0 1 3 -1不适用不适用不适用
9:0不适用不适用不适用不适用
10:0 0不适用1不适用不适用
11:0 0不适用2不适用不适用
12:1不适用不适用不适用不适用
13:1 0 -3不适用不适用不适用
14: 1 0 -2不适用不适用不适用
15:1 0 -1不适用不适用不适用
16:1 1 1不适用不适用不适用
17:1 1 2不适用不适用不适用
18:1 1 3不适用不适用不适用
19:1不适用不适用不适用不适用
20:7不适用不适用不适用不适用
21:7 0 -2不适用不适用
22:7 0 -1不适用不适用不适用不适用
23:7 1 1 -1不适用不适用不适用
24:7 0不适用1 -2不适用不适用
25:7 0 NA 2 -1 NA NA
26:7 1 NA NA 1 -1 NA
27:7 0 NA NA NA 1 -1
28:7 1 NA NA NA NA 1
29:7 1不适用不适用不适用2
30:7 1不适用不适用不适用3
id TF PM1 PM2 PM3 PM4 PM5
#验证结果是否相同
same(res,dfa)
[1]是
如果超过9张每个组中的es paste0( PM,V1)
应该替换为 sprintf( PM%02d,V1)
调用 dcast()
以确保 PM
列的顺序正确。
说明
tmp<-
#强制转换为数据。表
setDT(df)[
#创建行ID列(最后连接需要此行才能返回NA行)
,rn:= .I] [
#忽略NA行
!is.na( TF)] [
#每组内唯一值的数字条纹
,rl:= rleid(TF),by = id] [
#为每个条纹$ b创建升序和降序计数$ b#这样做一次,以避免为每个PM重复创建计数。
#(轻微的性能提升)
,c( up, dn):=。(seq_len(。 N),-rev(seq_len(.N))),按=。(id,rl)]
tmp []
id TF rn rl up dn
1: 0 0 2 1 1 -3
2:0 0 4 1 2 -2
3:0 0 5 1 3 -1
4:0 1 6 2 1 -3
5:0 1 7 2 2 -2
6:0 1 8 2 3 -1
7:0 0 10 3 1 -2
8:0 0 11 3 2 -1
9:1 0 13 1 1 -3
10:1 0 14 1 2 -2
11:1 0 15 1 3 -1
12:1 1 16 2 1 -3
13:1 1 17 2 2 -2
14:1 1 18 2 3 -1
15:7 0 21 1 1 -2
16:7 0 22 1 2 -1
17:7 1 23 2 1 -1
18:7 0 24 3 1 -2
19:7 0 25 3 2 -1
20:7 1 26 4 1 -1
21:7 0 27 5 1 -1
22:7 1 28 6 1 -3
23:7 1 29 6 2 -2
24:7 1 30 6 3 -1
id TF rn rl up dn
下一步,我们需要计算每个组中的变化 V1
tmp [, seq_len(max(rl)-1L),由=。(id)]
id V1
1:0 1
2:0 2
3:1 1
4:7 1
5:7 2
6: 7 3
7:7 4
8:7 5
现在,我们创建一个笛卡尔联接。每个组的行中所有可能发生的变化:
#右连接,每个组中的变化计数
tmp [tmp [, seq_len(max(rl)-1L),by =。(id)],on =。(id),allow.cartesian = TRUE] [
#将降序计数复制到开关
rl之前的行== V1,PM:= dn] [
#将升序计数复制到开关
rl之后的行== V1 + 1L,PM:= up] []
id TF rn rl up dn V1 PM
1:0 0 2 1 1 -3 1 -3
2:0 0 4 1 2 -2 1 -2
3:0 0 5 1 3 -1 1 -1
4:0 1 6 2 1- 3 1 1
5:0 1 7 2 2 -2 1 2
6:0 1 8 2 3 -1 1 3
7:0 0 10 3 1 -2 1 NA
8:0 0 11 3 2 -1 1 NA
9:0 0 2 1 1 -3 2 NA
10:0 0 4 1 2 -2 2 NA
11:0 0 5 1 3 -1 2 NA
12:0 1 6 2 1 -3 2 -3
13:0 1 7 2 2 -2 2 -2
14:0 1 8 2 3 -1 2 -1
15:0 0 10 3 1 -2 2 1
16:0 0 11 3 2 -1 2 2
17:1 0 13 1 1 -3 1 -3
18:1 0 14 1 2 -2 1 -2
19:1 0 15 1 3 -1 1 -1
20:1 1 16 2 1 -3 1 1
21:1 1 17 2 2 -2 1 2
22:1 1 18 2 3 -1 1 3
23 :7 0 21 1 1 -2 1 -2
24:7 0 22 1 2 -1 1 -1
25:7 1 23 2 1 -1 1 1
26:7 0 24 3 1 -2 1 NA
27:7 0 25 3 2 -1 1 NA
28:7 1 26 4 1 -1 1 NA
29:7 0 27 5 1 -1 1 NA
30:7 1 28 6 1 -3 1 NA
31:7 1 29 6 2 -2 1 NA
32:7 1 30 6 3 -1 1 NA
33:7 0 21 1 1 -2 2 NA
34:7 0 22 1 2 -1 2 NA
35:7 1 23 2 1 -1 2 -1
36:7 0 24 3 1 -2 2 1
37:7 0 25 3 2 -1 2 2
38:7 1 26 4 1 -1 2 NA
39:7 0 27 5 1- 1 2 NA
40:7 1 28 6 1 -3 2 NA
41:7 1 29 6 2 -2 2 NA
42:7 1 30 6 3 -1 2 NA
43:7 0 21 1 1 -2 3 NA
44:7 0 22 1 2 -1 3 NA
45:7 1 23 2 1 -1 3 NA
46:7 0 24 3 1 -2 3 -2
47:7 0 25 3 2 -1 3 -1
48:7 1 26 4 1 -1 3 1
49:7 0 27 5 1 -1 3 NA
50:7 1 28 6 1 -3 3 NA
51:7 1 29 6 2 -2 3 NA
52:7 1 30 6 3 -1 3 NA
53:7 0 21 1 1 -2 4 NA
54:7 0 22 1 2 -1 4 NA
55:7 1 23 2 1 -1 4 NA
56:7 0 24 3 1 -2 4 NA
57:7 0 25 3 2 -1 4 NA
58:7 1 26 4 1 -1 4 -1
59:7 0 27 5 1 -1 4 1
60:7 1 28 6 1 -3 4 NA
61:7 1 29 6 2 -2 4 NA
62:7 1 30 6 3 -1 4 NA
63:7 0 21 1 1- 2 5 NA
64:7 0 22 1 2 -1 5 NA
65:7 1 23 2 1 -1 5 NA
66:7 0 24 3 1 -2 5 NA
67:7 0 25 3 2 -1 5 NA
68:7 1 26 4 1 -1 5 NA
69:7 0 27 5 1 -1 5 -1
70: 7 1 28 6 1 -3 5 1
71:7 1 29 6 2 -2 5 2
72:7 1 30 6 3 -1 5 3
id TF rn up up dn V1 PM
最后,中间结果从长到宽重塑为
res<-
#创建笛卡尔联接;每个组的行的所有可能更改
tmp [tmp [,seq_len(max(rl)-1L),by =。(id)],on =。(id),allow.cartesian = TRUE] [
#将降序计数复制到开关
rl == V1之前的行,PM:= dn] [
#将升序计数复制到开关
rl == V1之后的行+ 1L,PM:= up] [
#从宽变长,随着新列
的变化而变化,dcast(.SD,id + TF + rn〜sprintf( PM%02d ,V1),value.var = PM)] [
#与原始df结合,以将NA行恢复为
df,on =。(rn,id,TF)] [
#省略辅助列
,- rn]
I am working to count occurrences of unique values within my groups, id
. I'm looking at TF
. When TF
changes I want to count both forward and backwards from that point. This counting should be stored in a new variable PM#
, so that PM#
holds both plus and minus to each unique shift in TF
. From what I've gathered I need to use rle
, but I am kinda stuck.
I made this working example to illustrate my issue.
I have this data
df <- structure(list(id = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 7L, 7L, 7L, 7L, 7L, 7L, 7L,
7L, 7L, 7L, 7L), TF = c(NA, 0L, NA, 0L, 0L, 1L, 1L, 1L, NA, 0L,
0L, NA, 0L, 0L, 0L, 1L, 1L, 1L, NA, NA, 0L, 0L, 1L, 0L, 0L, 1L,
0L, 1L, 1L, 1L)), .Names = c("id", "TF"), class = "data.frame", row.names = c(NA,
-30L))
This is the kinda data I am seeing
df[c(1:12,19:30),]
#> id TF
#> 1 0 NA
#> 2 0 0
#> 3 0 NA
#> 4 0 0
#> 5 0 0
#> 6 0 1
#> 7 0 1
#> 8 0 1
#> 9 0 NA
#> 10 0 0
#> 11 0 0
#> 12 1 NA
#> 19 1 NA
#> 20 7 NA
#> 21 7 0
#> 22 7 0
#> 23 7 1
#> 24 7 0
#> 25 7 0
#> 26 7 1
#> 27 7 0
#> 28 7 1
#> 29 7 1
#> 30 7 1
I've started meddling with ave
, cumsum
and with rle
, but haven't solved it this way yet.
df$PM01 <- with(df, ifelse(is.na(TF), NA, 1))
df$PM01 <- with(df, ave(PM01, TF, id, FUN=cumsum))
with(df, tapply(TF, rep(rle(id)[[2]], rle(id)[[1]]), count))
This is what I am trying to obtain,
dfa <- structure(list(id = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 7L, 7L, 7L, 7L, 7L, 7L, 7L,
7L, 7L, 7L, 7L), TF = c(NA, 0L, NA, 0L, 0L, 1L, 1L, 1L, NA, 0L,
0L, NA, 0L, 0L, 0L, 1L, 1L, 1L, NA, NA, 0L, 0L, 1L, 0L, 0L, 1L,
0L, 1L, 1L, 1L), PM1 = c(NA, -3L, NA, -2L, -1L, 1L, 2L, 3L, NA,
NA, NA, NA, -3L, -2L, -1L, 1L, 2L, 3L, NA, NA, -2L, -1L, 1L,
NA, NA, NA, NA, NA, NA, NA), PM2 = c(NA, NA, NA, NA, NA, -3L,
-2L, -1L, NA, 1L, 2L, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, -1L, 1L, 2L, NA, NA, NA, NA, NA), PM3 = c(NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, -2L, -1L, 1L, NA, NA, NA, NA), PM4 = c(NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, -1L, 1L, NA, NA, NA), PM5 = c(NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, -1L, 1L, 2L, 3L)), .Names = c("id",
"TF", "PM1", "PM2", "PM3", "PM4", "PM5"), class = "data.frame", row.names = c(NA,
-30L))
dfa[c(1:12,19:30),]
#> id TF PM1 PM2 PM3 PM4 PM5
#> 1 0 NA NA NA NA NA NA
#> 2 0 0 -3 NA NA NA NA
#> 3 0 NA NA NA NA NA NA
#> 4 0 0 -2 NA NA NA NA
#> 5 0 0 -1 NA NA NA NA
#> 6 0 1 1 -3 NA NA NA
#> 7 0 1 2 -2 NA NA NA
#> 8 0 1 3 -1 NA NA NA
#> 9 0 NA NA NA NA NA NA
#> 10 0 0 NA 1 NA NA NA
#> 11 0 0 NA 2 NA NA NA
#> 12 1 NA NA NA NA NA NA
#> 19 1 NA NA NA NA NA NA
#> 20 7 NA NA NA NA NA NA
#> 21 7 0 -2 NA NA NA NA
#> 22 7 0 -1 NA NA NA NA
#> 23 7 1 1 -1 NA NA NA
#> 24 7 0 NA 1 -2 NA NA
#> 25 7 0 NA 2 -1 NA NA
#> 26 7 1 NA NA 1 -1 NA
#> 27 7 0 NA NA NA 1 -1
#> 28 7 1 NA NA NA NA 1
#> 29 7 1 NA NA NA NA 2
#> 30 7 1 NA NA NA NA 3
This was really a tricky one, and I'm sure the code can be further improved. However, I was able to reproduce your expected result. Please, try this approach with your production data. If OK, I will add an explanation later.
library(data.table)
tmp <- setDT(df)[, rn := .I][!is.na(TF)][, rl := rleid(TF), by = id][
, c("up", "dn") := .(seq_len(.N), -rev(seq_len(.N))), by = .(id, rl)][]
res <- tmp[tmp[, seq_len(max(rl) - 1L), by = .(id)], on = .(id), allow.cartesian = TRUE][
rl == V1, PM := dn][rl == V1 + 1L, PM := up][
, dcast(.SD, id + TF + rn ~ paste0("PM", V1), value.var = "PM")][
df, on = .(rn, id, TF)][, -"rn"]
res
id TF PM1 PM2 PM3 PM4 PM5 1: 0 NA NA NA NA NA NA 2: 0 0 -3 NA NA NA NA 3: 0 NA NA NA NA NA NA 4: 0 0 -2 NA NA NA NA 5: 0 0 -1 NA NA NA NA 6: 0 1 1 -3 NA NA NA 7: 0 1 2 -2 NA NA NA 8: 0 1 3 -1 NA NA NA 9: 0 NA NA NA NA NA NA 10: 0 0 NA 1 NA NA NA 11: 0 0 NA 2 NA NA NA 12: 1 NA NA NA NA NA NA 13: 1 0 -3 NA NA NA NA 14: 1 0 -2 NA NA NA NA 15: 1 0 -1 NA NA NA NA 16: 1 1 1 NA NA NA NA 17: 1 1 2 NA NA NA NA 18: 1 1 3 NA NA NA NA 19: 1 NA NA NA NA NA NA 20: 7 NA NA NA NA NA NA 21: 7 0 -2 NA NA NA NA 22: 7 0 -1 NA NA NA NA 23: 7 1 1 -1 NA NA NA 24: 7 0 NA 1 -2 NA NA 25: 7 0 NA 2 -1 NA NA 26: 7 1 NA NA 1 -1 NA 27: 7 0 NA NA NA 1 -1 28: 7 1 NA NA NA NA 1 29: 7 1 NA NA NA NA 2 30: 7 1 NA NA NA NA 3 id TF PM1 PM2 PM3 PM4 PM5
# verify results are identical
identical(res, dfa)
[1] TRUE
In case of more than 9 changes per group paste0("PM", V1)
should be replaced by sprintf("PM%02d",V1)
in the call to dcast()
to ensure the PM
columns are ordered properly.
Explanation
tmp <-
# coerce to data.table
setDT(df)[
# create row id column (required for final join to get NA rows back in)
, rn := .I][
# ignore NA rows
!is.na(TF)][
# number streaks of unique values within each group
, rl := rleid(TF), by = id][
# create ascending and descending counts for each streak
# this is done once to avoid repeatedly creation of counts for each PM
# (slight performance gain)
, c("up", "dn") := .(seq_len(.N), -rev(seq_len(.N))), by = .(id, rl)]
tmp[]
id TF rn rl up dn 1: 0 0 2 1 1 -3 2: 0 0 4 1 2 -2 3: 0 0 5 1 3 -1 4: 0 1 6 2 1 -3 5: 0 1 7 2 2 -2 6: 0 1 8 2 3 -1 7: 0 0 10 3 1 -2 8: 0 0 11 3 2 -1 9: 1 0 13 1 1 -3 10: 1 0 14 1 2 -2 11: 1 0 15 1 3 -1 12: 1 1 16 2 1 -3 13: 1 1 17 2 2 -2 14: 1 1 18 2 3 -1 15: 7 0 21 1 1 -2 16: 7 0 22 1 2 -1 17: 7 1 23 2 1 -1 18: 7 0 24 3 1 -2 19: 7 0 25 3 2 -1 20: 7 1 26 4 1 -1 21: 7 0 27 5 1 -1 22: 7 1 28 6 1 -3 23: 7 1 29 6 2 -2 24: 7 1 30 6 3 -1 id TF rn rl up dn
For the next step, we need the count of changes V1
within each group
tmp[, seq_len(max(rl) - 1L), by = .(id)]
id V1 1: 0 1 2: 0 2 3: 1 1 4: 7 1 5: 7 2 6: 7 3 7: 7 4 8: 7 5
Now, we create a "cartesian join" of all possible changes with the rows of each group:
# right join with count of changes within each group
tmp[tmp[, seq_len(max(rl) - 1L), by = .(id)], on = .(id), allow.cartesian = TRUE][
# copy descending counts to rows before the switch
rl == V1, PM := dn][
# copy ascending counts to rows after the switch
rl == V1 + 1L, PM := up][]
id TF rn rl up dn V1 PM 1: 0 0 2 1 1 -3 1 -3 2: 0 0 4 1 2 -2 1 -2 3: 0 0 5 1 3 -1 1 -1 4: 0 1 6 2 1 -3 1 1 5: 0 1 7 2 2 -2 1 2 6: 0 1 8 2 3 -1 1 3 7: 0 0 10 3 1 -2 1 NA 8: 0 0 11 3 2 -1 1 NA 9: 0 0 2 1 1 -3 2 NA 10: 0 0 4 1 2 -2 2 NA 11: 0 0 5 1 3 -1 2 NA 12: 0 1 6 2 1 -3 2 -3 13: 0 1 7 2 2 -2 2 -2 14: 0 1 8 2 3 -1 2 -1 15: 0 0 10 3 1 -2 2 1 16: 0 0 11 3 2 -1 2 2 17: 1 0 13 1 1 -3 1 -3 18: 1 0 14 1 2 -2 1 -2 19: 1 0 15 1 3 -1 1 -1 20: 1 1 16 2 1 -3 1 1 21: 1 1 17 2 2 -2 1 2 22: 1 1 18 2 3 -1 1 3 23: 7 0 21 1 1 -2 1 -2 24: 7 0 22 1 2 -1 1 -1 25: 7 1 23 2 1 -1 1 1 26: 7 0 24 3 1 -2 1 NA 27: 7 0 25 3 2 -1 1 NA 28: 7 1 26 4 1 -1 1 NA 29: 7 0 27 5 1 -1 1 NA 30: 7 1 28 6 1 -3 1 NA 31: 7 1 29 6 2 -2 1 NA 32: 7 1 30 6 3 -1 1 NA 33: 7 0 21 1 1 -2 2 NA 34: 7 0 22 1 2 -1 2 NA 35: 7 1 23 2 1 -1 2 -1 36: 7 0 24 3 1 -2 2 1 37: 7 0 25 3 2 -1 2 2 38: 7 1 26 4 1 -1 2 NA 39: 7 0 27 5 1 -1 2 NA 40: 7 1 28 6 1 -3 2 NA 41: 7 1 29 6 2 -2 2 NA 42: 7 1 30 6 3 -1 2 NA 43: 7 0 21 1 1 -2 3 NA 44: 7 0 22 1 2 -1 3 NA 45: 7 1 23 2 1 -1 3 NA 46: 7 0 24 3 1 -2 3 -2 47: 7 0 25 3 2 -1 3 -1 48: 7 1 26 4 1 -1 3 1 49: 7 0 27 5 1 -1 3 NA 50: 7 1 28 6 1 -3 3 NA 51: 7 1 29 6 2 -2 3 NA 52: 7 1 30 6 3 -1 3 NA 53: 7 0 21 1 1 -2 4 NA 54: 7 0 22 1 2 -1 4 NA 55: 7 1 23 2 1 -1 4 NA 56: 7 0 24 3 1 -2 4 NA 57: 7 0 25 3 2 -1 4 NA 58: 7 1 26 4 1 -1 4 -1 59: 7 0 27 5 1 -1 4 1 60: 7 1 28 6 1 -3 4 NA 61: 7 1 29 6 2 -2 4 NA 62: 7 1 30 6 3 -1 4 NA 63: 7 0 21 1 1 -2 5 NA 64: 7 0 22 1 2 -1 5 NA 65: 7 1 23 2 1 -1 5 NA 66: 7 0 24 3 1 -2 5 NA 67: 7 0 25 3 2 -1 5 NA 68: 7 1 26 4 1 -1 5 NA 69: 7 0 27 5 1 -1 5 -1 70: 7 1 28 6 1 -3 5 1 71: 7 1 29 6 2 -2 5 2 72: 7 1 30 6 3 -1 5 3 id TF rn rl up dn V1 PM
Finally, the intermediate result is reshaped from long to wide format.
res <-
# create a "cartesian join" of all possible changes with the rows of each group
tmp[tmp[, seq_len(max(rl) - 1L), by = .(id)], on = .(id), allow.cartesian = TRUE][
# copy descending counts to rows before the switch
rl == V1, PM := dn][
# copy ascending counts to rows after the switch
rl == V1 + 1L, PM := up][
# reshape from wide to long with the change count as new columns
, dcast(.SD, id + TF + rn ~ sprintf("PM%02d", V1), value.var = "PM")][
# join with original df to get NA rows back in
df, on = .(rn, id, TF)][
# omit helper column
, -"rn"]
这篇关于在组内对值变化前后进行计数,为每个唯一的移位生成新变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!