R Pivot Longer With Multiple Columns-CodePudding

HAVE = data.frame(  COURSE  =c( 1, 1, 1, 2, 2, 2, 3, 3, 3   ),
    STUDENT =c( 'A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C' ),
    FISH    =c( 4, 8, 9, 1, 7, 1, 10, 10, 10    ),
    CAT =c( 9, 8, 10, 7, 1, 2, 8, 0, 2  ),
    FOX =c( 7, NA, 9, 0, NA, 10, 5, NA, 10  ),
    BUNNIE  =c( 6, NA, 0, 5, NA, 6, 4, NA, 1    ),
    RABBIT  =c( 2, NA, 0, 6, NA, 8, 3, NA, 0    ))


WANT = data.frame(  COURSE  =c( 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3 ),
    TEST    =c( 'FISH', 'CAT', 'FOX', 'BUNNIE', 'RABBIT', 'FISH', 'CAT', 'FOX', 'BUNNIE', 'RABBIT', 'FISH', 'CAT', 'FOX', 'BUNNIE', 'RABBIT'    ),
    A   =c( 4, 9, 7, 6, 2, 1, 7, 0, 5, 6, 10, 8, 5, 4, 3    ),
    B   =c( 8, 8, NA, NA, NA, 7, 1, NA, NA, NA, 10, 0, NA, NA, NA   ),
    C   =c( 9, 10, 9, 0, 0, 1, 2, 10, 6, 8, 10, 2, 10, 1, 0 ))

I try:

WANT = HAVE %>% pivot_longer(FISH:RABBIT, names_to = "TEST", values_to = A:C) with no success

CodePudding user response：

Basically you want to gather the animals names into a single column named "TEST", and then expand the student names in several columns. So you need two steps:

pivot_longer() where you gather the animals names
pivot_wider() where you expand the student names

library(tidyr)

HAVE = data.frame(  COURSE  =c( 1, 1, 1, 2, 2, 2, 3, 3, 3   ),
                    STUDENT =c( 'A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C' ),
                    FISH    =c( 4, 8, 9, 1, 7, 1, 10, 10, 10    ),
                    CAT =c( 9, 8, 10, 7, 1, 2, 8, 0, 2  ),
                    FOX =c( 7, NA, 9, 0, NA, 10, 5, NA, 10  ),
                    BUNNIE  =c( 6, NA, 0, 5, NA, 6, 4, NA, 1    ),
                    RABBIT  =c( 2, NA, 0, 6, NA, 8, 3, NA, 0    ))

out <- HAVE |> 
  pivot_longer(
    cols = c("FISH", "CAT", "FOX", "BUNNIE", "RABBIT"),
    names_to = "TEST"
  ) |> 
  pivot_wider(
    names_from = "STUDENT",
    values_from = "value"
  )

out
#> # A tibble: 15 × 5
#>    COURSE TEST       A     B     C
#>     <dbl> <chr>  <dbl> <dbl> <dbl>
#>  1      1 FISH       4     8     9
#>  2      1 CAT        9     8    10
#>  3      1 FOX        7    NA     9
#>  4      1 BUNNIE     6    NA     0
#>  5      1 RABBIT     2    NA     0
#>  6      2 FISH       1     7     1
#>  7      2 CAT        7     1     2
#>  8      2 FOX        0    NA    10
#>  9      2 BUNNIE     5    NA     6
#> 10      2 RABBIT     6    NA     8
#> 11      3 FISH      10    10    10
#> 12      3 CAT        8     0     2
#> 13      3 FOX        5    NA    10
#> 14      3 BUNNIE     4    NA     1
#> 15      3 RABBIT     3    NA     0

Check that the result is what is expected:

WANT = data.frame(  COURSE  =c( 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3 ),
                    TEST    =c( 'FISH', 'CAT', 'FOX', 'BUNNIE', 'RABBIT', 'FISH', 'CAT', 'FOX', 'BUNNIE', 'RABBIT', 'FISH', 'CAT', 'FOX', 'BUNNIE', 'RABBIT'    ),
                    A   =c( 4, 9, 7, 6, 2, 1, 7, 0, 5, 6, 10, 8, 5, 4, 3    ),
                    B   =c( 8, 8, NA, NA, NA, 7, 1, NA, NA, NA, 10, 0, NA, NA, NA   ),
                    C   =c( 9, 10, 9, 0, 0, 1, 2, 10, 6, 8, 10, 2, 10, 1, 0 ))

identical(out, as_tibble(WANT))
#> [1] TRUE

^{Created on 2022-10-05 with reprex v2.0.2}