如何在R中为每个经理对谈判时间执行向后外推

数据集 #

data_long=structure(list(id_manag = structure(c(50L, 50L, 50L, 50L, 50L, 
50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 50L, 
50L, 50L, 50L, 50L, 50L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 8L, 8L, 
8L, 8L), levels = c("Aldar Raldin", "Alek P", "Alex K", "Alice G.", 
"Amy G", "Ana M", "Andy", "Anton C", "Danny F", "Dimitris K", 
"Dmitrii Adsterra", "Egor M", "Eliza B", "Elizabeth D", "Emma M", 
"Evgeny Vlasov", "Greg E", "Ian K", "Jacob Adsterra", "James D", 
"Jeff S", "Jena M", "John M", "Kate C", "Kos", "Luke M", "Lyle F", 
"Mandy Miles", "Marie N", "Marina S", "MB_Adsterra", "Miles B", 
"Nadia K", "Nataly R", "Nickie D", "Paul J", "Roman K", "Ronnie T", 
"Saul B", "Sonya Adsterra", "Tanya S", "Ted B", "Trevor Adsterra", 
"Tsugur T", "Ulia L", "Vincent", "Violet W", "Xander D", "Zach K", 
"Zoya S"), class = "factor"), month = structure(c(19052, 19083, 
19113, 19144, 19174, 19205, 19236, 19266, 19297, 19327, 19358, 
19389, 19417, 19448, 19478, 19509, 19539, 19570, 19601, 19631, 
19662, 19692, 19723, 19052, 19083, 19113, 19144, 19174, 19205, 
19236, 19266, 19297, 19327, 19358, 19389, 19417, 19448, 19478, 
19509, 19539, 19570, 19601, 19631, 19662, 19692, 19723, 19052, 
19083, 19113, 19144), class = "Date"), seconds = c(0L, 0L, 0L, 
117L, 200L, 181L, 177L, 207L, 323L, 210L, 361L, 490L, 415L, 773L, 
372L, 275L, 322L, 391L, 335L, 294L, 253L, 269L, 278L, 0L, 0L, 
0L, 0L, 0L, 211L, 427L, 333L, 102L, 161L, 280L, 412L, 1058L, 
642L, 558L, 142L, 168L, 173L, 250L, 225L, 168L, 261L, 251L, 0L, 
0L, 0L, 210L), date_start = structure(c(18993, 18993, 18993, 
18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 
18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 
18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 
18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 
18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 18993, 
18993, 18993), class = "Date")), row.names = c(NA, 50L), class = "data.frame")

需求说明 #

数据集包含以下字段：

• id_manag：经理姓名
• month：月份日期
• seconds：谈判时长（数值型）
• date_start：入职日期

所有经理均在2022年3月1日后入职，需要基于每个经理的历史有效数据（排除seconds=0的过渡期），向前外推预测2022-01-01至其入职前一个月的seconds值，每个经理单独处理。

示例（Zoya S） #

Zoya S的有效数据起始于2022-06-01，需基于2022-06-01至2024-02-01的seconds数据，外推2022-01-01至2022-05-01的数值，预期输出如下：

id_manag    month   seconds
**Zoya S  01.01.2022  300
Zoya S    01.02.2022  326
Zoya S    01.03.2022  350
Zoya S    01.04.2022  323
Zoya S    01.05.2022  303**
Zoya S    01.06.2022  117
Zoya S    01.07.2022  200
Zoya S    01.08.2022  181
Zoya S    01.09.2022  177
Zoya S    01.10.2022  207
Zoya S    01.11.2022  323
Zoya S    01.12.2022  210
Zoya S    01.01.2023  361
Zoya S    01.02.2023  490
Zoya S    01.03.2023  415
Zoya S    01.04.2023  773
Zoya S    01.05.2023  372
Zoya S    01.06.2023  275
Zoya S    01.07.2023  322
Zoya S    01.08.2023  391
Zoya S    01.09.2023  335
Zoya S    01.10.2023  294
Zoya S    01.11.2023  253
Zoya S    01.12.2023  269
Zoya S    01.01.2024  278

最优实现方案 #

核心逻辑是按经理分组，先定位有效数据起始点，再用自动时间序列模型向前预测，最后合并结果。

步骤1：加载依赖包 #

library(tidyverse)
library(forecast)
library(lubridate)

步骤2：预处理数据，确定每个经理的有效起始日期 #

过滤掉每个经理seconds=0的记录，找到第一个有实际数据的月份：

data_clean <- data_long %>%
  group_by(id_manag) %>%
  filter(seconds != 0) %>%
  summarise(first_valid_month = min(month)) %>%
  right_join(data_long, by = "id_manag") %>%
  mutate(first_valid_month = replace_na(first_valid_month, min(month)))

步骤3：分组构建时间序列并向前预测 #

对每个经理单独处理，用auto.arima自动选择最优模型进行预测：

full_data <- data_clean %>%
  group_by(id_manag) %>%
  group_modify(function(.x, .y) {
    # 提取有效数据
    valid_data <- .x %>%
      filter(month >= .x$first_valid_month) %>%
      arrange(month)
    
    # 计算需要预测的月份数
    start_date <- ymd("2022-01-01")
    n_forecast <- interval(start_date, .x$first_valid_month %m-% months(1)) %/% months(1) + 1
    
    if(n_forecast > 0 && nrow(valid_data) >= 3) {
      # 构建月度时间序列
      ts_data <- ts(valid_data$seconds, 
                    start = c(year(valid_data$month[1]), month(valid_data$month[1])), 
                    frequency = 12)
      
      # 自动建模并预测
      arima_model <- auto.arima(ts_data)
      forecast_result <- forecast(arima_model, h = n_forecast)
      
      # 生成预测月份序列
      forecast_months <- seq(start_date, .x$first_valid_month %m-% months(1), by = "month")
      
      # 合并预测数据与原始有效数据
      forecast_df <- tibble(
        month = forecast_months,
        seconds = as.integer(round(forecast_result$mean))
      )
      
      bind_rows(forecast_df, valid_data %>% select(month, seconds)) %>%
        arrange(month) %>%
        mutate(id_manag = .y$id_manag)
    } else {
      # 数据不足时返回原始数据
      .x %>% select(id_manag, month, seconds) %>% arrange(month)
    }
  }) %>%
  ungroup() %>%
  select(id_manag, month, seconds)

步骤4：格式化日期（可选） #

如果需要将日期转为dd.mm.yyyy格式：

full_data_formatted <- full_data %>%
  mutate(month = format(month, "%d.%m.%Y"))

关键说明 #

• auto.arima会自动识别数据的趋势和季节性，无需手动指定模型参数，适配不同经理的时间序列模式。
• 仅当有效数据≥3个时才做预测，避免模型不稳定导致的无效结果。
• 预测结果取整为整数，符合seconds的变量属性。

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