R Reshape and Select Max-CodePudding

HAVE = data.frame("WEEK"=c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2),
"STUDENT"=c(1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3),
"CLASS"=c('A', 'A', 'B', 'B', 'C', 'C', 'H', 'A', 'A', 'B', 'B', 'C', 'C', 'H', 'A', 'A', 'B', 'B', 'C', 'C', 'H', 'A', 'A', 'B', 'B', 'C', 'C', 'H', 'A', 'A', 'B', 'B', 'C', 'C', 'H', 'A', 'A', 'B', 'B', 'C', 'C', 'H'),
"TEST"=c(1, 2, 1, 2, 1, 2, 1, 1, 2, 1, 2, 1, 2, 1, 1, 2, 1, 2, 1, 2, 1, 1, 2, 1, 2, 1, 2, 1, 1, 2, 1, 2, 1, 2, 1, 1, 2, 1, 2, 1, 2, 1),
"SCORE"=c(93, 97, 72, 68, 93, 51, 19, 88, 56, 53, 79, 69, 90, 61, 74, 50, 76, 97, 55, 63, 63, 59, 68, 77, 80, 52, 94, 74, 64, 74, 92, 98, 89, 84, 54, 51, 82, 86, 51, 90, 72, 86))

WANT = data.frame("WEEK"=c(1,1,1,2,2,2),
"STUDENT"=c(1,2,3,1,2,3),
"CLASS"=c('A','A','B','B','B','C'),
"H"=c(19,61,63,74,54,86),
"TEST1"=c(93,88,76,77,92,90),
"TEST2"=c(97,56,97,80,98,72))

I wish to group by WEEK and STUDENT and then for each combination of WEEK and STUDENT find the CLASS when SCORE equals to maximum(SCORE) where TEST equals to one. Then I wish to find the corresponding SCORE for TEST equals to 2 using that same CLASS. I wish to transform this into the data WANT from the data HAVE. And ALSO add the COLUMN H where it is just equals to the SCORE when CLASS equals to H

CodePudding user response：

We can reshape to 'wide' with pivot_wider, then grouped by 'WEEK', 'STUDENT', create the 'H' column with 'TEST1' values were 'CLASS' is "H" and then slice the max row for 'TEST1'

library(dplyr)
library(tidyr)
HAVE %>% 
  pivot_wider(names_from = TEST, values_from = SCORE, 
      names_glue = "TEST{TEST}") %>% 
  group_by(WEEK, STUDENT) %>%
  mutate(H = TEST1[CLASS == "H"], .before = 'TEST1') %>%
  slice_max(n = 1, order_by = TEST1, with_ties = FALSE) %>%
  ungroup

-output

# A tibble: 6 × 6
   WEEK STUDENT CLASS     H TEST1 TEST2
  <dbl>   <dbl> <chr> <dbl> <dbl> <dbl>
1     1       1 A        19    93    97
2     1       2 A        61    88    56
3     1       3 B        63    76    97
4     2       1 B        74    77    80
5     2       2 B        54    92    98
6     2       3 C        86    90    72

-checking with 'WANT'

> WANT
  WEEK STUDENT CLASS  H TEST1 TEST2
1    1       1     A 19    93    97
2    1       2     A 61    88    56
3    1       3     B 63    76    97
4    2       1     B 74    77    80
5    2       2     B 54    92    98
6    2       3     C 86    90    72