Get the sum of each species across weeks, then get the proportion of each species count at each week-CodePudding

My data are as follows:

week site dog cat mouse 
1    a    2   50  10
1    b    5   25  10
1    c    3   25  30
2    a    75  1   5
2    b    25  9   4
2    c    0   0   1

I would like a sum of each species across each week for each site.

Given this sum, I would then like to know the proportion of each species at each week. For example, there are a total of 77 dogs at site 'a' across weeks; two dogs (2.5%) in week 1 and 75 (97.4%) dogs in week 2. The output for dog proportion is shown below, but I would like an output for every species.

week site dog cat mouse dog.prop 
1    a    2   50  10    0.025
1    b    5   25  10    0.166
1    c    3   25  30    1
2    a    75  1   5     0.974
2    b    25  9   4     0.833
2    c    0   1   0     0

CodePudding user response：

Using prop.table:

df1 %>% 
  group_by(site) %>% 
  mutate(across(dog:mouse, 
                ~ prop.table (.x),
                .names = "{.col}_prob"))

CodePudding user response：

We can group_by(site) and then use across(). Here we divide .x (the number of species for each site each week) through sum(.x) (the total number of each species per site).

library(dplyr)

dat %>% 
  group_by(site) %>%
  mutate(
    across(-week, 
           ~ .x / sum(.x),
           .names = "{.col}_prob")
  )
#> # A tibble: 6 × 8
#> # Groups:   site [3]
#>    week site    dog   cat mouse dog_prob cat_prob mouse_prob
#>   <dbl> <chr> <dbl> <dbl> <dbl>    <dbl>    <dbl>      <dbl>
#> 1     1 a         2    50    10   0.0260   0.980      0.667 
#> 2     1 b         5    25    10   0.167    0.735      0.714 
#> 3     1 c         3    25    30   1        1          0.968 
#> 4     2 a        75     1     5   0.974    0.0196     0.333 
#> 5     2 b        25     9     4   0.833    0.265      0.286 
#> 6     2 c         0     0     1   0        0          0.0323

^{Created on 2022-09-29 by the reprex package (v0.3.0)}

The data

dat <- tribble(
  ~week, ~site, ~dog, ~cat, ~mouse, 
1,  "a",  2 , 50,  10,
1,  "b",  5 , 25,  10,
1,  "c",  3 , 25,  30,
2,  "a",  75, 1  ,  5,
2,  "b",  25, 9  ,  4,
2,  "c",  0 , 0  ,  1)