How to open a nested json column in a dataframe and create another dataframe

user_12213858 0
Viewed 48

I have a df and a column with format json like. When I read a row of this df I get this:

"{\"team_welcome\": {\"value\": [\"5\"], \"comment\": \"\"}, \"resources_access\": {\"value\": [\"5\"], \"comment\": \"\"}, \"admission_process\": {\"value\": [\"5\"], \"comment\": \"\"}, \"early_orientation\": {\"value\": [\"5\"], \"comment\": \"\"}, \"hiring_satisfaction\": {\"value\": [\"5\"], \"comment\": \"\"}, \"manager_interaction\": {\"value\": [\"4\"], \"comment\": \"\"}, \"employee_nps_onboarding\": {\"value\": [\"8\"]}, \"employee_nps_onboarding_why\": {\"value\": \"\"}, \"communication_channels_access\": {\"value\": [\"5\"], \"comment\": \"\"}}"

Keys and values can differ from row to row.

How I can open these json like rows and how can I get ride of these "" and \?

I am trying:

r = df[!,:json_column]
x =JSON3.read(r)

But it's failing.

My goals here is to create another dataframe that opens these json rows in columns, each key in a column.

Is there a way to do this in Julia?

julia
2 Answers

If you want to make changes to the string you can use the replace function.

As for reading the json, I can not replicate your issue

r = "{\"team_welcome\": {\"value\": [\"5\"], \"comment\": \"\"}, \"resources_access\": {\"value\": [\"5\"], \"comment\": \"\"}, \"admission_process\": {\"value\": [\"5\"], \"comment\": \"\"}, \"early_orientation\": {\"value\": [\"5\"], \"comment\": \"\"}, \"hiring_satisfaction\": {\"value\": [\"5\"], \"comment\": \"\"}, \"manager_interaction\": {\"value\": [\"4\"], \"comment\": \"\"}, \"employee_nps_onboarding\": {\"value\": [\"8\"]}, \"employee_nps_onboarding_why\": {\"value\": \"\"}, \"communication_channels_access\": {\"value\": [\"5\"], \"comment\": \"\"}}"

jsondict = JSON3.read(r)

works for me.

In general there is no easy way to map a json to a dataframe. The point is that a json inherently has a tree structure, which might be arbitrarily nested, whereas a dataframe has a tabular structure. A specific mapping needs to take the specific structure of your data into account.

That said, your specific example data can be brought into a flat structure like so

flatdict = Dict{Symbol,Union{Int,Nothing}}()
for key in keys(jsondict)
    value = jsondict[key][:value]
    if typeof(value) <: String
        flatdict[key] = tryparse(Int,value)
    elseif length(value) == 1
        flatdict[key] = tryparse(Int,value[1])
    else
        @warn "Too many entries in array." value
    end
end
newdf = DataFrame(flatdict)
user_189679390

I guess you want this:

JSON3.read.(r) |> Tables.dictrowtable |> DataFrame
user_12695670
Related