如何按年变异两个种群的比率

Question

   overseas_domestic_indicator ref_year count
   <chr>                          <dbl> <dbl>
 1 Domestic                        2014 17854
 2 Domestic                        2015 18371
 3 Domestic                        2016 18975
 4 Domestic                        2017 19455
 5 Domestic                        2018 19819
 6 Overseas                        2014  6491
 7 Overseas                        2015  7393
 8 Overseas                        2016  8594
 9 Overseas                        2017  9539
10 Overseas                        2018 10455

这是我的资料。我想要这样的东西：

ref_year Domestic/Overseas
2014    2.75
2015    ...
...     ...

但我不知道怎么用tidyverse做这件事。我试图使用变异，但我不知道如何澄清国内和海外的计数。提前谢谢。

Answer 1

这也应该有效，有多种方法可以做到这一点。

  df %>%
    pivot_wider(overseas_domestic_indicator, 
                names_from = overseas_domestic_indicator, 
                values_from = count) %>%
    mutate(Ratio = Domestic/Overseas)

Answer 2

我们可以用“ref_year”和“summarise”进行分组，方法是将对应于“国内”的“计数”与“海外”的“计数”分开，并在需要时将其重塑为“wide”。

library(dplyr)
library(tidyr)
df1 %>%
   group_by(ref_year) %>%
   summarise(
      `Domestic/Overseas` = count[overseas_domestic_indicator == 'Domestic']/
      count[overseas_domestic_indicator == 'Overseas']) 
# A tibble: 5 x 2
#  ref_year `Domestic/Overseas`
#     <int>               <dbl>
#1     2014                2.75
#2     2015                2.48
#3     2016                2.21
#4     2017                2.04
#5     2018                1.90

或arrange，然后再进行除法

df1 %>%
   arrange(ref_year, overseas_domestic_indicator) %>% 
   group_by(ref_year) %>% 
   summarise( `Domestic/Overseas` = first(count)/last(count))

或者是来自dcast的data.table

library(data.table)
dcast(setDT(df1), ref_year ~ overseas_domestic_indicator)[, 
      `Domestic/Overseas` := Domestic/Overseas][]

数据

df1 <- structure(list(overseas_domestic_indicator = c("Domestic", "Domestic", 
"Domestic", "Domestic", "Domestic", "Overseas", "Overseas", "Overseas", 
"Overseas", "Overseas"), ref_year = c(2014L, 2015L, 2016L, 2017L, 
2018L, 2014L, 2015L, 2016L, 2017L, 2018L), count = c(17854L, 
18371L, 18975L, 19455L, 19819L, 6491L, 7393L, 8594L, 9539L, 10455L
)), class = "data.frame", row.names = c("1", "2", "3", "4", "5", 
"6", "7", "8", "9", "10"))

Answer 3

您可以先获得宽格式的数据，然后再将Domestic除以Overseas。

library(dplyr)

df %>%
  tidyr::pivot_wider(names_from = overseas_domestic_indicator, 
                     values_from = count) %>%
   mutate(ratio = Domestic/Overseas)


#  ref_year Domestic Overseas ratio
#     <int>    <int>    <int> <dbl>
#1     2014    17854     6491  2.75
#2     2015    18371     7393  2.48
#3     2016    18975     8594  2.21
#4     2017    19455     9539  2.04
#5     2018    19819    10455  1.90