使用R data.table计算依赖前序行的列：基于累计销量预测日销量

Got it, let's break this down with practical data.table code—this package is made for exactly these kinds of sequential, group-based calculations! I'll walk you through each step with sample data and explain the logic along the way.

First, let's create realistic sample tables to mirror your scenario:

• A daily sales table with actual sales (and placeholder 0s for dates we need to forecast)
• A key table with each product's total expected sales and forecast rules

library(data.table)

# Daily sales data (0s = dates needing forecasts)
sales_dt <- data.table(
  product = rep(c("A", "B"), each = 10),
  date = seq.Date(as.Date("2024-01-01"), as.Date("2024-01-10"), by = "day") %>% rep(2),
  daily_sales = c(100, 120, 110, 90, 130, 100, 80, 120, 0, 0, 80, 90, 70, 100, 110, 90, 0, 0, 0, 0)
)

# Key table with targets and forecast logic
key_dt <- data.table(
  product = c("A", "B"),
  expected_total = c(1500, 1200),
  forecast_rule = c(
    "Split remaining sales evenly across remaining days",
    "Adjust daily forecast to match expected sales pace based on cumulative sold"
  )
)

We need grouped cumulative sales (per product) since each product's forecast depends on its own sales history. data.table makes this trivial with cumsum() and by = product:

# Compute rolling cumulative sales per product
sales_dt[, cum_sales := cumsum(daily_sales), by = product]

Merge the sales data with the key table to pull in each product's total expected sales. We use data.table's fast join syntax with on = "product":

# Join to get expected total sales for each product
sales_dt <- sales_dt[key_dt, on = "product"]

Now we'll calculate forecasted daily sales for dates where we don't have actual data. We'll handle two common scenarios based on your description:

Scenario 1: Evenly Split Remaining Sales #

For products like A, we split the remaining sales across remaining days. We'll first calculate remaining days and sales, then fill in forecasts:

# Calculate total days and remaining days per product
sales_dt[, total_days := .N, by = product]
sales_dt[, day_number := seq_len(.N), by = product]
sales_dt[, remaining_days := total_days - day_number]

# Calculate remaining sales needed to hit the target
sales_dt[, remaining_sales := expected_total - cum_sales]

# Generate forecast: use actual sales where available, else split remaining sales
sales_dt[, forecast_daily := ifelse(
  daily_sales != 0, 
  daily_sales, 
  ifelse(remaining_days > 0, remaining_sales / remaining_days, 0) # Avoid division by zero
), by = product]

Scenario 2: Pace-Based Forecast (Advanced Row Dependency) #

For products like B, where the forecast depends on the cumulative sales pace relative to the target, we can use shift() to reference the previous row's cumulative sales for sequential updates:

# For pace-based forecasting: calculate target cumulative sales per day
sales_dt[, target_cum := expected_total * (day_number / total_days), by = product]

# Update forecast to align with target pace (uses previous row's cumulative)
sales_dt[, forecast_daily_pace := daily_sales, by = product]
for (i in which(sales_dt$daily_sales == 0)) {
  prev_cum <- sales_dt[i-1, cum_sales]
  target_cum_current <- sales_dt[i, target_cum]
  sales_dt[i, forecast_daily_pace := target_cum_current - prev_cum]
  # Update cumulative sales for subsequent rows
  sales_dt[i:.N, cum_sales := prev_cum + cumsum(forecast_daily_pace), by = product]
}

• Always use by = product to ensure calculations are isolated per product
• shift() is your friend for referencing previous/next rows in sequential calculations
• Use ifelse() or fcase() to handle conditional logic (like distinguishing actual vs forecasted dates)
• Avoid loops for large datasets if possible—use vectorized operations or Reduce() for better performance

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