R语言中如何实现行转列、删除Time列并按日期整理多站点监测数据？

核心处理思路 #

• 按固定块拆分原始数据：每6行对应一个独立监测站点的记录，拆分后直接提取每组的站点标识（A/B/C）
• 过滤无效冗余行：移除每组内的站点名标记行、含Time/Value的表头行，直接剔除不需要的Time列关联数据
• 转换数据格式：将文本格式的日期、监测值转为R可识别的标准日期类型、数值类型
• 行转列展开：以日期为行索引，将三个站点的监测值展开为独立列，最后按日期升序排序得到目标规整表格

可直接运行的R代码 #

# 原始df结构（实际使用时若已加载则无需重复构造）
df <- data.frame(
  stringsAsFactors = FALSE,
  Column1 = c("Station Name:","Date",
              "01/01/2000","02/01/2000","07/03/2010","31/12/2020",
              "Station Name:","Date","01/01/2000","02/01/2000",
              "07/03/2010","31/12/2020","Station Name:","Date","01/01/1999",
              "02/01/1999","01/10/2009","31/12/2020"),
  Column2 = c("A","Time","00:00:00",
              "00:00:00","00:00:00","00:00:00","B","Time","00:00:00",
              "00:00:00","00:00:00","00:00:00","C","Time","00:00:00",
              "00:00:00","00:00:00","00:00:00"),
  Column3 = c("NA","Value","2.9","2.42",
              "2.06","1.92","NA","Value","1.19","1.16","1.13",
              "1.08","NA","Value","12.5","8.39","6.48","9.87")
)

# 加载依赖包
library(dplyr)
library(tidyr)

# 1. 为每行匹配所属站点标签，每个站点固定占6行
df$Station <- rep(c("A", "B", "C"), each = 6)

# 2. 筛选有效监测行：仅保留格式为标准日期的记录，自动跳过Time列相关非数据行
valid_data <- df %>% 
  filter(grepl("^\\d{2}/\\d{2}/\\d{4}$", Column1))

# 3. 整理为长表结构
long_format <- valid_data %>% 
  transmute(
    Date = as.Date(Column1, format = "%d/%m/%Y"),
    Station = Station,
    Value = as.numeric(Column3)
  )

# 4. 行转列生成宽表，按日期升序排列
final_result <- long_format %>% 
  pivot_wider(names_from = Station, values_from = Value) %>% 
  arrange(Date)

输出结果说明 #

运行代码后得到的规整表格符合要求：

• 第一列为标准化排序后的日期，类型为R内置Date格式
• 后续三列分别为站点A、B、C的对应日期监测值，完全移除了原结构中的Time列、重复表头、站点标记等冗余内容
• 站点间无匹配记录的日期位置自动填充为NA

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