# Custom DataFrame column sort order

**URL:** <https://discourse.julialang.org/t/custom-dataframe-column-sort-order/3716>\
**Category:** Data\
**Tags:** question, sort, dataframes\
**Created:** [May 15, 2017, 1:23pm UTC](https://discourse.julialang.org/t/custom-dataframe-column-sort-order/3716 "2017-05-15T13:23:53Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sylvaticus](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sylvaticus/32/203883_2.png) [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)\
**Post date:** [May 15, 2017, 1:23pm UTC](https://discourse.julialang.org/t/custom-dataframe-column-sort-order/3716/1 "2017-05-15T13:23:53Z")

</div>

Is it possible to [sort](https://dataframesjl.readthedocs.io/en/latest/sorting.html) a DataFrame column based on custom sort order, e.g.

```julia
df = DataFrame(col1= ['a','b','c'], col2 = [1,2,3])
sort!(df, col1=custom_order('a','c','b'))

```

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [May 15, 2017, 6:29pm UTC](https://discourse.julialang.org/t/custom-dataframe-column-sort-order/3716/2 "2017-05-15T18:29:36Z")

</div>

You can always use

```julia
df[p, :]

```

where `p` is a permutation that encodes your custom order, but if it can be specified by some user-supplied less-than function, you are better off using the `sort` method for `DataFrame`.

---

<div class="post-metadata">

**Author:** ![sylvaticus](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sylvaticus/32/203883_2.png) [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)\
**Post date:** [May 16, 2017, 8:38am UTC](https://discourse.julialang.org/t/custom-dataframe-column-sort-order/3716/3 "2017-05-16T08:38:57Z")

</div>

Elaborating on [this SO question](http://stackoverflow.com/questions/37932963/efficient-custom-ordering-in-julia-dataframes), I am trying to use a custom less-than function, to be used with the `lt` sort keyword, but I don’t know how to provide to the function a third parameter that is the wanted “custom order” (for multiple column sort):

For example, this works:

```julia
using DataFrames
df = DataFrame(
  c1 = ['a','b','c','a','b','c'],
  c2 = ["aa","aa","bb","bb","cc","cc"],
  c3 = [1,2,3,10,20,30]
)
6×3 DataFrames.DataFrame
│ Row │ c1 │ c2 │ c3 │
├─────┼─────┼──────┼────┤
│ 1 │ 'a' │ "aa" │ 1 │
│ 2 │ 'b' │ "aa" │ 2 │
│ 3 │ 'c' │ "bb" │ 3 │
│ 4 │ 'a' │ "bb" │ 10 │
│ 5 │ 'b' │ "cc" │ 20 │
│ 6 │ 'c' │ "cc" │ 30 │

ordc2 = ["bb","aa","cc"]
function customLt(r1,r2)
    return ( find(x -> x == r1, ordc2)[1] < find(x -> x == r2, ordc2)[1] )
end
sortedDf = sort(df, cols = [order(:c2, lt=customLt)])
6×3 DataFrames.DataFrame
│ Row │ c1 │ c2 │ c3 │
├─────┼─────┼──────┼────┤
│ 1 │ 'c' │ "bb" │ 3 │
│ 2 │ 'a' │ "bb" │ 10 │
│ 3 │ 'a' │ "aa" │ 1 │
│ 4 │ 'b' │ "aa" │ 2 │
│ 5 │ 'b' │ "cc" │ 20 │
│ 6 │ 'c' │ "cc" │ 30 │

```

But this doesn’t:

```julia
using DataFrames
df = DataFrame(
  c1 = ['a','b','c','a','b','c'],
  c2 = ["aa","aa","bb","bb","cc","cc"],
  c3 = [1,2,3,10,20,30]
)
ordc1 = ['b','a','c']
ordc2 = ["bb","aa","cc"]
function customLt(r1,r2,col)
    return ( find(x -> x == r1, col)[1] < find(x -> x == r2, col)[1] )
end
sortedDf = sort(df, cols = [order(:c2, lt=customLt(ordc2)),order(:c1, lt=customLt(ordc1))] )

MethodError: no method matching customLt(::Array{String,1})
Closest candidates are:
  customLt(::Any, !Matched::Any) at /home/lobianco/git/ffsm_pp/00_private/2016_foretcc/data/output/test.jl:59
  customLt(::Any, !Matched::Any, !Matched::Any) at /home/lobianco/git/ffsm_pp/00_private/2016_foretcc/data/output/test.jl:73
[...]

```

---

<div class="post-metadata">

**Author:** ![Tamas\_Papp](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tamas_papp/32/25949_2.png) [@Tamas\_Papp](https://discourse.julialang.org/u/Tamas_Papp)\
**Post date:** [May 16, 2017, 9:11am UTC](https://discourse.julialang.org/t/custom-dataframe-column-sort-order/3716/4 "2017-05-16T09:11:59Z")

</div>

This should be faster for lookup:

```julia
using DataFrames
df = DataFrame(
  c1 = ['a','b','c','a','b','c'],
  c2 = ["aa","aa","bb","bb","cc","cc"],
  c3 = [1,2,3,10,20,30]
)
ordc2 = ["bb","aa","cc"]
orderdict = Dict(x => i for (i,x) in enumerate(ordc2))
sort(df; cols = [:c2], by = x->orderdict[x])

```

---

<div class="post-metadata">

**Author:** ![sylvaticus](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sylvaticus/32/203883_2.png) [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)\
**Post date:** [May 16, 2017, 12:50pm UTC](https://discourse.julialang.org/t/custom-dataframe-column-sort-order/3716/5 "2017-05-16T12:50:47Z")

</div>

Thank you… I managed to implement it for multiple columns and to place it with a function where users can select custom order (eventually partially) for each of the columns:

```julia
function customSort!(df::DataFrame, sortops)
    sortv = []
    sortOptions = []
    if(isa(sortops, Array))
        sortv = sortops
    else
        push!(sortv,sortops)
    end
    for i in sortv
        if(isa(i, Tuple))
            if (isa(i[2], Array)) # The second option is a custom order
                orderArray = Array(collect(union( OrderedSet(i[2]), OrderedSet(unique(df[i[1]])) )))
                push!(sortOptions, order(i[1], by = x->Dict(x => i for (i,x) in enumerate(orderArray))[x] ))
            else # The second option is a reverse direction flag
                push!(sortOptions, order(i[1], rev = i[2]))
            end
        else
          push!(sortOptions, order(i))
        end
    end
    return sort!(df, cols = sortOptions)
end

df = DataFrame(
  c1 = ['a','b','c','a','b','c'],
  c2 = ["aa","aa","bb","bb","cc","cc"],
  c3 = [1,2,3,10,20,30],
)
customSort!(df, [(:c2,["bb","cc"]),(:c1,['b','a','c'])])
6×3 DataFrames.DataFrame
│ Row │ c1 │ c2 │ c3 │
├─────┼─────┼──────┼────┤
│ 1 │ 'a' │ "bb" │ 10 │
│ 2 │ 'c' │ "bb" │ 3 │
│ 3 │ 'b' │ "cc" │ 20 │
│ 4 │ 'c' │ "cc" │ 30 │
│ 5 │ 'b' │ "aa" │ 2 │
│ 6 │ 'a' │ "aa" │ 1 │

```

if someone needs it, I uploaded to github together with other utility functions on [GitHub - sylvaticus/LAJuliaUtils.jl: Utility functions for Julia, mainly dataframes operations](https://github.com/sylvaticus/LAJuliaUtils.jl)

To use it:

```julia
Pkg.clone("https://github.com/sylvaticus/LAJuliaUtils.jl.git")
using LAJuliaUtils
?customSort!

```

---

<div class="post-metadata">

**Author:** ![tshort](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tshort/32/43_2.png) [@tshort](https://discourse.julialang.org/u/tshort)\
**Post date:** [May 16, 2017, 1:41pm UTC](https://discourse.julialang.org/t/custom-dataframe-column-sort-order/3716/6 "2017-05-16T13:41:22Z")

</div>

Another way to tackle this problem is to use a column type where you can control the sorting. The advantage comes when sorting is done behind the scenes (with grouping or plotting for example); the order will come out like you want.

Here is an example with [PooledArrays.jl](https://github.com/JuliaComputing/PooledArrays.jl):

```julia
julia> d = PooledArray(["a", "b", "a"], ["b", "a"])
3-element PooledArrays.PooledArray{String,UInt32,1,Array{UInt32,1}}:
 "a"
 "b"
 "a"

julia> sort(d)
3-element PooledArrays.PooledArray{String,UInt32,1,Array{UInt32,1}}:
 "b"
 "a"
 "a"

```

Note that this trick doesn’t work with `DataArrays`. I didn’t check if it works with `CategoricalArrays`.