Weekly Weights Calculations in R Using dplyr

Question

I have the following data and looking to create the "Final Col" shown below using dplyr in R.

Please note, the "Trees" main category has a value of 0 for week 1, 2017. In this case, I would like the final values for both weeks to be 0.

I would appreciate your ideas.

| Year  | Week    | MainCat|Qty    |Final Col     |
|:----: |:------: |:-----: |:-----:|:------------:|
| 2017  | 1       | Edible |69     |69/(69+12)    |
| 2017  | 2       | Edible |12     |12/(69+12)    |
| 2017  | 1       | Trees  |00     |00            |
| 2017  | 2       | Trees  |12     |00            |
| 2017  | 1       | Flowers|88     |88/(88+47)    |
| 2017  | 2       | Flowers|47     |47/(88+47)    |
| 2018  | 1       | Edible |90     |90/(90+35)    |
| 2018  | 2       | Edible |35     |35/(90+35)    |
| 2018  | 1       | Trees  |32     |32/(32+12)    |
| 2018  | 2       | Trees  |12     |12/(32+12)    |
| 2018  | 1       | Flowers|78     |78/(78+85)    |
| 2018  | 2       | Flowers|85     |85/(78+85)    |

question from:https://stackoverflow.com/questions/65617183/weekly-weights-calculations-in-r-using-dplyr

Answer 1

For this, you can use a combination of grou_by and a regular if ... else clause

library(dplyr)
set.seed(0)

# dummy data
data <- tidyr::expand_grid(year = 2017:2018,
            quarter = 1:2,
            MainCat = LETTERS[1:3]) %>% 
  mutate(Qty = sample(0:15, 3*2*2)) %>% 
  arrange(year, MainCat, quarter)

data %>% 
  group_by(year, MainCat) %>% 
  mutate(finalCol = if(any(Qty == 0)){ 0 } else {Qty / sum(Qty)})
#> # A tibble: 12 x 5
#> # Groups:   year, MainCat [6]
#>     year quarter MainCat   Qty finalCol
#>    <int>   <int> <chr>   <int>    <dbl>
#>  1  2017       1 A          13    0.684
#>  2  2017       2 A           6    0.316
#>  3  2017       1 B           8    0    
#>  4  2017       2 B           0    0    
#>  5  2017       1 C           3    0.75 
#>  6  2017       2 C           1    0.25 
#>  7  2018       1 A          12    0.632
#>  8  2018       2 A           7    0.368
#>  9  2018       1 B          10    0.476
#> 10  2018       2 B          11    0.524
#> 11  2018       1 C           2    0.333
#> 12  2018       2 C           4    0.667