json - read mixed data into R

Question

I have a text file ' ' separated. First two columns are text and third one is in JSON format like {type: [{a: a1, timestamp: 1}, {a:a2, timestamp: 2}]} How can i put it into DF correctly?

I would like to parse line like factor1 param1 {type: [{a: a1, timestamp: 1}, {a:a2, timestamp: 2}]} into DF like

factor_column  param_column   a_column  ts_column
    factor1        param1        a1         1    
    factor1        param1        a2         2    

Answer 1

I have saved that one line of text you have provided into a file called 'parseJSON.txt'. You can then read the file in as per usual using read.table, then make use of library(jsonlite) to parse the 3rd column.

I've also formatted the line of text to include quotes around the JSON code: factor1 param1 {"type": [{"a": "a1", "timestamp": 1}, {"a":"a2", "timestamp": 2}]}

library(jsonlite)

dat <- read.table("parseJSON.txt", 
              sep="	", 
              header=F,
              quote="")

#parse 3rd column using jsonlite
js <- fromJSON(as.character(dat[1,3]))

js is now a list

> js
$type
   a timestamp
1 a1         1
2 a2         2

which can be combined with the first two columns of dat

res <- cbind(dat[,1:2],js$type)

names(res) <- c("factor_column", "param_column", "a_column", "ts_column")

which gives

> res
     factor_column param_column a_column ts_column
1       factor1       param1       a1         1
2       factor1       param1       a2         2