# Create lead and lag variable in DataFrame

**URL:** <https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658>\
**Category:** General Usage\
**Tags:** question\
**Created:** [January 28, 2018, 9:04pm UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658 "2018-01-28T21:04:41Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [January 28, 2018, 9:04pm UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/1 "2018-01-28T21:04:41Z")

</div>

I am trying to create a lag and lead variable in DataFrame, in R and Python this can be easily done with lag, lead, and shift function, but I still could not get it done in Julia.

My code is like this (does not work):

```julia
samplefine_call = @>begin
    samplefine_call
    @transform( price = blsprice.(:S, :K, :r, :T, :σ, :DIV) )
    @transform(RND = lead(:price,1) - lag(:price, 1))
end

```

I can do it like this, but I need to merge it back to my original DataFrame, which is not efficient

```julia

RND = samplefine_call[3:end, :price] - samplefine_call[1:end-2, :price]

```

---

<div class="post-metadata">

**Author:** ![Nosferican](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nosferican/32/9275_2.png) [@Nosferican](https://discourse.julialang.org/u/Nosferican)\
**Post date:** [January 29, 2018, 12:01am UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/2 "2018-01-29T00:01:25Z")

</div>

Take a look at [https://github.com/JuliaEconometrics/EconUtils.jl/blob/master/src/firstdifference.jl](https://github.com/JuliaEconometrics/EconUtils.jl/blob/master/src/firstdifference.jl).

The implementation I wrote is more general as it is meant to be used with panels and handle the nuance of steps (date-time, gaps, implied minimum step, etc.). I would recommend using `diff` if the case is simple enough.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [January 29, 2018, 12:12am UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/3 "2018-01-29T00:12:53Z")

</div>

> [@Nosferican](#):
>
> The implementation I wrote is more general as it is meant to be used with panels and handle the nuance of steps (date-time, gaps, implied minimum step, etc.). I would recommend using diff if the case is simple enough.

Thanks for the reply. In my case (not the example I gave here), I need to calculate price [i + 1] - 2\*price [i] + price[i - 1], which equals price [i+1] - price[i] - (price [i] - price[i-1]) which is double difference, which can use your function. However, I still want to know if a window function like lead and lag is available with dataframe in Julia, like what dplyr does in R, because that approach is more flexible.

[https://cran.r-project.org/web/packages/dplyr/vignettes/window-functions.html](https://cran.r-project.org/web/packages/dplyr/vignettes/window-functions.html)

---

<div class="post-metadata">

**Author:** ![Nosferican](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nosferican/32/9275_2.png) [@Nosferican](https://discourse.julialang.org/u/Nosferican)\
**Post date:** [January 29, 2018, 1:35am UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/4 "2018-01-29T01:35:51Z")

</div>

See [https://github.com/JuliaData/DataFrames.jl/issues/791](https://github.com/JuliaData/DataFrames.jl/issues/791).

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [January 29, 2018, 2:02am UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/5 "2018-01-29T02:02:37Z")

</div>

It seems the lag and lead functions in TimeSeries.jl only work on timeseries arrary

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [January 29, 2018, 9:07am UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/6 "2018-01-29T09:07:52Z")

</div>

I think the `lead` and `lag` functions in the recent [ShiftedArray](https://github.com/piever/ShiftedArrays.jl#shifting-the-data) package should suit exactly this need. We should really include these features by default with DataFrames in one way or another.

Cc: @piever

---

<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:** [January 29, 2018, 11:37am UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/7 "2018-01-29T11:37:09Z")

</div>

Yep, you could do something like:

```julia
using ShiftedArrays
v = lead(price) .- 2 .* price .+ lag(price)

```

which avoids unnecessary allocations. I think it works best on Julia 0.7 due to recent improvements on missing data handling by @nalimilan, but if you don’t think you’ll encounter performance issues it should be usable already.

To integrate this functionality in DataFrames, I guess the easiest is to add ShiftedArrays as a DataFrames dependency (ShiftedArrays is a small pure Julia package) and reexport `lead` and `lag`.

---

<div class="post-metadata">

**Author:** ![ExpandingMan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/expandingman/32/866_2.png) [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)\
**Post date:** [January 29, 2018, 2:59pm UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/8 "2018-01-29T14:59:06Z")

</div>

This probably isn’t the advice you’re looking for in this particular instance, but, for what it’s worth, I thought I should point out that a _huge_ advantage that Julia has over Python and R in cases like this is that pure Julia code is actually efficient. So, if you were to do, for example, `[v[i] - v[i-1] for i ∈ 2:length(v)]` what you get will more or less run like C code. Furthermore, since DataFrames columns are just `AbstractArray`s, you almost never need to worry about them having some sort of unorthodox behavior. In Julia the need for specialized or fancy code for data manipulation is much mitigated compared to Python and R. (This also means that, as much as I love DataFramesMeta.jl, it often simply isn’t necessary.)

---

<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:** [January 29, 2018, 3:10pm UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/9 "2018-01-29T15:10:17Z")

</div>

I agree on the conceptual distinction between working with Julia (arrays are naturally fast, a DataFrame is a collection of arrays of the same length) vs Pandas (to do something efficiently you often need a specialized function). However, in this case there is a different concern: it is annoying for newcomers to do this without off by one mistakes. Here for example, you get a column that is too short for a DataFrame, you should instead do:

`[i==1 ? missing : v[i] - v[i-1] for i ∈ 1:length(v)]`

or in the case of the question asked:

`[i <= 2 ? missing : v[i] - 2*v[i-1] + v[i] for i ∈ 1:length(v)]`

ShiftedArrays simply automatizes this procedure by automatically giving `missing` when you’re out of bounds (reasonably easy to implement in Julia thanks to the wonderful `AbstractArray` interface).

---

<div class="post-metadata">

**Author:** ![ExpandingMan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/expandingman/32/866_2.png) [@ExpandingMan](https://discourse.julialang.org/u/ExpandingMan)\
**Post date:** [January 29, 2018, 3:20pm UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/10 "2018-01-29T15:20:34Z")

</div>

Like I said, this is not necessarily the very best use case, but when doing these sorts of things myself I have found it very useful to keep in mind that you don’t _have_ to do _everything_ with specialized code. It’s a very liberating feeling.

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [January 29, 2018, 5:38pm UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/11 "2018-01-29T17:38:48Z")

</div>

Thanks for your reply. I tried to install this package, but could not get it done. Could you show how to use these functions?

---

<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:** [January 29, 2018, 7:41pm UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/12 "2018-01-29T19:41:17Z")

</div>

Does the following not work for you?

```julia
julia> Pkg.add("ShiftedArrays") # run only once to install the package
julia> using ShiftedArrays

julia> price = rand(10);

julia> v = price .- lag(price)
10-element Array{Any,1}:
   missing  
 -0.00216921
 -0.0259962 
 -0.078662  
 -0.766035  
  0.535359  
 -0.535341  
 -0.0775602 
  0.483483  
 -0.438419

```

What error do you get?

To use it with DataFrames, make sure you have at least version 0.11 as it is the first one to support `missing`

---

<div class="post-metadata">

**Author:** ![Yifan\_Liu](https://avatars.discourse-cdn.com/v4/letter/y/4da419/32.png) [@Yifan\_Liu](https://discourse.julialang.org/u/Yifan_Liu)\
**Post date:** [January 29, 2018, 8:04pm UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/13 "2018-01-29T20:04:14Z")

</div>

Thanks, it works now! I did this this morning, but could not install this package.

---

<div class="post-metadata">

**Author:** ![archaistvolts](https://avatars.discourse-cdn.com/v4/letter/a/58956e/32.png) [@archaistvolts](https://discourse.julialang.org/u/archaistvolts)\
**Post date:** [June 11, 2018, 7:50am UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/14 "2018-06-11T07:50:40Z")

</div>

I found `circshift()` which works great for this. It seems to be a part of 0.6.3.

```julia
df[:closelag] = circshift(df[:close], (-1))

```

---

<div class="post-metadata">

**Author:** ![merlin](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@merlin](https://discourse.julialang.org/u/merlin)\
**Post date:** [October 22, 2019, 5:05am UTC](https://discourse.julialang.org/t/create-lead-and-lag-variable-in-dataframe/8658/15 "2019-10-22T05:05:39Z")

</div>

The shiftedArrays tight notation is what I really like here, though I love the speed of native julia loops.

In my use-case its good to replicate the notation in this paper I’m working from.