reshape2 melt function on timestamps gives NAs

Question

I’m trying to melt a data frame with reshape2 melt function, in order to plot my dataset with ggplot2. However, with my reference column, containing a timestamp, it gives me NA after first iteration (timestamps are correctly displayed once each, and then I have these NAs).

Here is my dataset:

TIMESTAMP,RECNUM,Tair,Tground
2015-01-06 16:27,1,5.0,7.0
2015-01-06 16:28,2,6.0,7.0
2015-01-06 16:29,3,6.0,7.0
2015-01-06 16:30,4,6.5,7.0
2015-01-06 16:31,5,6.8,7.1
2015-01-06 16:32,6,6.8,7.1
2015-01-06 16:33,7,6.8,7.12
2015-01-06 16:34,8,7.1,7.1
2015-01-06 16:35,9,7.15,7.09
2015-01-06 16:36,10,7.18,7.1
2015-01-06 16:37,11,7.3,7.1

My R code:

library(ggplot2)
library(reshape2)
datafile <- file.choose()
dat <- read.csv(datafile)
dat$TIMESTAMP <- as.POSIXlt(strptime(dat$TIMESTAMP,"%Y-%m-%d %H:%M"))
meltedData = melt(dat, id.vars=c("RECNUM", "TIMESTAMP"), variable.name="Measure")

And the resulting meltedData variable:

> meltedData
   RECNUM           TIMESTAMP Measure value
1       1 2015-01-06 16:27:00    Tair  5.00
2       2 2015-01-06 16:28:00    Tair  6.00
3       3 2015-01-06 16:29:00    Tair  6.00
4       4 2015-01-06 16:30:00    Tair  6.50
5       5 2015-01-06 16:31:00    Tair  6.80
6       6 2015-01-06 16:32:00    Tair  6.80
7       7 2015-01-06 16:33:00    Tair  6.80
8       8 2015-01-06 16:34:00    Tair  7.10
9       9 2015-01-06 16:35:00    Tair  7.15
10     10 2015-01-06 16:36:00    Tair  7.18
11     11 2015-01-06 16:37:00    Tair  7.30
12      1                <NA> Tground  7.00
13      2                <NA> Tground  7.00
14      3                <NA> Tground  7.00
15      4                <NA> Tground  7.00
16      5                <NA> Tground  7.10
17      6                <NA> Tground  7.10
18      7                <NA> Tground  7.12
19      8                <NA> Tground  7.10
20      9                <NA> Tground  7.09
21     10                <NA> Tground  7.10
22     11                <NA> Tground  7.10

What am I doing wrong ? Is there a way to have proper timestamps all table long ?

Thanks,

J.

Edit This question is different from this one, as my question is not oriented on how to use reshape2 to change frow wide to long, but on why do reshape2::melt gives me NAs on timestamps.

@nicola I don't see where is the comma missing. In the dataset? — Jerry Magnin, Jan 19 '17 at 08:23
The problem arises for `as.POSIXlt` which is a `list`. Use `as.POSIXct` instead and you'll be fine. — nicola, Jan 19 '17 at 08:34
@akrun I reopened the question since the problem here arises from the fact that there is a `POSIXlt` column (which is actually a `list`) and `melt` doesn't handle it. So, OP here isn't asking how to reshape data, but rather why reshaping here doesn't work. — nicola, Jan 19 '17 at 10:28

score 1 · Accepted Answer · answered Jan 19 '17 at 10:36

The problem with your code arises from the fact that you have coerced your TIMESTAMP column to POSIXlt. Keep in mind that POSIXlt and POSIXct, while very similar from the exterior, are way different. POSIXlt is actually a list. Try for instance:

x<-as.POSIXlt("2017-01-15 15:00:00")
typeof(x)
#[1] "list"
unclass(x)
#$sec
#[1] 0
#$min
#[1] 0
#$hour
#[1] 15
#$mday
#[1] 15
#...

As you can see, a POSIXlt object is a list with elements indicating the seconds, minutes, hour and so on of a date. Let's POSIXct:

x<-as.POSIXct("2017-01-15 15:00:00")
typeof(x)
#[1] "double"
unclass(x)
#[1] 1484488800
#attr(,"tzone")
#[1] ""

POSIXct on the other hand are just the number of seconds from the UNIX epoch and so the date can be stored as just a single number. POSIXct are lighter than POSIXlt.

While it is possible to have a column in a data.frame which is a list, it's much better to have POSIXct:

dat$TIMESTAMP <- as.POSIXct(strptime(dat$TIMESTAMP,"%Y-%m-%d %H:%M"))
#this works now as intended
melt(dat, id.vars=c("RECNUM", "TIMESTAMP"), variable.name="Measure")

reshape2 melt function on timestamps gives NAs

1 Answers1