JSON to R data.frame

Question

I have a problem using rjson package to convert JSON to R data.frame.

I started with:

library("rjson")
json_file <- "btcusd.txt"
json_data <- fromJSON(paste(readLines(json_file), collapse=""))

btcusd.txt file contains the following:

{"Response":"Success","Type":100,"Aggregated":true,"Data":
        [{"time":1510650000,"close":6488.28,"high":6618.69,"low":6482.22,"open":6492.35,"volumefrom":9422.44,"volumeto":61626698.63},
        {"time":1510671600,"close":6541,"high":6592.05,"low":6487.35,"open":6549.1,"volumefrom":12618.61,"volumeto":82634018.7},],
        "TimeTo":1511298000,"TimeFrom":1510574400,"FirstValueInArray":true,"ConversionType":{"type":"direct","conversionSymbol":""}}

Can anyone help me to make it into a data.frame?

I get an error with jsonlite::fromJSON: `Error: parse error: unallowed token at this point in JSON text 18.61,"volumeto":82634018.7},], "TimeTo":1511298000,"TimeFro (right here) ------^` — hackR, Nov 21 '17 at 22:34

score 0 · Answer 1 · answered Nov 21 '17 at 22:35

0

I'd try jsonlite package.

Based on what you have I'd try this:

install.packages("jsonlite")
library(jsonlite)

mydata <- fromJSON("btcusd.txt")

But, I inspected your data and it's messy. Can you provide some additional information? if that comes from an API maybe it's easier to read the XML version instead.

answered Nov 21 '17 at 22:35

pachadotdev

3,345
6
33
60

Thank you for your answer, here's the API I was trying to use: https://min-api.cryptocompare.com/data/histominute?fsym=BTC&tsym=USD&limit=3&aggregate=3&e=CCCAGG – professorG Nov 21 '17 at 22:46

score 0 · Answer 2 · answered Nov 21 '17 at 22:55

There is an extra comma in your JSON input. If you remove the comma it works OK:

json_txt <- '{
    "Response": "Success",
    "Type": 100,
    "Aggregated": true,
    "Data": [{
            "time": 1510650000,
            "close": 6488.28,
            "high": 6618.69,
            "low": 6482.22,
            "open": 6492.35,
            "volumefrom": 9422.44,
            "volumeto": 61626698.63
    }, {
            "time": 1510671600,
            "close": 6541,
            "high": 6592.05,
            "low": 6487.35,
            "open": 6549.1,
            "volumefrom": 12618.61,
            "volumeto": 82634018.7
    }], 
    "TimeTo": 1511298000,
    "TimeFrom": 1510574400,
    "FirstValueInArray": true,
    "ConversionType": {
            "type": "direct",
            "conversionSymbol": ""
    }   
}'  
data.frame(fromJSON(json_txt))

Thanks. It gives me a 2 rows data.frame, where column names are "Response", "Type", "Aggregated" and so on. When I remove parts containing them from the JSON, I get a 2 rows data frame, where column names are (as desired): "time", "close", "high", "low", "open", "volumefrom", "volumeto". The problem is that it works only for one element of time series and I need to do this for hundreds of elements. — professorG, Nov 21 '17 at 23:25

JSON to R data.frame

2 Answers2