# JuliaDB: efficiently rank subgroups

**URL:** <https://discourse.julialang.org/t/juliadb-efficiently-rank-subgroups/24299>\
**Category:** Data\
**Created:** [May 17, 2019, 2:26am UTC](https://discourse.julialang.org/t/juliadb-efficiently-rank-subgroups/24299 "2019-05-17T02:26:12Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jade\_mackay](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jade_mackay/32/6659_2.png) [@jade\_mackay](https://discourse.julialang.org/u/jade_mackay)\
**Post date:** [May 17, 2019, 2:26am UTC](https://discourse.julialang.org/t/juliadb-efficiently-rank-subgroups/24299/1 "2019-05-17T02:26:12Z")

</div>

Hi, I would be grateful for some advice on how to create a column containing the ordinal rank of a score grouped by another column. For example:

```julia
using JuliaDB
using StatsBase

Nx, Ny = 4, 3
x = repeat(1:Nx,inner=Ny) # location
y = repeat(1:Ny,outer=Nx) # date
z = rand(Nx*Ny) # score
t = table((x=x,y=y,z=z))

trk = setcol(t,:rk, (:z,:x) => row -> begin
             t2 = filter(i->i.x == row.x,t)
             X = select(t2,:z)
             idx = findfirst(x->x==row.z,X)
             rk = ordinalrank(X,rev=true)[idx]
             rk
             end
             )

```

I would like to perform something like the above example on 1-5 million row tables, however it does not scale so well. My initial queries are:

- Is there a more sensible implementation?
- Should I parallelize it as per [Parallel Computing · The Julia Language](https://docs.julialang.org/en/v1/manual/parallel-computing/)?
- Or is it better to let JuliaDB handle parallelization by itself?  
Thanks!

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [May 19, 2019, 10:54pm UTC](https://discourse.julialang.org/t/juliadb-efficiently-rank-subgroups/24299/2 "2019-05-19T22:54:34Z")

</div>

It looks like you are looking for a `groupby`:

```julia
# compute the rank by x and y
trk = groupby(t, (:x, :y), select = :z, flatten = true) do v
    Columns(rk = ordinalrank(v, rev=true), z=v)
end

```

You need to wrap things that you return in a `Columns` because you need to return something that iterates rows to be able to flatten correctly.

Note that in general, if the first two columns represent location and date, you may want to use them as primary keys, so `t1 = reindex(t, (:x, :y))` and then you work on it. Grouping there defaults on the primary keys, so you would just need:

```julia
trk = groupby(t1, select = :z, flatten = true) do v
    Columns(rk = ordinalrank(v, rev=true), z=v)
end

```

To parallelize, yes, you would just start julia with say 4 processes, then do:

```julia
t1 = reindex(t, (:x, :y))
t_dist = distribute(t1, 4) # or whichever number of chunks works best
trk = groupby(t_dist, select = :z, flatten = true) do v
    Columns(rk = ordinalrank(v, rev=true), z=v)
end

```

but you probably don’t need to parallelize with 5 million rows.

---

<div class="post-metadata">

**Author:** ![jade\_mackay](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jade_mackay/32/6659_2.png) [@jade\_mackay](https://discourse.julialang.org/u/jade_mackay)\
**Post date:** [May 20, 2019, 1:04am UTC](https://discourse.julialang.org/t/juliadb-efficiently-rank-subgroups/24299/3 "2019-05-20T01:04:37Z")

</div>

Thank you very much! That is exactly the kind of advice I was seeking.  
In case anyone is interested in this example, the ranking I was attempting was on `:x` only:

```julia
trk = groupby(t, :x, select = :z, flatten = true) do v
    Columns(rk = ordinalrank(v, rev=true), z=v)
end

```

👍