All geek questions in one place

Odd behavior of data.table update on non-equi self-exchange

In preparing the answer to the question dplyr or data.table to calculate the accumulation of time series in R I noticed that I get different results depending on whether the table is updated in-place or returned as a new object. In addition, I get different results when I change the order of columns under conditions of nonequilibrium joining.

I currently have no explanation, perhaps due to a big misunderstanding on my side or a simple coding error.

Please note that in this matter there are explanations of the observed behavior of the relationships data.table. If you have alternative solutions to the main problem, please feel free to send an answer to the original question .

Original question and working answer

The initial question was how to calculate the number of hospitalizations occurring 365 days before this hospitalization (including the actual) for each patient using this data:

library(data.table)   # version 1.10.4 (CRAN) or 1.10.5 (devel built 2017-08-19)
DT0 <- data.table(
  patient.id = c(1L, 2L, 1L, 1L, 2L, 2L, 2L),
  hospitalization.date = as.Date(c("2013/10/15", "2014/10/15", "2015/7/16", "2016/1/7", 
                                   "2015/12/20", "2015/12/25", "2016/2/10")))
setorder(DT0, patient.id, hospitalization.date)
DT0

   patient.id hospitalization.date
1:          1           2013-10-15
2:          1           2015-07-16
3:          1           2016-01-07
4:          2           2014-10-15
5:          2           2015-12-20
6:          2           2015-12-25
7:          2           2016-02-10

The code below gives the expected answer (additional extra column added for clarity)

# add helper columns
DT0[, start.date := hospitalization.date - 365][
  , end.date := hospitalization.date][]
DT0

   patient.id hospitalization.date start.date   end.date
1:          1           2013-10-15 2012-10-15 2013-10-15
2:          1           2015-07-16 2014-07-16 2015-07-16
3:          1           2016-01-07 2015-01-07 2016-01-07
4:          2           2014-10-15 2013-10-15 2014-10-15
5:          2           2015-12-20 2014-12-20 2015-12-20
6:          2           2015-12-25 2014-12-25 2015-12-25
7:          2           2016-02-10 2015-02-10 2016-02-10

result <- DT0[DT0, on = c("patient.id", "hospitalization.date>=start.date", 
              "hospitalization.date<=end.date"), 
   .(hospitalizations.last.year = .N), by = .EACHI][]
result

   patient.id hospitalization.date hospitalization.date hospitalizations.last.year
1:          1           2012-10-15           2013-10-15                          1
2:          1           2014-07-16           2015-07-16                          1
3:          1           2015-01-07           2016-01-07                          2
4:          2           2013-10-15           2014-10-15                          1
5:          2           2014-12-20           2015-12-20                          1
6:          2           2014-12-25           2015-12-25                          2
7:          2           2015-02-10           2016-02-10                          3

with the exception of renamed and duplicated column names (which are left as a comparison).

For the patient.id == 2result, the last line is 3, because the patient was hospitalized in 2016-02-10 for the third time from 2015 to 02-10.

result - data.table, . data.table , :

# use copy of DT0 which can be safely modified
DT <- copy(DT0)

DT[DT, on = c("patient.id", "hospitalization.date>=start.date", 
            "hospitalization.date<=end.date"), 
   hospitalizations.last.year := .N, by = .EACHI]
DT

   patient.id hospitalization.date start.date   end.date hospitalizations.last.year
1:          1           2013-10-15 2012-10-15 2013-10-15                          1
2:          1           2015-07-16 2014-07-16 2015-07-16                          2
3:          1           2016-01-07 2015-01-07 2016-01-07                          2
4:          2           2014-10-15 2013-10-15 2014-10-15                          1
5:          2           2015-12-20 2014-12-20 2015-12-20                          3
6:          2           2015-12-25 2014-12-25 2015-12-25                          3
7:          2           2016-02-10 2015-02-10 2016-02-10                          3

DT , 5 6 3 1 2, . , .

.

:

result <- DT0[DT0, on = c("patient.id", "start.date<=hospitalization.date", 
                          "end.date>=hospitalization.date"), 
              .(hospitalizations.last.year = .N), by = .EACHI][]
result

, "start.date<=hospitalization.date" "hospitalization.date>=start.date" ( , < >),

   patient.id start.date   end.date hospitalizations.last.year
1:          1 2013-10-15 2013-10-15                          1
2:          1 2015-07-16 2015-07-16                          2
3:          1 2016-01-07 2016-01-07                          1
4:          2 2014-10-15 2014-10-15                          1
5:          2 2015-12-20 2015-12-20                          3
6:          2 2015-12-25 2015-12-25                          2
7:          2 2016-02-10 2016-02-10                          1

. ,

, ( ):

# use copy of DT0 which can be safely modified
DT <- copy(DT0)
DT[DT, on = c("patient.id", "start.date<=hospitalization.date", 
              "end.date>=hospitalization.date"), 
   hospitalizations.last.year := .N, by = .EACHI]
DT

   patient.id hospitalization.date start.date   end.date hospitalizations.last.year
1:          1           2013-10-15 2012-10-15 2013-10-15                          1
2:          1           2015-07-16 2014-07-16 2015-07-16                          2
3:          1           2016-01-07 2015-01-07 2016-01-07                          1
4:          2           2014-10-15 2013-10-15 2014-10-15                          1
5:          2           2015-12-20 2014-12-20 2015-12-20                          3
6:          2           2015-12-25 2014-12-25 2015-12-25                          2
7:          2           2016-02-10 2015-02-10 2016-02-10                          1

, github.

x. .

+4
1

by=.EACHI " i" " x".

# for readability / my sanity
DT = copy(DT0)
setnames(DT, "hospitalization.date", "h.date")

z = DT[DT, on = .(patient.id, h.date >= start.date, h.date <= end.date), 
   .(x.h.date, patient.id, i.start.date, i.end.date, g = .GRP, .N)
, by=.EACHI][, utils:::tail.default(.SD, 6)]

      x.h.date patient.id i.start.date i.end.date g N
 1: 2013-10-15          1   2012-10-15 2013-10-15 1 1 * 
 2: 2015-07-16          1   2014-07-16 2015-07-16 2 1 
 3: 2015-07-16          1   2015-01-07 2016-01-07 3 2 *
 4: 2016-01-07          1   2015-01-07 2016-01-07 3 2 *
 5: 2014-10-15          2   2013-10-15 2014-10-15 4 1 *  
 6: 2015-12-20          2   2014-12-20 2015-12-20 5 1
 7: 2015-12-20          2   2014-12-25 2015-12-25 6 2  
 8: 2015-12-25          2   2014-12-25 2015-12-25 6 2 
 9: 2015-12-20          2   2015-02-10 2016-02-10 7 3 *
10: 2015-12-25          2   2015-02-10 2016-02-10 7 3 *
11: 2016-02-10          2   2015-02-10 2016-02-10 7 3 *

1

  • .(start.date = 2012-10-15, end.date = 2013-10-15), 1
  • .(start.date = 2014-07-16, end.date = 2015-07-16), 1
  • .(start.date = 2015-01-07, end.date = 2016-01-07), - 2

, , .

:

. , , -, . , . :

a = data.table(id = c(1L, 1L, 2L, 3L, NA_integer_), 
  t = c(1L, 2L, 1L, 2L, NA_integer_), x = 11:15)
b = data.table(id = 1:2, y = c(11L, 15L))
b[a, on=.(id), x := i.x, verbose = TRUE ][]

# Calculated ad hoc index in 0 secs
# Starting bmerge ...done in 0.02 secs
# Detected that j uses these columns: x,i.x 
# Assigning to 3 row subset of 2 rows
#    id  y  x
# 1:  1 11 12
# 2:  2 15 13

"3 2 ".

- "Quick R Tutorial" , " "

OP verbose=TRUE , .

DT[DT, on = .(patient.id, h.date >= start.date, h.date <= end.date), 
   n := .N, by = .EACHI, verbose=TRUE]
# Non-equi join operators detected ... 
#   forder took ... 0.01 secs
#   Generating group lengths ... done in 0 secs
#   Generating non-equi group ids ... done in 0 secs
#   Found 1 non-equi group(s) ...
# Starting bmerge ...done in 0.02 secs
# Detected that j uses these columns: <none> 
# lapply optimization is on, j unchanged as '.N'
# Making each group and running j (GForce FALSE) ... 
#   memcpy contiguous groups took 0.000s for 7 groups
#   eval(j) took 0.000s for 7 calls
# 0.01 secs

, x , OP. . z[, mrk := replace(rep(0, .N), .N, 1), by=x.h.date].

, ...

DT[, n := 
  .SD[.SD, on = .(patient.id, h.date >= start.date, h.date <= end.date), .N, by=.EACHI]$N 
]

   patient.id hospitalization.date start.date   end.date     h.date n
1:          1           2013-10-15 2012-10-15 2013-10-15 2013-10-15 1
2:          1           2015-07-16 2014-07-16 2015-07-16 2015-07-16 1
3:          1           2016-01-07 2015-01-07 2016-01-07 2016-01-07 2
4:          2           2014-10-15 2013-10-15 2014-10-15 2014-10-15 1
5:          2           2015-12-20 2014-12-20 2015-12-20 2015-12-20 1
6:          2           2015-12-25 2014-12-25 2015-12-25 2015-12-25 2
7:          2           2016-02-10 2015-02-10 2016-02-10 2016-02-10 3

/ : x x :

x[, v := DT2[.SD, on=, j, by=.EACHI]$V1 ]
+4

Source: https://habr.com/ru/post/1684100/

More articles:


All Articles