如何在data.table的dcast中以列维度占比作为聚合函数？

Got it, let's tackle this problem step by step. You're already using dcast() to generate cross-tab counts, and now you want to convert those counts into column-wise proportions—where each cell’s value is the count divided by the total count of its corresponding var2 column. Here are two clean, efficient approaches using data.table:

Approach 1: Compute Counts First, Then Normalize #

This method starts with the count table you already generate, then scales each column by its total:

1. First, create your count table as usual:

   library(data.table)
   # Example sample data
   my_data <- data.table(
     var1 = sample(c("A", "B", "C"), 100, replace = TRUE),
     var2 = sample(c("X", "Y", "Z"), 100, replace = TRUE)
   )
   
   # Generate the count cross-tab
   count_table <- dcast(my_data, var1 ~ var2, length)
   

2. Calculate the total for each var2 column, then normalize every value in the column by its total:

   # Get column totals (exclude the var1 column)
   col_totals <- colSums(count_table[, -"var1"])
   
   # Create the proportion table: keep var1, normalize all other columns
   prop_table <- count_table[, 
     c("var1", lapply(.SD, function(x) round(x / col_totals[.SDcol], 3))),
     .SDcols = !"var1"
   ]
   

   The round() function is optional—use it to keep proportions to a readable number of decimal places (here, 3).

Approach 2: Pre-Calculate Proportions in Raw Data, Then Pivot #

If you prefer to handle the proportion logic before pivoting, you can compute the column totals first, merge them back to the raw data, calculate proportions per group, then pivot:

1. Calculate the total count for each var2 category:

   var2_totals <- my_data[, .(col_total = .N), by = var2]
   

2. Merge these totals back to your original data, then compute the proportion for each (var1, var2) group:

   my_data_merged <- merge(my_data, var2_totals, by = "var2")
   my_data_merged[, prop := .N / col_total, by = .(var1, var2)]
   

3. Pivot the data to get your proportion table. Since each (var1, var2) group has the same proportion value, use first() (or unique()) as the aggregation function:

   prop_table <- dcast(my_data_merged, var1 ~ var2, fun.aggregate = first, value.var = "prop")
   

Both methods will give you the column-wise proportion table you need. Approach 1 is usually faster for large datasets since it minimizes the number of operations on the raw data.

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