R语言将单个值转列值：整理Excel导入的凌乱数据（dplyr优先）

针对从Excel导入的非结构化数据，我们可以通过dplyr结合tidyr工具，将独立行的Contraption和Attempt信息转换为关联列，最终得到可分析的结构化数据。以下是基于提供的dput数据的复用性处理流程：

处理代码 #

library(dplyr)
library(tidyr)
library(stringr)

# 加载数据（替换为你的数据对象）
df <- structure(list(` #s` = c("GROUND TRUTH", " #s", "3->6",
                              "3->11", "3->14", "6->11", "6->14", "11->14", "Contraption 1",
                              "Attempt 1", " #s", "3->6", "3->11",
                              "3->14", "6->11", "6->14", "11->14", "Attempt 2",
                              " #s", "3->6"), AX = c(NA, "AX", "162.4435258", "67.325616600000004",
                                                          "97.847449400000002", "115.628574", "76.795228199999997", "164.19540980000002",
                                                          NA, NA, "AX", "17.729424000000002", "45.376750999999999", "20.891473000000001",
                                                          "50.795566999999998", "7.6219530000000004", "58.288466999999997",
                                                          NA, "AX", "160.67087599999999"), AY = c(NA, "AY", "17.371937600000003",
                                                                                                  "5.3626663999999993", "17.839726800000001", "12.1478062", "5.5127848000000004",
                                                                                                  "13.4053042", NA, NA, "AY", "17.269538000000001", "5.4522810000000002",
                                                                                                  "17.783394999999999", "12.026367", "6.0820509999999999", "13.563755",
                                                                                                  NA, "AY", "17.594677000000001"), AZ = c(NA, "AZ", "175.6576848",
                                                                                                                                          "67.382918000000004", "99.333905000000001", "116.01154019999998",
                                                                                                                                          "76.799018200000006", "162.97033279999999", NA, NA, "AZ", "6.0498320000000003",
                                                                                                                                          "45.120047999999997", "15.705375999999999", "50.471809999999998",
                                                                                                                                          "9.6571890000000007", "59.333387999999999", NA, "AZ", "167.606852"
), DX = c(NA, "DX", "9.1008000000000013", "32.729599999999998",
          "47.664360000000002", "23.628800000000002", "38.563539999999996",
          "14.93474", NA, NA, "DX", "-11.773400000000001", "-35.472099999999998",
          "-48.059600000000003", "-23.698699999999999", "-36.286200000000001",
          "-12.5875", NA, "DX", "-11.7559"), DY = c(NA, "DY", "-1.2008800000000002",
                                                    "-0.66572000000000009", "1.0446199999999999", "0.53513999999999995",
                                                    "2.2454800000000001", "1.71034", NA, NA, "DY", "-19.358799999999999",
                                                    "-18.635200000000001", "0.52759999999999996", "0.72360000000000002",
                                                    "19.886500000000002", "19.1629", NA, "DY", "-19.334599999999998"
), DZ = c(NA, "DZ", "-20.673919999999999", "-23.138440000000003",
          "-5.8264999999999993", "-2.4645199999999998", "14.847440000000001",
          "17.31194", NA, NA, "DZ", "1.09E-2", "-1.9400000000000001E-2",
          "-1.24E-2", "-3.04E-2", "-2.3400000000000001E-2", "7.0000000000000001E-3",
          NA, "DZ", "-9.7000000000000003E-3"), Distance = c(NA, "Distance",
                                                            "22.6203", "40.088119999999989", "48.030499999999996", "23.762999999999998",
                                                            "41.383979999999994", "22.927579999999999", NA, NA, "Distance",
                                                            "22.657900000000001", "40.069200000000002", "48.0625", "23.709700000000002",
                                                            "41.3782", "22.927299999999999", NA, "Distance", "22.628")), row.names = c(NA,
                                                                                                                                       -20L), class = c("tbl_df", "tbl", "data.frame"))

# 核心处理流程
structured_df <- df %>%
  # 提取Contraption编号：匹配以Contraption开头的行，提取数字
  mutate(Contraption = case_when(
    str_detect(` #s`, "^Contraption") ~ str_extract(` #s`, "\\d+"),
    TRUE ~ NA_character_
  )) %>%
  # 提取Attempt编号：匹配以Attempt开头的行，提取数字
  mutate(Attempt = case_when(
    str_detect(` #s`, "^Attempt") ~ str_extract(` #s`, "\\d+"),
    TRUE ~ NA_character_
  )) %>%
  # 向下填充Contraption和Attempt的缺失值，让每条数据关联对应分组
  fill(Contraption, Attempt, .direction = "down") %>%
  # 过滤掉无效行：包括分组标题行、表头重复行、GROUND TRUTH行
  filter(
    !str_detect(` #s`, "^(Contraption|Attempt|GROUND TRUTH)$"),
    ` #s` != " #s"
  ) %>%
  # 将数值列转换为数值类型
  mutate(across(c(AX, AY, AZ, DX, DY, DZ, Distance), as.numeric)) %>%
  # 调整列顺序，将分组列放到最后
  select(` #s`, AX, AY, AZ, DX, DY, DZ, Distance, Contraption, Attempt) %>%
  # 重命名` #s`为更整洁的#s
  rename(`#s` = ` #s`)

# 查看结果
print(structured_df)

步骤解释 #

1. 提取分组编号：通过str_detect识别Contraption和Attempt行，用str_extract提取其中的数字编号，存入新列。
2. 填充分组值：使用fill向下填充缺失的分组编号，确保每条数据记录都关联对应的Contraption和Attempt。
3. 过滤无效行：移除分组标题、重复表头以及GROUND TRUTH这类非数据行。
4. 数据类型转换：将原本为字符型的数值列转换为数值类型，方便后续分析。
5. 列整理：调整列顺序并修正列名格式，让结构更符合分析需求。

最终效果 #

处理后的数据将呈现为每条记录对应唯一的Contraption和Attempt编号，所有数值列可直接用于统计分析，与期望的结构化格式完全匹配。

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