[DataFrames Question]: How to convert single column with row of dictionary to multiple columns

Specific Domains
,
1

Question:

If I have a dataframe with a column that has rows which are dictionaries, whats the appropriate way to convert each key to a column and fill the elements with the value. I assume its through the transform! function, but I’m not sure exactly how to do this.

Example:

image
image1428×962 66.7 KB

2

Here is one way (interested to see other approaches):

## Setup
using DataFrames
df = DataFrame(x = [Dict(:y => 1.0, :z => 2.0), Dict(:yz=>1.0, :y=>0.0, :z=>0.5)])

## Scan first to get list of possible column names:
keylist = Set()

for row in eachrow(df)
   push!(keylist, keys(row.x)...)
end

## Now create the transformed dataframe:
for k in keylist
    df[!,k] = [haskey(row.x,k) ? row.x[k] : missing for row in eachrow(df)]
end
2 Likes
3

DataFrame constructors accept Dicts, so the simplest is probably:

julia> reduce(vcat, DataFrame.(df.x), cols = :union)
2×3 DataFrame
 Row │ y      z      yz
     │ Int64  Int64  Int64?
─────┼───────────────────────
   1 │     1      2  missing
   2 │     4      5        3

which you can then just hcat onto your existing table:

julia> df = DataFrame(x = [Dict(:y => 1, :z => 2), Dict(:yz => 3, :y => 4, :z => 5)])
2×1 DataFrame
 Row │ x
     │ Dict…
─────┼────────────────────────────
   1 │ Dict(:y=>1, :z=>2)
   2 │ Dict(:yz=>3, :y=>4, :z=>5)

julia> hcat(df, reduce(vcat, DataFrame.(df.x), cols = :union))
2×4 DataFrame
 Row │ x                           y      z      yz
     │ Dict…                       Int64  Int64  Int64?
─────┼───────────────────────────────────────────────────
   1 │ Dict(:y=>1, :z=>2)              1      2  missing
   2 │ Dict(:yz=>3, :y=>4, :z=>5)      4      5        3
5 Likes
4

@nilshg and @jd-foster thanks a bunch, this does what I need. I checked the timings with @time, no significant difference I see. Is there a reason not to do this with transform?

5

transform needs matching keys for each row, so if your second Dict has :yz as a key, it won’t work.

1 Like