# Excel's pivot table equivalent with DataFramesMeta

**URL:** <https://discourse.julialang.org/t/excels-pivot-table-equivalent-with-dataframesmeta/123456>\
**Category:** General Usage\
**Tags:** excel, dataframesmeta\
**Created:** [December 4, 2024, 2:16pm UTC](https://discourse.julialang.org/t/excels-pivot-table-equivalent-with-dataframesmeta/123456 "2024-12-04T14:16:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![HenriDeh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/henrideh/32/8316_2.png) [@HenriDeh](https://discourse.julialang.org/u/HenriDeh)\
**Post date:** [December 4, 2024, 2:16pm UTC](https://discourse.julialang.org/t/excels-pivot-table-equivalent-with-dataframesmeta/123456/1 "2024-12-04T14:16:01Z")

</div>

Hello,

So I have a table like

```julia
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:

```julia
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.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [December 4, 2024, 2:24pm UTC](https://discourse.julialang.org/t/excels-pivot-table-equivalent-with-dataframesmeta/123456/2 "2024-12-04T14:24:17Z")

</div>

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

```julia
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](https://discourse.julialang.org/t/how-best-to-transform-a-huge-dataframe-into-wide-format/123415/1) thread.

[Here](https://bkamins.github.io/julialang/2023/05/19/pivot.html) is a general explainer for doing “pivot table”-like operations with DataFrames

---

<div class="post-metadata">

**Author:** ![HenriDeh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/henrideh/32/8316_2.png) [@HenriDeh](https://discourse.julialang.org/u/HenriDeh)\
**Post date:** [December 4, 2024, 2:44pm UTC](https://discourse.julialang.org/t/excels-pivot-table-equivalent-with-dataframesmeta/123456/3 "2024-12-04T14:44:33Z")

</div>

Great ressources. Thanks a lot !