List of Lists to a merged matrix

Question

Sorry for asking a what may seem to you guys a very basic question on something I am trying to do in R.

My data is a list of lists.

dataset 
$Series1
date      Value
2015-11-01 1.301
2015-11-02 6.016
2015-11-03 4.871
2015-11-04 10.925
2015-11-05 7.638

$Series2
date      Value
2015-11-01 1.532
2015-11-02 3.730
2015-11-03 6.910
2015-11-04 3.554
2015-11-05 2.631

Any thoughts on how can I convert into following?

datamatrix
date      Series1 Series2
2015-11-01 1.301  1.532
2015-11-02 6.016  3.730
2015-11-03 4.871  6.910
2015-11-04 10.925 3.554
2015-11-05 7.638  2.631

Are you sure it is a list of lists? – Divi May 12 '16 at 22:18 — Divi, May 12 '16 at 22:18
yes but should it matter? – StackOverDose May 14 '16 at 07:51 — StackOverDose, May 14 '16 at 07:51

Silence Dogood · Accepted Answer · 2016-05-15T05:49:25.933

You can use Reduce to merge the timeseries

dataset = list(Series1=read.table(text="
date      Value
2015-11-01 1.301
2015-11-02 6.016
2015-11-03 4.871
2015-11-04 10.925
2015-11-05 7.638",header=TRUE,stringsAsFactors=FALSE),Series2=read.table(text="
date      Value
2015-11-01 1.532
2015-11-02 3.730
2015-11-03 6.910
2015-11-04 3.554
2015-11-05 2.631",header=TRUE,stringsAsFactors=FALSE))

mergeFun = function(x,y) merge(x,y,by="date") 

datamatrix = Reduce(mergeFun,dataset)
colnames(datamatrix) = c("date",names(dataset))

datamatrix
#       date Series1 Series2
# 2015-11-01   1.301   1.532
# 2015-11-02   6.016   3.730
# 2015-11-03   4.871   6.910
# 2015-11-04  10.925   3.554
# 2015-11-05   7.638   2.631

Using xts library for multiple series

I have added extra columns in the dataset and using xts you could do the following

require(xts)

dataset = list(Series1=read.table(text="
date      Value
2015-11-01 1.301
2015-11-02 6.016
2015-11-03 4.871
2015-11-04 10.925
2015-11-05 7.638",header=TRUE,stringsAsFactors=FALSE),Series2=read.table(text="
date      Value
2015-11-01 1.532
2015-11-02 3.730
2015-11-03 6.910
2015-11-04 3.554
2015-11-05 2.631",header=TRUE,stringsAsFactors=FALSE),Series3=read.table(text="
date      Value
2015-11-01 1
2015-11-02 3
2015-11-03 6
2015-11-04 3
2015-11-05 2",header=TRUE,stringsAsFactors=FALSE),Series4=read.table(text="
date      Value
2015-11-01 1.1
2015-11-02 3.2
2015-11-03 6.3
2015-11-04 3.4
2015-11-05 2.5",header=TRUE,stringsAsFactors=FALSE))


datasetXTS = lapply(dataset,function(x) {
z=xts(x[,-1],order.by=as.Date(x[,1],format="%Y-%m-%d"));
colnames(z) = tail(colnames(x),1);
z 
})

datamatrix = Reduce(merge,datasetXTS)

datamatrix
#            Value Value.1 Value.2 Value.3
#2015-11-01  1.301   1.532       1     1.1
#2015-11-02  6.016   3.730       3     3.2
#2015-11-03  4.871   6.910       6     6.3
#2015-11-04 10.925   3.554       3     3.4
#2015-11-05  7.638   2.631       2     2.

The series have been correctly merged but since you have the same column name in all series they are repeated. To resolve this:

colnames(datamatrix) = names(dataset)

datamatrix
#           Series1 Series2 Series3 Series4
#2015-11-01   1.301   1.532       1     1.1
#2015-11-02   6.016   3.730       3     3.2
#2015-11-03   4.871   6.910       6     6.3
#2015-11-04  10.925   3.554       3     3.4
#2015-11-05   7.638   2.631       2     2.5

Thanks for the response. This method doesn't work if I have more than 3 series in the list though? — StackOverDose, May 14 '16 at 07:50
It works for any number series in list provided they have the same common column — Silence Dogood, May 14 '16 at 14:20
Warning message: In merge.data.frame(x, y, by = "date") : column names ‘Value.x’, ‘Value.y’ are duplicated in the result This is the error I get if there are more than 3 series. — StackOverDose, May 14 '16 at 17:06

List of Lists to a merged matrix

1 Answers1