How to read a JSON file from URL into a DataFrame in Julia?

user_13112997 0
Viewed 621

I am trying to do the following. I have a url that links to a JSON file containing numerical data. I would like to read this data into a DataFrame. I have been following the advice here on how to read from a url into a JSON object. But, after running this code:

resp = HTTP.get("[url here]")   
str = String(resp.body)   jobj =
JSON.Parser.parse(str)

I get this error:

Expected 'r' here
Line: 0
Around: ...timestamp,open,high,lo...
_________^

Note, this code does run for a different JSON file that I used to test it. Next, after this step, I have tried following this advice on how to read a JSON file into a DataFrame. However, after running this code:

df = DataFrame(jsontable(jobj))

I get this error:

MethodError: no method matching read(::Dict{String,Any})

This step does not work on file I used to test the previous code. I appreciate any help with the above code or any new approaches to do this. Also I should mention I am very much a beginner when it comes to coding anything outside of data analysis. Thank you for any help!

EDIT: The first part of the code now runs correctly (I had the wrong URL). And I am getting a new error when I run the second part of the code. After running:

df = DataFrame(jsontable(jobj))

I get the error:

MethodError: no method matching read(::Dict{String,Any})

UPDATE: I ended up writing a function that is able to parse the file. It only extracts two of the columns but for now that works.

function json_df_parse(jobj)
    timestamp = collect(keys(jobj));close = String[];
    for key in timestamp
        jobj2 = jobj[key]
        push!(close,jobj2["4. close"])
    end
    df = DataFrame(timestamp = timestamp, close = close)
    return df
end

Here is a sample of what the JSON file looks like:

{
    "Meta Data": {
        "1. Information": "Intraday (1min) open, high, low, close prices and volume",
        "2. Symbol": "XXXX",
        "3. Last Refreshed": "2021-02-22 20:00:00",
        "4. Interval": "1min",
        "5. Output Size": "Full size",
        "6. Time Zone": "US/Eastern"
    },
    "Time Series (1min)": {
        "2021-02-22 20:00:00": {
            "1. open": "4.2800",
            "2. high": "4.2800",
            "3. low": "4.2800",
            "4. close": "4.2800",
            "5. volume": "149"
        },
        "2021-02-22 19:59:00": {
            "1. open": "4.2400",
            "2. high": "4.2500",
            "3. low": "4.2400",
            "4. close": "4.2500",
            "5. volume": "999"
julia dataframe json
2 Answers

First example - 1 element in json

I think the following should work:

using JSON, HTTP, DataFrames

url = "https://api.bitfinex.com/v1/pubticker/btcusd"
result = HTTP.request("GET", url)
json_result = JSON.parse(String(result.body))

df = DataFrame(json_result)

I get:

1×8 DataFrame
 Row │ ask      bid      high     last_price  low      mid      timestamp          volume         
     │ String   String   String   String      String   String   String             String         
─────┼────────────────────────────────────────────────────────────────────────────────────────────
   1 │ 47588.0  47554.0  55000.0  47588.0     45000.0  47571.0  1614113583.333932  33063.93120034

Second example - many elements

Here is a second example, more complicated (and probably not the best way to do it, but it works here...):

using JSON, HTTP, DataFrames

url = "https://raw.githubusercontent.com/rbell01824/djangopages_2/5b22451b68320b9b0d4aaae01fcbc0d357d43acd/git/test_data/graphtestdata/countries.json";
result = HTTP.request("GET", url)
json_result = JSON.parse(String(result.body))
dfi = [DataFrame(;[Symbol(k)=>v for (k,v) in json_result[i]["fields"]]...) for i in 1:length(json_result)]
df = reduce(vcat, dfi)

Result:

238×8 DataFrame
 Row │ a3   population  country_size  a2   num    country_name              infant_mortality  life_expectancy 
     │ Any  Int64       Int64         Any  Int64  String                    Float64           Float64         
─────┼────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │ AFG    29757566        652230  AF       4  Afghanistan                         123.89            49.33
   2 │ ALB     2994667         27398  AL       8  Albania                              14.61            77.41
   3 │ DZA    34994937       2381741  DZ      12  Algeria                              25.81            74.5
   4 │ ASM       67242           199  AS      16  American Samoa                        9.66            74.21
   5 │ AND       84825           468  AD      20  Andorra                               3.8             82.43
   6 │ AGO    17544728       1246700  AO      24  Angola                               85.28            54.14
...
user_1907910

Consider this JSON file:

{ "id":[1,2,3],
"vals":[1, 2.1, 3],
"v2":[4.1,null,6.3],
"v3":["7","8",null] }

To make it reproducible I have placed it under https://szufel.pl/stackoverflowdemo.json

Now you can do:

julia> using DataFrames, JSONTables, HTTP

julia> r = HTTP.get("https://szufel.pl/stackoverflowdemo.json");

julia> df = DataFrame(jsontable(r.body))
3×4 DataFrame
 Row │ id     vals  v2         v3
     │ Int64  Real  Float64?   String?
─────┼─────────────────────────────────
   1 │     1     1        4.1  7
   2 │     2   2.1  missing    8
   3 │     3     3        6.3  missing
user_99577100
Related