# Read and convert JSON file from a website
**URL:** https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556
**Category:** General Usage
**Tags:** json, json3
**Created:** [April 15, 2022, 10:35pm UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556 "2022-04-15T22:35:05Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Aminath\_Shausan](https://discourse.julialang.org/u/Aminath_Shausan)
#### Post date: [April 15, 2022, 10:35pm UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/1 "2022-04-15T22:35:05Z")
Hi I am trying to download the json file from [https://raw.githubusercontent.com/hodcroftlab/covariants/master/cluster\_tables/EUClusters\_data.json](https://raw.githubusercontent.com/hodcroftlab/covariants/master/cluster_tables/EUClusters_data.json)
And then to save it as a csv file. At the moment I have used
```julia
using CSV, DataFrames, HTTP, Dates, JSON, JSONTables, JSON3
r = HTTP.get("https://raw.githubusercontent.com/hodcroftlab/covariants/master/cluster_tables/EUClusters_data.json");
df_vaiants_raw = DataFrame(jsontable(r.body))
```
But getting the error:
```julia
ArgumentError: input `JSON3.Object` must only have `JSON3.Array` values to be considered a table
Stacktrace:
[1] jsontable(x::JSON3.Object{Vector{UInt8}, Vector{UInt64}})
@ JSONTables ~/.julia/packages/JSONTables/g5bSA/src/JSONTables.jl:26
[2] jsontable(source::Vector{UInt8})
@ JSONTables ~/.julia/packages/JSONTables/g5bSA/src/JSONTables.jl:15
[3] top-level scope
@ In[42]:3
[4] eval
@ ./boot.jl:360 [inlined]
[5] include_string(mapexpr::typeof(REPL.softscope), mod::Module, code::String, filename::String)
@ Base ./loading.jl:1116
```
I am not sure how to fix it, and would like to get help.
Thanks
Aminath
---
### Author:  [@dfdx](https://discourse.julialang.org/u/dfdx)
#### Post date: [April 16, 2022, 5:51am UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/2 "2022-04-16T05:51:52Z")
The provided JSON is nested, you will need to convert it to a flat representation before creating a DataFrame or writing a CSV file. Can you provide an example of the output you want to get?
---
### Author:  [@Aminath\_Shausan](https://discourse.julialang.org/u/Aminath_Shausan)
#### Post date: [April 16, 2022, 11:28pm UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/3 "2022-04-16T23:28:39Z")
Thanks @dfdx . My final goal is to know the dominant variant in each country for a given period of time. I was hoping that I could get a data frame with columns:
**Country; Week; Total sequence; variant name.**
I am not sure if the raw data provided can be structured to reach to my final goal. I can see the information I am looking for as a visual plot here: [CoVariants: Per Country](https://covariants.org/per-country).
Its mentioned that the visual plots are generated using the given raw data. So any alternate suggestion of a better way to approach/reach to my goal, that would be grateful.
Thanks
Aminath
---
### Author:  [@dfdx](https://discourse.julialang.org/u/dfdx)
#### Post date: [April 17, 2022, 8:04am UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/4 "2022-04-17T08:04:06Z")
You need to flatten the structure manually, something like this:
```julia
import HTTP, JSON3, DataFrames.DataFrame
function load_data()
r = HTTP.get("https://raw.githubusercontent.com/hodcroftlab/covariants/master/cluster_tables/EUClusters_data.json")
json = JSON3.read(r.body) # convert HTTP response to a nested dict
variant_names = [ # extracted from the first record
Symbol("20I (Alpha, V1)")
Symbol("20H (Beta, V2)")
Symbol("20J (Gamma, V3)")
Symbol("21A (Delta)")
Symbol("21I (Delta)")
Symbol("21J (Delta)")
Symbol("21K (Omicron)")
Symbol("21L (Omicron)")
Symbol("21B (Kappa)")
Symbol("21D (Eta)")
Symbol("21F (Iota)")
Symbol("21G (Lambda)")
Symbol("21H (Mu)")
Symbol("20B/S:732A")
Symbol("20A/S:126A")
Symbol("20E (EU1)")
Symbol("21C (Epsilon)")
Symbol("20A/S:439K")
Symbol("20A.EU2")
Symbol("20A/S:98F")
Symbol("20C/S:80Y")
Symbol("20B/S:626S")
]
df = DataFrame(
:country => [],
:week => [],
:total_sequences => [],
[name => [] for name in variant_names]... # put each variant as a separate column
# alternatively, create a single column for the dominant variant
)
for (country_name, country) in json["countries"]
for i in 1:length(country[:week])
row = (
country_name,
country[:week][i],
country[:total_sequences][i],
# if you want only the dominant variant, determine it here instead of recording all of them
[haskey(country, variant) ? country[variant][i] : 0 for variant in variant_names]...
)
push!(df, row)
end
end
return df
end
```
To summarize, `DataFrames` won’t magically understand the structure of your data, you have to tell it how to interpret it.
---
### Author:  [@Aminath\_Shausan](https://discourse.julialang.org/u/Aminath_Shausan)
#### Post date: [April 18, 2022, 2:29am UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/5 "2022-04-18T02:29:21Z")
Thanks @dfdx , I have `DataFrames` package installed. But when I tried to import `DataFrames.DataFrame`, I am getting the following error.
```julia
invalid using path: "DataFrame" does not name a module
Stacktrace:
[1] top-level scope
@ In[48]:1
[2] eval
@ ./boot.jl:360 [inlined]
[3] include_string(mapexpr::
```
I am not sure how to fix it. Any help please?
---
### Author:  [@jd-foster](https://discourse.julialang.org/u/jd-foster)
#### Post date: [April 18, 2022, 8:02am UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/6 "2022-04-18T08:02:55Z")
That way, Julia thinks you’re asking for a sub module of `DataFrames`.
Better to put it on a new line as
```julia
import DataFrames: DataFrame
```
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [April 19, 2022, 12:26pm UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/7 "2022-04-19T12:26:55Z")
a little crude, but it could be a good starting point
```julia
using DataFrames, HTTP, JSON3
r = HTTP.get("https://raw.githubusercontent.com/hodcroftlab/covariants/master/cluster_tables/EUClusters_data.json");
json = JSON3.read(r.body)
df=DataFrame(json.countries.Italy)
function dominant(;from ::String=json.plotting_dates.min_date , to::String=json.plotting_dates.max_date , in:: Symbol=:Italy)
df=DataFrame(json.countries[in])
dfs=subset(df, :week=> x-> from .<= x .<= to)
r=select(dfs, 1,Not(1:2)=>ByRow((x...)->[names(df)[2+argmax(x)],maximum(x)])=>[:dominant,:max_of_week])
end
dI=dominant(from="2021-01-01",to="2022-04-30",in=:Italy)
dF=dominant(from="2021-01-01",to="2022-04-30",in=:France)
dG=dominant(from="2021-01-01",to="2022-04-30",in=:Germany)
dominant(in=:USA)
dfcountries=DataFrame(contry=collect(keys(json.countries)))
...
```
```julia
function propcol(r)
R=collect(r)
tot=R[1]
R[1]-=sum(R[2:end])
(;zip(keys(r),R./tot)...)
end
r=dfI[10,2:end]
pr=transform(dfI, AsTable(Not(1))=>ByRow(propcol)=>AsTable)
prg=transform(dfG, AsTable(Not(1))=>ByRow(propcol)=>AsTable)
prm=combine(pr, Not(1).=>maximum)
perm=sortperm(collect(prm[1,:])).+1
using GLMakie
f = Figure()
Axis(f[1, 1])
xs=1:51
ys_low =ys_high= repeat([0.],51)
for c in eachcol(pr[:,perm])
band!(xs, ys_low, c+ys_low )
ys_low +=c
end
```
---
### Author:  [@Aminath\_Shausan](https://discourse.julialang.org/u/Aminath_Shausan)
#### Post date: [April 23, 2022, 10:05pm UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/8 "2022-04-23T22:05:29Z")
Thanks @jd-foster that worked
---
### Author:  [@Aminath\_Shausan](https://discourse.julialang.org/u/Aminath_Shausan)
#### Post date: [April 23, 2022, 10:08pm UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/9 "2022-04-23T22:08:05Z")
@rocco_sprmnt21 I like the idea of the code as it generates the dominant variant.
I ran the code but getting an error at `pr=transform(dfI, AsTable(Not(1))=>ByRow(propcol)=>AsTable) prg=transform(dfG, AsTable(Not(1))=>ByRow(propcol)=>AsTable) `
Here is the error:
```julia
MethodError: no method matching -(::String, ::Int64)
Closest candidates are:
-(::T, ::T) where T<:Union{Int128, Int16, Int32, Int64, Int8, UInt128, UInt16, UInt32, UInt64, UInt8} at int.jl:86
-(::T, ::Integer) where T<:AbstractChar at char.jl:222
-(::DataValues.DataValue{T1}, ::T2) where {T1<:Number, T2<:Number} at /Users/uqashaus/.julia/packages/DataValues/N7oeL/src/scalar/core.jl:212
...
Stacktrace:
[1] propcol(r::NamedTuple{(:dominant, :max_of_week), Tuple{String, Int64}})
@ Main ./In[24]:30
[2] iterate
@ ./generator.jl:47 [inlined]
[3] collect(itr::Base.Generator{Tables.NamedTupleIterator{Tables.Schema{(:dominant, :max_of_week), Tuple{String, Int64}}, Tables.RowIterator{NamedTuple{(:dominant, :max_of_week), Tuple{Vector{String}, Vector{Int64}}}}}, typeof(propcol)})
@ Base ./array.jl:678
[4] (::ByRow{typeof(propcol)})(table::NamedTuple{(:dominant, :max_of_week), Tuple{Vector{String}, Vector{Int64}}})
@ DataFrames ~/.julia/packages/DataFrames/vuMM8/src/abstractdataframe/selection.jl:174
[5] _transformation_helper(df::DataFrame, col_idx::AsTable, ::Base.RefValue{Any})
@ DataFrames ~/.julia/packages/DataFrames/vuMM8/src/abstractdataframe/selection.jl:378
[6] select_transform!(::Base.RefValue{Any}, df::DataFrame, newdf::DataFrame, transformed_cols::Set{Symbol}, copycols::Bool, allow_resizing_newdf::Base.RefValue{Bool})
@ DataFrames ~/.julia/packages/DataFrames/vuMM8/src/abstractdataframe/selection.jl:546
[7] _manipulate(df::DataFrame, normalized_cs::Vector{Any}, copycols::Bool, keeprows::Bool)
@ DataFrames ~/.julia/packages/DataFrames/vuMM8/src/abstractdataframe/selection.jl:1383
[8] manipulate(::DataFrame, ::Any, ::Vararg{Any, N} where N; copycols::Bool, keeprows::Bool, renamecols::Bool)
@ DataFrames ~/.julia/packages/DataFrames/vuMM8/src/abstractdataframe/selection.jl:1311
[9] #select#460
@ ~/.julia/packages/DataFrames/vuMM8/src/abstractdataframe/selection.jl:940 [inlined]
[10] transform(df::DataFrame, args::Any; copycols::Bool, renamecols::Bool)
@ DataFrames ~/.julia/packages/DataFrames/vuMM8/src/abstractdataframe/selection.jl:1018
[11] transform(df::DataFrame, args::Any)
@ DataFrames ~/.julia/packages/DataFrames/vuMM8/src/abstractdataframe/selection.jl:1007
[12] top-level scope
@ In[24]:36
[13] eval
@ ./boot.jl:360 [inlined]
[14] include_string(mapexpr::typeof(REPL.softscope), mod::Module, code::String, filename::String)
@ Base ./loading.jl:1116
```
I am not sure how to get rid of the error. Any ideas?
Thanks
---
### Author:  [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)
#### Post date: [April 24, 2022, 8:38am UTC](https://discourse.julialang.org/t/read-and-convert-json-file-from-a-website/79556/10 "2022-04-24T08:38:26Z")
the propcol function is applied to the second to last columns (Not(1)==2: end) of the dataframe.
You have to make sure that the data of these columns are numbers (integers).
I think the problem is tha the column “total …” is not of integer type.