# How to compute the rolling standardization using DataFrames

**URL:** <https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976>\
**Category:** General Usage\
**Tags:** packages, dataframes\
**Created:** [July 20, 2021, 10:31am UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976 "2021-07-20T10:31:29Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [July 20, 2021, 10:31am UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/1 "2021-07-20T10:31:29Z")

</div>

```julia
using DataFrames
data = DataFrame(time = rand(1:100, 1_000_000), val=rand(1_000_000))

```

consider the above data frame and say I wish to compute the average `val` within a rolling window and normalize each `val` using the rolling average and standard deviation. It’s almost like a moving average but notice that each time period has multiple values.

E.g say I am looking at a row with value `(time = 7, val= 0.551)` and I wish to normal `val` using a 12 time period average and stdev, I would need to compute the mean and standard deviation for all rows with `time` in 2:13 (cos it’s a 12 month moving average taking 5 values from the past and 6 values from the future), then I would standardize it for all.

The real problem is slightly more complicated and I would need to do the same for more columns, but the basic is the same.

The only way I can think of now is to do a groupby for each month, so that would involve compute it almost 100 times.

Is there a library that has this implemented already?

```julia
using DataFrames, Chain, DataFrameMacros, Statistics

data = DataFrame(time = rand(1:100, 1_000_000), val=rand(1_000_000))

function summarise(data, t)
    tmp = @chain data begin
        @subset t-5 <= :time <= t + 6
        @combine(:meanval = mean(:val), :stdval= std(:val))
    end

    tmp.meanval[1], tmp.stdval[1]
end

df_summ = DataFrame(time = 1:100, mean_std = [summarise(data, t) for t in 1:100])

data_fnl = @chain data begin
    leftjoin(df_summ, on = [:time])
    @transform :val_normlaised = (:val-:mean_std[1]) / :mean_std[2]
end

```

---

<div class="post-metadata">

**Author:** ![eliassno](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/eliassno/32/18917_2.png) [@eliassno](https://discourse.julialang.org/u/eliassno)\
**Post date:** [July 20, 2021, 10:45am UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/2 "2021-07-20T10:45:27Z")

</div>

Did you have something similar to [`DataFrame.rolling` from pandas](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.rolling.html) in mind?

---

<div class="post-metadata">

**Author:** ![Skoffer](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/skoffer/32/378_2.png) [@Skoffer](https://discourse.julialang.org/u/Skoffer)\
**Post date:** [July 20, 2021, 10:56am UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/3 "2021-07-20T10:56:57Z")

</div>

While `DataFrames` is undoubtedly one of the most favorite ways to deal with tabular data, it is worth noting that there are other alternatives, which can be more suited for particular tasks. For example, [TimeSeries.jl](https://github.com/JuliaStats/TimeSeries.jl) have special section devoted to window manipulations [Apply methods · TimeSeries.jl](https://juliastats.org/TimeSeries.jl/latest/apply/#moving-1). Alternatively, there is not so frequently updated, but useful package [RollingFunctions.jl](https://github.com/JeffreySarnoff/RollingFunctions.jl)

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [July 20, 2021, 10:58am UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/4 "2021-07-20T10:58:20Z")

</div>

it’s a bit harder than just rolling, cos there are multiple observations per time point.

---

<div class="post-metadata">

**Author:** ![DataFrames](https://avatars.discourse-cdn.com/v4/letter/d/e19b73/32.png) [@DataFrames](https://discourse.julialang.org/u/DataFrames)\
**Post date:** [July 20, 2021, 11:15pm UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/5 "2021-07-20T23:15:36Z")

</div>

probably you cannot skip the 100 times calculation, however, you can make it faster.

the `groupby` function gives the starts and ends of each group, so instead of using `subset` you can go through these starts and ends to filter observations.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [July 20, 2021, 11:49pm UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/6 "2021-07-20T23:49:43Z")

</div>

A colleague showed me the Spark’s (and SQL’s?) [https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.sql.Window.rangeBetween.html](https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.sql.Window.rangeBetween.html)

which can do this. Interesting.

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [July 21, 2021, 1:03pm UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/7 "2021-07-21T13:03:13Z")

</div>

@xiodai, have you also taken a look at the [RollingTimeWindows.jl](https://github.com/lukemerrick/RollingTimeWindows.jl) package and associated discourse [post](https://discourse.julialang.org/t/time-period-based-time-series-moving-windows-in-julia/59745)?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [July 21, 2021, 1:50pm UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/8 "2021-07-21T13:50:16Z")

</div>

No. But I will check them out now

---

<div class="post-metadata">

**Author:** ![luke](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/luke/32/24341_2.png) [@luke](https://discourse.julialang.org/u/luke)\
**Post date:** [July 21, 2021, 3:54pm UTC](https://discourse.julialang.org/t/how-to-compute-the-rolling-standardization-using-dataframes/64976/9 "2021-07-21T15:54:42Z")

</div>

Author of `RollingTimeWindows` (and the associated post) here. I’m happy to give you commit access if you want to make any modifications to `RollingTimeWindows` (e.g. adding fixed-width iteration or functions that live at a higher level of abstraction than the iterator) for your particular use-case – just ping me with your GitHub username 🙂