R语言含额外列内容的pivot_longer问题求解

要处理这种混合涡轮配置信息与观测数据的表格，核心思路是先拆分配置与数据，再给每个观测时段匹配对应站点的涡轮编码，最后整合成整洁格式。以下是具体实现步骤：

步骤1：拆分配置行与观测数据行 #

先把数据分为两类：带涡轮编码（AFG开头）的配置行，以及实际的观测数据行，同时统一处理空值：

library(tidyverse)
library(lubridate)

# 统一处理空值：把空格/空字符串转成NA
df_clean <- df %>% mutate(across(-date, ~na_if(., ""), ~na_if(., " ")))

# 提取涡轮配置行：只要有一列是AFG开头的行
turbine_config <- df_clean %>%
  filter(if_any(-date, ~str_detect(., "^AFG"))) %>%
  pivot_longer(-date, names_to = "station", values_to = "turbine") %>%
  drop_na(turbine) %>% # 剔除空的配置项
  arrange(station, date)

# 提取观测数据行：排除配置行，同时去掉无观测值的空行
obs_data <- df_clean %>%
  filter(!if_any(-date, ~str_detect(., "^AFG"))) %>%
  filter(!if_all(-date, is.na)) %>%
  pivot_longer(-date, names_to = "station", values_to = "value")

步骤2：构建站点涡轮的时间生效区间 #

给每个站点的涡轮编码生成生效时间范围，确保后续能匹配到对应时段的涡轮：

turbine_timeline <- turbine_config %>%
  group_by(station) %>%
  mutate(
    # 用未来日期作为最后一段涡轮的结束时间
    next_date = lead(date, default = ymd("9999-12-31"))
  ) %>%
  ungroup()

步骤3：匹配观测数据与涡轮编码 #

用非等连接将观测日期与涡轮生效区间关联，得到每个观测对应的正确涡轮：

final_tidy <- obs_data %>%
  left_join(turbine_timeline, by = "station") %>%
  # 筛选出观测日期落在涡轮生效区间内的记录
  filter(date.x >= date.y, date.x < next_date) %>%
  select(date = date.x, station, turbine, value) %>%
  arrange(date, station)

验证结果 #

查看前几行，与期望格式一致：

head(final_tidy)
#>         date  station turbine value
#> 1 2022-01-05 station1  AFG221     A
#> 2 2022-01-05 station2  AFG514    NA
#> 3 2022-01-05 station3  AFG111     C
#> 4 2022-01-06 station1  AFG221     C
#> 5 2022-01-06 station2  AFG514    NA
#> 6 2022-01-06 station3  AFG111     C

检查station3在2022-01-18之后的涡轮编码：

final_tidy %>% filter(station == "station3", date >= ymd("2022-01-18")) %>% head(3)
#>         date  station turbine value
#> 1 2022-01-18 station3  AFG143     B
#> 2 2022-01-19 station3  AFG143     A
#> 3 2022-01-20 station3  AFG143     C

补充说明 #

• 若原始CSV空值为NA，可跳过第一步的na_if处理
• 若需要保留空观测值（value为NA的行），去掉obs_data里的drop_na(value)即可
• 非等连接逻辑确保了每个观测日期匹配最新生效的涡轮编码，完美解决涡轮更换场景

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