Order columns in R by substring

Question

I want to order columns of a dataframe using a substring of the column name only. Suppose my columns are named

df <- data.frame("x_b"=5,
                 "y_b"=2,
                 "x_a"=6,
                 "y_a"=3,
                 "y_c"=1,
                 "x_c"=4)

and I want to order them using a first-order and a second-order key. The first-order key would be first_order <- c("y", "x") and the second-order key be like second_order <- c("c", "b", "a").

I try kind of ugly code where I build my desired order in a loop:

order <- list()
for (first_key in first_order){
  for (second_key in second_order){
    order <- cbind(order, paste(first_key, second_key, sep="_"))
  }
}

The result is as I want:

> df[as.character(order)]
  y_c y_b y_a x_c x_b x_a
1   1   2   3   4   5   6

But I wouldn't say that my code is very easy, nor quick. Do you have a better suggestion?

Answer 1

Memory Growth Issues:

When working with data in R avoid using cbind and rbind as much as possible if there are loops involved. R Inferno dedicates a chapter to the memory growth issue.

Strings are king:

In this case, you only care about column order. The order of columns within a data.frame can be easily adjusted by supplying a vector containing all of the column names in your preferred order to the data.frame object. As a result, the process is not very computationally expensive if you operate solely with column names.

With this being said, we can simplify the process greatly using:

# Starting Data Frame
df <- data.frame("x_b"=5,
                 "y_b"=2,
                 "x_a"=6,
                 "y_a"=3,
                 "y_c"=1,
                 "x_c"=4)

m = names(df) # Extract column names.

r = strsplit(m,"_") # Split the column names based on the _

# Create a data.frame with two columns X1 being x or y
# X2 being a, b, or c
q = data.frame(matrix(unlist(r),nrow=length(m),byrow=T)) 

# Order q according to spec
q = q[order(q$X1,q$X2, decreasing = T),]

# Reformat names
m = paste0(q$X1,"_",q$X2)

# Rearrange columns
df = df[,m]

The order of q is:

 X1 X2
  y  c
  y  b
  y  a
  x  c
  x  b
  x  a

Thus, the columns of df look like:

 y_c y_b y_a x_c x_b x_a
   1   2   3   4   5   6

Edit The responder has clarified that they would like the order to be slightly different in the last column e.g. (c,a,b) instead of (c,b,a). There are two ways to go about this: factor() or using match(). I'll opted to only present factor() in detail as the later is more convoluted.

Factors

In this case, you should turn both columns into factors and then order on the levels without the descend option. Using factors for both columns makes the code cleaner and more logical than before since the choice of levels is able to be done in their given order e.g. y,x and c,a,b.

If you were to modify the previous example that used descend, there would be an issue in the order of the X2 column. Most notably, the column would be sorted as 'b','a','c' instead of 'c','a','b'. The reason for this is due to levels being tied to numeric values, like 1,2,3, and the prior example used descend to sort both options. Thus, if we continued to use descend, we would of been sorting X2 on 'c' => 1, 'a' => 2, 'b' => 3. The fix to this is simply reversing the order of the levels - b, a, c - which may make everything a bit more confusing later on.

Thus, we have:

# Select sort order for X1: y, x
x1.values = c("y","x")
# Select sort order for X2: c, a, b
x2.values = c("c","a","b")

# Place call to factor for both columns
q = q[order(factor(q$X1, levels = x1.values), factor(q$X2, levels = alpha)),]

Giving q as:

 X1 X2
  y  c
  y  a
  y  b
  x  c
  x  a
  x  b

So that the columns of df are now:

 y_c y_a y_b x_c x_a x_b
   1   3   2   4   6   5