Julia set data from DataFrames to JSON

Question

related post: DataFrames to Database tables

Indeed, my plan is data passing from DataFrame to json. I am trying to use Genie frame work and Genie.json, the sample data is as following,

Row  │ id     name   address      age      sex    
     │ Int64  String String?    String?  String? 
─────┼───────────────────────────────────────────
   1 │ 1     Ono     Kyoto        60       m
   2 │ 2     Serena  PA           38       F
   3 │ 3     Edita   Slovakia     ３２       F

and this data packed to res as DataFrames, then

json( Dict( "alldata" => res ))

the json data is lined order by columns.

(A):{"alldata":{"columns":[[1,2,3], ["Ono","Serana","Edita"],["Kyoto","PA","Slovakia"],["60","38","３２"],["m","f","f"]]}}

But, of course, I would like to get each row like this

(B):{"alldata":{"columns":[[1,"Ono","Kyoto","60","m"],[2,"Serana","PA","38","f"],[3,"Edita","Slovakia","３２","f"]]}}

I posted the same question on Genie community then got answer using JSON3 package after serialization. That procedure made sense, however I wonder are there any ideas alike do not use serialize processing. The ideal process is direct pass from Dataframes to json to realize (B) json form data.

To json by serializing

oDf::DataFrame = SearchLight.query( sSelect )
if !isempty(oDf)
    for oRow in eachrow(oDf)
        _iid::Int = oRow.id
        _sname::String = oRow.name
            ・
            ・
        push!( aTmp["alldata"], Dict("columns"=>[_iid,_sname,_saddress,_sage,_ssex]))
    end
    aTmp["data_ready"] = 1
end

sRetJ = JSON3.write(aTmp)

I looked at DataFrames.jl but did not find the solution, maybe the book has it, but not yet. Thanks any advances.

Answer 1

I assume you want the following:

julia> df = DataFrame(a=[1, 2, 3], b=[11, 12, 13])
3×2 DataFrame
 Row │ a      b
     │ Int64  Int64
─────┼──────────────
   1 │     1     11
   2 │     2     12
   3 │     3     13

julia> Genie.Renderer.Json.json(Dict("alldata" => "columns" => Vector.(eachrow(df))))
HTTP.Messages.Response:
"""
HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{"alldata":{"columns":[[1,11],[2,12],[3,13]]}}"""

(however, it is strange that you want to call this "columns" while these are rows of your data)

If you want to retain column names you can do:

julia> Genie.Renderer.Json.json(Dict("alldata" => "columns" => copy.(eachrow(df))))
HTTP.Messages.Response:
"""
HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{"alldata":{"columns":[{"a":1,"b":11},{"a":2,"b":12},{"a":3,"b":13}]}}"""