By Samuel Song


2013-08-14 02:27:57 8 Comments

I wish to order a data frame based on different columns, one at a turn. I have a character vector with the relevant column names on which the order should be based:

parameter <- c("market_value_LOCAL", "ep", "book_price", "sales_price", "dividend_yield",
               "beta", "TOTAL_RATING_SCORE", "ENVIRONMENT", "SOCIAL", "GOVERNANCE")

I wish to loop over the names in parameter and dynamically select the column to be used to order my data:

Q1_R1000_parameter <- Q1_R1000[order(Q1_R1000$parameter[X]), ]

where X is 1:10 (because I have 10 items in parameter).


To make my example reproducible, 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, in a similar way as above (Q1_R1000$parameter[X]), the column is not selected:

cols <- c("cyl", "am")
mtcars$cols[1]
# NULL

8 comments

@ValaravausBlack 2019-01-22 06:37:20

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
> 

@EJ2015 2018-12-19 02:48:06

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

@makarand kulkarni 2018-07-13 08:15:11

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

@makarand kulkarni 2018-07-17 06:13:34

I don't know why negatively voted, but it works and easy way instead of writing complicated functions

@R Keene 2017-12-13 17:32:23

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]))
}

@manotheshark 2016-11-15 17:48:30

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 to allow dynamically building the sort list

sort_list <- c("gear", "desc(mpg)")
mtcars %>% arrange_(.dots = sort_list)

@discipulus 2017-07-03 05:53:15

What does NSE mean here?

@manotheshark 2017-07-03 15:46:29

@discipulus non-standard evaluation; it's for working with delayed expressions to dynamically build the code with strings instead of hard-coding. See here for more info: cran.r-project.org/web/packages/lazyeval/vignettes/…

@David 2013-08-14 03:32:57

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

@Simon O'Hanlon 2013-08-14 09:57:03

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

@Hong Ooi 2013-08-14 10:20:00

Q1_R1000[do.call(order, Q1_R1000[parameter]), ]

Related Questions

Sponsored Content

17 Answered Questions

[SOLVED] R - list to data frame

  • 2010-11-19 16:40:52
  • Btibert3
  • 561293 View
  • 432 Score
  • 17 Answer
  • Tags:   r list dataframe

18 Answered Questions

[SOLVED] Changing column names of a data frame

  • 2011-05-21 11:31:23
  • Son
  • 1093296 View
  • 331 Score
  • 18 Answer
  • Tags:   r dataframe

14 Answered Questions

[SOLVED] Select rows from a DataFrame based on values in a column in pandas

13 Answered Questions

[SOLVED] How to join (merge) data frames (inner, outer, left, right)?

13 Answered Questions

[SOLVED] Drop factor levels in a subsetted data frame

19 Answered Questions

[SOLVED] Drop data frame columns by name

  • 2011-01-05 14:34:29
  • Btibert3
  • 1168944 View
  • 744 Score
  • 19 Answer
  • Tags:   r dataframe r-faq

1 Answered Questions

[SOLVED] Dynamically select data frame columns using $

  • 2017-10-04 02:27:38
  • Gerg
  • 102 View
  • -2 Score
  • 1 Answer
  • Tags:   r dataframe

3 Answered Questions

[SOLVED] Select rows from a data frame based on values in a vector

  • 2012-07-23 12:08:48
  • Joe King
  • 141151 View
  • 54 Score
  • 3 Answer
  • Tags:   r subset r-faq

2 Answered Questions

[SOLVED] Extract data frame columns based on multiple criteria on column names

2 Answered Questions

[SOLVED] subsetting data frame based on search pattern in vector

  • 2013-12-07 11:40:36
  • sms
  • 1115 View
  • 2 Score
  • 2 Answer
  • Tags:   r

Sponsored Content