CODEWITHSUNDEEP
jsonrdataframejsonlite
Ashag
Ashag

Reputation: 867

Convert json fetched into dataframe using R

I've json like below, which i got from below URL:

{
   "info" : {
      "1484121600" : [
         212953175.053333,212953175.053333,null
      ],
      "1484125200" : [
         236203014.133333,236203014.133333,236203014.133333
      ],
      "1484128800" : [
         211414832.968889,null,211414832.968889
      ],
      "1484132400" : [
         208604573.791111,208604573.791111,208604573.791111
      ],
      "1484136000" : [
         231358374.288889,231358374.288889,231358374.288889
      ],
      "1484139600" : [
         210529301.097778,210529301.097778,210529301.097778
      ],
      "1484143200" : [
         212009682.04,null,212009682.04
      ],
      "1484146800" : [
         232364759.566667,232364759.566667,232364759.566667
      ],
      "1484150400" : [
         218138788.524444,218138788.524444,218138788.524444
      ],
      "1484154000" : [
         218883301.282222,218883301.282222,null
      ],
      "1484157600" : [
         237874583.771111,237874583.771111,237874583.771111
      ],
      "1484161200" : [
         216227081.924444,null,216227081.924444
      ],
      "1484164800" : [
         227102054.082222,227102054.082222,null
      ]
},
"summary" : "data",
"end" : 1484164800,
"start": 1484121600
}

I'm fetching this json from some url using jsonlite package in R like below:

library(jsonlite)

input_data <- fromJSON(url)

timeseries <- input_data[['info']]       # till here code is fine

abc <- data.frame(ds = names(timeseries[[1]]), 
                  y = unlist(timeseries[[1]]), stringsAsFactors = FALSE)

(something is wrong in above line)

I need to convert this data in timeseries variable into data frame; which will have index column as the epoch time and no. of columns in dataframe will depend upon no. of values in array and all arrays will have same no. of values for sure. But no. of values in array can be 1 0r 2 or etc; it is not fixed. Like in below example array size is 3 for all.

for eg : dataframe should look like:

index        y1                     y2                    y3
1484121600   212953175.053333       212953175.053333      null
1484125200   236203014.133333       236203014.133333   236203014.133333

Please suggest how do I do this in R. I'm new to it.

JSON with only 1 item in array:

{
"info": {
"1484121600": [
212953175.053333
],
"1484125200": [
236203014.133333
],
"1484128800": [
211414832.968889
],
"1484132400": [
208604573.791111
],
"1484136000": [
231358374.288889
],
"1484139600": [
210529301.097778
],
"1484143200": [
212009682.04
],
"1484146800": [
232364759.566667
],
"1484150400": [
218138788.524444
],
"1484154000": [
218883301.282222
],
"1484157600": [
237874583.771111
],
"1484161200": [
216227081.924444
],
"1484164800": [
227102054.082222
]
},
"summary": "data",
"end": 1484164800,
"start": 1484121600
}

Upvotes: 0

Views: 189

Answers (1)

Parfait
Parfait

Reputation: 107587

Consider binding the list of json values to a matrix with sapply(), then transpose columns to rows with t(), and finally convert to dataframe with data.frame()

abc <- data.frame(t(sapply(timeseries, c)))
colnames(abc) <- gsub("X", "y", colnames(abc))

abc
#                   y1        y2        y3
# 1484121600 212953175 212953175        NA
# 1484125200 236203014 236203014 236203014
# 1484128800 211414833        NA 211414833
# 1484132400 208604574 208604574 208604574
# 1484136000 231358374 231358374 231358374
# 1484139600 210529301 210529301 210529301
# 1484143200 212009682        NA 212009682
# 1484146800 232364760 232364760 232364760
# 1484150400 218138789 218138789 218138789
# 1484154000 218883301 218883301        NA
# 1484157600 237874584 237874584 237874584
# 1484161200 216227082        NA 216227082
# 1484164800 227102054 227102054        NA

Upvotes: 2

Related Questions