rnested-listsibrokers

convert triple nested list to dataframe

I'm trying to convert a triple nested list into a dataframe. This question has helped, but I can't get the dataframe I'd like.

The list is an options chain obtained from IBrokers, a summary is shown below. I've uploaded the actual chain here which is more detailed.

Chain <- 
  list(
    list(
      list(
        list(version="8",contract=list(symbol="BHP",right="C",expiry="20180621",strike="25")),
        list(version="8",contract=list(symbol="BHP",right="C",expiry="20180621",strike="26"))
      ),
      list(
        list(version="8",contract=list(symbol="BHP",right="C",expiry="20180730",strike="25")),
        list(version="8",contract=list(symbol="BHP",right="C",expiry="20180730",strike="26"))
      )
    ),
    list(
      list(
        list(version="8",contract=list(symbol="CBA",right="C",expiry="20180621",strike="65")),
        list(version="8",contract=list(symbol="CBA",right="C",expiry="20180621",strike="64"))
      ),
      list(
        list(version="8",contract=list(symbol="CBA",right="C",expiry="20180730",strike="65")),
        list(version="8",contract=list(symbol="CBA",right="C",expiry="20180730",strike="64"))
      )
    )
  )

I'd like to convert the list into a dataframe like this:

Contracts <- data.frame(symbol=c("BHP","BHP","BHP","BHP","CBA","CBA","CBA","CBA"),
                        right=c("C","C","C","C","C","C","C","C"),
                        expiry=c("20180621","20180621","20180730","20180730","20180621","20180621","20180730","20180730"),
                        strike=c("25","26","25","26","65","64","65","64"))

I tried this code, but it didn't give me the dataframe I wanted.

X <- lapply(Chain,function(x) as.data.frame.list(lapply(x,as.data.frame.list)))
dfx <- do.call(rbind,X)

Any suggestions please?

Solution

  • You can use unstack

     unstack(data.frame(d<-unlist(Chain),names(d)))
  contract.expiry contract.right contract.strike contract.symbol version
1        20180621              C              25             BHP       8
2        20180621              C              26             BHP       8
3        20180730              C              25             BHP       8
4        20180730              C              26             BHP       8
5        20180621              C              65             CBA       8
6        20180621              C              64             CBA       8
7        20180730              C              65             CBA       8
8        20180730              C              64             CBA       8

    If you want you can delete the word contract.

    unstack(data.frame(d<-unlist(Chain),sub(".*[.]","",names(d))))
    expiry right strike symbol version
1 20180621     C     25    BHP       8
2 20180621     C     26    BHP       8
3 20180730     C     25    BHP       8
4 20180730     C     26    BHP       8
5 20180621     C     65    CBA       8
6 20180621     C     64    CBA       8
7 20180730     C     65    CBA       8
8 20180730     C     64    CBA       8

    This can also be written as unstack(data.frame(d<-unlist(Chain),sub("contract[.]","",names(d)))) Although I would prefer to maintain the name contract in order to know which columns indeed form the contract dataframe needed

    Or even you can change the names After unstacking.

    With the new data:

    a=readLines("https://raw.githubusercontent.com/hughandersen/OptionsTrading/master/Stocks_option_chain")
b=eval(parse(text=paste(a,collapse="")))
s=unstack(data.frame(d<-unlist(b[6]),names(d)))