如何按单列分组，并从多行多列生成Python风格列表字符串

• 按mcode列对数据分组，每个分组生成两类行
• 每个分组取key为TRUE的行的Cat1、Cat2、Cat3分类值
• 分别将pcode和needed列的所有非NA值组合成Python风格的列表字符串

注：当mcode首次出现新值时，key列值为TRUE

示例数据 #

Cat1  Cat2  Cat3  mcode key   pcode needed
 1 C1    C2    C31   B3100 TRUE  P001  P001  
 2 C1    C2    C31   B3100 FALSE P002  P002  
 3 C1    C2    C31   B5500 TRUE  P003  P003  
 4 C1    C2    C31   B5500 FALSE P004  NA    
 5 C1    C2    C31   B5500 FALSE P005  NA    
 6 C1    C2    C32   B1000 TRUE  P006  NA    
 7 C1    C2    C32   B1000 FALSE P007  P007  
 8 C1    C2    C32   B1000 FALSE P008  NA    
 9 C1    C2    C32   B1000 FALSE P009  P009  
10 C1    C2    C32   B1000 FALSE P010  P010  

期望输出 #

mcode Cat1  Cat2  Cat3  type   extended_info                                             
1 B1000 C1    C2    C32   pcode  ['P006','P007','P008','P009','P010']
2 B1000 C1    C2    C32   needed ['P007','P009','P010']              
3 B3100 C1    C2    C31   pcode  ['P001','P002']                     
4 B3100 C1    C2    C31   needed ['P001','P002']                     
5 B5500 C1    C2    C31   pcode  ['P003','P004','P005']              
6 B5500 C1    C2    C31   needed ['P003']   

复现代码 #

library(tibble)
df <- tribble(
  ~Cat1, ~Cat2, ~Cat3, ~mcode, ~key,   ~pcode, ~needed,
  "C1",        "C2",        "C31",       "B3100",      TRUE,   "P001",       "P001",
  "C1",        "C2",        "C31",       "B3100",      FALSE,  "P002",       "P002",
  "C1",        "C2",        "C31",       "B5500",      TRUE,   "P003",       "P003",
  "C1",        "C2",        "C31",       "B5500",      FALSE,  "P004",       NA,
  "C1",        "C2",        "C31",       "B5500",      FALSE,  "P005",       NA,
  "C1",        "C2",        "C32",       "B1000",      TRUE,   "P006",       NA,
  "C1",        "C2",        "C32",       "B1000",      FALSE,  "P007",       "P007",
  "C1",        "C2",        "C32",       "B1000",      FALSE,  "P008",       NA,
  "C1",        "C2",        "C32",       "B1000",      FALSE,  "P009",       "P009",
  "C1",        "C2",        "C32",       "B1000",      FALSE,  "P010",       "P010"
)
expected_output <- tribble(
  ~mcode, ~Cat1, ~Cat2, ~Cat3, ~type,   ~extended_info,
  "B1000", "C1", "C2", "C32", "pcode",  "['P006','P007','P008','P009','P010']",
  "B1000", "C1", "C2", "C32", "needed", "['P007','P009','P010']",
  "B3100", "C1", "C2", "C31", "pcode",  "['P001','P002']",
  "B3100", "C1", "C2", "C31", "needed", "['P001','P002']",
  "B5500", "C1", "C2", "C31", "pcode",  "['P003','P004','P005']",
  "B5500", "C1", "C2", "C31", "needed", "['P003']"
)

实现代码 #

用dplyr和tidyr即可完成需求，核心逻辑是分组聚合后转长格式：

library(dplyr)
library(tidyr)

result <- df %>%
  group_by(mcode) %>%
  summarise(
    # 提取key=TRUE行的分类值
    Cat1 = first(Cat1[key == TRUE]),
    Cat2 = first(Cat2[key == TRUE]),
    Cat3 = first(Cat3[key == TRUE]),
    # 拼接非NA值为Python风格列表
    pcode = paste0("['", paste(na.omit(pcode), collapse = "','"), "']"),
    needed = paste0("['", paste(na.omit(needed), collapse = "','"), "']")
  ) %>%
  # 转成长格式，生成两类行
  pivot_longer(
    cols = c(pcode, needed),
    names_to = "type",
    values_to = "extended_info"
  ) %>%
  # 按mcode和type排序，匹配期望输出顺序
  arrange(mcode, type)

# 验证结果与期望输出一致
all.equal(result, expected_output)

内容的提问来源于stack exchange，提问作者Kra.P