CODEWITHSUNDEEP
rgroup-bydata.table
tushaR
tushaR

Reputation: 3116

Complicated group by processing in data.table

Apologies for the unclear description but I don't think a one-liner could explain the requirement that I have.

I have a data.table dt1 as shared below:

      id  pg   pd                  dt capp vt
 1: 1111  hm <NA> 20-10-2020 21:07:54   NA  5
 2: 1111 abc  abc 20-10-2020 21:07:53 1234  5
 3: 1111  hm <NA> 20-10-2020 16:07:56   NA  4
 4: 1111 cde <NA> 20-10-2020 16:06:57   NA  4
 5: 1111 cde <NA> 20-10-2020 16:05:58   NA  4
 6: 1111 def  def 20-07-2020 12:07:59  345  3
 7: 1111 abc <NA> 20-06-2020 22:07:59   NA  2
 8: 1111 def <NA> 20-06-2020 22:07:58   NA  2
 9: 1111 abc <NA> 20-05-2020 21:07:59   NA  1
10: 1112  hm <NA> 20-10-2020 21:07:52   NA  4
11: 1112 cde  cde 20-10-2020 21:07:39  456  4
12: 1112  hm <NA> 20-10-2020 16:07:56   NA  3
13: 1112 abc <NA> 20-10-2020 16:06:57   NA  3
14: 1112 abc <NA> 20-07-2020 16:05:58   NA  2
15: 1112 def  abc 20-07-2020 16:04:59  234  2
16: 1112 cde <NA> 20-06-2020 22:07:59   NA  1
17: 1112 def <NA> 20-06-2020 21:07:59   NA  1
18: 1112 cde <NA> 20-05-2020 21:07:59   NA  0

The requirement is as follows: I want to create a new variable prev which for a given id and capp (where capp is not null) is max of vt where:

vt is less than the value of vt corresponding to non-null capp

and

pg is equal to value in pd corresponding to non-null capp

My expected output would look like this:

      id  pg   pd                  dt capp vt   prev
 1: 1111  hm <NA> 20-10-2020 21:07:54   NA  5   <NA>
 2: 1111 abc  abc 20-10-2020 21:07:53 1234  5      2
 3: 1111  hm <NA> 20-10-2020 16:07:56   NA  4   <NA>
 4: 1111 cde <NA> 20-10-2020 16:06:57   NA  4   <NA>
 5: 1111 cde <NA> 20-10-2020 16:05:58   NA  4   <NA>
 6: 1111 def  def 20-07-2020 12:07:59  345  3      2
 7: 1111 abc <NA> 20-06-2020 22:07:59   NA  2   <NA>
 8: 1111 def <NA> 20-06-2020 22:07:58   NA  2   <NA>
 9: 1111 abc <NA> 20-05-2020 21:07:59   NA  1   <NA>
10: 1112  hm <NA> 20-10-2020 21:07:52   NA  4   <NA>
11: 1112 cde  cde 20-10-2020 21:07:39  456  4      1
12: 1112  hm <NA> 20-10-2020 16:07:56   NA  3   <NA>
13: 1112 abc <NA> 20-10-2020 16:06:57   NA  3   <NA>
14: 1112 abc <NA> 20-07-2020 16:05:58   NA  2   <NA>
15: 1112 def  abc 20-07-2020 16:04:59  234  2 NA/Inf
16: 1112 cde <NA> 20-06-2020 22:07:59   NA  1   <NA>
17: 1112 def <NA> 20-06-2020 21:07:59   NA  1   <NA>
18: 1112 cde <NA> 20-05-2020 21:07:59   NA  0   <NA>

dt1 definition is as mentioned below:

structure(list(id = c(1111L, 1111L, 1111L, 1111L, 1111L, 1111L, 
1111L, 1111L, 1111L, 1112L, 1112L, 1112L, 1112L, 1112L, 1112L, 
1112L, 1112L, 1112L), pg = c("hm", "abc", "hm", "cde", "cde", 
"def", "abc", "def", "abc", "hm", "cde", "hm", "abc", "abc", 
"def", "cde", "def", "cde"), pd = c(NA, "abc", NA, NA, NA, "def", 
NA, NA, NA, NA, "cde", NA, NA, NA, "abc", NA, NA, NA), dt = c("20-10-2020 21:07:54", 
"20-10-2020 21:07:53", "20-10-2020 16:07:56", "20-10-2020 16:06:57", 
"20-10-2020 16:05:58", "20-07-2020 12:07:59", "20-06-2020 22:07:59", 
"20-06-2020 22:07:58", "20-05-2020 21:07:59", "20-10-2020 21:07:52", 
"20-10-2020 21:07:39", "20-10-2020 16:07:56", "20-10-2020 16:06:57", 
"20-07-2020 16:05:58", "20-07-2020 16:04:59", "20-06-2020 22:07:59", 
"20-06-2020 21:07:59", "20-05-2020 21:07:59"), capp = c(NA, 1234L, 
NA, NA, NA, 345L, NA, NA, NA, NA, 456L, NA, NA, NA, 234L, NA, 
NA, NA), vt = c(5L, 5L, 4L, 4L, 4L, 3L, 2L, 2L, 1L, 4L, 4L, 3L, 
3L, 2L, 2L, 1L, 1L, 0L)), .Names = c("id", "pg", "pd", "dt", 
"capp", "vt"), row.names = c(NA, -18L), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x0000000002650788>)

Upvotes: 1

Views: 53

Answers (2)

chinsoon12
chinsoon12

Reputation: 25225

Here is another option using a non-equi join for each row of non-null capp and then update by reference:

dt1[!is.na(capp), prev := 
    dt1[.SD, on=.(id, pg=pd, vt<vt), max(x.vt), by=.EACHI]$V1
]

Upvotes: 1

ekoam
ekoam

Reputation: 8844

Is this what you need?

dt1[, 
  prev := with(.SD, vapply(
    seq_along(vt), 
    function(i) {tmp <- vt[vt < vt[[i]] & pg == pd[[i]] & !is.na(capp[[i]])]; if (length(tmp) < 1L) NA_real_ else max(tmp)}, 
    numeric(1L)
  )), 
  by = id
]

Output

      id  pg   pd                  dt capp vt prev
 1: 1111  hm <NA> 20-10-2020 21:07:54   NA  5   NA
 2: 1111 abc  abc 20-10-2020 21:07:53 1234  5    2
 3: 1111  hm <NA> 20-10-2020 16:07:56   NA  4   NA
 4: 1111 cde <NA> 20-10-2020 16:06:57   NA  4   NA
 5: 1111 cde <NA> 20-10-2020 16:05:58   NA  4   NA
 6: 1111 def  def 20-07-2020 12:07:59  345  3    2
 7: 1111 abc <NA> 20-06-2020 22:07:59   NA  2   NA
 8: 1111 def <NA> 20-06-2020 22:07:58   NA  2   NA
 9: 1111 abc <NA> 20-05-2020 21:07:59   NA  1   NA
10: 1112  hm <NA> 20-10-2020 21:07:52   NA  4   NA
11: 1112 cde  cde 20-10-2020 21:07:39  456  4    1
12: 1112  hm <NA> 20-10-2020 16:07:56   NA  3   NA
13: 1112 abc <NA> 20-10-2020 16:06:57   NA  3   NA
14: 1112 abc <NA> 20-07-2020 16:05:58   NA  2   NA
15: 1112 def  abc 20-07-2020 16:04:59  234  2   NA
16: 1112 cde <NA> 20-06-2020 22:07:59   NA  1   NA
17: 1112 def <NA> 20-06-2020 21:07:59   NA  1   NA
18: 1112 cde <NA> 20-05-2020 21:07:59   NA  0   NA

Upvotes: 1

Related Questions