如何基于ID匹配为数据框添加对应站点的日期范围行？

问题描述 #

我有一个命名日期列表date_list，列表元素名称包含站点ID、月份和年份；同时有一个包含station_id和state列的数据框df。已经通过代码从列表中提取出日期范围date_range，但不知道如何生成对应重复的station_id和state列，最终得到包含站点ID、州、开始日期、结束日期的目标数据框。

示例数据定义 #

日期列表定义

A1_apr_23 <- structure(c(1680346800, 1680406800, 1680466800, 1680526800, 1680586800, 
                         1680646800, 1680706800, 1680766800, 1680826800, 1680886800, 1680946800, 
                         1681006800, 1681066800, 1681169160, 1681229160, 1681289160, 1681349160, 
                         1681409160, 1681469160, 1681529160, 1681589160, 1681649160, 1681709160, 
                         1681769160, 1681829160, 1681889160, 1681949160, 1682009160, 1682069160, 
                         1682129160, 1682189160, 1682249160, 1682309160), class = c("POSIXct", 
                                                                                    "POSIXt"), tzone = "America/Chicago")

A1_dec_22 <- structure(c(1669877940, 1669937940, 1669997940, 1670057940, 1670117940, 
                         1670177940, 1670237940, 1670297940, 1670357940, 1670417940, 1670477940, 
                         1670537940, 1670597940, 1670657940, 1670717940, 1670777940, 1670837940, 
                         1670897940, 1670957940, 1671017940, 1671077940, 1671137940, 1671197940, 
                         1671257940, 1671317940, 1671377940, 1671437940, 1671497940, 1671557940, 
                         1671617940, 1671677940, 1671737940, 1671797940), class = c("POSIXct", 
                                                                                    "POSIXt"), tzone = "America/Chicago")


B1_mar_23 <- structure(c(1677650400, 1677710400, 1677770400, 1677830400, 1677890400, 
                         1677950400, 1678010400, 1678070400, 1678130400, 1678190400, 1678250400, 
                         1678352040, 1678412040, 1678472040, 1678532040, 1678592040, 1678652040, 
                         1678712040, 1678772040, 1678832040, 1678892040, 1678952040, 1679012040, 
                         1679072040, 1679132040, 1679192040, 1679252040, 1679312040, 1679372040, 
                         1679432040, 1679492040, 1679552040, 1679612040), class = c("POSIXct", 
                                                                                    "POSIXt"), tzone = "America/Chicago")
B1_may_23 <- structure(c(1683099260, 1683159980, 1683019980, 1683079980, 1683139980, 
                         1683199980, 1683259980, 1683319980, 1683379980, 1683439980, 1683499980, 
                         1683559980, 1683619980, 1683679980, 1683781980, 1683841980, 1683901980, 
                         1683961980, 1684021980, 1684081980, 1684141980, 1684201980, 1684261980, 
                         1684321980, 1684381980, 1684441980, 1684543440, 1684603440, 1684663440, 
                         1684723440, 1684783440, 1684843440, 1684903440), class = c("POSIXct", 
                                                                                    "POSIXt"), tzone = "America/Chicago") 
B1_dec_22 <- structure(c(1670520840, 1670580840, 1670640840, 1670700840, 1670760840, 
                         1670820840, 1670880840, 1670940840, 1671000840, 1671060840, 1671120840, 
                         1671180840, 1671240840, 1671300840, 1671360840, 1671420840, 1671480840, 
                         1671540840, 1671600840, 1671660840, 1671720840, 1671780900, 1671840900, 
                         1671900900, 1671960900, 1672020900, 1672080900, 1672140900, 1672200900, 
                         1672260900, 1672320900, 1672380900, 1672440900), class = c("POSIXct", 
                                                                                    "POSIXt"), tzone = "America/Chicago")
C1_feb_23 <- structure(c(1675242060, 1675302060, 1675362060, 1675422060, 1675482060, 
                         1675542060, 1675602060, 1675662060, 1675763640, 1675823640, 1675883640, 
                         1675943640, 1676003640, 1676063640, 1676123640, 1676183640, 1676243640, 
                         1676346120, 1676406120, 1676466120, 1676526120, 1676586120, 1676646120, 
                         1676706120, 1676766120, 1676826120, 1676886120, 1676946120, 1677048600, 
                         1677108600, 1677168600, 1677228600, 1677288600), class = c("POSIXct", 
                                                                                    "POSIXt"), tzone = "America/Chicago")

date_list <- list(A1_apr_23, A1_dec_22, B1_mar_23, B1_may_23, B1_dec_22, C1_feb_23) %>% 
  setNames(c("A1_apr_23", "A1_dec_22", "B1_mar_23", "B1_may_23", "B1_dec_22", "C1_feb_23"))

站点信息数据框定义

df <- data.frame(
  station_id = c("A1", "B1", "C1"),
  state = c("CA", "IL", "CA")
)

已提取的日期范围

date_range <- Map(
  function(d) return(lubridate::date(range(d))),
  date_list
)

目标数据框 #

需要生成如下结构的数据框：

new_df <- data.frame(
  station_id = c("A1", "A1", "B1", "B1", "B1", "C1"),
  state = c("CA", "CA", "IL", "IL", "IL", "CA"),
  start_date = date_range %>% sapply(`[[`, 1) %>% as.vector() %>% as.Date(origin="1970-01-01"),
  end_date = date_range %>% sapply(`[[`, 2) %>% as.vector() %>% as.Date(origin="1970-01-01")
)

解决方案 #

通过以下步骤可以实现需求：

1. 从日期列表名称提取站点ID：用正则表达式从date_list的元素名称中提取开头的站点ID部分。
2. 转换日期范围为数据框：将date_range中的每个日期范围向量转为数据框的行，并添加站点ID列。
3. 匹配站点州信息：将站点ID与df关联，补充对应的state列。

完整代码 #

library(dplyr)
library(lubridate)
library(stringr)

# 提取每个日期列表元素对应的station_id
station_ids <- names(date_list) %>% 
  str_extract("^[A-Z0-9]+")

# 将date_range转换为数据框并绑定站点ID
date_range_df <- date_range %>% 
  bind_rows() %>% 
  rename(start_date = V1, end_date = V2) %>% 
  mutate(station_id = station_ids)

# 合并站点信息，生成目标数据框
new_df <- date_range_df %>% 
  left_join(df, by = "station_id") %>% 
  select(station_id, state, start_date, end_date)

# 查看结果
print(new_df)

代码说明 #

• str_extract("^[A-Z0-9]+")：匹配日期列表名称开头的字母+数字组合，精准提取站点ID，适配示例中的A1/B1/C1格式。
• bind_rows()：将date_range中的多个向量转换为数据框的行结构，方便后续处理。
• left_join()：通过station_id关联df中的州信息，确保每个日期范围行都对应正确的站点所属州。

运行代码后即可得到目标格式的数据框。

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