如何忽略其他列值，仅对DataFrame的单个列进行排序？

我有一个包含Sheet、Sample、Replicate、Bacteria、Fungi五列的DataFrame，除Sample列为数值型外，其余均为字符型。目前无法将Sample列按升序排列，表面看似已排序，但实际查看数据时发现数值43后跟着2、13等数值。推测是Sheet列分组干扰了Sample列排序，尝试arrange(Sample)无效，希望实现忽略其他列值、仅对Sample列升序排序的效果。

数据结构与处理代码 #

read_files <- function (dir) {
  
  if (! dir.exists (paths = dir)) {
    
    stop ("The specified directory does not exist.")
    
  }
  
  dir_files <- list.files (path = dir, pattern = "\\.xlsx$", full.names = TRUE)
  
  datatables <- map (dir_files, function (file) {
    
    file_sheets <- excel_sheets (file)

    sheet_data <- map (file_sheets, function (sheet) {
      
      table <- read_excel      (file, sheet = sheet)                                             |>
               mutate          (across (everything (), as.character))                            |> 
               select          (1, 2, 4, 3)                                                      |>
               rename          (Sample = 1, Replicate = 2, Bacteria = 3, Fungi = 4)              |>
               mutate          (Sheet = sheet)                                                   |>
               relocate        (Sheet, .before = Sample)                                         |> 
               fill            (Sample, .direction = "down")                                     |>
               mutate          (Sample = iconv (Sample, from = "UTF-8", to = "ASCII//TRANSLIT")) |>
               mutate          (Sample = gsub ("\\n|\\t", "", Sample))                           |>
               mutate          (Sample = gsub ("\\xC2\\xA0+", "", Sample))                       |>
               mutate          (Sample = gsub ("\\s+", "", Sample))                              |> 
               mutate          (Sample = trimws (x = Sample, which = "both"))                    |> 
               mutate          (Sample = as.numeric (Sample))                                    |>
               arrange         (Sample, Replicate, Sheet)                                        #|&gt;
               #mutate          (Fungi = as.numeric (Fungi))
      
      return (table)
      
    })
    
    bind_rows (sheet_data)
    
  })

  names (datatables) <- basename (dir_files)
  
  return (datatables)
  
}

data <- read_files (dir = "./data/")

# 数据结构示例
> dput (head (data [[1]]))

structure(list(Sheet = c("Tabela01_Mes00", "Tabela01_Mes00", 
"Table01_Month00", "Table01_Month00", "Table01_Month00", "Table01_Month00"
), Sample = c(1, 1, 1, 3, 3, 3), Replicate = c("A ", "B ", 
"C ", "A ", "B ", "C "), Bacteria = c("<4000", "<4000", "<4000", 
"<4000", "<4000", "<4000"), Fungi = c("30", "20 ", "40", "00 ", 
"00 ", "20")), row.names = c(NA, -6L), class = c("tbl_df", "tbl", 
"data.frame"))

# 数据预览
> head (data [[1]], n = 5)

# A tibble: 5 × 5
  Sheet          Sample Replicate Bacteria Fungi
  <chr>           <dbl> <chr>     <chr>    <chr>
1 Table01_Month00      1 A         <4000    30   
2 Table01_Month00      1 B         <4000    20   
3 Table01_Month00      1 C         <4000    40   
4 Table01_Month00      3 A         <4000    00   
5 Table01_Month00      3 B         <4000    00 

解决方案 #

问题根源是当前代码在每个Sheet的子数据框内单独排序，之后才将所有Sheet数据绑定，导致全局数据是按Sheet分组后各自排序，无法实现Sample列的全局升序。

方式一：修改函数，绑定所有Sheet数据后统一排序 #

移除单个Sheet处理中的arrange，改为在所有Sheet数据绑定后执行全局排序：

read_files <- function (dir) {
  
  if (! dir.exists (paths = dir)) {
    
    stop ("The specified directory does not exist.")
    
  }
  
  dir_files <- list.files (path = dir, pattern = "\\.xlsx$", full.names = TRUE)
  
  datatables <- map (dir_files, function (file) {
    
    file_sheets <- excel_sheets (file)

    sheet_data <- map (file_sheets, function (sheet) {
      
      table <- read_excel      (file, sheet = sheet)                                             |>
               mutate          (across (everything (), as.character))                            |> 
               select          (1, 2, 4, 3)                                                      |>
               rename          (Sample = 1, Replicate = 2, Bacteria = 3, Fungi = 4)              |>
               mutate          (Sheet = sheet)                                                   |>
               relocate        (Sheet, .before = Sample)                                         |> 
               fill            (Sample, .direction = "down")                                     |>
               mutate          (Sample = iconv (Sample, from = "UTF-8", to = "ASCII//TRANSLIT")) |>
               mutate          (Sample = gsub ("\\n|\\t", "", Sample))                           |>
               mutate          (Sample = gsub ("\\xC2\\xA0+", "", Sample))                       |>
               mutate          (Sample = gsub ("\\s+", "", Sample))                              |> 
               mutate          (Sample = trimws (x = Sample, which = "both"))                    |> 
               mutate          (Sample = as.numeric (Sample))                                    # 移除单个Sheet内的排序
               #arrange         (Sample, Replicate, Sheet)                                       
               #mutate          (Fungi = as.numeric (Fungi))
      
      return (table)
      
    })
    
    # 绑定所有Sheet数据后，执行全局排序
    bind_rows (sheet_data) |>
      arrange(Sample, Replicate, Sheet)
    
  })

  names (datatables) <- basename (dir_files)
  
  return (datatables)
  
}

方式二：读取数据后，对单个文件数据单独排序 #

如果不想修改原函数，可在读取数据后直接对目标数据框执行全局排序：

data <- read_files(dir = "./data/")
# 对第一个文件的数据按Sample全局升序排序
data[[1]] <- data[[1]] |> arrange(Sample)
# 若需处理所有文件的数据，使用map批量处理
data <- map(data, ~ .x |> arrange(Sample))

效果验证 #

修改后，Sample列会按数值全局升序排列，不再受Sheet分组限制，不会出现大数值后跟随小数值的情况。

内容的提问来源于stack exchange，提问作者ginn