I have a data frame that looks like this:

I want to group columns with similar names together so that the live columns are together, and the same for dead and total. Since I'd like to columns to be in a custom order (ie not alphabetical), I can't use order(names(df)).

I'd like to rearrange the columns so that it looks something like this:

If possible, I also want to add empty columns separating the different groups. So one empty column separating live.3 and dead.1 and another empty column separating dead.3 and total.1.

Here's the code to reproduce the example:

df <- data.frame(c(1, 2, 3),
                 c(4, 5, 6),
                 c(7, 8, 9),
                 c(10, 11, 12),
                 c(13, 14, 15),
                 c(16, 17, 18),
                 c(19, 20, 21),
                 c(22, 23, 24),
                 c(25, 26, 27))
names(df) <- c("live.1", "dead.1", "total.1", "live.2", "dead.2", "total.2", "live.3", "dead.3", "total.3")

Thanks in advance!

CodePudding user response：

One (admittedly somewhat ugly) option is to predetermine the order.

cn <- as.character(t(outer(c("live.", "dead.", "total."), 1:3, paste0)))
df[cn]
#  live.1 live.2 live.3 dead.1 dead.2 dead.3 total.1 total.2 total.3
#1      1     10     19      4     13     22       7      16      25
#2      2     11     20      5     14     23       8      17      26
#3      3     12     21      6     15     24       9      18      27

CodePudding user response：

I made a function that numbers next to live or etc may not in 1, 2, 3.

reorrder <- function(df, x){
  vec <- c()
  for (i in 1:length(x)){
    vec <- c(vec, which(grepl(x[i],names(df1))))
  }
  return(df[,vec])
}

If you want order of live, dead, and total,

x <- c("live", "dead", "total")
reorrder(df1, x)

  live.1 live.2 live.3 dead.1 dead.2 dead.3 total.1 total.2 total.3
1      1     10     19      4     13     22       7      16      25
2      2     11     20      5     14     23       8      17      26
3      3     12     21      6     15     24       9      18      27

CodePudding user response：

The answer is pretty simple:

df[, sort(names(df))]

  dead.1 dead.2 dead.3 live.1 live.2 live.3 total.1 total.2 total.3
1      4     13     22      1     10     19       7      16      25
2      5     14     23      2     11     20       8      17      26
3      6     15     24      3     12     21       9      18      27

What's happening here is that sort(names(df)) is just alphabetically sorting the columns and returning result as a vector:

> sort(names(df))
[1] "dead.1"  "dead.2"  "dead.3"  "live.1"  "live.2"  "live.3"  "total.1"
[8] "total.2" "total.3"

Then, we use the fact that you an index a data frame's columns (the second item in square brackets) using a character vector, which will return columns in the same order as the vector you provided, e.g.

> df[, c('live.1', 'total.3')]
  live.1 total.3
1      1      25
2      2      26
3      3      27