# DataFrame how to \`groupby\` then index with unspecified keys (merge them)

**URL:** <https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248>\
**Category:** General Usage\
**Tags:** question, dataframes\
**Created:** [November 14, 2022, 5:06pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248 "2022-11-14T17:06:22Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![jling](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jling/32/212909_2.png) [@jling](https://discourse.julialang.org/u/jling)\
**Post date:** [November 14, 2022, 5:06pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/1 "2022-11-14T17:06:22Z")

</div>

```julia
gdf = groupby(df, [:gender, :nationality, :hair_color]);

```

we know we can `gdf[(; gender = :X, natiaonlity = :US, hair_color=:blue)]` to get a specific subdataframe, but this doesn’t work:

```julia
gdf[(; gender = :X, natiaonlity = :US)]

```

the desired behavior is to merge all `hair_color` variations

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [November 14, 2022, 5:59pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/2 "2022-11-14T17:59:54Z")

</div>

I’m not sure I understand what you’re looking for, but maybe if you do a looser groupby you’ll have the “merge” you want

```julia
gdf_hair_color = groupby(df, [:gender, :nationality])

```

---

<div class="post-metadata">

**Author:** ![jling](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jling/32/212909_2.png) [@jling](https://discourse.julialang.org/u/jling)\
**Post date:** [November 14, 2022, 6:21pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/3 "2022-11-14T18:21:15Z")

</div>

yeah but I SOMETIMES want the `hair_color`, I want to avoid making a new gdf for every single combination of columns I need.

---

<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:** [November 14, 2022, 6:26pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/4 "2022-11-14T18:26:24Z")

</div>

I think this has been asked before and this is not possible. You will need to re-group I think.

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [November 14, 2022, 6:32pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/5 "2022-11-14T18:32:44Z")

</div>

Yes I recall @bkamins answering this before as well, unfortunately the discourse search is hopeless so I can’t find the thread (or maybe it was just on slack)

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [November 14, 2022, 6:36pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/6 "2022-11-14T18:36:16Z")

</div>

then something like this

```julia
using DataFrames

df=DataFrame(g=rand(["m","f"], 20), n=rand(1:5, 20), hc=rand(['b','w','B','g'],20), val=rand(20))
gdf = groupby(df, [:g, :n, :hc])

df[df.g.=="f" .&& df.n.==5,:]

```

?

or one of these

```julia

filter(x->x.g[1]=="f" && x.n[1]==5, gdf)
filter(x->x.g[1]=="f" && x.n[1]==5, gdf, ungroup=true)

```

?

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [November 14, 2022, 6:44pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/7 "2022-11-14T18:44:39Z")

</div>

> [@jling](#):
>
> I want to avoid making a new gdf for every single combination of columns I need.

This will be most efficient in general. Why do you want to avoid it?

---

<div class="post-metadata">

**Author:** ![jling](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jling/32/212909_2.png) [@jling](https://discourse.julialang.org/u/jling)\
**Post date:** [November 14, 2022, 9:46pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/8 "2022-11-14T21:46:39Z")

</div>

cuz I’m doing explorational work and looking at many potential feature combos?

---

<div class="post-metadata">

**Author:** ![bkamins](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bkamins/32/208538_2.png) [@bkamins](https://discourse.julialang.org/u/bkamins)\
**Post date:** [November 14, 2022, 10:25pm UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/9 "2022-11-14T22:25:27Z")

</div>

> [@jling](#):
>
> I’m doing explorational work and looking at many potential feature combos

Then if you find a case when doing grouping several times on different columns is inconvenient please let me know and we will think if we can add what you ask for (note though that it will not be fast as it will have O(number of groups) cost, as opposed to O(1) cost of current lookup).

---

<div class="post-metadata">

**Author:** ![jling](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jling/32/212909_2.png) [@jling](https://discourse.julialang.org/u/jling)\
**Post date:** [November 15, 2022, 12:50am UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/10 "2022-11-15T00:50:22Z")

</div>

> [@bkamins](#):
>
> inconvenient

I mean it is inconvenient simply because I have to type and name my gdf a different name every time; not anything else like I would have:

```julia
gdf1= groupby(df, [:gender])
gdf2 = groupby(df, [:gender, :nationality])
gdf3 = groupby(df, [:gender, :nationality, :hair_color])
gdf4 = groupby(df, [:gender, :hair_color])
gdf5 = groupby(df,[:nationality, :hair_color])

```

> [@bkamins](#):
>
> note though that it will not be fast as

I don’t think people group and look up groups in a loop? Isn’t it far more likely that people:

> make split apply combine in a loop (so one groupby per iteration)

---

<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:** [November 15, 2022, 1:46am UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/11 "2022-11-15T01:46:52Z")

</div>

This should be useful.

```julia
julia> function regroup(gd; kwargs...)
           omitted_keys = setdiff(groupcols(gd), keys(kwargs))
           new_keys = Any[]
           for k in keys(gd)
               res = false
               for c in keys(kwargs)
                   if k[c] == kwargs[c]
                       push!(new_keys, k)
                   end
               end
           end
           gd[new_keys]
       end

```

overwriting `getindex` should be trivial from here.

---

<div class="post-metadata">

**Author:** ![jling](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jling/32/212909_2.png) [@jling](https://discourse.julialang.org/u/jling)\
**Post date:** [November 15, 2022, 3:02am UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/12 "2022-11-15T03:02:09Z")

</div>

thanks but I really just want to know if there’s something already exist; I don’t have a good place to put this utility function

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [November 15, 2022, 3:59am UTC](https://discourse.julialang.org/t/dataframe-how-to-groupby-then-index-with-unspecified-keys-merge-them/90248/13 "2022-11-15T03:59:22Z")

</div>

For general Julia collections and for many table types, see `group` + `addmargins` from [FlexiGroups.jl](https://discourse.julialang.org/t/ann-flexigroups-jl-composable-and-general-dataset-group-bys/88998):

```julia
using FlexiGroups

tbl = ...
gm = group(x -> (;x.gender, x.nationality, x.hair_color), tbl) |> addmargins
# these should work - use `total` to select
# the group containing all values of the corresponding parameter:
gm[(; gender = :X, natiaonlity = :US, hair_color=:blue)]
gm[(; gender = :X, natiaonlity = :US, hair_color=total)]
gm[(; gender = total, natiaonlity = :US, hair_color=total)]

```

For dense multidimensional grouping (as your case seems to be), keyed arrays can be convenient instead of dictionaries (the default). See the docs, `FlexiGroups` support this as well.

I don’t think `DataFrames` and `FlexiGroups` work together though, so the above can only indirectly be applied in your specific case as you start from a `DataFrame`.