# How DataFramesMeta @by works if you want to groupby two columns?

**URL:** <https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305>\
**Category:** New to Julia\
**Tags:** dataframes, dataframesmeta\
**Created:** [October 26, 2022, 2:37pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305 "2022-10-26T14:37:12Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [October 26, 2022, 2:37pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/1 "2022-10-26T14:37:12Z")

</div>

Hi all, I was reading the DataFramesMeta docu trying to solve a small issue that I have, but I couldn’t find the answer. Any help is welcome!

Basically, I have a df that looks like this:

```julia
df = DataFrame(
            a = repeat(1:4, outer = 2),
            b = ["a", "b", "c", "d", "e", "f", "g", "h"],
            c = [23,76,9,90,123,67,13,5])

```

And what I am looking for is getting a df that only holds the maximum value of ` c` for each repeated value of `a`, and the value of `b` that correspond to that row. Something like this:

```julia
|a| b| c|
|-|---|---|
|1|"e"|123|
|2|"b"| 76|
|3|"g"| 13|
|4|"d"| 90|

```

I already know that I can use this line to get the first and last column:

```julia
@by(df, :a, :c= maximum(:c))

```

But I don’t really know how to add the corresponding line of `b` for each value of `a`. I tried a couple of things, but none worked.

Is there an easy way of doing this?

Thanks a lot!

---

<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:** [October 26, 2022, 3:05pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/2 "2022-10-26T15:05:30Z")

</div>

I don’t use DataFramesMeta but in base DataFrames you can write

```julia
julia> combine(groupby(df, :a), 
    :c => maximum => :c, 
    [:b, :c] => ((b, c) -> b[findmax(c)[2]]) => :b)
4×3 DataFrame
 Row │ a c b
     │ Int64 Int64 String
─────┼──────────────────────
   1 │ 1 123 e
   2 │ 2 76 b
   3 │ 3 13 g
   4 │ 4 90 d

```

---

<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:** [October 26, 2022, 3:09pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/3 "2022-10-26T15:09:35Z")

</div>

You can use the `@astable` macro-flag for this.

```julia
julia> df = DataFrame(
                   a = repeat(1:4, outer = 2),
                   b = ["a", "b", "c", "d", "e", "f", "g", "h"],
                   c = [23,76,9,90,123,67,13,5]);

julia> @by df :a @astable begin 
           maxval, ind = findmax(:c)
           :c = maxval
           :b = :b[ind]
       end
4×3 DataFrame
 Row │ a c b      
     │ Int64 Int64 String 
─────┼──────────────────────
   1 │ 1 123 e
   2 │ 2 76 b
   3 │ 3 13 g
   4 │ 4 90 d

```

---

<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:** [October 26, 2022, 4:50pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/4 "2022-10-26T16:50:58Z")

</div>

Also you can just do:

```julia
julia> @by(df, :a, :c = maximum(:c), :b = :b[argmax(:c)])
4×3 DataFrame
 Row │ a c b
     │ Int64 Int64 String
─────┼──────────────────────
   1 │ 1 123 e
   2 │ 2 76 b
   3 │ 3 13 g
   4 │ 4 90 d

```

(it will be slower than what @pdeffebach proposed, but maybe simpler to read)

---

<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:** [October 26, 2022, 5:28pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/5 "2022-10-26T17:28:49Z")

</div>

Ah of course, I also should have used `argmax(c)` instead of `findmax(c)[2]`.

---

<div class="post-metadata">

**Author:** ![Juan\_Mac\_Donagh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/juan_mac_donagh/32/31798_2.png) [@Juan\_Mac\_Donagh](https://discourse.julialang.org/u/Juan_Mac_Donagh)\
**Post date:** [October 26, 2022, 6:51pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/6 "2022-10-26T18:51:51Z")

</div>

Thank you all!! All of the answers work for me.

Best!  
Juan

---

<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:** [October 30, 2022, 6:06pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/7 "2022-10-30T18:06:30Z")

</div>

```julia
g=groupby(df,:a)
combine(g, [:b,:c]=>((x,y)->[argmax(last,zip(x,y))])=>[:b,:c])

```

_argmax(f, domain)_

_Return a value `x` in the domain of `f` for which `f(x)` is maximised._

---

<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:** [October 30, 2022, 6:26pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/8 "2022-10-30T18:26:22Z")

</div>

I made a couple of attempts to use dataframesmeta, but the second failed.

```julia
julia> @by df :a @astable begin
           x, y = argmax(last,zip(:b,:c))
           :b=x
           :c=y
       end
4×3 DataFrame
 Row │ a b c     
     │ Int64 String Int64
─────┼──────────────────────
   1 │ 1 e 123
   2 │ 2 b 76
   3 │ 3 g 13
   4 │ 4 d 90

julia> @by df :a @astable begin
           :b, :c = argmax(last,zip(:b,:c))
       end
0×1 DataFrame

```

this works (or almost)

```julia
@by df :a @astable :bc=argmax(last,zip(:b,:c))

# 4×2 DataFrame
# Row │ a bc
# │ Int64 Tuple…
# ─────┼───────────────────
# 1 │ 1 ("e", 123)
# 2 │ 2 ("b", 76)
# 3 │ 3 ("g", 13)
# 4 │ 4 ("d", 90)

```

---

<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:** [October 30, 2022, 10:02pm UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/9 "2022-10-30T22:02:41Z")

</div>

Yes,

```julia
:b, :c = argmax(last,zip(:b,:c))

```

is not supported.

I think the [docs](https://juliadata.github.io/DataFramesMeta.jl/stable/#Creating-multiple-columns-at-once-with-@astable) are clear here:

> In a single block, all assignments of the form `:y = f(:x)` or `$y = f(:x)` at the top-level generate new columns. In the second form, `y` must be a string or `Symbol`.

It says nothing about tuple assignment.

If you think this would be a good feature, please file an issue to track it. There might be an implementation that tracks _all_ assignments and keeps track of them, but the implementation was difficult so I make no guarantees it will work.

---

<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:** [October 31, 2022, 7:55am UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/10 "2022-10-31T07:55:58Z")

</div>

I don’t have a very strong belief on the subject.  
I don’t know how “important” this use case is.  
However, it seems to me a “natural” extension of the basic syntax.  
I have no idea how much complexity implementing such a feature implies.  
If the idea is implementable, it should be implemented in such a way as to be able to manage a generic numeric of variables.  
I insert the example of 3 variables to make the idea …

```julia
julia> df = DataFrame( a = repeat(1:4, outer = 2),
                          b = ["a", "b", "c", "d", "e", "f", "g", "h"],   
                          c=rand(1:10,8),
                          d = [23,76,9,90,123,67,13,5])
8×4 DataFrame
 Row │ a b c d     
     │ Int64 String Int64 Int64
─────┼─────────────────────────────
   1 │ 1 a 1 23
   2 │ 2 b 3 76
   3 │ 3 c 6 9
   4 │ 4 d 10 90
   5 │ 1 e 1 123
   6 │ 2 f 7 67
   7 │ 3 g 9 13
   8 │ 4 h 10 5

julia> 

julia> @by df :a @astable begin
              :d=argmax(last,zip(:b,:c,:d))
          end
4×2 DataFrame
 Row │ a d
     │ Int64 Tuple…
─────┼──────────────────────
   1 │ 1 ("e", 1, 123)
   2 │ 2 ("b", 3, 76)
   3 │ 3 ("g", 9, 13)
   4 │ 4 ("d", 10, 90)

julia> @by df :a @astable begin
              x=argmax(last,zip(:b,:c,:d))
              :b=x[1]
              :c=x[2]
              :d=x[3]
          end
4×4 DataFrame
 Row │ a b c d     
     │ Int64 String Int64 Int64
─────┼─────────────────────────────
   1 │ 1 e 1 123
   2 │ 2 b 3 76
   3 │ 3 g 9 13
   4 │ 4 d 10 90

julia> @by df :a @astable begin
             :b,:c,:d=argmax(last,zip(:b,:c,:d))
          end
0×1 DataFrame
# In any case, this should give a warning about the correct use of the syntax

```

---

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.julialang.org/u/jules)\
**Post date:** [October 31, 2022, 10:33am UTC](https://discourse.julialang.org/t/how-dataframesmeta-by-works-if-you-want-to-groupby-two-columns/89305/11 "2022-10-31T10:33:14Z")

</div>

> [@rocco\_sprmnt21](#):
>
> ```julia
> julia> @by df :a @astable begin
> :b,:c,:d=argmax(last,zip(:b,:c,:d))
> end
> 0×1 DataFrame
> 
> ```

Hm weird I thought DataFramesMeta.jl and DataFrameMacros.jl worked the same way here, but apparently not?

```julia
using DataFrameMacros

df = DataFrame(a = repeat(1:4, outer = 2),
               b = ["a", "b", "c", "d", "e", "f", "g", "h"],   
               c=rand(1:10,8),
               d = [23,76,9,90,123,67,13,5])

julia> @combine groupby(df, :a) @astable :b,:c,:d = argmax(last,zip(:b,:c,:d))
4×4 DataFrame
 Row │ a b c d     
     │ Int64 String Int64 Int64 
─────┼─────────────────────────────
   1 │ 1 e 8 123
   2 │ 2 b 5 76
   3 │ 3 g 7 13
   4 │ 4 d 1 90

```