如何在R中按规则清洗Excel导入的结构化数据集

针对你提供的不规则结构数据集，我们可以分步骤完成列名重构、行数据整理，最终得到目标的df1和df2：

步骤1：定义列名映射与分组 #

首先明确人口统计列的正确名称（来自原数据第二行），以及题目列的新命名规则：

# 加载原数据
df <- data.frame(Text1 = c(NA, "Name","Jack","Jack"),
                 Text2 = c(NA, "District","Leon","Leon"),
                 Text3 = c(NA, "School","Black","Black"),
                 Text4 = c(NA, "Grade",1,1),
                 Scoring = c("Text", "Teacher","Ana","Ana"),
                 Item1 = c("11_ada", "/a/","1.0","0.0"),
                 Text7 = c(NA, "/c/","1.0","1.0"),
                 Text8 = c(NA, "/a/","1.0","1.0"),
                 Item2 = c("10_bada", "/x/","1.0","0.0"),
                 Text10 = c(NA, "/y/","0.0","1.0"),
                 Text11 = c(NA, "/z/","1.0","0.0"))

# 提取人口统计列的正式名称（取自原数据第二行）
demo_cols <- c("Text1", "Text2", "Text3", "Text4", "Scoring")
demo_col_names <- as.character(df[2, demo_cols])

# 定义题目列的新名称
item_col_names <- c("1.1", "1.2", "1.3", "2.1", "2.2", "2.3")

# 合并得到完整的目标列名
full_col_names <- c(demo_col_names, item_col_names)

步骤2：构建包含Item标识行的df2 #

先整理Item标识行、有效作答行的数据，再合并：

# 构建Item标识行：人口统计列填NA，题目列重复对应Item的标识
item_id_row <- c(
  rep(NA, length(demo_col_names)),
  rep(df[1, "Item1"], 3),  # 1.1-1.3对应Item1的标识
  rep(df[1, "Item2"], 3)   # 2.1-2.3对应Item2的标识
)

# 提取有效作答行的人口统计数据
demo_responses <- df[3:4, demo_cols]

# 提取有效作答行的题目数据
item_responses <- df[3:4, c("Item1", "Text7", "Text8", "Item2", "Text10", "Text11")]

# 合并所有行并设置列名
df2 <- data.frame(
  rbind(item_id_row, demo_responses, item_responses),
  stringsAsFactors = FALSE
)
colnames(df2) <- full_col_names

# 转换作答列类型：把"1.0"/"0.0"转为数值，保留Item标识字符串
df2[, item_col_names] <- lapply(df2[, item_col_names], function(x) {
  ifelse(x %in% c("1.0", "0.0"), as.numeric(x), x)
})

# 转换Grade列为数值型
df2$Grade <- as.numeric(df2$Grade)

步骤3：构建仅保留有效作答行的df1 #

只需移除df2的第一行（Item标识行）即可：

df1 <- df2[-1, ]

验证结果 #

df1输出：

print(df1)
#>    Name District School Grade Teacher 1.1 1.2 1.3 2.1 2.2 2.3
#> 2  Jack     Leon  Black     1     Ana 1.0 1.0 1.0 1.0 0.0 1.0
#> 3  Jack     Leon  Black     1     Ana 0.0 1.0 1.0 0.0 1.0 0.0

df2输出：

print(df2)
#>    Name District School Grade Teacher     1.1     1.2     1.3      2.1      2.2      2.3
#> 1  <NA>     <NA>   <NA>    NA    <NA> 11_ada 11_ada 11_ada 10_bada 10_bada 10_bada
#> 2  Jack     Leon  Black     1     Ana    1.0    1.0    1.0     1.0     0.0     1.0
#> 3  Jack     Leon  Black     1     Ana    0.0    1.0    1.0     0.0     1.0     0.0

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