Rblpapi, unlist data.frame zoo xts

Question

Using the Rblpapi, I get stockdata of 3 indices in a data.frame with several lists. Then, I want to get it in either a zoo or preferably xts format. However, I first have to unlist properly.

Since not everyone has access to Rblpapi and therefore cannot replicate, please look at the str output and suggest me how to unlist.

Any leads or help appreciated!

library(Rblpapi)
library(zoo)
library(xts)

str(res)



List of 3
 $ :'data.frame':   9 obs. of  2 variables:
  ..$ date   : Date[1:9], format:  ...
  ..$ PX_LAST: num [1:9] 201 194 188 190 190 ...
 $ :'data.frame':   9 obs. of  2 variables:
  ..$ date   : Date[1:9], format:  ...
  ..$ PX_LAST: num [1:9] 4891 4686 4477 4568 4517 ...
 $ :'data.frame':   9 obs. of  2 variables:
  ..$ date   : Date[1:9], format:  ...
  ..$ PX_LAST: num [1:9] 19.3 22.5 26.1 22.5 22 ...

head(res)
[[1]]
        date  PX_LAST
1 2016-01-05 201.3600
2 2016-01-12 193.6608
3 2016-01-19 188.0600
4 2016-01-26 190.2000
5 2016-02-02 190.1600
6 2016-02-09 185.4300
7 2016-02-16 189.7800
8 2016-02-23 192.3200
9 2016-03-01 197.9700

[[2]]
        date  PX_LAST
1 2016-01-05 4891.430
2 2016-01-12 4685.919
3 2016-01-19 4476.950
4 2016-01-26 4567.673
5 2016-02-02 4516.946
6 2016-02-09 4268.763
7 2016-02-16 4435.956
8 2016-02-23 4503.583
9 2016-03-01 4680.479

[[3]]
        date PX_LAST
1 2016-01-05   19.34
2 2016-01-12   22.47
3 2016-01-19   26.05
4 2016-01-26   22.50
5 2016-02-02   21.98
6 2016-02-09   26.54
7 2016-02-16   24.11
8 2016-02-23   20.98
9 2016-03-01   17.85

Unlist to get one data.frame / zoo / xts object with (date, pricedata1, pricedata2, pricedata3)

df <- data.frame(matrix(unlist(res), nrow=9))

head(df)

     X1       X2    X3       X4    X5    X6
1 16805   201.36 16805  4891.43 16805 19.34
2 16812 193.6608 16812 4685.919 16812 22.47
3 16819   188.06 16819  4476.95 16819 26.05
4 16826    190.2 16826 4567.673 16826  22.5
5 16833   190.16 16833 4516.946 16833 21.98
6 16840   185.43 16840 4268.763 16840 26.54

However, this is not what I want. column X3 en X5 should not be there. Plus the date format is not good. Therefore getting it to zoo or xts doesn't work:

price<-read.zoo(df, format="%Y%m%d")

df$date <-as.Date(as.character(df$date),format="%Y%m%d")

x<-xts(df$date, df$px_last)

Error in read.zoo(df, format = "%Y%m%d") : index has bad entries at data rows: 1 2 3 4 5 6 7 8 9

Error in xts(df$date, df$px_last) : order.by requires an appropriate time-based object

Daniel Sparing Daniel Sparing · Accepted Answer · 2016-03-02T11:25:49

I believe all you need is to join each list element by the date.

However, for that, first you need to rename all those variables PX_LAST to something unique. For example:

require(data.table)
for (i in 1:length(res)) {
  setnames(res[[i]],"PX_LAST",paste("PX_LAST",i,sep="_"))
}

Then you can join, either by pairwise mergeing, or using the plyr::join_all function:

require(plyr)
df <- join_all(res, by="date", type="full")

#         date PX_LAST_1 PX_LAST_2 PX_LAST_3
# 1 2016-01-05  201.3600  4891.430     19.34
# 2 2016-01-12  193.6608  4685.919     22.47
# 3 2016-01-19  188.0600  4476.950     26.05
# 4 2016-01-26  190.2000  4567.673     22.50
# 5 2016-02-02  190.1600  4516.946     21.98
# 6 2016-02-09  185.4300  4268.763     26.54
# 7 2016-02-16  189.7800  4435.956     24.11
# 8 2016-02-23  192.3200  4503.583     20.98
# 9 2016-03-01  197.9700  4680.479     17.85

Then finally you can use

include(zoo)
price<-read.zoo(df)

include(xts)
xts(df, df$date)

Rblpapi, unlist data.frame zoo xts

1 Answers