r - 我怎样才能更广泛地旋转并转换我的数据框？

Question

我有一个这样的数据框:

tibble(
  School = c(1, 1, 2, 3, 3, 4),
  City = c("A","A", "B", "C", "C", "B"),
  Grade = c("7th", "7th", "7th", "6th", "8th", "8th"),
  Number_Students = c(20, 23, 25, 21, 28, 34),
  Type_school = c("public", "public", "private", "public", "public", "private")
  )

<表类="s-表"><头>身份证学校城市年级Number_StudentsType_school<正文>11一个第七届20公开21一个第七届23公开32B第七届25私有(private)43C第六名21公开53C第八名28公开64B第八名34私有(private)

分析单位是教室，但我想把它变成一个数据框，其中分析单位是学校，但要进行一些计算。像这样:

tibble(
  School = c(1, 2, 3, 4),
  City = c("A", "B", "C", "B"),
  N_6th = c(0, 0, 1, 0), # here is the number of grade 6h classrooms in each school 
  N_7th = c(2,1,0,0),
  N_8th = c(0,0,1,1),
  Students_6th = c(0, 0, 25, 0), # here is the number of students in grade 6th from each school (the sum of all 7th grade classrooms from each school)
  Students_7th = c(43, 25, 0, 0), 
  Students_8th = c(0, 0, 28, 34),
  Type_school = c("public", "private", "public", "private")
)

<表类="s-表"><头>学校城市第N_6<日>第N_7日 <日>第N_8日 Students_6thStudents_7thStudents_8thType_school<正文>1一个0200430公开2B0100250私有(private)3C10125028公开4B0010034私有(private)

我正在尝试 pivot_wider()，但这不足以满足我的需求。我需要求和每所学校同年级的教室数量和每所学校同年级的学生人数。

Answer 1

进行分组并返回计数，以及“Number_Students”的 sum，然后使用 pivot_wider 并将 names_from 指定为 ' Grade' 和 values_from 作为列向量

library(dplyr)
library(tidyr)
df1 %>%
  group_by(School, City, Grade, Type_school) %>%
  summarise(N = n(), Students = sum(Number_Students), .groups = 'drop') %>%
  pivot_wider(names_from = Grade, values_from = c(N, Students), values_fill = 0)

-输出

# A tibble: 4 × 9
  School City  Type_school N_7th N_6th N_8th Students_7th Students_6th Students_8th
   <dbl> <chr> <chr>       <int> <int> <int>        <dbl>        <dbl>        <dbl>
1      1 A     public          2     0     0           43            0            0
2      2 B     private         1     0     0           25            0            0
3      3 C     public          0     1     1            0           21           28
4      4 B     private         0     0     1            0            0           34