按组计算的四舍五入平均值总和与组总数相同

Question

我有这样的数据:

library(dplyr)
                    
Data <- tibble(
      ID = c("Code001", "Code001","Code001","Code002","Code002","Code002","Code002","Code002","Code003","Code003","Code003","Code003"),
      Value = c(107,107,107,346,346,346,346,346,123,123,123,123))

我需要计算每行每组的平均值。但是，该值需要四舍五入(因此没有小数位)并且组总和需要等于 Value 的组总和。

所以像这样的解决方案是行不通的:

  Data %>%
  add_count(ID) %>%
  group_by(ID) %>%
  mutate(Prop_Value_1 = Value/n,
         Prop_Value_2 = round(Value/n))

有没有一种解决方案可以产生这样的输出:

Data %>%
      mutate(Prop_Value = c(35,36,36,69,69,69,69,70,30,31,31,31))

Answer 1

可以使用ceiling 然后使用row_number 到达那里:

Data %>%
  group_by(ID) %>%
  mutate(count = n(),
         ceil_avg = ceiling(Value/count)) %>%
  mutate(sum_ceil_avg = sum(ceil_avg),
         diff_sum = sum_ceil_avg - Value,
         rn = row_number()) %>%
  mutate(new_avg = ifelse(rn <= diff_sum,
                          ceil_avg - 1,
                          ceil_avg))

# A tibble: 12 × 8
# Groups:   ID [3]
   ID      Value count ceil_avg sum_ceil_avg diff_sum    rn new_avg
   <chr>   <dbl> <int>    <dbl>        <dbl>    <dbl> <int>   <dbl>
 1 Code001   107     3       36          108        1     1      35
 2 Code001   107     3       36          108        1     2      36
 3 Code001   107     3       36          108        1     3      36
 4 Code002   346     5       70          350        4     1      69
 5 Code002   346     5       70          350        4     2      69
 6 Code002   346     5       70          350        4     3      69
 7 Code002   346     5       70          350        4     4      69
 8 Code002   346     5       70          350        4     5      70
 9 Code003   123     4       31          124        1     1      30
10 Code003   123     4       31          124        1     2      31
11 Code003   123     4       31          124        1     3      31
12 Code003   123     4       31          124        1     4      31