基于参考数据框批量替换数据框多列值的技术需求

需求说明 #

需要将df_skeleton中的多列值替换为df_ref的对应值，满足以下条件：

• df_skeleton的列名匹配df_ref列名中的描述符子串（如Phase匹配A1 - Phase中的Phase）
• df_skeleton的ID值匹配df_ref列名中的标识符子串（如A1匹配A1 - Phase中的A1）
• 两数据框的Date Time列值完全相等

df_ref的列名格式为「标识符-描述符」，需通过正则拆分列名，匹配对应维度后完成替换。

数据定义 #

library(tidyverse)
library(lubridate)

# 参考数据框
df_ref <- tibble(`Date Time` = c(seq(ymd_hm("2023-1-1 0:00"), ymd_hm("2023-1-1 5:00"), by = "hour")),
                 `A1 - Phase` = c("NA", "Stdby", "Stdby", "Active", "Active", "Active"),
                 `A1 - Value` = seq(1, 12, by = 2),
                 `B1 - Phase` = c("NA", "NA", "Stdby", "Active", "Active", "Active"),
                 `B1 - Value` = seq(2, 7, by = 1))

# 待替换的骨架数据框
df_skeleton <- tibble(`Date Time` = rep(seq(ymd_hm("2023-1-1 0:00"), ymd_hm("2023-1-1 5:00"), by = "hour"), 2),
                      ID = c("A1", "A1", "A1", "A1", "A1", "A1", "B1", "B1", "B1", "B1", "B1", "B1"),
                      Phase = 1790,
                      `Value` = 1790)

# 期望输出
df_desired_output <- tibble(`Date Time` = rep(seq(ymd_hm("2023-1-1 0:00"), ymd_hm("2023-1-1 5:00"), by = "hour"), 2),
                            ID = c("A1", "A1", "A1", "A1", "A1", "A1", "B1", "B1", "B1", "B1", "B1", "B1"),
                            Phase = c("NA", "Stdby", "Stdby", "Active", "Active", "Active", "NA", "NA", "Stdby", "Active", "Active", "Active"),
                            `Value` = c("1", "3", "5", "7", "9", "11", "2", "3", "4", "5", "6", "7"))

解决方案 #

核心思路是先将df_ref从宽格式转为长格式，拆分出标识符和描述符，再与df_skeleton按匹配条件合并替换值。

# 处理参考数据框：宽转长，拆分列名得到ID和描述符
df_ref_long <- df_ref %>%
  pivot_longer(
    cols = -`Date Time`,
    names_to = c("ID", "descriptor"),
    names_pattern = "(.*) - (.*)",  # 正则拆分「标识符-描述符」格式的列名
    values_to = "ref_value"
  )

# 替换骨架数据框的值：按匹配条件合并后替换
df_result <- df_skeleton %>%
  pivot_longer(
    cols = c(Phase, Value),  # 指定需要替换的列
    names_to = "descriptor",
    values_to = "skeleton_value"
  ) %>%
  left_join(df_ref_long, by = c("Date Time", "ID", "descriptor")) %>%
  mutate(skeleton_value = ref_value) %>%  # 替换值
  select(-ref_value) %>%
  pivot_wider(
    names_from = "descriptor",
    values_from = "skeleton_value"
  )

# 验证结果是否与期望输出一致
all.equal(df_result, df_desired_output, check.attributes = FALSE)
# [1] TRUE

步骤解释 #

1. 宽转长并拆分列名：用pivot_longer将df_ref的多列转为行，通过正则(.*) - (.*)拆分列名，得到ID和描述符，同时保留对应的值和Date Time。
2. 骨架数据框转长格式：将df_skeleton中需要替换的列转为长格式，方便后续匹配。
3. 合并并替换值：通过left_join按Date Time、ID、描述符三个条件匹配，将参考值替换骨架中的原始值。
4. 转回宽格式：将处理后的长格式数据转回宽格式，得到最终结果。

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