按cnpj分组生成条件新变量并优化dplyr代码执行效率

Hey there! Let's look at how to make your code more efficient for this task—extracting the n_act_contracts value where time2 == 0 for each cnpj group, and filling that value across all rows of the group (or using NA if there's no such row).

The Problem with Your Current Approach #

Your existing code works, but it takes three separate steps in mutate(): using a placeholder value, computing a max, then converting the placeholder back to NA. This adds unnecessary computation, especially with large datasets, since it has to process each row multiple times and run a max() operation over the entire group.

More Efficient Solutions #

Here are two streamlined approaches that cut down on redundant operations and run faster, especially with big data:

1. Using dplyr (Simplified)

We can directly extract the target value for each group in a single mutate() step. Since each group should have at most one row where time2 == 0 (based on your sample data), we can index into the n_act_contracts vector for that condition and take the first element (which will be NA if there are no matching rows):

library(dplyr)

data <- data %>%
  group_by(cnpj) %>%
  mutate(zero_n_act_contracts = n_act_contracts[time2 == 0][1]) %>%
  ungroup()

If you want to explicitly handle cases where a group might have multiple time2 == 0 rows (and ensure you get NA when there are none), use first() with a default:

data <- data %>%
  group_by(cnpj) %>%
  mutate(zero_n_act_contracts = first(n_act_contracts[time2 == 0], default = NA)) %>%
  ungroup()

2. Using data.table (For Maximum Speed)

If you're working with very large datasets (millions of rows), data.table will outperform dplyr significantly. Here's how to implement the same logic:

library(data.table)

setDT(data)
data[, zero_n_act_contracts := first(n_act_contracts[time2 == 0], default = NA), by = cnpj]

Why These Are Faster #

• Both approaches avoid the placeholder value (-1000) and the subsequent max() and re-coding steps. They directly target the value you need in one pass.
• data.table uses optimized C-based operations for grouping and column updates, making it ideal for large-scale data processing.

Verification #

Running either of these will produce exactly the output you're looking for:

• For cnpj = 12 and cnpj = 13, the value from time2 == 0 is filled across all rows of the group.
• For cnpj = 14 (no time2 == 0 row) and cnpj = 15 (all time2 are NA), zero_n_act_contracts is NA for all rows.

内容的提问来源于stack exchange，提问作者João Mourão