# Sort DataFrame by the greater of multiple columns

**URL:** <https://discourse.julialang.org/t/sort-dataframe-by-the-greater-of-multiple-columns/92481>\
**Category:** New to Julia\
**Tags:** question, sort, dataframes\
**Created:** [January 4, 2023, 2:43am UTC](https://discourse.julialang.org/t/sort-dataframe-by-the-greater-of-multiple-columns/92481 "2023-01-04T02:43:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [January 4, 2023, 2:43am UTC](https://discourse.julialang.org/t/sort-dataframe-by-the-greater-of-multiple-columns/92481/1 "2023-01-04T02:43:00Z")

</div>

Is there a way to `sort` a `DataFrame` by the greater of multiple columns e.g.

```julia
df = DataFrame(A = [21,22,18,19], B = [18,17,25,30])
4×2 DataFrame
 Row │ A B     
     │ Int64 Int64 
─────┼──────────────
   1 │ 21 18
   2 │ 22 17
   3 │ 18 25
   4 │ 19 30

```

`df` should be sorted such that the row with the greater of `df.A` and `df.B` should be placed first. The desired outcome would be

```julia
4×2 DataFrame
 Row │ A B     
     │ Int64 Int64 
─────┼──────────────
   1 │ 19 30
   2 │ 18 25
   3 │ 22 17
   4 │ 21 18

```

I haven’t been able to find anything in the `sort` documentation that would facilitate this so any thoughts would be appreciated. Thanks!

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [January 4, 2023, 3:05am UTC](https://discourse.julialang.org/t/sort-dataframe-by-the-greater-of-multiple-columns/92481/2 "2023-01-04T03:05:29Z")

</div>

One method might be:

```julia
julia> df[sortperm(max.(df.A, df.B); rev=true),:]
4×2 DataFrame
 Row │ A B     
     │ Int64 Int64 
─────┼──────────────
   1 │ 19 30
   2 │ 18 25
   3 │ 22 17
   4 │ 21 18

```

and to mutate `df` according to new order:

```julia
permute!(df, sortperm(max.(df.A, df.B); rev=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:** [January 4, 2023, 6:06am UTC](https://discourse.julialang.org/t/sort-dataframe-by-the-greater-of-multiple-columns/92481/3 "2023-01-04T06:06:27Z")

</div>

This is the approach for plain DataFrames.jl. With DataFramesMeta.jl do:

```julia
julia> using DataFramesMeta

julia> @orderby(df, -max.(:A, :B))
4×2 DataFrame
 Row │ A B
     │ Int64 Int64
─────┼──────────────
   1 │ 19 30
   2 │ 18 25
   3 │ 22 17
   4 │ 21 18

```

---

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [January 4, 2023, 11:41am UTC](https://discourse.julialang.org/t/sort-dataframe-by-the-greater-of-multiple-columns/92481/4 "2023-01-04T11:41:47Z")

</div>

Thanks so much these solutions they are super helpful! Sorry it took me a little longer to understand how `sortperm` was working here. Just to clarify `sortperm` is returning a vector of row indices ordered by the row wise max of the values in the two columns?

Also it won’t make a practical difference in this example since they are both so fast, so I understand if you don’t have time to go into it, but I was just wondering why the difference in speed of the two approaches appears relatively large.

```julia
# sortperm indexing approach
@benchmark df[sortperm(max.(df.A, df.B); rev=true),:] 
BenchmarkTools.Trial: 10000 samples with 10 evaluations.
 Range (min … max): 1.462 μs … 4.633 μs ┊ GC (min … max): 0.00% … 0.00%
 Time (median): 1.500 μs ┊ GC (median): 0.00%
 Time (mean ± σ): 1.518 μs ± 84.809 ns ┊ GC (mean ± σ): 0.00% ± 0.00%

     ██ ▃▁                                                    
  ▂▆▇█████▅▆▅▃▃▃▂▃▃▃▃▃▂▃▃▂▂▂▂▂▂▁▂▂▁▂▂▂▂▁▂▂▁▂▂▂▁▂▁▁▂▂▂▂▂▂▂▂▂▂ ▃
  1.46 μs Histogram: frequency by time 1.86 μs <

 Memory estimate: 1.14 KiB, allocs estimate: 15.

# DataFramesMeta orderby approach
@benchmark @orderby(df, -max.(:A, :B))
BenchmarkTools.Trial: 10000 samples with 1 evaluation.
 Range (min … max): 16.791 μs … 90.875 μs ┊ GC (min … max): 0.00% … 0.00%
 Time (median): 17.292 μs ┊ GC (median): 0.00%
 Time (mean ± σ): 17.402 μs ± 970.047 ns ┊ GC (mean ± σ): 0.00% ± 0.00%

            ▅ ▆▇▃ █ ▃▅ ▄▃ ▁                                    
  ▂▂▁▂▃▁▅▇▅▇█▁███▆█▆██▁██▇▅█▄▆▇▇▁▆▅▃▅▃▄▅▄▁▄▃▂▃▃▁▃▂▁▃▂▂▂▂▁▂▂▁▂▂ ▄
  16.8 μs Histogram: frequency by time 18.5 μs <

 Memory estimate: 9.41 KiB, allocs estimate: 164.

```

and with a larger number of rows in the `DataFrame`

```julia
df6 = DataFrame( A = rand(Int,1000000), B = rand(Int,1000000))

# DataFramesMeta orderby method
@benchmark @orderby(df6, -max(:A,:B))
BenchmarkTools.Trial: 39 samples with 1 evaluation.
 Range (min … max): 122.419 ms … 148.830 ms ┊ GC (min … max): 0.00% … 0.00%
 Time (median): 128.563 ms ┊ GC (median): 0.00%
 Time (mean ± σ): 129.022 ms ± 4.472 ms ┊ GC (mean ± σ): 0.00% ± 0.00%

                █                                                
  ▄▁▁▇▁▆▁▄▁▇▄▆▆▇█▇▁▁▇▁▄▄▄▁▁▁▁▁▁▆▁▁▁▄▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▄ ▁
  122 ms Histogram: frequency by time 149 ms <

 Memory estimate: 90.90 MiB, allocs estimate: 65116.
 
# sortperm indexing method 
@benchmark df6[sortperm(max.(df6.A, df6.B); rev=true),:]
BenchmarkTools.Trial: 60 samples with 1 evaluation.
 Range (min … max): 82.176 ms … 91.108 ms ┊ GC (min … max): 0.00% … 0.00%
 Time (median): 84.036 ms ┊ GC (median): 0.00%
 Time (mean ± σ): 84.381 ms ± 1.486 ms ┊ GC (mean ± σ): 0.00% ± 0.00%

               █▂                                              
  ▄▃▃▁▃▁▁▁▃▃▃████▅▃▄▄▃▄▃▅▁▁▁▁▁▁▁▁▁▃▁▃▁▃▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▃ ▁
  82.2 ms Histogram: frequency by time 90.2 ms <

 Memory estimate: 30.52 MiB, allocs estimate: 2

```

---

<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:** [January 4, 2023, 12:21pm UTC](https://discourse.julialang.org/t/sort-dataframe-by-the-greater-of-multiple-columns/92481/5 "2023-01-04T12:21:25Z")

</div>

The difference in performance is because solution using `sortperm` is a custom approach and `@orderby` uses a more general mechanism (thus it is less optimized).

---

<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:** [January 4, 2023, 2:27pm UTC](https://discourse.julialang.org/t/sort-dataframe-by-the-greater-of-multiple-columns/92481/6 "2023-01-04T14:27:28Z")

</div>

I’m not sure that’s the case. I can replicate the benchmark:

```julia

julia> nomac(df6) = df6[sortperm(@.(-max(df6.A, df6.B))),:];

julia> mac(df6) = @orderby(df6, @. -max(:A,:B));

julia> @btime mac($df6);
  224.913 ms (65104 allocations: 90.91 MiB)

julia> @btime nomac($df6);
  151.738 ms (24 allocations: 30.52 MiB)

```

but looking at the [implementation](https://github.com/JuliaData/DataFramesMeta.jl/blob/8ddc1243dd2d5b54f917b5ae466761023e781702/src/macros.jl#L1228), it does use `sortperm`.

```julia
function orderby_helper(x, args...)
    x, exprs, outer_flags, kw = get_df_args_kwargs(x, args...; wrap_byrow = false)
    t = (fun_to_vec(ex; gensym_names = true, outer_flags = outer_flags) for ex in exprs)
    quote
        $orderby($x, $(t...); $(kw...))
    end
end

function orderby(x::AbstractDataFrame, @nospecialize(args...))
    t = DataFrames.select(x, args...; copycols = false)
    x[sortperm(t), :]
end

```

the difference is too big to be due to the overhead of `select`, especially with `copycols = false`. So I don’t know what’s going on.

EDIT: I see, it’s the difference between `sortperm(v::Vector)` and `sortperm(df::DataFrame)`. The latter is what `@orderby` calls, and this requires extra work in case the DataFrame has multiple columns.

There is room for a specialized implementation if the `DataFrame` has one column, which I think DataFramesMeta.jl can do.