Reshaping data table by dropping column names into a new column

Question

Morning all,

I decided to give R a try with regards to a task I usually perform on Excel, but due to the size of the table, the computer refused to work. Usually, I follow the procedure described here.

In short, I'd like to convert this:

dt <- data.table(name = c("Jack", "Gill", "Tom", "Jerry"), Math = c(55,65,75,85), History = c(50,60,70,80), Geography = c(55,66,77,88))

        Math    History   Geography
Jack    55      50        55
Gill    65      60        66
Tom     75      70        80
Jerry   85      80        88

into this:

Jack   Math       55
Jack   History    50
Jack   Geography  55
Gill   Math       65
Gill   History    60
Gill   Geography  66
Tom    Math       75
Tom    History    70
Tom    Geography  77
Jerry  Math       85
Jerry  History    80
Jerry  Geography  88

Any suggestions greatly appreciated :)

Edit: Duplicate. Thank you, akrun for pointing me in the right direction!

Check the packages `reshape2` or `data.table` and their `melt` function, this is exactly what you need. — Manuel Bickel, Mar 21 '18 at 11:12

Dom · Answer 1 · 2018-03-22T00:40:06.893

library(tidyverse)

dt <- data.frame(name = c("Jack", "Gill", "Tom", "Jerry"), Math = c(55,65,75,85), History = c(50,60,70,80), Geography = c(55,66,77,88))

dt %>% 
  gather(subjects, score, -name) %>% 
  arrange(name)

    name  subjects    score
1   Gill      Math       65
2   Gill   History       60
3   Gill Geography       66
4   Jack      Math       55
5   Jack   History       50
6   Jack Geography       55
7  Jerry      Math       85
8  Jerry   History       80
9  Jerry Geography       88
10   Tom      Math       75
11   Tom   History       70
12   Tom Geography       77

Reshaping data table by dropping column names into a new column

1 Answers1