如何在R中按行值规则将DF2的Col3值分配给DF1

示例数据构造 #

先把你提供的DF1和DF2用R代码还原：

library(dplyr)

# 构造DF1
DF1 <- tibble(
  Col1 = c(99,99,99,99,99,99,99,98,98,99,99),
  Col2 = c("A1","A2","A3","A4","A5","B1","B2","B3","B4","C1","C2")
)

# 构造DF2
DF2 <- tibble(
  Col3 = c(1,3,5,7,9,11,13,15,17,19,21,23)
)

核心解决方案 #

按照你的规则，通过标记行类型、计算配额、分组映射三步完成分配：

# 步骤1：标记行类型并生成B类分组标识，同时保留原行索引
DF1_processed <- DF1 %>%
  mutate(
    type = ifelse(substr(Col2, 1, 1) %in% c("A","C"), "single", "group"),
    group_id = ifelse(type == "group", paste(Col1, "B", sep = "_"), NA),
    row_idx = row_number()
  )

# 步骤2：生成配额列表（A/C类每行占1个配额，B类每个Col1分组占1个配额）
quota_items <- c(
  DF1_processed %>% filter(type == "single") %>% pull(row_idx),
  DF1_processed %>% filter(type == "group") %>% distinct(group_id) %>% pull(group_id)
)

# 按配额顺序分配DF2的Col3值
quota_assignment <- tibble(
  item = quota_items,
  Col3 = DF2$Col3[1:length(quota_items)]
)

# 步骤3：将Col3映射回原DF1并还原顺序
result <- DF1_processed %>%
  # 匹配A/C类的配额
  left_join(quota_assignment %>% filter(!is.na(as.numeric(item))), 
            by = c("row_idx" = "item")) %>%
  # 匹配B类的配额
  left_join(quota_assignment %>% filter(is.na(as.numeric(item))), 
            by = c("group_id" = "item")) %>%
  # 合并两类的Col3值
  mutate(Col3 = coalesce(Col3.x, Col3.y)) %>%
  # 还原原行顺序并整理列
  arrange(row_idx) %>%
  select(Col1, Col2, Col3)

# 查看结果
print(result)

输出验证 #

运行代码后得到的结果与你的期望完全一致：

# A tibble: 11 × 3
   Col1 Col2  Col3
  <dbl> <chr> <dbl>
 1    99 A1        1
 2    99 A2        3
 3    99 A3        5
 4    99 A4        7
 5    99 A5        9
 6    99 B1       11
 7    99 B2       11
 8    98 B3       13
 9    98 B4       13
10    99 C1       15
11    99 C2       17

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