# How would I sort user transactions by date, and only keep the N most recent entries

**URL:** <https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261>\
**Category:** General Usage\
**Tags:** question\
**Created:** [February 6, 2020, 4:56pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261 "2020-02-06T16:56:16Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Julia1](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Julia1](https://discourse.julialang.org/u/Julia1)\
**Post date:** [February 6, 2020, 4:56pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/1 "2020-02-06T16:56:16Z")

</div>

Hi how’s it going?

I have a dataframe where I have unique user entries and the date as one of the features. I know how to sort by date, but I’m having difficulty extracting say the last 5 entries per user. In Pandas there’s a GroupBy Nth method that does the trick.

Any help would be greatly appreciated!

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [February 6, 2020, 5:13pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/2 "2020-02-06T17:13:59Z")

</div>

You can do things like `map(x->x[(end-4):end,:],groupby(df,:user))` to get them separately or wrap that in a `DataFrame()` call to get the result as one.

This requires DataFramesMeta.jl I believe (but you should definitely be using that package anyway).

This assumes `df` was sorted beforehand.

---

<div class="post-metadata">

**Author:** ![haberdashPI](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/haberdashpi/32/26337_2.png) [@haberdashPI](https://discourse.julialang.org/u/haberdashPI)\
**Post date:** [February 6, 2020, 5:21pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/3 "2020-02-06T17:21:15Z")

</div>

One solution (for the last two entries) would be:

```julia

julia> using Random, Dates, DataFrames

julia> df = DataFrame(name = rand(["joe","jill"],11),
                      time = shuffle(Date(now()):Day(1):Date(now()+Day(10))))
11×2 DataFrame
│ Row │ name │ time │
│ │ String │ Date │
├─────┼────────┼────────────┤
│ 1 │ jill │ 2020-02-13 │
│ 2 │ jill │ 2020-02-06 │
│ 3 │ joe │ 2020-02-10 │
│ 4 │ jill │ 2020-02-09 │
│ 5 │ joe │ 2020-02-16 │
│ 6 │ joe │ 2020-02-11 │
│ 7 │ joe │ 2020-02-14 │
│ 8 │ jill │ 2020-02-15 │
│ 9 │ joe │ 2020-02-07 │
│ 10 │ joe │ 2020-02-12 │
│ 11 │ joe │ 2020-02-08 │

julia> by(df,:name) do group
       sort(group,:time)[end-2:end,:]
       end
6×2 DataFrame
│ Row │ name │ time │
│ │ String │ Date │
├─────┼────────┼────────────┤
│ 1 │ jill │ 2020-02-09 │
│ 2 │ jill │ 2020-02-13 │
│ 3 │ jill │ 2020-02-15 │
│ 4 │ joe │ 2020-02-12 │
│ 5 │ joe │ 2020-02-14 │
│ 6 │ joe │ 2020-02-16 │

```

You can also probably use [`Query`](https://github.com/queryverse/Query.jl) to do this, but I find the documentation for `@mapmany` (which you would need to use, along with `@groupby` and `@orderby`) to be so opaque that I can’t figure out how to do it right now. The `Query` solution might run faster, but I don’t know for sure.

---

<div class="post-metadata">

**Author:** ![Julia1](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Julia1](https://discourse.julialang.org/u/Julia1)\
**Post date:** [February 6, 2020, 6:48pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/4 "2020-02-06T18:48:29Z")

</div>

> [@tbeason](#):
>
> `map(x->x[(end-4):end,:],groupby(df,:user))`

Thanks, now I’m facing the issue where some groups have less than 5 users and I’m getting a bounds error. How would I add a conditional to this? (I’m brand new to Julia, always worked in Python/Pandas)

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [February 6, 2020, 7:00pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/5 "2020-02-06T19:00:40Z")

</div>

Well that will sort of break us away from the one-liner function but no worries.

```julia
function myselect(x)
    N = size(x,1)
    if N <= 5
        return x # alternatively, you could return nothing if you didn't want these cases
    else
        return x[(end-4):end,:]
    end
end
map(myselect,groupby(df,:user))

```

There are probably better ways to do all of this but the great thing about Julia is that you can just freely express what you want to do and it will (almost surely) work.

---

<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:** [February 6, 2020, 7:03pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/6 "2020-02-06T19:03:35Z")

</div>

```julia
by(sdf -> last(sort(sdf, :time), 5), df, :user)

```

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [February 6, 2020, 7:06pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/7 "2020-02-06T19:06:20Z")

</div>

See, I told you there was a better way. 😆

---

<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:** [February 6, 2020, 7:07pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/8 "2020-02-06T19:07:40Z")

</div>

But the key point is what you have commented - most of the time if you do not know the library in detail it is easy enough to implement a solution by hand 😄.

---

<div class="post-metadata">

**Author:** ![Julia1](https://avatars.discourse-cdn.com/v4/letter/j/db5fbb/32.png) [@Julia1](https://discourse.julialang.org/u/Julia1)\
**Post date:** [February 6, 2020, 11:20pm UTC](https://discourse.julialang.org/t/how-would-i-sort-user-transactions-by-date-and-only-keep-the-n-most-recent-entries/34261/9 "2020-02-06T23:20:37Z")

</div>

Thanks so much guys! Very refreshing how helpful and friendly the community is here.