Move datatable cells in R

Question

In R I've got the following data table with one line per day. But the hourly observation values are stored in columns. I would like to have one line per timestamp and observation value.

Day          x1   x2      x3    x4
2018-01-31  31a   31b    31c   31d
2018-01-30  30a   30b    30c   30d
2018-01-29  29a   29b    29c   29d

How can I transform the table to the following result?

Day        hour value  
2018-01-31  x1   31a  
2018-01-31  x2   31b   
2018-01-31  x3   31c  
2018-01-31  x4   31d   
2018-01-30  x1   30a
2018-01-30  x2   30b 
2018-01-30  x3   30c
2018-01-30  x4   30d  
2018-01-29  x1   29a
2018-01-29  x2   29b
2018-01-29  x3   29c
2018-01-29  x4   29d

use [`gather`](http://r4ds.had.co.nz/tidy-data.html#gathering) from `tidyr` package — RLesur, Jan 31 '18 at 20:26

denis · Accepted Answer · 2018-01-31T20:47:07.743

with data.table, (reshape will be similar)

library(data.table)
DT <- setDT(read.table(text = "Day          x1   x2      x3    x4
2018-01-31  31a   31b    31c   31d
2018-01-30  30a   30b    30c   30d
2018-01-29  29a   29b    29c   29d", header = T))

melt(DT,measure.vars = patterns("^x"))

           Day variable value
 1: 2018-01-31       x1   31a
 2: 2018-01-30       x1   30a
 3: 2018-01-29       x1   29a
 4: 2018-01-31       x2   31b
 5: 2018-01-30       x2   30b
 6: 2018-01-29       x2   29b
 7: 2018-01-31       x3   31c
 8: 2018-01-30       x3   30c
 9: 2018-01-29       x3   29c
10: 2018-01-31       x4   31d
11: 2018-01-30       x4   30d
12: 2018-01-29       x4   29d

with tidyr

library(tidyr)
DT %>%
gather( variable,value,-Day)

The gather function in tidyr is the cleanest solution. Thanks! — Thies, Jan 31 '18 at 20:57

Move datatable cells in R

1 Answers1