# DataFrames: is it possible to group data horizontally (multilevel)?

**URL:** <https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239>\
**Category:** Statistics\
**Created:** [April 17, 2019, 2:01pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239 "2019-04-17T14:01:43Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![mike\_k](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mike_k/32/211864_2.png) [@mike\_k](https://discourse.julialang.org/u/mike_k)\
**Post date:** [April 17, 2019, 2:01pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/1 "2019-04-17T14:01:43Z")

</div>

Dear community,  
suppose we have a simple dataframe

```julia
df = DataFrame(Cat1 = ["A", "A", "B", "B"],
               Cat2 = ["C", "D", "C", "D"],
               val1 = 1:4,
               val2 = 5:8)

```

which looks like

```julia
julia> df
4×4 DataFrame
│ Row │ Cat1 │ Cat2 │ val1 │ val2 │
│ │ String │ String │ Int64 │ Int64 │
├─────┼────────┼────────┼───────┼───────┤
│ 1 │ A │ C │ 1 │ 5 │
│ 2 │ A │ D │ 2 │ 6 │
│ 3 │ B │ C │ 3 │ 7 │
│ 4 │ B │ D │ 4 │ 8 │

```

Is it possible to group the df by Cat1 horizontally such that we obtain something like:

```julia
               | A | B
│ Row │ Cat2 │ val1 │ val2 │ val1 │ val2 │
│ │ String │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼────────┼───────┼───────┼───────┼───────┤
│ 1 │ C │ 1 │ 5 │ 1 │ 5 │
│ 2 │ D │ 2 │ 6 │ 2 │ 6 │
│ 3 │ C │ 3 │ 7 │ 3 │ 7 │
│ 4 │ D │ 4 │ 8 │ 4 │ 8 │

```

Thank you in advance!!

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [April 17, 2019, 2:50pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/2 "2019-04-17T14:50:40Z")

</div>

No, that’s not supported. Have a look at IndexedTables’s `NDSparse` type instead.

---

<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:** [April 17, 2019, 2:59pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/3 "2019-04-17T14:59:14Z")

</div>

Can you clarify what you are trying to do with the data in this way? I don’t really understand your second data frame

```julia
               | A | B
│ Row │ Cat2 │ val1 │ val2 │ val1 │ val2 │
│ │ String │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼────────┼───────┼───────┼───────┼───────┤
│ 1 │ C │ 1 │ 5 │ 1 │ 5 │
│ 2 │ D │ 2 │ 6 │ 2 │ 6 │
│ 3 │ C │ 3 │ 7 │ 3 │ 7 │
│ 4 │ D │ 4 │ 8 │ 4 │ 8 │

```

The last two rows in the `A :val2` column don’t make sense because `:val2` never equals `7` or `8` on an `"A"` observation.

---

<div class="post-metadata">

**Author:** ![mike\_k](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mike_k/32/211864_2.png) [@mike\_k](https://discourse.julialang.org/u/mike_k)\
**Post date:** [April 17, 2019, 3:18pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/4 "2019-04-17T15:18:29Z")

</div>

Sorry, this example is maybe oversimplified. In my real application I have a large dataframe with a lot of computational results which depend on several parameters. I obtain these results with different computational models which I like to compare. So I group by parameters and models, e.g.,

```julia
aggdf = by(df, [:model, :param1, :param2, :param3],
            nopt = :opt => sum,
            gap = :gap => mean,
            rt = :rt => mean,
            )

```

This works fine, however, the resulting dataframe lists the results of each model vertically. Since I want to figure out which model performs best for a specific parameter combination, it would be much nicer for me to compare the horizontally (and I would also create a table like that for a publication with multirow/multicol in Latex).  
Clearly, I could split the dataframe accordingly and append them next to each other. But in this case I would also need to rename the columns, e.g., nopt1, gap1,…

---

<div class="post-metadata">

**Author:** ![mike\_k](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mike_k/32/211864_2.png) [@mike\_k](https://discourse.julialang.org/u/mike_k)\
**Post date:** [April 17, 2019, 3:19pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/5 "2019-04-17T15:19:40Z")

</div>

Thank you, I will give it a try. However, I think this would be a nice feature also for DataFrames.

---

<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:** [April 17, 2019, 3:24pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/6 "2019-04-17T15:24:44Z")

</div>

Ah I understand.

I think the best method is just to have `:A_param1`, `:B_param1` etc. in your dataframes. Other alternatives could be to make a custom `struct` which holds all your parameters and have each observation in that column be one of those.

I see your point about exporting to latex. There isn’t a good replacement for something as complicated as R’s [ggtable](https://github.com/briatte/ggtable/blob/master/ggtable.R) which would allow grouped columns like that.

Thinking about adding this feature to DataFrames would probably cause a lot of headaches in an already complicated indexing story, so groupings like this would probably have to be done at the latex printing stage, in another package.

---

<div class="post-metadata">

**Author:** ![mike\_k](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mike_k/32/211864_2.png) [@mike\_k](https://discourse.julialang.org/u/mike_k)\
**Post date:** [April 17, 2019, 3:33pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/7 "2019-04-17T15:33:27Z")

</div>

I see,… thank you.

I was hoping that there is an easier option (although I found nothing in the docs), since I know that at least with pandas such kind of grouping works fine with unstack().

---

<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:** [April 17, 2019, 3:36pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/8 "2019-04-17T15:36:25Z")

</div>

I can see the appeal. DataFrames is currently looking for a better API for `stack` / `melt` as evidenced by [this issue](https://github.com/JuliaData/DataFrames.jl/issues/1736).

---

<div class="post-metadata">

**Author:** ![jacobadenbaum](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jacobadenbaum](https://discourse.julialang.org/u/jacobadenbaum)\
**Post date:** [April 17, 2019, 4:13pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/9 "2019-04-17T16:13:42Z")

</div>

> [@pdeffebach](#):
>
> Thinking about adding this feature to DataFrames would probably cause a lot of headaches in an already complicated indexing story, so groupings like this would probably have to be done at the latex printing stage, in another package.

Shameless self-promotion here, but if you’re looking for a nice way to generate grouped summary stats from Dataframes and print them out to latex, I have a package called TexTables that does this already works fairly well for this sort of stuff. There’s a function called `summarize_by` that might do what you want right out of the box, or it’s fairly easy to use it to construct the tables of summary statistics programatically to look exactly how you want them.

---

<div class="post-metadata">

**Author:** ![mike\_k](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mike_k/32/211864_2.png) [@mike\_k](https://discourse.julialang.org/u/mike_k)\
**Post date:** [April 17, 2019, 4:53pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/10 "2019-04-17T16:53:16Z")

</div>

Thank you, this looks promising at the first glance. I will give it a try.

---

<div class="post-metadata">

**Author:** ![jacobadenbaum](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jacobadenbaum](https://discourse.julialang.org/u/jacobadenbaum)\
**Post date:** [April 17, 2019, 5:11pm UTC](https://discourse.julialang.org/t/dataframes-is-it-possible-to-group-data-horizontally-multilevel/23239/11 "2019-04-17T17:11:25Z")

</div>

If you wind up trying it let me know if there’s anything that it should do that it doesn’t, or if you run into bugs. I’ve been fixing things to fit my use cases over time, but it’s always useful to get feedback from other people.