# DataFrame group by first column, and sort by last column

**URL:** <https://discourse.julialang.org/t/dataframe-group-by-first-column-and-sort-by-last-column/16357>\
**Category:** Data\
**Tags:** dataframes\
**Created:** [October 15, 2018, 6:04pm UTC](https://discourse.julialang.org/t/dataframe-group-by-first-column-and-sort-by-last-column/16357 "2018-10-15T18:04:27Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mbeach42](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mbeach42/32/5747_2.png) [@mbeach42](https://discourse.julialang.org/u/mbeach42)\
**Post date:** [October 15, 2018, 6:04pm UTC](https://discourse.julialang.org/t/dataframe-group-by-first-column-and-sort-by-last-column/16357/1 "2018-10-15T18:04:28Z")

</div>

Hi,

Probably a simple question, but with DataFrames.jl I’m having trouble returning the data frame grouped by the first column, the sorted by the last column. Here’s a MWE

```julia
 using DataFrames
  A = rand(5,5)
  A[:,1] = [1, 1, 2, 2, 4]
  df = DataFrame(A)                                                                                                  
  df2 = by(df, :x1, df -> minimum(df.x2))

```

Output is:

```julia
julia> df
5×5 DataFrame
│ Row │ x1 │ x2 │ x3 │ x4 │ x5 │
│ │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼──────────┼───────────┼──────────┼──────────┤
│ 1 │ 1.0 │ 0.684006 │ 0.27617 │ 0.453495 │ 0.701109 │
│ 2 │ 1.0 │ 0.282817 │ 0.94968 │ 0.531294 │ 0.262201 │
│ 3 │ 2.0 │ 0.802795 │ 0.0950535 │ 0.538556 │ 0.155517 │
│ 4 │ 2.0 │ 0.986956 │ 0.609984 │ 0.633382 │ 0.169541 │
│ 5 │ 4.0 │ 0.975459 │ 0.876686 │ 0.105175 │ 0.221114 │

julia> df2
3×2 DataFrame
│ Row │ x1 │ x1_1 │
│ │ Float64 │ Float64 │
├─────┼─────────┼──────────┤
│ 1 │ 1.0 │ 0.282817 │
│ 2 │ 2.0 │ 0.802795 │
│ 3 │ 4.0 │ 0.975459 │

```

So in the above, df2 is a sorted with just the first and last column, but I’d like to return all columns. Anyone know how to go about doing that?

Thanks!

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [October 15, 2018, 7:32pm UTC](https://discourse.julialang.org/t/dataframe-group-by-first-column-and-sort-by-last-column/16357/2 "2018-10-15T19:32:18Z")

</div>

So you are ok to have only one row per group, right? What values would you want to see for the other columns? Also use the `minimum` aggregation per group, or something else?

---

<div class="post-metadata">

**Author:** ![mbeach42](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mbeach42/32/5747_2.png) [@mbeach42](https://discourse.julialang.org/u/mbeach42)\
**Post date:** [October 15, 2018, 7:52pm UTC](https://discourse.julialang.org/t/dataframe-group-by-first-column-and-sort-by-last-column/16357/3 "2018-10-15T19:52:55Z")

</div>

that’s right, one value per group. I dont want the minimum of each column, but the row corresponding to the minimum of the right-most column for each group

---

<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 15, 2018, 8:43pm UTC](https://discourse.julialang.org/t/dataframe-group-by-first-column-and-sort-by-last-column/16357/4 "2018-10-15T20:43:45Z")

</div>

I think this should work:

```julia
by(df, :x1) do d
   DataFrame([minimum(d[name]) for name in names(d)]', names(d)) 
   # for every group, get a row vector of the minimums for that group
  # make a DataFrame out of it, (which is kinda expensive), and give 
  # it the names of the sub-dataframe you are acting on. 
end

```

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [October 16, 2018, 12:26am UTC](https://discourse.julialang.org/t/dataframe-group-by-first-column-and-sort-by-last-column/16357/5 "2018-10-16T00:26:48Z")

</div>

The Query.jl way would be this:

```julia
df |> @groupby(_.x1) |> @map(first(sort(_, by=i->i.x2))) |> DataFrame

```

If we get [https://github.com/JuliaLang/julia/issues/28210](https://github.com/JuliaLang/julia/issues/28210) into Base eventually, it would simplify to:

```julia
df |> @groupby(_.x1) |> @map(minimum(_, by=i->i.x2)) |> DataFrame

```

Here is how this works: the first `@groupby` creates a stream of three groups, where each group is a table itself. A table is just an array of rows (named tuples). So calling `sort` on that will sort the rows, and then `first` will pick the first row from each group.