如何按降序对gt表格按年、月、周及SampleDate排序？

问题说明 #

需要对生成的gt表格按year、month、week和SampleDate排序，要求最新年份（本例为2013）显示在表格顶部。原代码生成的表格中2012年记录排在了2013年之前，不符合需求。

原代码如下：

library(dplyr)
library(tidyr)
library(gt)

a <- structure(list(SampleDate = structure(c(15710, 15713, 15713, 
15710, 15710, 15713, 15713, 15710, 15708, 15713, 15712, 15708, 
15708, 15713, 15712, 15708), class = "Date"), year = c("2012", 
"2013", "2013", "2012", "2013", "2013", "2013", "2013", "2013", 
"2012", "2013", "2013", "2013", "2013", "2013", "2013"), F = c(0, 
1, 0, 0, 0, 1, 0, 0, 0, 22, 0, 0, 0, 65, 0, 0), W = c(0, 0, 1, 
0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0), S = c(0, 0, 0, 0, 1, 
0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0), LF = c(1, 0, 0, 1, 0, 0, 0, 
1, 0, 0, 0, 0, 1, 0, 0, 1), week = c("01", "02", "02", "01", 
"01", "02", "02", "01", "01", "02", "02", "01", "01", "02", "02", 
"01"), month = c("January", "January", "January", "January", 
"January", "January", "January", "January", "January", "January", 
"January", "January", "January", "January", "January", "January"
)), row.names = c(NA, -16L), class = "data.frame")

a |>
  mutate(SampleDate = as.character(SampleDate)) |>
  group_by(year, month, week, SampleDate) |>
  summarise(across(c(W, F, LF, S), sum)) |>
  gt() |>
  summary_rows(
    columns = -SampleDate,
    fns =  list(label = "Total", fn = "sum"))

解决方案 #

在数据处理阶段添加排序逻辑，确保最新年份优先展示，修改后的代码如下：

library(dplyr)
library(gt)

a <- structure(list(SampleDate = structure(c(15710, 15713, 15713, 
15710, 15710, 15713, 15713, 15710, 15708, 15713, 15712, 15708, 
15708, 15713, 15712, 15708), class = "Date"), year = c("2012", 
"2013", "2013", "2012", "2013", "2013", "2013", "2013", "2013", 
"2012", "2013", "2013", "2013", "2013", "2013", "2013"), F = c(0, 
1, 0, 0, 0, 1, 0, 0, 0, 22, 0, 0, 0, 65, 0, 0), W = c(0, 0, 1, 
0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0), S = c(0, 0, 0, 0, 1, 
0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0), LF = c(1, 0, 0, 1, 0, 0, 0, 
1, 0, 0, 0, 0, 1, 0, 0, 1), week = c("01", "02", "02", "01", 
"01", "02", "02", "01", "01", "02", "02", "01", "01", "02", "02", 
"01"), month = c("January", "January", "January", "January", 
"January", "January", "January", "January", "January", "January", 
"January", "January", "January", "January", "January", "January"
)), row.names = c(NA, -16L), class = "data.frame")

a |>
  # 将字符型的年份、周数转成数值，避免排序异常
  mutate(year = as.numeric(year),
         week = as.numeric(week),
         SampleDate = as.Date(SampleDate)) |>
  group_by(year, month, week, SampleDate) |>
  summarise(across(c(W, F, LF, S), sum), .groups = "drop") |>
  # 按需求排序：年份降序（最新年份在前），月份、周数、日期升序
  arrange(desc(year), month, week, SampleDate) |>
  # 将年份转回字符型，保持表格显示格式不变
  mutate(year = as.character(year)) |>
  gt() |>
  summary_rows(
    columns = -SampleDate,
    fns =  list(label = "Total", fn = "sum"))

关键要点 #

• 转换字段类型：把year和week从字符型转为数值型，避免字符串排序可能出现的错误（比如"10"会排在"2"前面）；
• 排序逻辑：使用arrange(desc(year), month, week, SampleDate)实现核心需求，年份降序保证最新年份置顶，其余字段升序保证同年内记录按合理顺序展示；
• 清理分组：summarise中添加.groups = "drop"，避免分组残留影响后续排序操作；
• 格式还原：最后将year转回字符型，确保表格显示格式与原代码一致。

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