如何按日期和类别计算均价并显示滞后的前一交易日均价？

解决思路 #

先计算每个类别每日的均价，再给每个类别的均价数据按日期排序并生成前一个记录的滞后均价，最后将该滞后均价匹配回原始表格的对应行。

代码实现 #

1. 准备原始数据

df <- data.frame(
  Date = c("10-12-2024", "10-12-2024", "10-12-2024", "10-12-2024",
           "10-17-2024", "10-17-2024", "10-19-2024", "10-19-2024"),
  category = c("Red", "Red", "Blue", "Blue", "Blue", "Blue", "Red", "Blue"),
  Price = c(0.9, 0.92, 1.23, 1.12, 0.93, 1.14, 0.99, 1.31)
)

2. 安装并加载所需包

如果你还没安装dplyr和lubridate，先执行安装命令：

install.packages(c("dplyr", "lubridate"))

加载包：

library(dplyr)
library(lubridate)

3. 计算每日均价并生成滞后均价

# 计算每个类别每日的均价，同时转换日期格式（避免字符串排序错误）
daily_avg <- df %>%
  mutate(Date = mdy(Date)) %>%
  group_by(category, Date) %>%
  summarise(daily_mean = mean(Price), .groups = "drop")

# 按类别分组、日期排序，生成前一个有记录日期的均价
daily_avg_lag <- daily_avg %>%
  group_by(category) %>%
  arrange(Date) %>%
  mutate(prev_day_avg = lag(daily_mean)) %>%
  ungroup()

4. 合并回原始表格并调整格式

result <- df %>%
  mutate(Date = mdy(Date)) %>%
  # 将滞后均价匹配回原表
  left_join(daily_avg_lag, by = c("category", "Date")) %>%
  # 调整列名和顺序
  select(Date, category, Price, `Average price previous trading day` = prev_day_avg) %>%
  # 将日期转回原始字符串格式
  mutate(Date = format(Date, "%m-%d-%Y"))

# 查看结果
print(result)

运行结果 #

Date category Price Average price previous trading day
1 10-12-2024      Red  0.90                                NA
2 10-12-2024      Red  0.92                                NA
3 10-12-2024     Blue  1.23                                NA
4 10-12-2024     Blue  1.12                                NA
5 10-17-2024     Blue  0.93                             1.175
6 10-17-2024     Blue  1.14                             1.175
7 10-19-2024      Red  0.99                              0.91
8 10-19-2024     Blue  1.31                             1.035

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