Gather multiple columns into one key and two values

user_13933790 0
Viewed 225

I need to gather multiple columns into one key and two values. I encountered a lot of similar questions here, but i cannot reproduce the answers since they were very specific and not well explained imho. If there is one, which answers my question, i´m happy with a link.

Example df:

df <- data.frame(semester=rep(1:6),
                 Anna_try1=c(1,2,2,1,4,2),
                 Lena_try1=c(3,4,2,3,2,1),
                 Anna_try2=c(2,3,1,2,1,4),
                 Lena_try2=c(2,4,2,1,5,2))

which looks like this:

enter image description here

And i need something like this:

enter image description here

r
5 Answers

If you had better column names you could easily use reshape.

But we can fix this first.

names(df)[-1] <- sapply(strsplit(names(df)[-1], "_"), function(x) 
  paste(rev(x), collapse="."))
names(df)
# [1] "semester"  "try1.Anna" "try1.Lena" "try2.Anna" "try2.Lena"

df <- reshape(df, timevar="name", varying=2:5, direction="long")
df
#        semester name try1 try2 id
# 1.Anna        1 Anna    1    2  1
# 2.Anna        2 Anna    2    3  2
# 3.Anna        3 Anna    2    1  3
# 4.Anna        4 Anna    1    2  4
# 5.Anna        5 Anna    4    1  5
# 6.Anna        6 Anna    2    4  6
# 1.Lena        1 Lena    3    2  1
# 2.Lena        2 Lena    4    4  2
# 3.Lena        3 Lena    2    2  3
# 4.Lena        4 Lena    3    1  4
# 5.Lena        5 Lena    2    5  5
# 6.Lena        6 Lena    1    2  6
user_65740380

You can use tidyr::pivot_longer

tidyr::pivot_longer(df, 
                    cols = -semester, 
                    names_to = c('name', '.value'),
                    names_sep = '_')

#   semester name   try1  try2
#      <int> <chr> <dbl> <dbl>
# 1        1 Anna      1     2
# 2        1 Lena      3     2
# 3        2 Anna      2     3
# 4        2 Lena      4     4
# 5        3 Anna      2     1
# 6        3 Lena      2     2
# 7        4 Anna      1     2
# 8        4 Lena      3     1
# 9        5 Anna      4     1
#10        5 Lena      2     5
#11        6 Anna      2     4
#12        6 Lena      1     2
user_39629140

Try reshaping to long, separate the variables into desired new values and then reshape to wide. Here the code using tidyverse functions:

library(tidyverse)
#Code
df <- df %>% pivot_longer(-semester) %>%
  separate(name,c('name','try'),sep = '_') %>%
  mutate(try=paste0('grade_',try)) %>%
  pivot_wider(names_from = try,values_from=value) %>%
  arrange(name,semester)

Output:

# A tibble: 12 x 4
   semester name  grade_try1 grade_try2
      <int> <chr>      <dbl>      <dbl>
 1        1 Anna           1          2
 2        2 Anna           2          3
 3        3 Anna           2          1
 4        4 Anna           1          2
 5        5 Anna           4          1
 6        6 Anna           2          4
 7        1 Lena           3          2
 8        2 Lena           4          4
 9        3 Lena           2          2
10        4 Lena           3          1
11        5 Lena           2          5
12        6 Lena           1          2
user_20808480

This is not the most elegant solution but it gets the job done:

library(data.table)
library(stringr)

df1 <- setDT(df[, -grep("try1", colnames(df))])
df2 <- setDT(df[, -grep("try2", colnames(df))])

melted1 <- melt(df1, id.vars=c("semester"), variable.name = "name", value.name = "grade_try2")
melted2 <- melt(df2, id.vars=c("semester"), variable.name = "name", value.name = "grade_try1")

melted1$name <- str_sub(melted1$name, -str_length(melted1$name), -6)
melted2$name <- str_sub(melted2$name, -str_length(melted2$name), -6)

final <- melted2[melted1, on = .(semester = semester, name = name)]
user_117475750

We can use melt from data.table with patterns

library(data.table)
melt(setDT(df), measure = patterns('try1', 'try2'),
     value.names = c('grade_try1', 'grade_try2'))
user_37322710
Related