在R中计算竞品价格变动至我方价格匹配的天数

给定如下R语言数据框：

df <- data.frame(
  scrape_date = as.Date(c("2023-01-01", "2023-01-02", "2023-01-03", "2023-01-04", "2023-01-05", "2023-01-06", "2023-01-07", "2023-01-08")),
  own_product_id = c("00617","00617","00617","00617","00617","00617","00617","00617"),
  own_price = c(70, 70, 70, 70, 70,70,70,71),
  comp_price = c(70, 71, 71, 71, 71,71,71,71)
)

数据预览：

scrape_date own_product_id own_price comp_price
1  2023-01-01          00617        70         70
2  2023-01-02          00617        70         71
3  2023-01-03          00617        70         71
4  2023-01-04          00617        70         71
5  2023-01-05          00617        70         71
6  2023-01-06          00617        70         71
7  2023-01-07          00617        70         71
8  2023-01-08          00617        71         71

需求：计算从comp_price发生变动的日期开始，到own_price调整至与comp_price相同所花费的天数，期望输出格式如下：

product_id   time_taken_to_match
00617                     6 days 

方法一：基础R实现 #

无需额外安装包，直接用基础R函数完成：

# 定位comp_price首次变动的日期
comp_change_date <- df$scrape_date[which(df$comp_price != lag(df$comp_price))[1]]

# 定位own_price首次匹配变动后竞品价格的日期
match_date <- df$scrape_date[which(df$own_price == df$comp_price & df$scrape_date > comp_change_date)[1]]

# 整理成目标格式的结果
result <- data.frame(
  product_id = unique(df$own_product_id),
  time_taken_to_match = paste(as.integer(match_date - comp_change_date), "days")
)

print(result)

运行后输出：

product_id time_taken_to_match
1      00617              6 days

方法二：tidyverse工具包实现 #

如果日常使用tidyverse生态，可以用链式操作简化代码：

library(tidyverse)

# 先获取竞品价格首次变动的日期
comp_change_date <- df %>%
  filter(comp_price != lag(comp_price, default = first(comp_price))) %>%
  pull(scrape_date) %>%
  first()

# 计算并整理结果
df %>%
  group_by(own_product_id) %>%
  filter(own_price == comp_price, scrape_date > comp_change_date) %>%
  slice(1) %>%
  mutate(time_taken_to_match = paste(as.integer(scrape_date - comp_change_date), "days")) %>%
  select(product_id = own_product_id, time_taken_to_match) %>%
  ungroup()

输出结果：

# A tibble: 1 × 2
  product_id time_taken_to_match
  <chr>      <chr>              
1 00617      6 days             

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