R code: columns to rows i.e group to individual persons

user_8775690 0
Viewed 48

I am aiming to take certain columns to rows, where the columns share an id, for example students in a class group.

please see here:

  Group Student1 Age1 Grade1 Student2 Age2 Grade2
1     1    Sarah   17      A     John   16      B
2     2      Tom   15      B    Harry   16      C
3     3     Mary   15      C     Jack   18      A

I would like this data so that each row is a student not the group as above:

  Group Student Age Grade
1     1   Sarah  17     A
2     1    John  16     B
3     2     Tom  15     B
4     2   Harry  16     C
5     3    Mary  15     C
6     3    Jack  18     A

I have tried using

newData <- melt(dat, id.vars = c("id"))

but this gives me a list of id and all the other values as a column. Is there a function to get the result above?

Data:

dat <- structure(
    list(
      Group = 1:3,
      Student1 = c("Sarah", "Tom", "Mary"),
      Age1 = c(17L, 15L, 15L),
      Grade1 = c("A", "B", "C"),
      Student2 = c("John", "Harry", "Jack"),
      Age2 = c(16L, 16L, 18L),
      Grade2 = c("B", "C", "A")
    ),
    class = "data.frame",
    row.names = c(NA,-3L)
  )
reshape r
2 Answers
reshape(df, 2:ncol(df), idvar = 'Group', sep='', dir = 'long')
    Group time Student Age Grade
1.1     1    1   Sarah  17     A
2.1     2    1     Tom  15     B
3.1     3    1    Mary  15     C
1.2     1    2    John  16     B
2.2     2    2   Harry  16     C
3.2     3    2    Jack  18     A

tidyr::pivot_longer(df, -Group, names_pattern ='(\\D+)', names_to = '.value')
# A tibble: 6 x 4
  Group Student    Age Grade
  <int> <chr>    <int> <chr>
1     1 " Sarah"    17 " A" 
2     1 " John"     16 " B" 
3     2 " Tom"      15 " B" 
4     2 " Harry"    16 " C" 
5     3 " Mary"     15 " C" 
6     3 " Jack"     18 " A"
user_83802720

Using data.table

library(data.table)
melt(setDT(dat), id.var = 'Group',
    measure = patterns("Student", "Age",  "Grade"),
    value.name = c("Student", "Age", "Grade"))[, variable := NULL][]
   Group Student Age Grade
1:     1   Sarah  17     A
2:     2     Tom  15     B
3:     3    Mary  15     C
4:     1    John  16     B
5:     2   Harry  16     C
6:     3    Jack  18     A
user_37322710
Related