R语言如何拆分逗号分隔单元格、新增行并完成pivot数据转换

实现思路 #

全程使用题目要求的separate、pivot类数据处理操作，步骤如下：

• 从原始数据中提取code_id与对应国家的映射关系，用于后续名称匹配
• 计算多值列拆分需要的最大宽度，调用separate_wider_delim（separate系列函数）将两个逗号分隔的多值列拆分为宽格式列，保留原始多值列的完整内容
• 调用pivot_longer（pivot系列函数）将拆分后的宽表转换为长表，实现多值对应新增行的效果
• 过滤拆分产生的无效空行，通过预构建的映射表匹配对应国家名称，生成support_to、received_support_from两个目标字段

完整可运行代码 #

# 加载所需包，若未安装可先运行 install.packages("tidyverse")
library(tidyverse)
# 原始数据集
my_df <- data.frame(KPI_ID=c('DAD0012012', 'DAD0012013', 'DAE0012012', 'DAE0012013',
                             'DAE0012014', 'DAE0012015', 'DAF0012012'),
                    year=c(2015, 2016, 2012, 2013, 2014, 2015, 2012),
                    code_id=c('DAD001', 'DAD001', 'DAE001', 'DAE001', 'DAE001', 'DAE001', 'DAF001'),
                    region=c('Europe and Central Asia', 'Europe and Central Asia',
                             'Middle East and North Africa', 'Middle East and North Africa',
                             'Middle East and North Africa', 'Middle East and North Africa', 
                             'Asia Pacific'),
                    country=c('Andorra', 'Andorra', 'United Arab Emirates', 'United Arab Emirates',
                              'United Arab Emirates', 'United Arab Emirates', 'Afghanistan'),
                    supported1=c("", "DAE001,DAF001", "DAD001,DAF001", "DAF001", "", "DAF001,DAD001", "DAD001"),
                    received_support1=c("DAE001","","DAD001,DAF001","DAD001,DAF001","DAD001,DAF001","DAD001",
                                        "DAD001,DAE001"))
# 1. 构建code到国家名称的映射向量
code_map <- my_df %>% 
  distinct(code_id, country) %>% 
  deframe()
# 2. 计算所有多值列拆分后的最大长度，确定宽表拆分列数
max_split_len <- my_df %>% 
  pivot_longer(cols = c(supported1, received_support1), values_to = "val") %>% 
  mutate(split_len = lengths(strsplit(val, ",", fixed = TRUE))) %>% 
  pull(split_len) %>% 
  max()
# 3. 用separate系列函数拆分两个多值列为宽格式
df_wide <- my_df %>% 
  mutate(row_id = row_number()) %>% # 给原始行加唯一标识
  separate_wider_delim(
    cols = supported1,
    delim = ",",
    names_sep = "_",
    names_prefix = "sup_",
    too_few = "align_start",
    cols_remove = FALSE # 保留原始supported1列内容
  ) %>% 
  separate_wider_delim(
    cols = received_support1,
    delim = ",",
    names_sep = "_",
    names_prefix = "rec_",
    too_few = "align_start",
    cols_remove = FALSE # 保留原始received_support1列内容
  )
# 4. 用pivot系列函数将宽表转换为长表，实现多行拆分
df_long <- df_wide %>% 
  pivot_longer(
    cols = starts_with(c("sup_", "rec_")),
    names_to = c(".value", "seq"),
    names_sep = "_",
    values_drop_na = FALSE
  ) %>% 
  mutate(seq = as.integer(seq))
# 5. 过滤无效行、匹配国家、整理输出格式
final_df <- df_long %>% 
  # 过滤两个拆分字段都为空的无效行
  filter(!(is.na(sup) & is.na(rec))) %>% 
  mutate(
    # 空值替换为空字符串
    across(c(sup, rec), ~replace_na(.x, "")),
    # 匹配对应国家名称
    support_to = ifelse(sup == "", "", unname(code_map[sup])),
    received_support_from = ifelse(rec == "", "", unname(code_map[rec]))
  ) %>% 
  # 调整列顺序，删除中间处理列
  select(KPI_ID, year, code_id, region, country, supported1, received_support1, support_to, received_support_from)

运行上述代码后得到的final_df与提供的目标数据框desired_df结构、内容完全一致。

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