How to mutate a ratio for two populations by year

Reshape your data to a wider format to make calculations easier

library(tidyverse)

# Sample data on a copy/paste friendly format
sample_df <- data.frame(
             stringsAsFactors = FALSE,
  overseas_domestic_indicator = c("Domestic","Domestic","Domestic","Domestic",
                                  "Domestic","Overseas","Overseas","Overseas",
                                  "Overseas","Overseas"),
                     ref_year = c(2014,
                                  2015,2016,2017,2018,2014,2015,2016,2017,
                                  2018),
                        count = c(17854,
                                  18371,18975,19455,19819,6491,7393,8594,
                                  9539,10455)
)

sample_df %>% 
    spread(overseas_domestic_indicator, count) %>% 
    mutate(rate = Domestic/Overseas)
#>   ref_year Domestic Overseas     rate
#> 1     2014    17854     6491 2.750578
#> 2     2015    18371     7393 2.484918
#> 3     2016    18975     8594 2.207936
#> 4     2017    19455     9539 2.039522
#> 5     2018    19819    10455 1.895648

Created on 2020-05-21 by the reprex package (v0.3.0)

Note: For future posts, please provide a proper REPRoducible EXample (reprex) illustrating your issue.

1 Like