How can I pivot wider and transform my data frame?

Solution 1:

Do a group by and return the count, and the sum of 'Number_Students' and then use pivot_wider with names_from specified as the 'Grade' and the values_from as a vector of columns

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)

-output

# 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

Solution 2:

Here is an alternative approach: Not comparable with the perfect approach of akrun, but it contains some interesting feature how we could get the same result:

library(tidyr)
library(dplyr)

df1 <- df %>% 
  pivot_wider(id_cols = c(School, City, Grade, Type_school),
              names_from = "Grade",
              values_from = "Number_Students",
              values_fn = list(Number_Students = length),
              values_fill = 0,
              names_glue = "N_{Grade}") 

df %>% 
  pivot_wider(id_cols = c(School, City, Grade, Number_Students),
              names_from = Grade,
              values_from = Number_Students,
              values_fn = list(Number_Students = sum),
              names_glue = "Students_{Grade}"
              ) %>% 
  right_join(df1, by=c("School", "City"))

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