time-series in a data frame: re-order data

Question

I have a time series data set produced by a measuring software with the following structure:

ID1 ID2 START   mes1    mes2    mes3    mes4    mes5    mes6
myidA   aa  2000    12  58  45  66  88  77
myidB   aa  2004    44  89  NA  NA  NA  NA
myidC   ab  2001    69  58  77  88  87  NA
myidD   ab  2004    78  66  NA  NA  NA  NA

START indicates the year of the older measurement which was saved in the first measurement column (mes1). For each sample (each line of the data frame), the start year can be different.

I'd like to create the following data frame, where measurements are ordered by years (to replace n° of measurement with years of the measure):

ID1 ID2 START   2000    2001    2002    2003    2004    2005
myidA   aa  2000    12  58  45  66  88  77
myidB   aa  2004    NA  NA  NA  NA  44  89
myidC   ab  2001    NA  69  58  77  88  87
myidD   ab  2004    NA  NA  NA  NA  78  66

I may have to use a time series object but I don't know how to cope with the IDs (I need to keep them) and with the START...

Could you elaborate a bit more on the order of your data by years. It is not that obvious. — iugrina, May 02 '14 at 11:58
ok, are the total measurements fixed to 6 always? In other words, does START range from 2000 to 2005 only? — Christian Borck, May 02 '14 at 12:02
Possible duplicate of: http://stackoverflow.com/questions/7980030/how-to-pivot-unpivot-cast-melt-data-frame or http://stackoverflow.com/questions/19814246/r-combining-multiple-columns-together-within-a-data-frame-while-keeping-conne/19814597 — Steph Locke, May 02 '14 at 12:43
@StephLocke, on the surface, those look like duplicates, but I don't think this is as direct of a "reshape" problem. — A5C1D2H2I1M1N2O1R2T1, May 03 '14 at 16:45

score 1 · Accepted Answer · answered May 03 '14 at 16:42

Here's the approach I would take:

library(reshape2)
dfL <- melt(mydf, id.vars=c("ID1", "ID2", "START"))
dfL <- dfL[complete.cases(dfL), ]
head(dfL)
#     ID1 ID2 START variable value
# 1 myidA  aa  2000     mes1    12
# 2 myidB  aa  2004     mes1    44
# 3 myidC  ab  2001     mes1    69
# 4 myidD  ab  2004     mes1    78
# 5 myidA  aa  2000     mes2    58
# 6 myidB  aa  2004     mes2    89

dfL$year <- dfL$START + as.numeric(gsub("mes", "", dfL$variable))-1

dcast(dfL, ID1 + ID2 + START ~ year, value.var="value")
#     ID1 ID2 START 2000 2001 2002 2003 2004 2005
# 1 myidA  aa  2000   12   58   45   66   88   77
# 2 myidB  aa  2004   NA   NA   NA   NA   44   89
# 3 myidC  ab  2001   NA   69   58   77   88   87
# 4 myidD  ab  2004   NA   NA   NA   NA   78   66

The basic idea is to make use of the "mes1", "mes2" values to "push" the values to their correct place in the newly widened data.frame.

Here's the "mydf" that I used, in case anyone else wants to take a stab at this.

mydf <- structure(
  list(ID1 = c("myidA", "myidB", "myidC", "myidD"), 
       ID2 = c("aa", "aa", "ab", "ab"), 
       START = c(2000L, 2004L, 2001L, 2004L), 
       mes1 = c(12L, 44L, 69L, 78L), mes2 = c(58L, 89L, 58L, 66L), 
       mes3 = c(45L, NA, 77L, NA), mes4 = c(66L, NA, 88L, NA), 
       mes5 = c(88L, NA, 87L, NA), mes6 = c(77L, NA, NA, NA)), 
  .Names = c("ID1", "ID2", "START", "mes1", "mes2", "mes3", 
             "mes4", "mes5", "mes6"), class = "data.frame", 
  row.names = c(NA, -4L))
mydf
#     ID1 ID2 START mes1 mes2 mes3 mes4 mes5 mes6
# 1 myidA  aa  2000   12   58   45   66   88   77
# 2 myidB  aa  2004   44   89   NA   NA   NA   NA
# 3 myidC  ab  2001   69   58   77   88   87   NA
# 4 myidD  ab  2004   78   66   NA   NA   NA   NA

time-series in a data frame: re-order data

1 Answers1