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

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

CodePudding user response：

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

CodePudding user response：

A first solution is to use integer division:

Data %>% 
  group_by(ID) %>% 
  mutate(Prop_Value = ifelse(row_number() <= Value %% n(), Value %/% n()   1, Value %/% n()))

# A tibble: 12 × 3
# Groups:   ID [3]
   ID      Value Prop_Value
   <chr>   <dbl>      <dbl>
 1 Code001   107         36
 2 Code001   107         36
 3 Code001   107         35
 4 Code002   346         70
 5 Code002   346         69
 6 Code002   346         69
 7 Code002   346         69
 8 Code002   346         69
 9 Code003   123         31
10 Code003   123         31
11 Code003   123         31
12 Code003   123         30