0

I have dataframe column containing JSON format of varying length(i mean the values), i have used--fromJSON function and parse it, but it contains multiple nested lists and I don't know how to separate each value in the JSON into a multiple columns...

Values
{"ssdata":[{"v1":"-49.88","v2":"00.02"}],"sdata":[{"v3":"59","v4":"26.4","v5":"24.40","v6":"61.34"}]}
{"ssdata":[{"v1":"-49.76","v2":"00.00"}],"sdata":[{"v3":"60","v4":"26.4","v5":"24.40","v6":"62.13"}]}
{"ssdata":[{"v1":"-49.76","v2":"00.06"}],"sdata":[{"v3":"54","v4":"28.0","v5":"25.97","v6":"56.72"}]}
{"ssdata":[{"v1":"","v2":"","v3":"14"}],"sdata":[{"v4":"46.42","v5":"25.40"}]}
{"ssdata":[{"v1":"","v2":"","v3":"14"}],"sdata":[{"v4":"0.40","v5":"108.94"}]}

can anyone help like i need each value (i.e) v1,v2,v3,v4... in separte columns...like is there way to do it in R..??really need help on this...!!

Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
rock4sridhar
  • 31
  • 3
  • 2
    What have you tried? Have you looked at the `jsonlite` package? It's quite popular. Or the `rjson` package that you tag your question with? – Gregor Thomas Jun 21 '18 at 03:51
  • What is the relationship between the v3 present in sdata and the one present in ssdata? – Ralf Stubner Jun 21 '18 at 04:53
  • sorry for it..in ssdata the variables are with different names and in sdata also the names are different....so v3 in ssdata data has no relation with v3 in sdata....you can think each of these has diffrent names... – rock4sridhar Jun 21 '18 at 05:14
  • I have tried using the json pakcage but there are multiple lists being nested upon and its quite compex to split and covert it to seperate column – rock4sridhar Jun 21 '18 at 05:15

2 Answers2

0

As already mentioned by @Gregor you can try jsonlite

library(jsonlite)
library(data.table)

df1 <- rbindlist(lapply(df$values, function(x) as.data.frame(fromJSON(x))), fill = T)

which gives

> df1
   ssdata.v1 ssdata.v2 sdata.v3 sdata.v4 sdata.v5 sdata.v6 ssdata.v3
1:    -49.88     00.02       59     26.4    24.40    61.34      <NA>
2:    -49.76     00.00       60     26.4    24.40    62.13      <NA>
3:    -49.76     00.06       54     28.0    25.97    56.72      <NA>
4:                         <NA>    46.42    25.40     <NA>        14
5:                         <NA>     0.40   108.94     <NA>        14


Sample data:

df <- structure(list(values = c("{\"ssdata\":[{\"v1\":\"-49.88\",\"v2\":\"00.02\"}],\"sdata\":[{\"v3\":\"59\",\"v4\":\"26.4\",\"v5\":\"24.40\",\"v6\":\"61.34\"}]}", 
"{\"ssdata\":[{\"v1\":\"-49.76\",\"v2\":\"00.00\"}],\"sdata\":[{\"v3\":\"60\",\"v4\":\"26.4\",\"v5\":\"24.40\",\"v6\":\"62.13\"}]}", 
"{\"ssdata\":[{\"v1\":\"-49.76\",\"v2\":\"00.06\"}],\"sdata\":[{\"v3\":\"54\",\"v4\":\"28.0\",\"v5\":\"25.97\",\"v6\":\"56.72\"}]}", 
"{\"ssdata\":[{\"v1\":\"\",\"v2\":\"\",\"v3\":\"14\"}],\"sdata\":[{\"v4\":\"46.42\",\"v5\":\"25.40\"}]}", 
"{\"ssdata\":[{\"v1\":\"\",\"v2\":\"\",\"v3\":\"14\"}],\"sdata\":[{\"v4\":\"0.40\",\"v5\":\"108.94\"}]}"
)), .Names = "values", class = "data.frame", row.names = c(NA, 
-5L))
Prem
  • 11,775
  • 1
  • 19
  • 33
0

library(jsonlite) library(data.table)

df1 <- rbindlist(lapply(df$values, function(x) as.data.frame(fromJSON(x))), fill = T)

rock4sridhar
  • 31
  • 3