在R语言中将数据框指定行转换为列的实现方法

我有如下格式的数据集：

data
# A tibble: 12 × 3
    Number Letter      Identifier   
    <chr>  <chr>       <chr>
  1 1      A           AB   
  2 2      B           BC   
  3 <NA>   <NA>        <NA> 
  4 Site   Bow River   <NA> 
  5 Date   2020-06-01  <NA> 
  6 <NA>   <NA>        <NA> 
  7 Number Letter      Identifier   
  8 3      C           CD   
  9 4      D           DE   
 10 <NA>   <NA>        <NA> 
 11 Site   Elbow River <NA> 
 12 Date   2019-05-01  <NA>

需要将包含Site和Date的行转换为列，并向下填充对应值，得到如下结果：

# A tibble: 4 × 5
  Number Letter Identifer Site        Date      
  <chr>  <chr>  <chr>     <chr>       <chr>     
1 1      A      AB        Bow River   2020-06-01
2 2      B      BC        Bow River   2020-06-01
3 3      C      CD        Elbow River 2019-05-01
4 4      D      DE        Elbow River 2019-05-01

我试过用pivot_longer和pivot_wider都没成功，因为Site和Date行在数据中间，把数据分成了两部分。熟悉dplyr语法，也试过base R，但都没解决问题。可复现代码如下：

data <- tibble::tribble(~Number, ~Letter, ~Identifer, 
                "1", "A", "AB",
                "2", "B", "BC",
                NA_character_, NA_character_, NA_character_,
                "Site", "Bow River", NA_character_,
                "Date", "2020-06-01", NA_character_,
                NA_character_, NA_character_, NA_character_,
                "Number", "Letter", "Identifier",
                "3", "C", "CD",
                "4", "D", "DE",
                NA_character_, NA_character_, NA_character_,
                "Site", "Elbow River", NA_character_,
                "Date", "2020-05-01", NA_character_)

可以通过分组标记数据块、提取元数据、合并填充的方式处理，用dplyr和tidyr的组合操作就能实现：

library(dplyr)
library(tidyr)

result <- data %>%
  # 标记每个独立数据块（以空行为分隔）
  mutate(block = cumsum(ifelse(is.na(Number) & is.na(Letter), 1, 0))) %>%
  # 过滤空行和重复的表头行
  filter(!(is.na(Number) & is.na(Letter)) & Number != "Number") %>%
  # 按数据块分组，提取Site和Date值并填充
  group_by(block) %>%
  mutate(
    Site = ifelse(Number == "Site", Letter, NA),
    Date = ifelse(Number == "Date", Letter, NA)
  ) %>%
  fill(Site, Date, .direction = "updown") %>%
  # 过滤掉Site和Date的标记行，保留有效数据行
  filter(!Number %in% c("Site", "Date")) %>%
  # 调整列顺序并取消分组
  select(Number, Letter, Identifer, Site, Date) %>%
  ungroup() %>%
  # 可选：将Number转为数值型
  mutate(Number = as.integer(Number))

print(result)

代码解释： #

• 标记数据块：用cumsum和ifelse识别空行，给每组数据分配独立编号block，区分开不同的数据集及其元数据。
• 过滤无效行：移除空行和重复的表头行，只保留有效数据和元数据行。
• 提取并填充元数据：在每个数据块内，把Site和Date对应的Letter列值提取出来，用fill实现上下填充，让每个数据行匹配到对应的元数据。
• 清理结果：过滤掉Site和Date的标记行，调整列顺序，按需转换数据类型。

运行后得到的结果如下：

# A tibble: 4 × 5
  Number Letter Identifer Site        Date      
   <int> <chr>  <chr>     <chr>       <chr>     
1      1 A      AB        Bow River   2020-06-01
2      2 B      BC        Bow River   2020-06-01
3      3 C      CD        Elbow River 2020-05-01
4      4 D      DE        Elbow River 2020-05-01

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