基于参考数据框按组排序R嵌套DataFrame并标记未排序项

需求说明 #

我有一个嵌套结构的DataFrame，需要按ID和Year分组后排序，排序规则由另一个参考DataFrame指定。参考表无法覆盖待排序表中所有Location组合：

• 若某个ID-Year组内的所有Location都能在对应ID的参考表中找到，新增Sort列标记为1，并按参考表的优先级排序
• 若组内存在不在参考表的Location，Sort标记为0，组内保持原顺序

示例数据 #

待排序DataFrame #

ID Year Location Note
1  2000 LA       West Coast
1  2000 Boston   East Coast
1  2000 SF       West Coast
1  2001 Boston   East Coast
1  2001 LA       West Coast
2  1998 SF       West Coast
2  1999 Atlanta  East Coast
2  1999 NYC      East Coast

参考DataFrame #

ID          Location
1           LA
1           Miami
1           Boston
1           Salt Lake City
1           NYC
2           NYC
2           Atlanta

期望输出 #

ID Year Location Note        Sort 
1  2000 LA       West Coast  0
1  2000 Boston   East Coast  0
1  2000 SF       West Coast  0
1  2001 LA       West Coast  1
1  2001 Boston   East Coast  1
2  1998 SF       West Coast  1
2  1999 NYC      East Coast  1
2  1999 Atlanta  East Coast  1

解决方案 #

使用tidyverse工具链可以高效完成需求，步骤如下：

1. 加载依赖包 #

library(tidyverse)

2. 构造示例数据（如果已有数据可跳过） #

# 待排序DataFrame
df <- tibble(
  ID = c(1,1,1,1,1,2,2,2),
  Year = c(2000,2000,2000,2001,2001,1998,1999,1999),
  Location = c("LA", "Boston", "SF", "Boston", "LA", "SF", "Atlanta", "NYC"),
  Note = c("West Coast", "East Coast", "West Coast", "East Coast", "West Coast", "West Coast", "East Coast", "East Coast")
)

# 参考DataFrame
ref_df <- tibble(
  ID = c(1,1,1,1,1,2,2),
  Location = c("LA", "Miami", "Boston", "Salt Lake City", "NYC", "NYC", "Atlanta")
)

3. 数据处理与排序 #

# 为参考表添加排序权重，按ID分组后的行号确定优先级
ref_df <- ref_df %>%
  group_by(ID) %>%
  mutate(sort_weight = row_number()) %>%
  ungroup()

# 合并数据、标记Sort状态并排序
result <- df %>%
  # 连接参考表，获取每个Location的排序权重
  left_join(ref_df, by = c("ID", "Location")) %>%
  # 按ID和Year分组处理
  group_by(ID, Year) %>%
  # 判断组内所有Location是否都在参考表中，生成Sort列
  mutate(Sort = ifelse(all(!is.na(sort_weight)), 1, 0)) %>%
  # 排序逻辑：有权重的按权重排序，无权重的保留原顺序并后置
  arrange(ifelse(is.na(sort_weight), Inf, sort_weight), row_number()) %>%
  # 移除临时权重列
  select(-sort_weight) %>%
  ungroup()

# 查看结果
print(result)

代码说明 #

• 给参考表添加sort_weight：每个ID下的Location按出现顺序分配权重，用于后续排序
• left_join：匹配待排序表和参考表，未匹配的Location会得到NA权重
• 分组判断Sort：用all(!is.na(sort_weight))检查组内所有Location是否都在参考表中
• 排序规则：有权重的按参考表顺序排列，无权重的保留原数据顺序并放在组内最后
• 移除临时列：清理不需要的sort_weight列，得到最终结果

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