Rounded averages by group that sum to the same as the group total

November 22, 2022

I have data that looks like this:

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))

I need to work out the average value per group per row. However, the value needs to be rounded (so no decimal places) and the group sum needs to equal the group sum of Value.

So solutions like this won’t work:

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

Is there a solution that can produce an output like this:

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

>Solution :

Can use ceiling and then row_number to get there:

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