不修改原data.table，在新表中实现分组负数值计数

Great question! When dealing with multiple grouping columns and needing to compute aggregated stats without altering your original data.table, we can use a modular, scalable approach that leverages data.table's efficient grouping capabilities. Here's how to do it cleanly:

Step 1: Define Your Columns First #

Start by explicitly listing which columns are numeric (to count negatives) and which are grouping columns. This makes the code easy to adjust later if your dataset changes:

set.seed(43)
dt <- data.table(
 a = rnorm(10),
 b = rnorm(10),
 c = rnorm(10),
 d = rnorm(10),
 e = sample(c("x","y"),10,replace = T),
 f=sample(c("t","s"),10,replace = T)
)

# Define columns to work with
num_cols <- c("a", "b", "c", "d")  # Columns to count negatives in
group_cols <- c("e", "f")          # Columns to group by

Step 2: Create a Reusable Counting Function #

Write a helper function that takes a single grouping column name, computes the negative count for all numeric columns, and renames the result columns to match your desired format. This avoids repeating code for each group:

count_negatives <- function(group_col) {
  # Calculate negative counts per group
  group_stats <- dt[, lapply(.SD, function(x) sum(x < 0, na.rm = TRUE)),
                    by = group_col,
                    .SDcols = num_cols]
  
  # Rename columns to your requested format (neg_{col}_count)
  setnames(group_stats, num_cols, paste0("neg_", num_cols, "_count"))
  
  return(group_stats)
}

• .SDcols restricts computation to only your numeric columns, making it faster for large datasets.
• na.rm = TRUE handles any potential NA values in your numeric columns (adjust this if you want NA to propagate instead).

Step 3: Batch Process All Group Columns #

Use lapply to run the function on all your grouping columns, then combine the results (or print them separately if you prefer the exact format in your example):

# Option 1: Combine into a single table (with NA filling for missing group columns)
result_list <- lapply(group_cols, count_negatives)
final_combined <- rbindlist(result_list, use.names = TRUE, fill = TRUE)
print(final_combined)

# Option 2: Print each group's results separately (matches your example output exactly)
cat("Results grouped by e:\n")
print(count_negatives("e"))
cat("\nResults grouped by f:\n")
print(count_negatives("f"))

Why This Works #

• No modification to original data: All computations create new tables, so your dt stays untouched.
• Scalable: Add more grouping columns to group_cols or numeric columns to num_cols without rewriting core logic.
• Efficient: Uses data.table's optimized grouping and .SD operations, which are far faster than base R for large datasets.

Example Output (Option 2) #

Results grouped by e:
   e neg_a_count neg_b_count neg_c_count neg_d_count
1: x           6           3           5           3
2: y           2           1           3           0

Results grouped by f:
   f neg_a_count neg_b_count neg_c_count neg_d_count
1: s           4           2           3           1
2: t           4           2           5           2

(Note: The y group's neg_d_count is 0 with your seed, not NA—if you intentionally want NA for groups with no negatives, you can adjust the function to replace 0 with NA using ifelse.)

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