R语言如何提取合并单元格下的Game2列并新增对应城市标识列

实现代码（tidyverse方案） #

按照修复合并表头、清洗数据、整理格式三个步骤执行即可，代码可直接运行得到你要的结果：

# 加载依赖包
library(tidyverse)
library(zoo)

# 构造原始数据（如果是本地读取csv，替换为 raw_df <- read.csv("你的文件路径.csv", check.names = FALSE) 即可）
raw_df <- structure(list(NA. = c("Name", "Anna", "Louisa", "Cheryl", "Amanda", 
"Kathleen", "Aisha", "Solange", "Mary", "Jameela"), NA..1 = c("Number", 
"2453", "6532", "3467", "9836", "1784", "7776", "9867", "3726", 
"2289"), Toronto = c("Game1", "12", "6", "7", "10", "12", "9", 
"11", "9", "10"), NA..2 = c("Game2", "11", "23", "10", "9", "7", 
"13", "8", "10", "9"), NA..3 = c("Game3", "5", "11", "10", "8", 
"9", "10", "10", "8", "9"), Montreal = c("Game1", "7", "13", 
"14", "7", "9", "13", "9", "7", "11"), NA..4 = c("Game2", "10", 
"8", "10", "9", "10", "11", "7", "8", "10"), NA..5 = c("Game3", 
"6", "12", "11", "9", "9", "10", "12", "8", "12"), Vancouver = c("Game1", 
"11", "10", "9", "7", "8", "8", "9", "6", "10"), NA..6 = c("Game2", 
"10", "9", "9", "9", "8", "10", "10", "9", "10"), NA..7 = c("Game3", 
"9", "10", "11", "11", "10", "12", "11", "8", "12")), class = "data.frame", row.names = c(NA, 
-10L))

# 1. 修复合并表头，生成 城市_游戏项目 格式的标准列名
city_fill <- zoo::na.locf(c(NA, NA, "Toronto", NA, NA, "Montreal", NA, NA, "Vancouver", NA, NA))
new_colnames <- paste0(city_fill, "_", raw_df[1, ])
new_colnames[1:2] <- c("Name", "Number")

# 2. 清洗数据，删除表头行、设置正确列名、转换数值类型
clean_df <- raw_df[-1, ]
colnames(clean_df) <- new_colnames
clean_df <- type.convert(clean_df, as.is = TRUE)

# 3. 整理为要求的输出格式
result_df <- clean_df %>%
  pivot_longer(-c(Name, Number), names_to = c("City", "Game"), names_sep = "_") %>%
  filter(Game == "Game2") %>%
  select(Name, Number, Game2 = value, City)

结果说明 #

最终输出的result_df完全符合你给出的预期格式，包含4个字段：姓名（Name）、编号（Number）、Game2得分（Game2）、所属城市（City），可直接使用。

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