# How to compute a "cumulative" in a dataframe (without a for loop)
**URL:** https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156
**Category:** Data
**Tags:** question, dataframes
**Created:** [February 17, 2017, 4:10pm UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156 "2017-02-17T16:10:53Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)
#### Post date: [February 17, 2017, 4:10pm UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/1 "2017-02-17T16:10:53Z")
I am trying to add a new column with a cumulative value in regard to an other column., e.g.:
In:
```julia
df = DataFrame(region = ["US","US","US","US","EU","EU","EU","EU"],
year = [2010,2011,2012,2013,2010,2011,2012,2013],
value = [3,3,2,2,2,2,1,1])
```
Out:
```julia
region year value
1 US 2010 3
2 US 2011 3
3 US 2012 2
4 US 2013 2
5 EU 2010 2
6 EU 2011 2
7 EU 2012 1
8 EU 2013 1
```
I did try something like:
In:
```julia
df[:cumValue] = copy(df[:value])
[r[:cumValue] .= df[(df[:region] .== r[:region]) & (df[:year] .== (r[:year]-1)),:cumValue] for r in eachrow(df) if r[:year] != minimum(df[:year])]
```
But I get the following error:
Out:
`LoadError: MethodError: no method matching broadcast!(::Base.#identity, ::Int64, ::DataArrays.DataArray{Int64,1})`
---
### Author:  [@amellnik](https://discourse.julialang.org/u/amellnik)
#### Post date: [February 17, 2017, 4:23pm UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/2 "2017-02-17T16:23:01Z")
> [@sylvaticus](#):
>
> df = DataFrame(region = [“US”,“US”,“US”,“US”,“EU”,“EU”,“EU”,“EU”],
> year = [2010,2011,2012,2013,2010,2011,2012,2013],
> value = [3,3,2,2,2,2,1,1])
Use `cumsum`.
```julia
df = DataFrame(region = ["US","US","US","US","EU","EU","EU","EU"],
year = [2010,2011,2012,2013,2010,2011,2012,2013],
value = [3,3,2,2,2,2,1,1])
df[:cumValue] = cumsum(df[:value])
df
```
```julia
region year value cumValue
1 US 2010 3 3
2 US 2011 3 6
3 US 2012 2 8
4 US 2013 2 10
5 EU 2010 2 12
6 EU 2011 2 14
7 EU 2012 1 15
8 EU 2013 1 16
```
It’s not as efficient, but you can also do things like `df[:cumValue] = [sum(df[1:i, :value]) for i in 1:nrow(df)]`, which can be adapted if you need to do something other than sum the value
---
### Author:  [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)
#### Post date: [February 17, 2017, 4:32pm UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/3 "2017-02-17T16:32:55Z")
> [@amellnik](#):
>
> df[:cumValue] = [sum(df[1:i, :value]) for i in 1:nrow(df)]
Thank you, but I need to subgroup the summing by region (sorry I didn’t specified).
At the end I found:
- with a for loop (maybe more clear):
```julia
df[:cumValue] = cumsum(df[:value])
for r in eachrow(df)
if r[:year] != minimum(df[:year])
value = df[(df[:region] .== r[:region]) & (df[:year] .== (r[:year]-1)),:cumValue]
r[:cumValue] = value[1] + r[:value]
end
end
```
- with list comprehension:
```julia
df[:cumValue] = cumsum(df[:value])
[r[:cumValue] = df[(df[:region] .== r[:region]) & (df[:year] .== (r[:year]-1)),:cumValue][1] + r[:value] for r in eachrow(df) if r[:year] != minimum(df[:year])]
```
Out:
```julia
region year value cumValue
1 US 2010 3 3
2 US 2011 3 6
3 US 2012 2 8
4 US 2013 2 10
5 EU 2010 2 2
6 EU 2011 2 4
7 EU 2012 1 5
8 EU 2013 1 6
```
---
### Author:  [@piever](https://discourse.julialang.org/u/piever)
#### Post date: [February 17, 2017, 4:57pm UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/4 "2017-02-17T16:57:04Z")
Not sure if it’s the most efficient, but I often find it useful to allocate a new column and then modify it in place with a `by`.
In this case:
```julia
df[:cumsum] = similar(df[:value])
by(df, :region) do dd
dd[:cumsum] = cumsum(dd[:value])
return
end
```
The empty return is to specify that you are not interested in getting any output from this `by`, you only want the side effect.
---
### Author:  [@ElOceanografo](https://discourse.julialang.org/u/ElOceanografo)
#### Post date: [February 17, 2017, 6:21pm UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/5 "2017-02-17T18:21:50Z")
You can also accomplish this using [DataFramesMeta](https://github.com/JuliaStats/DataFramesMeta.jl) and chaining operations together using the `@linq` macro and pipe `|>`,
```julia
using DataFramesMeta
df = @linq df |>
groupby(:region) |>
transform(cumValue = cumsum(:value))
```
which yields
```julia
8×4 DataFrames.DataFrame
│ Row │ region │ year │ value │ cumvalue │
├─────┼────────┼──────┼───────┼──────────┤
│ 1 │ "EU" │ 2010 │ 2 │ 2 │
│ 2 │ "EU" │ 2011 │ 2 │ 4 │
│ 3 │ "EU" │ 2012 │ 1 │ 5 │
│ 4 │ "EU" │ 2013 │ 1 │ 6 │
│ 5 │ "US" │ 2010 │ 3 │ 3 │
│ 6 │ "US" │ 2011 │ 3 │ 6 │
│ 7 │ "US" │ 2012 │ 2 │ 8 │
│ 8 │ "US" │ 2013 │ 2 │ 10 │
```
I haven’t compared the performance of these various approaches, but for most “normal” sized data sets the difference shouldn’t be worth worrying about. Personally, I find this clearer to read than a “by, do” loop, especially if you start doing a lot of successive transformations and manipulations.
---
### Author:  [@sylvaticus](https://discourse.julialang.org/u/sylvaticus)
#### Post date: [February 19, 2017, 3:08pm UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/6 "2017-02-19T15:08:00Z")
Thank you. Both @piever and @ElOceanografo solutions are much more efficient (and cleaner) than the “manual” naive solution I found by myself. Good I did ask 😉
---
### Author:  [@SubTer](https://discourse.julialang.org/u/SubTer)
#### Post date: [September 3, 2021, 12:34am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/7 "2021-09-03T00:34:08Z")
I have a question along this line, especially @ElOceanografo response, been playing with DataFramesMeta, which is awesome so far, but stuck on trying to get more conditional parameters to work.
So in the example in this thread, how would I go about adding a condition to the transform(CumValue) statement if say I wanted to have another column that added only for the last 2 years, and not everything in the groupby(:region)? like CumvalueTrailing2Years type of column.
thanks!
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 3, 2021, 12:42am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/8 "2021-09-03T00:42:26Z")
[RollingFunctions.jl](https://github.com/JeffreySarnoff/RollingFunctions.jl) will handle this, I think. Something like
```julia
@chain df begin
groupby(:state)
@transform :y_rolling_mean = rollmean(:y, 12)
end
```
But if your data is in dates and isn’t like, every day or every week, meaning `x[i-5]` always meaning the same thing, then I think things are harder, and I don’t know what exactly the solution would be.
---
### Author:  [@SubTer](https://discourse.julialang.org/u/SubTer)
#### Post date: [September 3, 2021, 12:47am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/9 "2021-09-03T00:47:15Z")
thats a cool package, didnt know it existed!
but I dont see it allowing for product like functions, or rolling sums. ie. it doesnt have cumprod or cumsum equivalent that i can see in the docs. (might be blind…)
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 3, 2021, 12:48am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/10 "2021-09-03T00:48:25Z")
It seems to support having your own functions, see [here](https://github.com/JeffreySarnoff/RollingFunctions.jl#works-with-your-functions). I’m sure we could help with an MWE, though I haven’t worked too much with this function.
---
### Author:  [@SubTer](https://discourse.julialang.org/u/SubTer)
#### Post date: [September 3, 2021, 12:54am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/11 "2021-09-03T00:54:29Z")
hmm… how would that look like code wise?
transform(columnname = rolling(cumsum(:value), 2) \<–?
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 3, 2021, 12:59am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/12 "2021-09-03T00:59:36Z")
It would be `rolling(cumsum, :value, 2)`.
EDIT: I guess the function in `rolling` needs to return a scalar. So I’m not 100% sure what the solution to this problem is.
---
### Author:  [@SubTer](https://discourse.julialang.org/u/SubTer)
#### Post date: [September 3, 2021, 1:07am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/13 "2021-09-03T01:07:25Z")
yeah was about to say that, as i run into that issue trying to execute it… hoping there is an elegant solution with dataframesmeta here…
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 3, 2021, 1:08am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/14 "2021-09-03T01:08:59Z")
I think this is possible. Can you give an example of what you want, say with the input `x = [1, 2, 3, 4, 5, 6]`?
---
### Author:  [@SubTer](https://discourse.julialang.org/u/SubTer)
#### Post date: [September 3, 2021, 1:20am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/15 "2021-09-03T01:20:29Z")
just using the original posters’ df here, example of what I am trying to get to:
```julia
df9 = DataFrame(region = ["US","US","US","US","EU","EU","EU","EU"],
year = [2010,2011,2012,2013,2010,2011,2012,2013],
value = [3,3,2,2,2,2,1,1])
df9 = @linq df9 |>
groupby(:region) |>
transform(cumValueTrailing2YR = rolling(cumsum, :value, 2)
```
and hopefully this type of solution works on cumprod, other functions. But at the base of it, i have a bunch of datasets with Date / Values / GroupTypes, where I need to add columns that are trailing in nature doing cumulative product function.
with example above, you can just do “=cumprod(:value)”, but then thats just doing it by your grouping function, and no way I know of to give is depedency on another column group, say Year in that above df.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 3, 2021, 1:27am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/16 "2021-09-03T01:27:16Z")
Please use triple backticks
````julia
```
like this
```
````
to format code.
I mean, given the input
```julia
x = [1, 2, 3, 4, 5, 6]
```
What kind of output do you want? Because I am having trouble understanding what kind of output you actually want. Forget about grouping for the time being, I think.
---
### Author:  [@SubTer](https://discourse.julialang.org/u/SubTer)
#### Post date: [September 3, 2021, 1:33am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/17 "2021-09-03T01:33:04Z")
oh the output would just be another column in the df, thats simply cumulative sum of last 2 years per group. So you need 2 inputs, you cant have a single vector input in this problem. cumsum is dependent on a column thats not part of the groupby.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 3, 2021, 1:37am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/18 "2021-09-03T01:37:13Z")
But not every entry in the array can be a cumulative sum of a different window, right?
I can’t imagine how that would work without it just bring a rolling sum, not a `cumsum`.
---
### Author:  [@SubTer](https://discourse.julialang.org/u/SubTer)
#### Post date: [September 3, 2021, 1:44am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/19 "2021-09-03T01:44:21Z")
depends on the condition of the function, you would end up with earlier parts of the array as null (or defaulted value of some sort).
so if X = [2001, 2002, 2003, 2004], and Y = [2, 3, 6, 4] and you are rolling sum every 2 years, your theoretical column Z = [null, 5, 9, 10], as for 2001, there is no last 2 years to sum.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 3, 2021, 1:55am UTC](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156/20 "2021-09-03T01:55:44Z")
That’s just `rollsum`, then
```julia
julia> using RollingFunctions
julia> Y = [2, 3, 6, 4];
julia> rolling(sum, Y, 2)
3-element Vector{Float64}:
5.0
9.0
10.0
```
I see what you mean about the missing values. It’s unfortunate this isn’t supported. I will file an issue to add it. You can do
```julia
Z = [missing; rolling(sum, Y, 2))]
```
to get the behavior you want.
[Next page](https://discourse.julialang.org/t/how-to-compute-a-cumulative-in-a-dataframe-without-a-for-loop/2156.md?page=2)