Excel's pivot table equivalent with DataFramesMeta

General Usage
,
1

Hello,

So I have a table like

df = DataFrame(a= [1,1,2,2], b = [1,2,1,2], c = [1,2,3,4])

And I would like to create a table that, if I were to work in Excel, I could obtain with a pivot table with a for the Rows and b for the Columns. I assume uniqueness in the combinations {a,b} but in Excel you’d have to ask for the sum of c for the Values field.
The result would be as follows:

a  | b=1  b=2
------------------
1  | 1    2
2  | 3    4

I’m trying to reproduce this with DataFrames(Meta) but I can’t figure out how to do that. Is there a built in way to do it or will I have to program it myself ?
For the rows it’s easy, using @by or groupby does it, but working with columns seems less supported.

2

Here’s how to do it using unstack in DataFrames.jl

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

julia> unstack(df, :b, :c; renamecols = v -> "b_$v")
2×3 DataFrame
 Row │ a      b_1     b_2    
     │ Int64  Int64?  Int64? 
─────┼───────────────────────
   1 │     1       1       2
   2 │     2       3       4

For a more complicated prolem, where you have multiple value variables (instead of just :c like you have here), see this thread.

Here is a general explainer for doing “pivot table”-like operations with DataFrames

2 Likes
3

Great ressources. Thanks a lot !