如何使用dplyr生成包含份额计算的(透视)表

siv3szwd  于 2023-05-20  发布在  其他
关注(0)|答案(1)|浏览(76)

我想从数据集生成一个表。在要显示的变量中,两个是字符串,另外三个是数值。数值变量包含绝对数字信息。在表中显示这些绝对数字(从不同大学开始的学生总数)的旁边,我想显示妇女所占的百分比。该数据集将妇女总数和妇女在总人数中所占的比例作为单独的变量(以栏表示)。
该表按x1和x2分组,应包含总额(x3 a、x4 a、x5 a)和女生所占百分比(x3 b、x4 b、x5 b)。
由于数据集包含变量中的总和,我想我只需要在某种分组函数之后的某个地方将相应的变量添加到代码中,但仍然需要计算份额,然后打印到新的变量/列中,我似乎无法为整个表找到代码。我知道应该有一些group_by,summarise和mutate函数,并且字符串必须被分解才能使代码运行,但我还没有找到解决方案。
任何帮助将不胜感激!
这就是它应该看起来的样子:

library(dplyr)
df=data.frame(
  x1=c("chr","chr","chr","chr","chr","chr","chr","chr","chr","chr"),
  x2=c("chr","chr","chr","chr","chr","chr","chr","chr","chr","chr"),
  x3=c(1,0,0,NA,0,1,1,NA,0,1), #x3=year of university start
  #x3a=containing total number (of students starting university)
  #x3b=containing percentage of female students, calculated on x3a
  x4=c(1,1,NA,1,1,0,NA,NA,0,1),#x4=year of university start
  #x4a
  #x4b
  x5=c(1,0,NA,1,0,0,NA,0,0,1)) #x5=year of university start
  #x5a
  #x5b

> df

我尝试了summarise和mutate函数,但无法获得所描述的具有正确计算份额的表。

fv2wmkja

fv2wmkja1#

我不确定我是否正确理解了这个问题,因为你的数据示例过于通用。我希望我的回答与你想要的有关,并能对你有用!
首先,我回顾了示例数据集,考虑到其中一个字符串表示大学的名称,另一个是引用女性的定性变量。下面是dput

df <- structure(list(W_or_not = c("chr", "W", "chr", "W", "chr", "W", 
"chr", "chr", "chr", "chr"), Univ = c("univ1", "univ1", "univ1", 
"univ1", "univ1", "univ2", "univ2", "univ2", "univ2", "univ2"
), year1 = c(1, 0, 0, NA, 0, 1, 1, NA, 0, 1), year2 = c(1, 1, 
NA, 1, 1, 0, NA, NA, 0, 1), year3 = c(1, 0, NA, 1, 0, 0, NA, 
0, 0, 1)), class = "data.frame", row.names = c(NA, -10L))

head(df)

   W_or_not  Univ year1 year2 year3
1       chr univ1     1     1     1
2         W univ1     0     1     0
3       chr univ1     0    NA    NA
4         W univ1    NA     1     1
5       chr univ1     0     1     0
6         W univ2     1     0     0
7       chr univ2     1    NA    NA
8       chr univ2    NA    NA     0
9       chr univ2     0     0     0
10      chr univ2     1     1     1

我建议你先计算每所大学的学生总数(第一个表),然后计算每所大学的女生总数(第二个表)。最后,您可以通过计算每所大学的女性百分比来更新第一个表,如下所示:

### First table
df_sum_univ <- df %>%
  group_by(Univ) %>%
  summarise_if(is.numeric, .funs = ~ sum(.x, na.rm = TRUE)) %>%
  rename_if(is.numeric, ~paste0('sum_', .))

### Second one
df_sum_univ_F <- df %>%
  filter(W_or_not == 'W') %>%
  group_by(Univ) %>%
  summarise_if(is.numeric, .funs = ~ sum(.x, na.rm = TRUE)) %>%
  rename_if(is.numeric, ~paste0('perc_w_', .))

### Updating the first one in a new table
df_final <- df_sum_univ %>%
  bind_cols(df_sum_univ_F[,2:4]/df_sum_univ[,2:4]*100)

head(df_final)
# A tibble: 2 × 7
  Univ  sum_year1 sum_year2 sum_year3 perc_w_year1 perc_w_year2 perc_w_year3
  <chr>     <dbl>     <dbl>     <dbl>        <dbl>        <dbl>        <dbl>
1 univ1         1         4         2          0             50           50
2 univ2         3         1         1         33.3            0            0

相关问题