Filter data table by dynamic column name

Question

lets say I have a data.table with columns A, B and C

I'd like to write a function that applies a filter (for example A>1) but "A" needs to be dynamic (the function's parameter) so if I inform A, it does A>1; If I inform B, it does B>1 and so on... (A and B always being the columns names, of course)

Example: Lets say my data is bellow, I'd like to do "A==1" and it would return the green line, or do "B==1 & C==1" and return the blue line.

enter image description here

Can this be done? thanks

possible duplicate of [Variably selecting/assigning to fields in a data.table](http://stackoverflow.com/questions/12391950/variably-selecting-assigning-to-fields-in-a-data-table) — Frank, Apr 11 '15 at 14:22

akrun · Answer 1 · 2015-04-10T14:57:23.850

14

You can try

f1 <- function(dat, colName){dat[eval(as.name(colName))>1]}
setDT(df1)
f1(df1, 'A')
f1(df1, 'B')

If you need to make the value also dynamic

f2 <- function(dat, colName, value){dat[eval(as.name(colName))>value]}
f2(df1, 'A', 1)
f2(df1, 'A', 5)

data

set.seed(24)
df1 <- data.frame(A=sample(-5:10, 20, replace=TRUE), 
      B=rnorm(20), C=LETTERS[1:20], stringsAsFactors=FALSE)

edited Apr 10 '15 at 14:57

answered Apr 10 '15 at 14:42

akrun

874,273
37
540
662

score 9 · Answer 2 · answered Apr 10 '15 at 14:40

9

Try:

dt = data.table(A=c(1,1,2,3,1), B=c(4,5,1,1,1))

f=function(dt, colName) dt[dt[[colName]]>1,]
#> f(dt, 'A')
#   A B
#1: 2 1
#2: 3 1

answered Apr 10 '15 at 14:40

Colonel Beauvel

30,423
11
47
87

rmuc8 · Accepted Answer · 2015-04-10T19:11:34.537

8

If your data is

a <- c(1:9)
b <- c(10:18)
# create a data.frame
df <- data.frame(a,b)
# or a data.table
dt <- data.table(a,b)

you can store your condition(s) in a variable x

x <- quote(a >= 3)

and filter the data.frame using dplyr (subsetting with [] won't work)

library(dplyr)
filter(df, x)

or using data.table as suggested by @Frank

library(data.table)
dt[eval(x),]

edited Apr 10 '15 at 19:11

answered Apr 10 '15 at 14:39

rmuc8

2,869
7
27
36

thanks for the tip anyway, it works like a charm on data frames – Diego Apr 10 '15 at 15:55
2

Nice solution; there is something very similar for `data.table`, actually: `setDT(df)[eval(x),]` – Frank Apr 10 '15 at 17:22
@Frank Thx. Feel free to post it. If not, should I include it? – rmuc8 Apr 10 '15 at 18:25
1

Yes, please go ahead and add it (if you want). I mentioned it here because I thought it was a natural fit with your answer. – Frank Apr 10 '15 at 19:05

User7598 · Answer 4 · 2015-04-10T14:49:42.737

0

Why write a function? You can do this...

Specifically:

d.new=d[d$A>1,]

where d is the dataframe d$A is the variable and d.new is a new dataframe.

More generally:

data=d #data frame
variable=d$A #variable 
minValue=1 #minimum value
d.new=data[variable>minValue,] #create new data frame (d.new) filtered by min value

To create a new column:

If you don't want to actually create a new dataframe but want to create an indicator variable you can use ifelse. This is most similar to coloring rows as shown in your example. Code below:

d$indicator1=ifelse(d$X1>0,1,0)

edited Apr 10 '15 at 14:49

answered Apr 10 '15 at 14:37

User7598

1,658
1
15
28

1

`A` needs to be dynamic, like `myvar <- "A"` and you only work with `myvar`. With a `data.table` you could do `d[get(myvar)>1,]` or with a data.frame, `d[d[[myvar]]>1,]`, but I don't think `$` can solve this problem. – Frank Apr 10 '15 at 14:53

Filter data table by dynamic column name

4 Answers4

data

Linked

Related