# Balancing groups in DataFrame

**URL:** <https://discourse.julialang.org/t/balancing-groups-in-dataframe/118284>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [August 16, 2024, 6:12pm UTC](https://discourse.julialang.org/t/balancing-groups-in-dataframe/118284 "2024-08-16T18:12:11Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![miguelborrero](https://avatars.discourse-cdn.com/v4/letter/m/eb9ed0/32.png) [@miguelborrero](https://discourse.julialang.org/u/miguelborrero)\
**Post date:** [August 16, 2024, 6:12pm UTC](https://discourse.julialang.org/t/balancing-groups-in-dataframe/118284/1 "2024-08-16T18:12:11Z")

</div>

Quick question: suppose I have a DataFrame of the form

```julia
df = DataFrame(grouping1 = [1, 1, 2, 2], grouping2 = [true, true, true, false])

```

![Captura de pantalla 2024-08-16 a la(s) 20.07.20](https://global.discourse-cdn.com/julialang/original/3X/f/f/ffd6e393d172950a67d201331cbe4ae16fedd32b.png)

Even though, for the grouping1 category: 1, there are no observations with grouping2 value: false, I would like to then count observations forcing a homogenous nesting across grouping1 values so that when I do something along the lines of:

```julia
df = combine(DataFrames.groupby(df, [:grouping1, :grouping2]), nrow)

```

Instead of getting this:  
 ![Captura de pantalla 2024-08-16 a la(s) 20.09.59](https://global.discourse-cdn.com/julialang/original/3X/0/2/02dae6a228c41baee0d143f88da41c2a52acb8c0.png)  
I get what I refer to a _balanced_ df where fro grouping1 value 1, grouping2 has two values too and false would apear to have 0 counts.

This seems like useful when structuring data into nests and I thought there would be some function/option already available but I cant seem to find one.

---

<div class="post-metadata">

**Author:** ![Nathan\_Boyer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nathan_boyer/32/14825_2.png) [@Nathan\_Boyer](https://discourse.julialang.org/u/Nathan_Boyer)\
**Post date:** [August 16, 2024, 8:54pm UTC](https://discourse.julialang.org/t/balancing-groups-in-dataframe/118284/2 "2024-08-16T20:54:37Z")

</div>

🤷

```julia-repl
julia> gdf = groupby(df, :grouping1);

julia> combine(
           gdf,
           :grouping2 => count => :trues,
           :grouping2 => (count ∘ .!) => :falses,
       )
2×3 DataFrame
 Row │ grouping1 trues falses
     │ Int64 Int64 Int64
─────┼──────────────────────────
   1 │ 1 2 0
   2 │ 2 1 1

```

---

<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:** [August 16, 2024, 8:54pm UTC](https://discourse.julialang.org/t/balancing-groups-in-dataframe/118284/3 "2024-08-16T20:54:59Z")

</div>

I would just create a new data frame that has all the combinations present. You can use the function `allcombinations` for this

```julia
julia> df = DataFrame(grouping1 = [1, 1, 2, 2], grouping2 = [true, true, true, false])
4×2 DataFrame
 Row │ grouping1 grouping2 
     │ Int64 Bool      
─────┼──────────────────────
   1 │ 1 true
   2 │ 1 true
   3 │ 2 true
   4 │ 2 false

julia> df_complete = allcombinations(DataFrame, grouping1 = [1, 2], grouping2 = [true, false])
4×2 DataFrame
 Row │ grouping1 grouping2 
     │ Int64 Bool      
─────┼──────────────────────
   1 │ 1 true
   2 │ 2 true
   3 │ 1 false
   4 │ 2 false

julia> df_collapsed = @chain df begin
           groupby([:grouping1, :grouping2])
           combine(nrow)
           leftjoin(df_complete, _, on = [:grouping1, :grouping2])
           @transform :nrow = replace(:nrow, missing => 0)
       end
4×3 DataFrame
 Row │ grouping1 grouping2 nrow  
     │ Int64 Bool Int64 
─────┼─────────────────────────────
   1 │ 1 true 2
   2 │ 2 true 1
   3 │ 2 false 1
   4 │ 1 false 0

```

---

<div class="post-metadata">

**Author:** ![dmbates](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dmbates/32/44_2.png) [@dmbates](https://discourse.julialang.org/u/dmbates)\
**Post date:** [August 17, 2024, 5:20pm UTC](https://discourse.julialang.org/t/balancing-groups-in-dataframe/118284/4 "2024-08-17T17:20:51Z")

</div>

If you are willing to live with `missing` instead of zero counts you can achieve this with

```julia
julia> unstack(
           combine(
                   groupby(
                         df,
                        [:grouping1, :grouping2],
                   ),
                   nrow => :n,
           ),
           :grouping2,
           :n,
    )
2×3 DataFrame
 Row │ grouping1 true false   
     │ Int64 Int64? Int64?  
─────┼────────────────────────────
   1 │ 1 2 missing 
   2 │ 2 1 1

```

I guess to get the result the OP wants you would need to `stack` after unstacking.

---

<div class="post-metadata">

**Author:** ![dmbates](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dmbates/32/44_2.png) [@dmbates](https://discourse.julialang.org/u/dmbates)\
**Post date:** [August 17, 2024, 5:31pm UTC](https://discourse.julialang.org/t/balancing-groups-in-dataframe/118284/5 "2024-08-17T17:31:18Z")

</div>

It’s a bit more compact to use column numbers instead of names in this case.

```julia
julia> stack(unstack(combine(groupby(df, 1:2), nrow => :n), 2, :n), 2:3)
4×3 DataFrame
 Row │ grouping1 variable value   
     │ Int64 String Int64?  
─────┼──────────────────────────────
   1 │ 1 true 2
   2 │ 2 true 1
   3 │ 1 false missing 
   4 │ 2 false 1

```

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [August 17, 2024, 6:16pm UTC](https://discourse.julialang.org/t/balancing-groups-in-dataframe/118284/6 "2024-08-17T18:16:32Z")

</div>

Another option, which might be simpler for non-experts of the DataFrames’ mini-language:

```julia
df_all = allcombinations(DataFrame, grouping1=[1, 2], grouping2=[true, false])
df_all.count = [count(values(c)==values(r) for r in eachrow(df)) for c in eachrow(df_all)]
df_all

```