基于count权重按比例重分配R表格中a组的var1与var2值

相关问题 #

• 基于另一表格调整表格中的数值
• 将多值分配给多组

数据定义 #

我在R中有以下两个数据框：

myt_with_counts <- data.frame(
  name = c("a", "a", "a", "b", "b", "c", "c", "c", "d", "e", "e"),
  var1 = c(120, 80, 100, 20, 30, 15, 25, 35, 40, 50, 60), 
  var2 = c(90, 60, 70, 25, 35, 20, 30, 40, 45, 55, 65),
  count = c(10, 5, 15, 8, 12, 6, 9, 3, 7, 11, 4)
)

rel_table <- data.frame(
    name = c("a", "b", "c"),
    proportion = c(0.5, 0.4, 0.1)
)

需求说明 #

我需要完成以下数据处理：

• 将myt_with_counts中name为"a"的组的var1和var2数值，按照rel_table指定的比例重新分配：50%保留在a组，40%分配给b组，10%分配给c组。
• 分配时必须以count为权重：从a组每行移除的数值，要按该行count占a组总count的比例计算；添加到b/c组每行的数值，要按该行count占对应组总count的比例计算。
• 重分配后，var1和var2的整体总和必须保持不变。

尝试的代码 #

我自己写了一个redistribute函数尝试实现，但遇到了困难，代码如下：

redistribute <- function(x, val.cols, name.col, count.col, prop, from) {
   val.col <- match(val.cols, names(x))
   x.names <- x[[name.col]]
   prop.names <- prop[[1]]
   rows <- match(prop.names, x.names)
   
   from_totals <- colSums(x[x.names == from, val.col, drop=FALSE])
   from_counts <- x[x.names == from, count.col]
   total_from_counts <- sum(from_counts)
   
   for(i in seq_along(prop.names)) {
       group_name <- prop.names[i]
       group_prop <- prop[[2]][i]
       group_rows <- x.names == group_name
       
       if(group_name == from) {
           proportion_to_remove <- 1 - group_prop
           amount_to_remove <- from_totals * proportion_to_remove
           for(j in seq_along(val.col)) {
               removal_per_count <- amount_to_remove[j] / total_from_counts
               x[group_rows, val.col[j]] <- x[group_rows, val.col[j]] - from_counts * removal_per_count
           }
       } else {
           group_counts <- x[group_rows, count.col]
           total_group_counts <- sum(group_counts)
           amount_to_add <- from_totals * group_prop
           for(j in seq_along(val.col)) {
               addition_per_count <- amount_to_add[j] / total_group_counts
               x[group_rows, val.col[j]] <- x[group_rows, val.col[j]] + group_counts * addition_per_count
           }
       }
   }
   x
}

result <- redistribute(myt_with_counts, val.cols=c("var1", "var2"), name.col="name", count.col="count", prop=rel_table, from="a")

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