I have a particular use case where I need to often set the values of a single row in a keyed data.table object in R. Currently I'm using the := notation, but read in the help page that there are some situations where set() can be even faster.
Is this true for keyed data.tables? Or is there a way to use set() with keyed data.tables? I guess I'm not sure what is going on under the hood.
library(data.table)
#> Warning: package 'data.table' was built under R version 4.0.2
mt <- as.data.table(mtcars, keep.rownames = TRUE)
setkey(mt, rn)
head(mt)
#> rn mpg cyl disp hp drat wt qsec vs am gear carb
#> 1: AMC Javelin 15.2 8 304 150 3.15 3.435 17.30 0 0 3 2
#> 2: Cadillac Fleetwood 10.4 8 472 205 2.93 5.250 17.98 0 0 3 4
#> 3: Camaro Z28 13.3 8 350 245 3.73 3.840 15.41 0 0 3 4
#> 4: Chrysler Imperial 14.7 8 440 230 3.23 5.345 17.42 0 0 3 4
#> 5: Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
#> 6: Dodge Challenger 15.5 8 318 150 2.76 3.520 16.87 0 0 3 2
mt["AMC Javelin", mpg := -10] # want to do this, but faster?
head(mt)
#> rn mpg cyl disp hp drat wt qsec vs am gear carb
#> 1: AMC Javelin -10.0 8 304 150 3.15 3.435 17.30 0 0 3 2
#> 2: Cadillac Fleetwood 10.4 8 472 205 2.93 5.250 17.98 0 0 3 4
#> 3: Camaro Z28 13.3 8 350 245 3.73 3.840 15.41 0 0 3 4
#> 4: Chrysler Imperial 14.7 8 440 230 3.23 5.345 17.42 0 0 3 4
#> 5: Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
#> 6: Dodge Challenger 15.5 8 318 150 2.76 3.520 16.87 0 0 3 2
set(mt, "AMC Javelin", 2L, -10) # this doesn't work
#> Error in set(mt, "AMC Javelin", 2L, -10): i is type 'character'. Must be integer, or numeric is coerced with warning. If i is a logical subset, simply wrap with which(), and take the which() outside the loop if possible for efficiency.
set(mt, 1L, 2L, -10) # this would work if I could get the row number of a given key...
Created on 2021-08-06 by the reprex package (v0.3.0)
UPDATE: Ronak Shah and sindri_baldur's answer and comment work great for the question that I proposed (see the benchmarking below). Unfortunately, I think my simple example doesn't match the actual use case that I have. In my case, there are multiple keyed columns and so match and chmatch don't work. Is there a solution that works on data.tables with multiple key columns?
library(data.table)
#> Warning: package 'data.table' was built under R version 4.0.2
library(microbenchmark)
# Original question
mt <- as.data.table(mtcars, keep.rownames = TRUE)
setkey(mt, rn)
key <- "AMC Javenlin"
microbenchmark(
mt[key, mpg := -10],
set(mt, 1L, 2L, -10),
set(mt, match(key, mt$rn), 2L, -10),
set(mt, chmatch(key, mt$rn), 2L, -10)
)
#> Unit: microseconds
#> expr min lq mean median
#> mt[key, `:=`(mpg, -10)] 490.129 568.7480 746.67525 619.0085
#> set(mt, 1L, 2L, -10) 1.597 1.8980 4.17609 2.8475
#> set(mt, match(key, mt$rn), 2L, -10) 3.104 3.7130 6.60660 4.9275
#> set(mt, chmatch(key, mt$rn), 2L, -10) 2.740 3.3025 5.27118 4.3200
#> uq max neval cld
#> 701.094 8996.071 100 b
#> 4.298 87.451 100 a
#> 7.726 45.807 100 a
#> 7.002 11.811 100 a
My situation is closer to this, where there are multiple keys...
dt <- CJ(a = 1:10, b = 1:10, c = 1:60)
setkey(dt)
dt$d <- NA
key <- list(a = 2, b = 7, c = 35)
microbenchmark(
{ dt[key, d := 1] },
{ set(dt, 1L, 4L, 1)}
)
#> Unit: microseconds
#> expr min lq mean median uq
#> { dt[key, `:=`(d, 1)] } 634.125 666.5825 768.59937 756.9030 819.7585
#> { set(dt, 1L, 4L, 1) } 2.019 2.5355 3.95986 3.9325 4.6590
#> max neval cld
#> 1171.794 100 b
#> 22.945 100 a
match(key, dt[, .(a, b, c)]) # doesn't work
#> [1] NA NA NA
chmatch(key, dt[, .(a, b, c)]) # doesn't work
#> Error in chmatch(key, dt[, .(a, b, c)]): table is type 'list' (must be 'character' or NULL)
Created on 2021-08-06 by the reprex package (v0.3.0)
dt[key, which=TRUE], fwiw. If you must do one row at a time, it will be a lot slower than if you can bundle a bunch of rows together and update at once (they can have differentdvalues, just store the abc -> d mapping all in one table). - Frank