CODEWITHSUNDEEP
rdata.table
zell kim
zell kim

Reputation: 69

make values NA with row range condition in r data.table

dt<-fread("ID 0 1 2 3 4 5 6 7 8 9 t1 t2
           b  10 11 12 13 14 15 16 17 18 19 4 8
           c  20 21 22 23 24 25 26 27 28 29 2 7
           d  30 31 32 33 34 35 36 37 38 39 7 9
           a  40 41  42 43 44 45 46 47 48 49 3 6" )

dt

   ID  0  1  2  3  4  5  6  7  8  9 t1 t2
1:  b 10 11 12 13 14 15 16 17 18 19  4  8
2:  c 20 21 22 23 24 25 26 27 28 29  2  7
3:  d 30 31 32 33 34 35 36 37 38 39  7  9
4:  a 40 41 42 43 44 45 46 47 48 49  3  6

I try to change values to NA with reference value t1,t2

I tried to use set function in data.table

col <- colnames(dt) for (i in 2:length(col)) { set(x = dt, i = which(dt[["t1"]]<=i | i<= dt[["t2"]]), j=j, value = NA) }

but it is not working

what I want is make table like this

change values to NA with not in range t1:t2

   ID  0  1  2  3  4  5  6  7  8  9 t1 t2
1:  b NA NA NA NA 14 15 16 17 18 NA  4  8
2:  c NA NA 22 23 24 25 26 27 NA NA  2  7
3:  d NA NA NA NA NA NA NA 37 38 39  7  9
4:  a NA NA NA 43 44 45 46 NA NA NA  3  6

is there any way to use date.table set function?

because actual data is pretty big so I want use data.table

Upvotes: 0

Views: 124

Answers (2)

tmfmnk
tmfmnk

Reputation: 39858

Here is also one dplyr solution:

df %>%
 group_by(ID) %>%
 mutate_at(2:11, funs(ifelse(substr(., nchar(.), nchar(.)) %in% t1:t2, ., NA)))

  ID    X0    X1       X2    X3    X4    X5    X6    X7    X8    X9    t1    t2
  <fct> <lgl> <lgl> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int>
1 b     NA    NA       NA    NA    14    15    16    17    18    NA     4     8
2 c     NA    NA       22    23    24    25    26    27    NA    NA     2     7
3 d     NA    NA       NA    NA    NA    NA    NA    37    38    39     7     9
4 a     NA    NA       NA    43    44    45    46    NA    NA    NA     3     6

It is grouping by "ID" and then compares whether the last digit from columns 2:11 is in the range of columns "t1" and "t2".

Upvotes: 1

akrun
akrun

Reputation: 887118

A base R option would be to use row/col indexing

lst <- Map(function(x, y) match(setdiff(col, x:y), names(dt)),  dt$t1, dt$t2)
dt[cbind(rep(seq_along(lst), lengths(lst)), unlist(lst))] <- NA
dt
#  ID  0  1  2  3  4  5  6  7  8  9 t1 t2
#1  b NA NA NA NA 14 15 16 17 18 NA  4  8
#2  c NA NA 22 23 24 25 26 27 NA NA  2  7
#3  d NA NA NA NA NA NA NA 37 38 39  7  9
#4  a NA NA NA 43 44 45 46 NA NA NA  3  6

data

dt <- structure(list(ID = c("b", "c", "d", "a"), `0` = c(10L, 20L, 
30L, 40L), `1` = c(11L, 21L, 31L, 41L), `2` = c(12L, 22L, 32L, 
42L), `3` = c(13L, 23L, 33L, 43L), `4` = c(14L, 24L, 34L, 44L
 ), `5` = c(15L, 25L, 35L, 45L), `6` = c(16L, 26L, 36L, 46L), 
`7` = c(17L, 27L, 37L, 47L), `8` = c(18L, 28L, 38L, 48L), 
`9` = c(19L, 29L, 39L, 49L), t1 = c(4L, 2L, 7L, 3L), t2 = c(8L, 
7L, 9L, 6L)), class = "data.frame", row.names = c(NA, -4L
   ))

col <- names(dt)[2:11]

Upvotes: 1

Related Questions