DataFrame unmelt-like operation

Specific Domains Data
1

I have a DataFrame with observations for 2 individuals in a household, and want to “unmelt” it so that they end up in columns, one for each person. MWE:

julia> df = DataFrame(household = repeat(1:5, inner = 2),
                      person = repeat(1:2, outer = 5),
                      a = 1:10,
                      b = 11:20)
10×4 DataFrames.DataFrame
│ Row │ household │ person │ a  │ b  │
├─────┼───────────┼────────┼────┼────┤
│ 1   │ 1         │ 1      │ 1  │ 11 │
│ 2   │ 1         │ 2      │ 2  │ 12 │
│ 3   │ 2         │ 1      │ 3  │ 13 │
│ 4   │ 2         │ 2      │ 4  │ 14 │
│ 5   │ 3         │ 1      │ 5  │ 15 │
│ 6   │ 3         │ 2      │ 6  │ 16 │
│ 7   │ 4         │ 1      │ 7  │ 17 │
│ 8   │ 4         │ 2      │ 8  │ 18 │
│ 9   │ 5         │ 1      │ 9  │ 19 │
│ 10  │ 5         │ 2      │ 10 │ 20 │

## this is what the end result should look like
julia> DataFrame(household = 1:5,
                 a_1 = 1:2:9,
                 a_2 = 2:2:10,
                 b_1 = 11:2:19,
                 b_2 = 12:2:20)
5×5 DataFrames.DataFrame
│ Row │ household │ a_1 │ a_2 │ b_1 │ b_2 │
├─────┼───────────┼─────┼─────┼─────┼─────┤
│ 1   │ 1         │ 1   │ 2   │ 11  │ 12  │
│ 2   │ 2         │ 3   │ 4   │ 13  │ 14  │
│ 3   │ 3         │ 5   │ 6   │ 15  │ 16  │
│ 4   │ 4         │ 7   │ 8   │ 17  │ 18  │
│ 5   │ 5         │ 9   │ 10  │ 19  │ 20  │
2

Those are the simplest I could think of, either

res = join(unstack.(df, :household, :person, [:a, :b])..., on=:household)
names!(res, [:household, :a_1, :a_2, :b_1, :b_2])

or

tmp = melt(df, [:household, :person])
tmp[:variable] = string.(tmp[:variable], "_", tmp[:person])
res2 = unstack(tmp, :household, :variable, :value)
1 Like