如何在R语言中基于日期区间生成带复制值的每日数据集？

现有一组包含dates、bi7dr、Month、Year、Day字段的数据集，需要生成从最早给定日期至当日的每日数据，规则为：每个日期沿用前一个给定日期对应的bi7dr值，直至下一个给定日期。

输入数据集 #

dates bi7dr Month Year Day
 1: 2023-01-19  5.75     1 2023  19
 2: 2022-12-22  5.50    12 2022  22
 3: 2022-11-17  5.25    11 2022  17
 4: 2022-10-20  4.75    10 2022  20
 5: 2022-09-22  4.25     9 2022  22
 6: 2022-08-23  3.75     8 2022  23
 7: 2022-07-21  3.50     7 2022  21
 8: 2022-06-23  3.50     6 2022  23
 9: 2022-05-24  3.50     5 2022  24

期望输出示例 #

until today 5.75
2023-01-21  5.75
2023-01-20  5.75
2023-01-19  5.75
2023-01-18  5.50
2023-01-17  5.50
...........
2022-12-22  5.50
2022-12-21  5.25
2022-12-20  5.25
until the latest data

解决方案 #

方案1：R语言（data.table） #

适合处理大规模数据，效率较高：

library(data.table)

# 构造输入数据（若已有数据集可直接加载）
dt <- data.table(
  dates = as.Date(c("2023-01-19", "2022-12-22", "2022-11-17", "2022-10-20", "2022-09-22", "2022-08-23", "2022-07-21", "2022-06-23", "2022-05-24")),
  bi7dr = c(5.75, 5.50, 5.25, 4.75, 4.25, 3.75, 3.50, 3.50, 3.50),
  Month = c(1,12,11,10,9,8,7,6,5),
  Year = c(2023,2022,2022,2022,2022,2022,2022,2022,2022),
  Day = c(19,22,17,20,22,23,21,23,24)
)

# 按日期降序排序，确定每个bi7dr的生效区间
dt <- dt[order(-dates)]
dt[, next_date := shift(dates, type = "lead") - 1]
dt[nrow(dt), next_date := dates]

# 生成从最早日期到今日的完整日期序列
full_dates <- seq(from = min(dt$dates), to = Sys.Date(), by = "day")

# 匹配每个日期对应的bi7dr值并按降序输出
result <- data.table(date = full_dates)[
  dt, on = .(date >= next_date, date <= dates), bi7dr := i.bi7dr
][order(-date)]

# 查看结果
print(result)

方案2：Python语言（pandas） #

适合熟悉Python生态的用户：

import pandas as pd
from datetime import datetime

# 构造输入数据（若已有数据集可直接加载）
data = {
    "dates": ["2023-01-19", "2022-12-22", "2022-11-17", "2022-10-20", "2022-09-22", "2022-08-23", "2022-07-21", "2022-06-23", "2022-05-24"],
    "bi7dr": [5.75, 5.50, 5.25, 4.75, 4.25, 3.75, 3.50, 3.50, 3.50],
    "Month": [1,12,11,10,9,8,7,6,5],
    "Year": [2023,2022,2022,2022,2022,2022,2022,2022,2022],
    "Day": [19,22,17,20,22,23,21,23,24]
}

df = pd.DataFrame(data)
df["dates"] = pd.to_datetime(df["dates"]).dt.date

# 按日期降序排序
df = df.sort_values(by="dates", ascending=False).reset_index(drop=True)

# 生成完整日期序列
min_date = df["dates"].min()
today = datetime.today().date()
full_dates = pd.date_range(start=min_date, end=today).date
result_df = pd.DataFrame({"date": full_dates})

# 为每个日期匹配对应的bi7dr值
for idx, row in df.iterrows():
    if idx == len(df)-1:
        mask = result_df["date"] <= row["dates"]
    else:
        next_date = df.loc[idx+1, "dates"]
        mask = (result_df["date"] > next_date) & (result_df["date"] <= row["dates"])
    result_df.loc[mask, "bi7dr"] = row["bi7dr"]

# 按日期降序排序并输出
result_df = result_df.sort_values(by="date", ascending=False).reset_index(drop=True)
print(result_df)

内容的提问来源于stack exchange，提问作者Ewang Khoirul Asrori