基于dplyr优化大数据框按组每行≥当前值的求和效率

Hey there! Dealing with 2 million rows means we absolutely need to ditch row-wise loops—they’re way too slow for that scale, since they force R to process each row one at a time instead of using vectorized operations. Let’s break down two efficient solutions: one with dplyr (since you mentioned trying that) and another with data.table (which is optimized for large datasets).

First: Fixing the dplyr Approach #

The key here is to avoid per-row filtering by leveraging grouping, aggregation, and cumulative sums. Here’s how to do it:

library(dplyr)

# Your sample data
sample_df <- data.frame(
  group_id = c(1,1,1,1,2,2,2,2),
  value = c(10,12,14,12,8,8,21,10)
)

result_df <- sample_df %>%
  # Step 1: Calculate total sum for each (group_id, value) pair
  group_by(group_id, value) %>%
  summarise(sum_val = sum(value), .groups = "drop_last") %>%
  # Step 2: Sort values descending within each group, then compute cumulative sum
  arrange(group_id, desc(value)) %>%
  mutate(cum_total = cumsum(sum_val)) %>%
  # Step 3: Join back to the original data to map the cumulative sum to every row
  right_join(sample_df, by = c("group_id", "value")) %>%
  # Step 4: Restore the original row order (optional but useful if you need it)
  arrange(match(row_number(), as.integer(rownames(sample_df)))) %>%
  # Keep only the columns you need
  select(group_id, value, cum_total)

Let’s walk through what this does:

• We first group by both group_id and value to calculate the total sum of all identical values in each group.
• Sorting values in descending order lets us use cumsum() to accumulate the total of all values greater than or equal to the current value.
• Finally, we join back to the original data frame so every row gets its corresponding cumulative total, and restore the original row order if needed.

Even Faster: Using data.table #

For 2 million rows, data.table is often faster and more memory-efficient than dplyr. Here’s a concise solution:

library(data.table)

# Convert to data.table
setDT(sample_df)

# Calculate the cumulative sum of values >= current row's value, per group
sample_df[, sum_val := sum(value), by = .(group_id, value)]
sample_df[, cum_total := cumsum(sum_val), by = group_id, order = -value]

This works because:

• We first compute the total sum for each unique (group_id, value) pair.
• Then, within each group, we sort values in descending order and compute the cumulative sum of those totals. Since identical values share the same sum_val, their cum_total will also be identical—exactly what we need for the "sum of all values >= current row" requirement.

Why This Beats Loops #

Row-wise loops have a time complexity of roughly O(n²) because you’re filtering the entire group for every single row. These vectorized approaches use O(n log n) time (mostly from sorting) which is drastically faster for large datasets like yours.

内容的提问来源于stack exchange，提问作者John K.