Reorder columns in a specific order

tidyverse
1

I have this tibble and i need to reorder the columns.

Nombre Equipo fecha_1_365 fecha_2_365 fecha_3_365 fecha_1_sofa fecha_2_sofa fecha_3_sofa fecha_1_who fecha_2_who fecha_3_who fecha_1_prom fecha_2_prom fecha_3_prom
John Amarillo 6 6.5 6.6 6.1 6.9 6.6 5.77 6.43 6.19 5.96 6.61 6.46
Maria Amarillo 7.7 NA NA 7.3 NA NA 6.99 NA NA 7.33 NA NA
Selena Amarillo 6.7 6.3 NA 7.1 6.5 NA 6.82 6.23 NA 6.87 6.34 NA
Leonardo Azul NA 8.3 NA NA 9.3 NA NA 8.78 NA NA 8.79 NA
Brad Azul 6 7.4 NA 6.3 7.3 NA 6.06 7.16 NA 6.12 7.29 NA

I need the order be: fecha_1_365, fecha_1_sofa, fecha_1_who, fecha_1_prom, fecha_2_365, fecha_2_sofa, fecha_2_who, fecha_2_prom, fecha_3_365, fecha_3_sofa, fecha_3_who, fecha_3_prom.
How can I do it in a fast and automated way? I could do:

df %>%
select(Nombre, Equipo, posicion, 
         starts_with("fecha_1"),
         starts_with("fecha_2"),
         starts_with("fecha_3"))

But i prefer no because in the case of having many variables it would take a long time to specify.

2

Here is a quick example on how to do it.

dat1 <-   data.frame(aa = letters[1:5], bb = 1:5, cc = 5:1,
                     dd = 11:15)
dat2 <-   dat1[ , c("aa", "dd", "cc", "bb")]
3

Hello, thanks for the answer. but I was looking for a solution where I don't have to write the variable names one by one

4

Automation requires you to define logic, what would be the ordering logic to apply?, for example, if I take alphabetical order as the ordering logic, I can do something like this:

library(dplyr)

#Sample data in a copy/paste friendly format
df <- data.frame(
  stringsAsFactors = FALSE,
            Nombre = c("John", "Maria", "Selena", "Leonardo", "Brad"),
            Equipo = c("Amarillo", "Amarillo", "Amarillo", "Azul", "Azul"),
       fecha_1_365 = c(6, 7.7, 6.7, NA, 6),
       fecha_2_365 = c(6.5, NA, 6.3, 8.3, 7.4),
       fecha_3_365 = c(6.6, NA, NA, NA, NA),
      fecha_1_sofa = c(6.1, 7.3, 7.1, NA, 6.3),
      fecha_2_sofa = c(6.9, NA, 6.5, 9.3, 7.3),
      fecha_3_sofa = c(6.6, NA, NA, NA, NA),
       fecha_1_who = c(5.77, 6.99, 6.82, NA, 6.06),
       fecha_2_who = c(6.43, NA, 6.23, 8.78, 7.16),
       fecha_3_who = c(6.19, NA, NA, NA, NA),
      fecha_1_prom = c(5.96, 7.33, 6.87, NA, 6.12),
      fecha_2_prom = c(6.61, NA, 6.34, 8.79, 7.29),
      fecha_3_prom = c(6.46, NA, NA, NA, NA)
)

# Relevant code
df %>% 
    select(Nombre, Equipo, sort(names(df[-c(1:2)])))
#>     Nombre   Equipo fecha_1_365 fecha_1_prom fecha_1_sofa fecha_1_who
#> 1     John Amarillo         6.0         5.96          6.1        5.77
#> 2    Maria Amarillo         7.7         7.33          7.3        6.99
#> 3   Selena Amarillo         6.7         6.87          7.1        6.82
#> 4 Leonardo     Azul          NA           NA           NA          NA
#> 5     Brad     Azul         6.0         6.12          6.3        6.06
#>   fecha_2_365 fecha_2_prom fecha_2_sofa fecha_2_who fecha_3_365 fecha_3_prom
#> 1         6.5         6.61          6.9        6.43         6.6         6.46
#> 2          NA           NA           NA          NA          NA           NA
#> 3         6.3         6.34          6.5        6.23          NA           NA
#> 4         8.3         8.79          9.3        8.78          NA           NA
#> 5         7.4         7.29          7.3        7.16          NA           NA
#>   fecha_3_sofa fecha_3_who
#> 1          6.6        6.19
#> 2           NA          NA
#> 3           NA          NA
#> 4           NA          NA
#> 5           NA          NA

Created on 2022-06-17 by the reprex package (v2.0.1)

5

Unless, as @ andresrcs suggests there is a clear decision rule all I can suggest to reduce typing is to use numbers rather than the column names

dat1 <-   data.frame(aa = letters[1:5], bb = 1:5, cc = 5:1,
                     dd = 11:15)
dat2 <-   dat1[ , c("aa", "dd", "cc", "bb")]

dat3 <-   dat1[ , c(1, 4, 3, 2)]
6

Hello, thanks for the answer. Yes, there is a logical order. It is divided into groups of dates and they are ordered in ascending numerical order. fecha_1 groups first, then fecha_2 groups, then fecha_3 groups, etc.
Within each group, the order would be: 365, sofa, score (these 3 first, the order is not relevant but the one chosen has to be repeated in the following groups) and finally _prom.
The final order is: fecha_1_365, fecha_1_sofa, fecha_1_who, fecha_1_prom, fecha_2_365, fecha_2_sofa, fecha_2_who, fecha_2_prom, fecha_3_365, fecha_3_sofa, fecha_3_who, fecha_3_prom.
I can't make a code that works.

7

A little hacky but it can work as a starting point for you to refine

library(tidyverse)

#Sample data in a copy/paste friendly format
df <- data.frame(
    stringsAsFactors = FALSE,
    Nombre = c("John", "Maria", "Selena", "Leonardo", "Brad"),
    Equipo = c("Amarillo", "Amarillo", "Amarillo", "Azul", "Azul"),
    fecha_1_365 = c(6, 7.7, 6.7, NA, 6),
    fecha_2_365 = c(6.5, NA, 6.3, 8.3, 7.4),
    fecha_3_365 = c(6.6, NA, NA, NA, NA),
    fecha_1_sofa = c(6.1, 7.3, 7.1, NA, 6.3),
    fecha_2_sofa = c(6.9, NA, 6.5, 9.3, 7.3),
    fecha_3_sofa = c(6.6, NA, NA, NA, NA),
    fecha_1_who = c(5.77, 6.99, 6.82, NA, 6.06),
    fecha_2_who = c(6.43, NA, 6.23, 8.78, 7.16),
    fecha_3_who = c(6.19, NA, NA, NA, NA),
    fecha_1_prom = c(5.96, 7.33, 6.87, NA, 6.12),
    fecha_2_prom = c(6.61, NA, 6.34, 8.79, 7.29),
    fecha_3_prom = c(6.46, NA, NA, NA, NA)
)

names <- data.frame(names = names(df[-c(1,2)])) %>% 
    separate(names, into = c("date", "var"), sep = "(?<=fecha_\\d)") %>% 
    mutate(var = factor(var, levels = c('_365', '_sofa', '_who', '_prom'))) %>% 
    arrange(date, var) %>% 
    transmute(names = paste0(date, var)) %>% 
    pull(names)

df %>% 
    select(Nombre, Equipo, all_of(names))
#>     Nombre   Equipo fecha_1_365 fecha_1_sofa fecha_1_who fecha_1_prom
#> 1     John Amarillo         6.0          6.1        5.77         5.96
#> 2    Maria Amarillo         7.7          7.3        6.99         7.33
#> 3   Selena Amarillo         6.7          7.1        6.82         6.87
#> 4 Leonardo     Azul          NA           NA          NA           NA
#> 5     Brad     Azul         6.0          6.3        6.06         6.12
#>   fecha_2_365 fecha_2_sofa fecha_2_who fecha_2_prom fecha_3_365 fecha_3_sofa
#> 1         6.5          6.9        6.43         6.61         6.6          6.6
#> 2          NA           NA          NA           NA          NA           NA
#> 3         6.3          6.5        6.23         6.34          NA           NA
#> 4         8.3          9.3        8.78         8.79          NA           NA
#> 5         7.4          7.3        7.16         7.29          NA           NA
#>   fecha_3_who fecha_3_prom
#> 1        6.19         6.46
#> 2          NA           NA
#> 3          NA           NA
#> 4          NA           NA
#> 5          NA           NA

Created on 2022-06-17 by the reprex package (v2.0.1)

1 Like
8

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.