base::ifelse() within dplyr::arrange() for conditional arrangement of grouped rows

user_9494044 0
Viewed 246

I'm trying to order the rows of a data.frame conditional upon the value of another column.

Here's an example below:

library(magrittr)
library(dplyr)

df <- data.frame(grp = c(1,1,1,2,2,2), 
                 ori = c("f","f","f","r","r","r"),
                 ite = c("A","B","C","A","B","C"))

df

# #  grp ori ite
# 1   1   f   A
# 2   1   f   B
# 3   1   f   C
# 4   2   r   A
# 5   2   r   B
# 6   2   r   C

df %>%
  group_by(grp) %>%
  arrange(ifelse(ori == "f", ite, desc(ite)), .by_group = TRUE) %>%
  ungroup()

# # A tibble: 6 × 3
# # Groups:   grp [2]
#     grp ori   ite  
#   <dbl> <chr> <chr>
# 1     1 f     A    
# 2     1 f     B    
# 3     1 f     C    
# 4     2 r     A    
# 5     2 r     B    
# 6     2 r     C

The expected output is:

# #  grp ori ite
# 1   1   f   A
# 2   1   f   B
# 3   1   f   C
# 4   2   r   C
# 5   2   r   B
# 6   2   r   A

I have a general idea of why it doesn't work: arrange() cannot look at things on a per-row basis, which is what the ifelse() is asking it to do.

Is there a better way of accomplishing this?

dplyr data-manipulation r
4 Answers

One option to achieve your desired result would be to make use of split + arrange + bind_rows like so:

library(dplyr)
library(purrr)

df %>%
  split(.$ori) %>% 
  purrr::imap(
    ~ if (.y == "f") arrange(.x, grp, ite) else arrange(.x, grp, desc(ite))
  ) %>% 
  dplyr::bind_rows()
#>   grp ori ite
#> 1   1   f   A
#> 2   1   f   B
#> 3   1   f   C
#> 4   2   r   C
#> 5   2   r   B
#> 6   2   r   A

And thanks to the suggestion by @MartinGal we could save the bind_rows step by making use of purrr::imap_dfr:


df %>%
  split(.$ori) %>% 
  purrr::imap_dfr(
    ~ if (.y == "f") arrange(.x, grp, ite) else arrange(.x, grp, desc(ite))
  )
user_129938610

One possible way is splitting the column ori and creating a function to then combine the results as following:

df %>% 
  split(.$ori) %>% 
  map(function(x) {
    
    if ('f' %in% x$ori) {
      
      x %>% 
        group_by(grp) %>% 
        arrange(ite, .by_group = TRUE)
    }
    else {
        x %>% 
        group_by(grp) %>% 
        arrange(desc(ite), .by_group = TRUE)
      }
    }) %>% 
  bind_rows()

# A tibble: 6 x 3
## Groups:   grp [2]
#    grp ori   ite  
#  <dbl> <chr> <chr>
#1     1 f     A    
#2     1 f     B    
#3     1 f     C    
#4     2 r     C    
#5     2 r     B    
#6     2 r     A
user_86761700

The idea to use ifelse(ori == "f", ite, desc(ite)) is basically good, unfortunately desc(ite) has a negative numeric vector as output, whereas the output of ite is a character vector.

ifelse(df$ori == "f", df$ite, dplyr::desc(df$ite))
#> [1] "A"  "B"  "C"  "-1" "-3" "-5"

To bring the result of ite in reverse order using the same output as input we can write a function asc() which just does the opposite of desc():

asc <- function(x) {
  xtfrm(x)
}

No we can use both inside ifelse():

library(dplyr)

df <- data.frame(grp = c(1,1,1,2,2,2), 
                 ori = c("f","f","f","r","r","r"),
                 ite = c("A","B","C","A","B","C"))

df %>%
  arrange(ori, ifelse(ori == "r", desc(ite), asc(ite)))

#>   grp ori ite
#> 1   1   f   A
#> 2   1   f   B
#> 3   1   f   C
#> 4   2   r   C
#> 5   2   r   B
#> 6   2   r   A

Created on 2022-08-21 by the reprex package (v2.0.1)

user_93493020

Here's another solution.

df %>% 
  arrange(
    ifelse(ori == 'f', ite, NA), 
    desc(ifelse(ori != 'f', ite, NA))
    )
user_102912910
Related