如何高效补全缺失日期的无购买数据并计算累计消费总额？

Hey there! Let's work through this problem to get you that daily cumulative spending data efficiently. I see you're currently using a full user-date table merge approach, and we can refine that to be more performant—especially if you're dealing with larger datasets.

First, let's set up our example data properly #

I'll expand your sample data to include order amounts (since we need something to sum) and clean up the date formatting:

user <- c("Tom", "Tom", "Jim", "Jim")
order_time <- as.Date(c("2018-01-01", "2018-01-04", "2018-01-02", "2018-01-05"))
amount <- c(100, 200, 150, 50) # Adding sample order amounts for calculation
df <- data.frame(user, order_time, amount)

Method 1: Optimized Full User-Date Merge (Your Original Approach, Tweaked) #

Instead of generating a massive 2010-2011 table upfront (which wastes memory if most users don't have orders in that range), we can generate a date grid that only covers the actual date range present in your order data:

# Get unique users and the min/max dates across all orders
all_users <- unique(df$user)
date_range <- seq(min(df$order_time), max(df$order_time), by = "day")

# Create the full user-date grid
full_grid <- expand.grid(user = all_users, order_time = date_range, stringsAsFactors = FALSE)

# Merge with original data and fill missing amounts with 0 (no purchase = $0 spend)
merged_data <- merge(full_grid, df, by = c("user", "order_time"), all.x = TRUE)
merged_data$amount[is.na(merged_data$amount)] <- 0

# Calculate cumulative spending per user
merged_data <- merged_data %>%
  dplyr::arrange(user, order_time) %>%
  dplyr::group_by(user) %>%
  dplyr::mutate(cumulative_spend = cumsum(amount)) %>%
  dplyr::ungroup()

Method 2: Grouped Date Completion (More Efficient for Large Data) #

Using tidyr::complete lets us handle each user's date range individually, avoiding the need for a huge full grid. This is way faster and uses less memory when you have many users:

library(dplyr)
library(tidyr)

cumulative_spend_data <- df %>%
  # Group by each user to handle their unique date range
  group_by(user) %>%
  # Fill in all days between the user's first and last order
  complete(order_time = seq(min(order_time), max(order_time), by = "day")) %>%
  # Replace missing order amounts with 0
  replace_na(list(amount = 0)) %>%
  # Calculate running total of spending
  mutate(cumulative_spend = cumsum(amount)) %>%
  ungroup()

What the Result Looks Like for Tom #

After running either method, Tom's daily cumulative data will look like this:

Note on Full 2010-2011 Range #

If you really need to include every day from 2010-01-01 to 2011-12-31 (even for users with no orders), just replace the seq() argument in either method to use that fixed date range instead of the min/max from your data. Just be aware this will create a much larger dataset!

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