# Need for speed: looping over subdataframes to construct lags

**URL:** <https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258>\
**Category:** Performance\
**Tags:** question, dataframes\
**Created:** [March 17, 2023, 6:15pm UTC](https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258 "2023-03-17T18:15:05Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![moshi](https://avatars.discourse-cdn.com/v4/letter/m/f07891/32.png) [@moshi](https://discourse.julialang.org/u/moshi)\
**Post date:** [March 17, 2023, 6:15pm UTC](https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258/1 "2023-03-17T18:15:05Z")

</div>

I have a very large DataFrame with columns `j`, `t`, `b`, `b_t`.

I am creating a new column `s` by `df.s = df.b`  
and then I am making changes to the values in `s` depending on certain conditions of whether `b_t` and `t` differ, for each group `j`. The following code shows the changes I am making. It works but it takes forever:

```julia
for g in groupby(df, :j)
   g.s = ifelse.(g.b_t .== g.t .- 1, lag(g.b, -1), g.b )
end

```

The `lag` function uses `ShiftedArrays` package.

Even putting the above loop in a function, and making it available `@everywhere` with `using Distributed` to parallelize it, only has modest gains in speed. I am wondering is there an easy efficiency gain that I am missing here. Thanks!

---

<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:** [March 17, 2023, 7:41pm UTC](https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258/2 "2023-03-17T19:41:24Z")

</div>

First comment is that `df.s = df.b` is not a good pattern. It creates alias of `:s` and `:b`. They have the same memory location. Do `transform!(df, :b => :s)` or `df.s = copy(df.b)` to ensure you have a copy.

Having said this it is likely this is not needed at all as it should be enough to just write:

```julia
transform!(groupby(df, :j), [:b, :t, :b_t] => ((b, t, b_t) -> ifelse.(b_t .== t .- 1, lag(b, -1), b)) => :s)

```

It is possible to further improve the performance as this solution does some unnecessary allocations, but maybe this is already good enough.

---

<div class="post-metadata">

**Author:** ![moshi](https://avatars.discourse-cdn.com/v4/letter/m/f07891/32.png) [@moshi](https://discourse.julialang.org/u/moshi)\
**Post date:** [March 17, 2023, 11:18pm UTC](https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258/3 "2023-03-17T23:18:59Z")

</div>

Thanks so much @bkamins ! It improved speed. And also thanks for correcting my sloppy assignment of `b` !

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [March 18, 2023, 1:27am UTC](https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258/4 "2023-03-18T01:27:07Z")

</div>

Can you clarify what is the desired result here? The `lag` function depends on the ordering of rows. Yet a database relation has no record order. A DataFrame does have row order, but it is implicit with DataFrame construction, and the `groupby` guarantee of order should be looked up in the docs or code or @bkamins. If `b_t` and `t` are “time” ordering within groups, perhaps somehow sorting the DataFrame can make this whole operation quicker.

---

<div class="post-metadata">

**Author:** ![moshi](https://avatars.discourse-cdn.com/v4/letter/m/f07891/32.png) [@moshi](https://discourse.julialang.org/u/moshi)\
**Post date:** [March 18, 2023, 2:37am UTC](https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258/5 "2023-03-18T02:37:00Z")

</div>

Yes they are time variables. The data was already sorted. I did not mention that small detail because if not, the exercise would have been incorrect regardless.

---

<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:** [March 18, 2023, 11:16am UTC](https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258/6 "2023-03-18T11:16:33Z")

</div>

`groupby` guarantees to keep the row order within groups. That is one of the crucal advantages of having a data frame over just a data base.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [March 18, 2023, 11:25am UTC](https://discourse.julialang.org/t/need-for-speed-looping-over-subdataframes-to-construct-lags/96258/7 "2023-03-18T11:25:51Z")

</div>

you could try to see if a for loop is not more suitable for your case

```julia
@views for i in 1:length(b)-1
    if b_t[i]==t[i]-1
        b[i]=b[i+1]
    end
end
if b_t[end]==t[end]-1
    b[end]==missing
end
end

```