复杂的公式内部安排

Question

我想得到一个通用公式来排列具有不同数量列的数据帧。

例如，在这种情况下，数据帧包含“类别1，类别2，点1，点2”:

  library(tidyverse)
  set.seed(1)
  nrows <- 20
  df <- tibble(
    other_text = sample(letters,
                        nrows, replace = TRUE),
    categ_1 = sample(c("A", "B"), nrows, replace = TRUE),
    categ_2 = sample(c("A", "B"), nrows, replace = TRUE),
    points_1 = sample(20:25, nrows, replace = TRUE),
    points_2 = sample(20:25, nrows, replace = TRUE),
  ) %>%
    rowwise() %>%
    mutate(total = sum(c_across(starts_with("points_")))) %>%
    ungroup()

以及要安排的公式：

df %>%
  arrange(
    desc(total),
    categ_1, categ_2,
    desc(points_1), desc(points_2)
  )

但df可以有更多的列：“categ_1，categ_2，categ_3，points_1，points_2，points_3”。因此，在这种情况下，公式应为：

df %>%
  mutate(
    categ_3 = sample(c("A", "B"), nrows, replace = TRUE),
    points_3 = sample(20:25, nrows, replace = TRUE),
  ) %>%
    rowwise() %>%
    mutate(total = sum(c_across(starts_with("points_")))) %>%
    ungroup() %>%
    arrange(
      desc(total),
      categ_1, categ_2, categ_3,
      desc(points_1), desc(points_2), desc(points_3)
    )

我尝试编写一个通用公式（跨使用）：

  library(daff)

  daff::diff_data(
    df %>%
      arrange(
        desc(total),
        categ_1, categ_2,
        desc(points_1), desc(points_2)
      )
    ,
    df %>%
      arrange(
        desc(total),
        across(starts_with("categ_")),
        across(starts_with("points_"), desc)
      )
  )
#> Daff Comparison: ‘df %>% arrange(desc(total), categ_1, categ_2, desc(points_1), ’ ‘    desc(points_2))’ vs. ‘df %>% arrange(desc(total), across(starts_with("categ_")), across(starts_with("points_"), ’ ‘    desc))’
#>           A:A        B:B     ... E:E      F:F
#>       @@  other_text categ_1 ... points_2 total
#>       ... ...        ...     ... ...      ...
#> 10:9      z          A       ... 23       45
#> 9:10  :   v          A       ... 22       45
#> 11:11     s          B       ... 23       45
#>       ... ...        ...     ... ...      ...

arrange中似乎有一个bug：arrange只考虑参数，直到跨的第一个

我还尝试在case_when中编写条件，但找不到正确的语法：

  # not working
  df %>%
    arrange(
      across(everything(), ~ case_when(
        . == "total" ~ .,
        str_detect(., "categ_") ~ .,
        str_detect(., "points_") ~ desc(.),
        TRUE ~ 1
      )
      )
    )
#> Error in `arrange()`:
#> ! Problem with the implicit `transmute()` step.

在< code>arrange中编写该公式的一般方法是什么？(欢迎其他替代方案，但我更喜欢tidyverse解决方案。)

Answer 1

您可以尝试将< code>arrange()中的所有内容打包到一个数据框中。看起来< code>arrange()做了一些代码操作，以特殊方式处理顶级< code>desc()调用，这与< code>across()创建的数据帧有不良交互。但是使用数据帧解包功能可以避免这种情况。

library(tidyverse)

set.seed(3)
nrows <- 20

df <- tibble(
  other_text = sample(letters, nrows, replace = TRUE),
  categ_1 = sample(c("A", "B"), nrows, replace = TRUE),
  categ_2 = sample(c("A", "B"), nrows, replace = TRUE),
  points_1 = sample(20:25, nrows, replace = TRUE),
  points_2 = sample(20:25, nrows, replace = TRUE),
) %>%
  rowwise() %>%
  mutate(total = sum(c_across(starts_with("points_")))) %>%
  ungroup()

identical(
  df %>%
    arrange(
      desc(total),
      categ_1, categ_2,
      desc(points_1), desc(points_2)
    ),
  df %>%
    arrange(
      tibble(
        desc(total),
        across(starts_with("categ_")),
        across(starts_with("points_"), desc)
      )
    )
)
#> [1] TRUE

Answer 2

安装开发版本：

# remotes::install_github("tidyverse/dplyr")
library(tidyverse)

set.seed(144)
nrows <- 20
df <- tibble(
  other_text = sample(letters,
                      nrows, replace = FALSE),
  categ_1 = sample(c("A", "B"), nrows, replace = TRUE),
  categ_2 = sample(c("A", "B"), nrows, replace = TRUE),
  points_1 = sample(1:25, nrows, replace = FALSE),
  points_2 = sample(100:125, nrows, replace = FALSE),
) %>%
  rowwise() %>%
  mutate(total = sum(c_across(starts_with("points_")))) %>%
  ungroup()

out1 <- df %>%
  arrange(
    desc(total),
    categ_1, categ_2,
    desc(points_1), desc(points_2)
  )

out2 <- df %>%
  arrange(
    desc(total),
    across(starts_with("categ_")),
    across(starts_with("points_"), desc)
  )

daff::diff_data(out1, out2)
#> Daff Comparison: 'out1' vs. 'out2' 
#>      other_text categ_1 ...

Answer 3

一种简单（排序）的解决方案是使用cur_column（）来确定最终确定排序顺序的列名：

library(dplyr)
library(daff)

set.seed(32211)
nrows <- 20

df <- tibble(
   other_text = sample(letters,
                       nrows, replace = TRUE),
   categ_1 = sample(c("A", "B"), nrows, replace = TRUE),
   categ_2 = sample(c("A", "B"), nrows, replace = TRUE),
   points_1 = sample(20:25, nrows, replace = TRUE),
   points_2 = sample(20:25, nrows, replace = TRUE),
) %>%
   rowwise() %>%
   mutate(total = sum(c_across(starts_with("points_")))) %>%
   ungroup()

by_hand <- df %>%
   arrange(
      desc(total),
      categ_1, categ_2,
      desc(points_1), desc(points_2)
   )

with_across <- df %>%
   arrange(
      across(
         c(total, starts_with("categ_"), starts_with("points_")),
         .fns = function(x) {
            if (grepl("^total$|^points_.*$", cur_column()))
               desc(x)
            else
               x
         })
   )

diff_data(by_hand, with_across)
# Daff Comparison: ‘by_hand’ vs. ‘with_across’ 
#      other_text categ_1 categ_2 points_1 points_2 total