Pivottable in over multiple columns in Julia

user_3734258 0
Viewed 227

I'd like to do a pivot table on a DataFrame in julia. From the documentation, I know I can do that with by and unstack. E.g.

julia> using DataFrames, Random

julia> Random.seed!(42);

julia> df = DataFrame(
           Step = rand(1:3, 15) |> sort,
           Label1 = rand('A':'B', 15) .|> Symbol,
           Label2 = rand('Q':'R', 15) .|> Symbol
       )
15×3 DataFrame
│ Row │ Step  │ Label1 │ Label2 │
│     │ Int64 │ Symbol │ Symbol │
├─────┼───────┼────────┼────────┤
│ 1   │ 1     │ A      │ Q      │
│ 2   │ 1     │ A      │ Q      │
│ 3   │ 1     │ B      │ R      │
│ 4   │ 1     │ B      │ R      │
│ 5   │ 1     │ B      │ Q      │
│ 6   │ 2     │ B      │ Q      │
│ 7   │ 2     │ B      │ Q      │
│ 8   │ 2     │ B      │ R      │
│ 9   │ 2     │ B      │ R      │
│ 10  │ 3     │ B      │ R      │
│ 11  │ 3     │ B      │ Q      │
│ 12  │ 3     │ B      │ R      │
│ 13  │ 3     │ A      │ R      │
│ 14  │ 3     │ B      │ R      │
│ 15  │ 3     │ B      │ Q      │

julia> unstack(by(df, [:Step, :Label1, :Label2], nrow), :Label1, :nrow)
6×4 DataFrame
│ Row │ Step  │ Label2 │ A       │ B      │
│     │ Int64 │ Symbol │ Int64?  │ Int64? │
├─────┼───────┼────────┼─────────┼────────┤
│ 1   │ 1     │ Q      │ 2       │ 1      │
│ 2   │ 1     │ R      │ missing │ 2      │
│ 3   │ 2     │ Q      │ missing │ 2      │
│ 4   │ 2     │ R      │ missing │ 2      │
│ 5   │ 3     │ Q      │ missing │ 2      │
│ 6   │ 3     │ R      │ 1       │ 3      │

Now, how do I do a pivot on two columns, here Label1 and Label2, that I get the row counts for each combination of the elements of these two columns? The expected output would be something like

│ Row │ Step  │ AQ      │ AR      │ BQ      │ BR      │
│     │ Int64 │ Int64?  │ Int64?  │ Int64?  │ Int64?  │
├─────┼───────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ 1     │ 2       │ missing │ 1       │ 2       │
│ 3   │ 2     │ missing │ missing │ 2       │ 2       │
│ 5   │ 3     │ missing │ 1       │ 2       │ 3       │

Thanks in advance! Tim

julia pivot-table dataframe pivot
1 Answers

First - by is deprecated (the manual will be updated in a few days to reflect that) so one should write:

julia> unstack(combine(groupby(df, [:Step, :Label1, :Label2]), nrow), :Label1, :nrow)
6×4 DataFrame
│ Row │ Step  │ Label2 │ A       │ B      │
│     │ Int64 │ Symbol │ Int64?  │ Int64? │
├─────┼───────┼────────┼─────────┼────────┤
│ 1   │ 1     │ Q      │ 2       │ 1      │
│ 2   │ 1     │ R      │ missing │ 2      │
│ 3   │ 2     │ Q      │ missing │ 2      │
│ 4   │ 2     │ R      │ missing │ 2      │
│ 5   │ 3     │ Q      │ missing │ 2      │
│ 6   │ 3     │ R      │ 1       │ 3      │

However, if you want row counts I would rather do something like:

julia> gdf = groupby(df, [:Step, :Label2], sort=true);

julia> lev = unique(df.Label1)
2-element Array{Symbol,1}:
 :A
 :B

julia> combine(gdf, :Label1 .=> [x -> count(==(l), x) for l in lev] .=> lev)
6×4 DataFrame
│ Row │ Step  │ Label2 │ A     │ B     │
│     │ Int64 │ Symbol │ Int64 │ Int64 │
├─────┼───────┼────────┼───────┼───────┤
│ 1   │ 1     │ Q      │ 2     │ 1     │
│ 2   │ 1     │ R      │ 0     │ 2     │
│ 3   │ 2     │ Q      │ 0     │ 2     │
│ 4   │ 2     │ R      │ 0     │ 2     │
│ 5   │ 3     │ Q      │ 0     │ 2     │
│ 6   │ 3     │ R      │ 1     │ 3     │

so you have 0 not missing in place where you have a missing value.

This pattern generalizes to multiple groups:

julia> gdf = groupby(df, :Step, sort=true);

julia> l1 = unique(df.Label1)
2-element Array{Symbol,1}:
 :A
 :B

julia> l2 = unique(df.Label2)
2-element Array{Symbol,1}:
 :Q
 :R

julia> combine(gdf, [[:Label1, :Label2] =>
                     ((x,y) -> count(((x,y),) -> x==v1 && y==v2, zip(x,y))) =>
                     Symbol(v1, v2) for v1 in l1 for v2 in l2])
3×5 DataFrame
│ Row │ Step  │ AQ    │ AR    │ BQ    │ BR    │
│     │ Int64 │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┼───────┼───────┤
│ 1   │ 1     │ 2     │ 0     │ 1     │ 2     │
│ 2   │ 2     │ 0     │ 0     │ 2     │ 2     │
│ 3   │ 3     │ 0     │ 1     │ 2     │ 3     │

another way to do it using your original code would be:

julia> unstack(combine(groupby(select(df, :Step, [:Label1, :Label2] => ByRow(Symbol) => :Label), [:Step, :Label]), nrow), :Label, :nrow)
3×5 DataFrame
│ Row │ Step  │ AQ      │ AR      │ BQ     │ BR     │
│     │ Int64 │ Int64?  │ Int64?  │ Int64? │ Int64? │
├─────┼───────┼─────────┼─────────┼────────┼────────┤
│ 1   │ 1     │ 2       │ missing │ 1      │ 2      │
│ 2   │ 2     │ missing │ missing │ 2      │ 2      │
│ 3   │ 3     │ missing │ 1       │ 2      │ 3      │

However, I agree this is not super easy. This issue is tracked in https://github.com/JuliaData/DataFrames.jl/issues/2148 and relatedly https://github.com/JuliaData/DataFrames.jl/issues/2205.

user_12695670
Related