Dynamically select data frame columns using $ and a character value
RDataframeR FaqR Problem Overview
I have a vector of different column names and I want to be able to loop over each of them to extract that column from a data.frame. For example, consider the data set mtcars
and some variable names stored in a character vector cols
. When I try to select a variable from mtcars
using a dynamic subset of cols
, nether of these work
cols <- c("mpg", "cyl", "am")
col <- cols[1]
col
# [1] "mpg"
mtcars$col
# NULL
mtcars$cols[1]
# NULL
how can I get these to return the same values as
mtcars$mpg
Furthermore how can I loop over all the columns in cols
to get the values in some sort of loop.
for(x in seq_along(cols)) {
value <- mtcars[ order(mtcars$cols[x]), ]
}
R Solutions
Solution 1 - R
You can't do that kind of subsetting with $
. In the source code (R/src/main/subset.c
) it states:
> /*The $ subset operator.
We need to be sure to only evaluate the first argument.
The second will be a symbol that needs to be matched, not evaluated.
*/
Second argument? What?! You have to realise that $
, like everything else in R, (including for instance (
, +
, ^
etc) is a function, that takes arguments and is evaluated. df$V1
could be rewritten as
`$`(df , V1)
or indeed
`$`(df , "V1")
But...
`$`(df , paste0("V1") )
...for instance will never work, nor will anything else that must first be evaluated in the second argument. You may only pass a string which is never evaluated.
Instead use [
(or [[
if you want to extract only a single column as a vector).
For example,
var <- "mpg"
#Doesn't work
mtcars$var
#These both work, but note that what they return is different
# the first is a vector, the second is a data.frame
mtcars[[var]]
mtcars[var]
You can perform the ordering without loops, using do.call
to construct the call to order
. Here is a reproducible example below:
# set seed for reproducibility
set.seed(123)
df <- data.frame( col1 = sample(5,10,repl=T) , col2 = sample(5,10,repl=T) , col3 = sample(5,10,repl=T) )
# We want to sort by 'col3' then by 'col1'
sort_list <- c("col3","col1")
# Use 'do.call' to call order. Seccond argument in do.call is a list of arguments
# to pass to the first argument, in this case 'order'.
# Since a data.frame is really a list, we just subset the data.frame
# according to the columns we want to sort in, in that order
df[ do.call( order , df[ , match( sort_list , names(df) ) ] ) , ]
col1 col2 col3
10 3 5 1
9 3 2 2
7 3 2 3
8 5 1 3
6 1 5 4
3 3 4 4
2 4 3 4
5 5 1 4
1 2 5 5
4 5 3 5
Solution 2 - R
Using dplyr provides an easy syntax for sorting the data frames
library(dplyr)
mtcars %>% arrange(gear, desc(mpg))
It might be useful to use the NSE version as shown here to allow dynamically building the sort list
sort_list <- c("gear", "desc(mpg)")
mtcars %>% arrange_(.dots = sort_list)
Solution 3 - R
If I understand correctly, you have a vector containing variable names and would like loop through each name and sort your data frame by them. If so, this example should illustrate a solution for you. The primary issue in yours (the full example isn't complete so I"m not sure what else you may be missing) is that it should be order(Q1_R1000[,parameter[X]])
instead of order(Q1_R1000$parameter[X])
, since parameter is an external object that contains a variable name opposed to a direct column of your data frame (which when the $
would be appropriate).
set.seed(1)
dat <- data.frame(var1=round(rnorm(10)),
var2=round(rnorm(10)),
var3=round(rnorm(10)))
param <- paste0("var",1:3)
dat
# var1 var2 var3
#1 -1 2 1
#2 0 0 1
#3 -1 -1 0
#4 2 -2 -2
#5 0 1 1
#6 -1 0 0
#7 0 0 0
#8 1 1 -1
#9 1 1 0
#10 0 1 0
for(p in rev(param)){
dat <- dat[order(dat[,p]),]
}
dat
# var1 var2 var3
#3 -1 -1 0
#6 -1 0 0
#1 -1 2 1
#7 0 0 0
#2 0 0 1
#10 0 1 0
#5 0 1 1
#8 1 1 -1
#9 1 1 0
#4 2 -2 -2
Solution 4 - R
Another solution is to use #get:
> cols <- c("cyl", "am")
> get(cols[1], mtcars)
[1] 6 6 4 6 8 6 8 4 4 6 6 8 8 8 8 8 8 4 4 4 4 8 8 8 8 4 4 4 8 6 8 4
Solution 5 - R
I would implement the sym
function of purrr
package. Let's say the col
has value as "mpg"
. The idea is to subset it.
mtcars %>% pull(!!sym(col))
# [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7 15.0
# [32] 21.4
Keep Coding!
Solution 6 - R
mtcars[do.call(order, mtcars[cols]), ]
Solution 7 - R
Had similar problem due to some CSV files that had various names for the same column.
This was the solution:
I wrote a function to return the first valid column name in a list, then used that...
# Return the string name of the first name in names that is a column name in tbl
# else null
ChooseCorrectColumnName <- function(tbl, names) {
for(n in names) {
if (n %in% colnames(tbl)) {
return(n)
}
}
return(null)
}
then...
cptcodefieldname = ChooseCorrectColumnName(file, c("CPT", "CPT.Code"))
icdcodefieldname = ChooseCorrectColumnName(file, c("ICD.10.CM.Code", "ICD10.Code"))
if (is.null(cptcodefieldname) || is.null(icdcodefieldname)) {
print("Bad file column name")
}
# Here we use the hash table implementation where
# we have a string key and list value so we need actual strings,
# not Factors
file[cptcodefieldname] = as.character(file[cptcodefieldname])
file[icdcodefieldname] = as.character(file[icdcodefieldname])
for (i in 1:length(file[cptcodefieldname])) {
cpt_valid_icds[file[cptcodefieldname][i]] <<- unique(c(cpt_valid_icds[[file[cptcodefieldname][i]]], file[icdcodefieldname][i]))
}
Solution 8 - R
if you want to select column with specific name then just do
A <- mtcars[,which(conames(mtcars)==cols[1])]
# and then
colnames(mtcars)[A]=cols[1]
you can run it in loop as well reverse way to add dynamic name eg if A is data frame and xyz is column to be named as x then I do like this
A$tmp <- xyz
colnames(A)[colnames(A)=="tmp"]=x
again this can also be added in loop
Solution 9 - R
Happened to me several times. Use data.table package. When you only have 1 column that you need to refer to. Use either
DT[[x]]
or
DT[,..x]
When you have 2 or more columns to refer to, make sure to use:
DT[,..x]
That x can be strings in another data.frame.
Solution 10 - R
too late.. but I guess I have the answer -
Here's my sample study.df dataframe -
>study.df
study sample collection_dt other_column
1 DS-111 ES768098 2019-01-21:04:00:30 <NA>
2 DS-111 ES768099 2018-12-20:08:00:30 some_value
3 DS-111 ES768100 <NA> some_value
And then -
> ## Selecting Columns in an Given order
> ## Create ColNames vector as per your Preference
>
> selectCols <- c('study','collection_dt','sample')
>
> ## Select data from Study.df with help of selection vector
> selectCols %>% select(.data=study.df,.)
study collection_dt sample
1 DS-111 2019-01-21:04:00:30 ES768098
2 DS-111 2018-12-20:08:00:30 ES768099
3 DS-111 <NA> ES768100
>