How to create dynamic query in data table in R

Question

updated with example: I have a function as follows:

myfun <- function(DT, var){  
  for(i in 1:length(var)){
    s = substitute(!(is.na(x) | is.nan(x)), list(x=as.symbol(eval(var[i]))))
    DT = DT[eval(s)]
  }
  return(DT)
}

input:

> dt = data.table(id=c(1,2,3,4,5), x=c(1,2,NA,4,5), y=c(1,NA,3,4,NA))
> dt
   id  x  y
1:  1  1  1
2:  2  2 NA
3:  3 NA  3
4:  4  4  4
5:  5  5 NA

runs:

> myfun(dt, var=c("x", "y"))
   id x y
1:  1 1 1
2:  4 4 4
> myfun(dt, var=c("x"))
   id x  y
1:  1 1  1
2:  2 2 NA
3:  4 4  4
4:  5 5 NA

var is an character array of some variables in DT. The goal is to only obtain rows in DT which do not have any NA or NaN wrt to any of variables in var.

I do not WANT the for loop. I want to construct a query s with all the conditions and then evaluate that query for DT. for the first case I want:

s = !(is.na(x) | is.nan(x) | is.na(y) | is.nan(y))

and for the second case I want:

s = !(is.na(x) | is.nan(x))

How can I construct a dynamic query s and just run it once as an i/where query in the data table.

More generally how can I create dynamic expression based on input. Using expression(paste()) did not help me. Then I can use substitute.

Maybe give us example input and its associated expected output? — Frank
– Frank, Commented Sep 4, 2013 at 13:56
Never mind. I found it in: stackoverflow.com/questions/11677424/… str=paste0("is.na(",var,") |", " is.nan(",var,")", collapse="|") s = parse(text=paste("!(",str,")")) dt[eval(s)] — user1971988
– user1971988, Commented Sep 4, 2013 at 14:27
You should probably move your answer to the "Your Answer" box below. Answering your own question is actually encouraged: stackoverflow.com/help/self-answer — Frank
– Frank, Commented Sep 4, 2013 at 14:34

Community · Accepted Answer · 2017-05-23 12:06:33Z

3

Ans:

var = c("x","y")
str=paste0("is.na(",var,") |", " is.nan(",var,")", collapse="|")
s = parse(text=paste("!(",str,")"))
DT[eval(s)]

source: How to use an unknown number of key columns in a data.table

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Sep 4, 2013 at 14:39

user1971988

8738 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Dowle Over a year ago

+1 This works as long as "s" isn't a column name. If "s" might ever be a column name in future, use ".s" instead of "s", or build the entire query (including the "DT[" bit) and eval that as a whole.

user1971988 Over a year ago

@MatthewDowle - Thanks! That is a very important point! Do you have any pointers about the detailed structure of an expression in R. There is some mention of it in the Writing R Extensions doc, but any detailed info will be appreciated.

Matt Dowle Over a year ago

NP. Not sure what you mean by detailed structure. .Internal(inspect(expression(1+2+3))) ?

user1971988 Over a year ago

In 5.11 Evaluating R expressions from C (cran.r-project.org/doc/manuals/…), it says when constructing an expression: "There are three steps: the call is constructed as a pairlist of length 3, the list is filled in, and the expression represented by the pairlist is evaluated." The example then shows the list being filled it. It is kind of vague for beginners, but the .Internal with inspect gives me some hope :) Thanks!

Collectives™ on Stack Overflow

How to create dynamic query in data table in R

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related