Stream: helpdesk (published)

Topic: get rows of dataframes which are maximal in some value

Mason Protter (Jan 24 2022 at 18:20):

Suppose I have this dataframe:

julia> df = DataFrame(x = [1, 2, 3, 1, 2, 3, 1, 2], y = [:foo, :foo, :foo, :bar, :bar, :bar, :baz, :baz], z = rand(8))
8×3 DataFrame
 Row │ x      y       z
     │ Int64  Symbol  Float64
─────┼─────────────────────────
   1 │     1  foo     0.467047
   2 │     2  foo     0.63518
   3 │     3  foo     0.310892
   4 │     1  bar     0.696665
   5 │     2  bar     0.206687
   6 │     3  bar     0.998533
   7 │     1  baz     0.642528
   8 │     2  baz     0.721937

What I want is to reduce this to just have the entries with maxmimal x for each y. E.g. I want the output to be

 Row │ x      y       z
     │ Int64  Symbol  Float64
─────┼─────────────────────────
   1 │     3  foo     0.310892
   2 │     3  bar     0.998533
   3 │     2  baz     0.721937

I feel like this should be a rather basic operation, but I can't figure out the right way to do it.

Mason Protter (Jan 24 2022 at 18:20):

The closest I can figure out would be

julia> groupby(df, :x)[end]
2×3 SubDataFrame
 Row │ x      y       z
     │ Int64  Symbol  Float64
─────┼─────────────────────────
   1 │     3  foo     0.310892
   2 │     3  bar     0.998533

but because there is no entry for :baz with x=3, it doesn't get included, so that's not quite there.

Andrey Oskin (Jan 24 2022 at 18:26):

I like SQL approach (so called window functions, which in DataFrames are just groupby with combine)

combine(groupby(df, :y), x -> last(x, 1))

Andrey Oskin (Jan 24 2022 at 18:28):

For it to work properly, you need to sort over :x at the beginning, of course.

Mason Protter (Jan 24 2022 at 18:34):

Very nice, thank you. I had been having some trouble getting my head around combine

Andrey Oskin (Jan 24 2022 at 18:39):

I have very simple mental image: groupby splits dataframe into a vector of dataframes and combine applies function f to each dataframe in this vector and combines them together.

You can think of combine as combine(gdf, f) = vcat(map(f, gdf)) I do not think that it is made this way, but it's easier to understand.

Mason Protter (Jan 24 2022 at 18:47):

Makes sense

Nils (Jan 25 2022 at 10:47):

Note that you can index the SubDataFrames in your GroupedDataFrame just like normal DataFrames, so a more general solution which doesn't require sorting is:

julia> combine(groupby(df, :y), sdf -> sdf[argmax(sdf.x), :])
3×3 DataFrame
 Row │ y       x      z
     │ Symbol  Int64  Float64
─────┼─────────────────────────
   1 │ foo         3  0.215512
   2 │ bar         3  0.896303
   3 │ baz         2  0.97978

Nils (Jan 25 2022 at 10:48):

(this will of course return more than one row for y groups in which the maximum value of x occurs multiple times)

Last updated: Oct 02 2023 at 04:34 UTC