如何基于另一DataFrame的行列信息为df1填充温度列？

架构老林

2026-7-1

问题

现有两个R语言数据框：

df1包含Station（站点）和带时分信息的Date列，结构如下：

df1 <- structure(list(Station = c("A", "A", "B", "C", "C", "C"), Date = c(
  "1/07/2022 15:45",
  "20/08/2022 23:50", "12/03/2023 21:26", "10/08/2022 13:26", "14/08/2022 13:10",
  "15/08/2022 15:26"
)), row.names = c(NA, -6L), spec = structure(list(
  cols = list(Station = structure(list(), class = c(
    "collector_character",
    "collector"
  )), Date = structure(list(), class = c(
    "collector_character",
    "collector"
  ))), default = structure(list(), class = c(
    "collector_guess",
    "collector"
  )), delim = ","
), class = "col_spec"), class = c(
  "spec_tbl_df",
  "tbl_df", "tbl", "data.frame"
))

df2以YYYYMMDD格式的日期作为列名，存储各站点对应日期的温度值，结构如下：

df2 <- structure(list(Station = c("A", "B", "C", "D"), `20220701` = c(
  24.1,
  22.5, 23.5, 22.3
), `20220810` = c(18.7, 13.4, 16.3, 12.2), `20220814` = c(
  21.4,
  18.9, 27.8, 25.6
), `20220815` = c(16.5, 21.7, 22.5, 28.4), `20220820` = c(
  23.6,
  23.4, 31.6, 24.4
), `20230312` = c(25.5, 17.5, 23.7, 22.1), `20230820` = c(
  32.6,
  12.2, 21.1, 29.4
)), row.names = c(NA, -4L), spec = structure(list(
  cols = list(Station = structure(list(), class = c(
    "collector_character",
    "collector"
  )), `20220701` = structure(list(), class = c(
    "collector_double",
    "collector"
  )), `20220810` = structure(list(), class = c(
    "collector_double",
    "collector"
  )), `20220814` = structure(list(), class = c(
    "collector_double",
    "collector"
  )), `20220815` = structure(list(), class = c(
    "collector_double",
    "collector"
  )), `20220820` = structure(list(), class = c(
    "collector_double",
    "collector"
  )), `20230312` = structure(list(), class = c(
    "collector_double",
    "collector"
  )), `20230820` = structure(list(), class = c(
    "collector_double",
    "collector"
  ))), default = structure(list(), class = c(
    "collector_guess",
    "collector"
  )), delim = ","
), class = "col_spec"), class = c(
  "spec_tbl_df",
  "tbl_df", "tbl", "data.frame"
))

需要为df1新增Temperature列，根据站点和日期匹配df2中的对应温度值，得到目标结果：

structure(list(Station = c("A", "A", "B", "C", "C", "C"), Date = c(
  "1/07/2022 15:45",
  "20/08/2022 23:50", "12/03/2023 21:26", "10/08/2022 13:26", "14/08/2022 13:10",
  "15/08/2022 15:26"
), Temperature = c(
  24.1, 23.6, 17.5, 16.3,
  27.8, 28.4
)), row.names = c(NA, -6L), spec = structure(list(cols = list(
  Station = structure(list(), class = c(
    "collector_character",
    "collector"
  )), Date = structure(list(), class = c(
    "collector_character",
    "collector"
  )), Temperature = structure(list(), class = c(
    "collector_double",
    "collector"
  ))
), default = structure(list(), class = c(
  "collector_guess",
  "collector"
)), delim = ","), class = "col_spec"), class = c(
  "spec_tbl_df",
  "tbl_df", "tbl", "data.frame"
))

解决方案

通过转换df2为长格式，同时提取df1日期的YYYYMMDD格式，再进行匹配合并即可实现需求，具体步骤如下：

加载所需工具包

library(tidyverse)

处理df1：解析日期并提取YYYYMMDD格式的日期字符串

df1_processed <- df1 %>%
  mutate(Date_ymd = format(dmy_hms(Date), "%Y%m%d"))

这里用dmy_hms()解析带时分的日期字符串，再用format()转为与df2列名一致的格式。

将df2转换为长格式数据

df2_long <- df2 %>%
  pivot_longer(cols = -Station, names_to = "Date_ymd", values_to = "Temperature")

把原df2中以日期命名的列转为Date_ymd列，对应的温度值转为Temperature列，让df2结构与df1_processed可匹配。

合并数据并整理成目标格式

result <- df1_processed %>%
  left_join(df2_long, by = c("Station", "Date_ymd")) %>%
  select(Station, Date, Temperature)

通过left_join()根据站点和转换后的日期匹配温度值，最后保留需要的列即可得到目标数据框。

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