# Create Running Total Columns in a Data Frame for Multiple Variables with Dynamic Column Name Creation
**URL:** https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299
**Category:** Data
**Tags:** dataframes, gettingstarted, function
**Created:** [September 9, 2020, 1:42am UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299 "2020-09-09T01:42:50Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@clibassi](https://discourse.julialang.org/u/clibassi)
#### Post date: [September 9, 2020, 1:42am UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299/1 "2020-09-09T01:42:50Z")
I’ve got some sports data in a dataframe I’m playing with that has data on teams throughout a season and I have a number of performance measures I’d like to create cumulative sums for. I found some great suggestions for how to do this [here](https://syl1.gitbook.io/julia-language-a-concise-tutorial/useful-packages/dataframes#compute-cumulative-sum-by-categories), but I’m struggling with how to conveniently iterate through the columns I want to create because 1) the colon in the name indexing of columns makes things tricky and 2) I’m not sure how to iterate over a list of column names and use those column names to dynamically create new column names for the cumulative sums. Lastly - I would love to do these cumulative sums so that they were lagged by one time period (i.e. the value for the current row is not included in the running total). Here’s what I’ve got so far:
```julia
#To Pull the Data
using HTTP, CSV, DataFrames
url = "https://projects.fivethirtyeight.com/nfl-api/nfl_elo.csv"
f38 = CSV.read(HTTP.get(url).body)
```
For creating just one column of cumulative sums, this works great:
```julia
f38[:, :cumul_elo1_pre] .= 0.0
for subdf in groupby(f38,[:team1, :season])
subdf[:, :cumul_elo1_pre] .= cumsum(subdf.elo1_pre)
end
```
But if I’d like to do the same thing for all these columns, how can I do it without writing everything out, but iterating?
```julia
cumul_vars = ["elo1_pre", "elo2_pre", "qbelo1_pre", "qbelo2_pre", "qb1_value_pre", "qb2_value_pre",
"qb1_adj", "qb2_adj", "score1", "score2"];
```
Any suggestions?
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 9, 2020, 2:09am UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299/2 "2020-09-09T02:09:08Z")
What language are you coming from? This looks stata-esque.
The solution here is to do combine. Read the DataFrames docs [here](https://juliadata.github.io/DataFrames.jl/stable/man/split_apply_combine/) for more info.
```julia
cumul_vars = ["elo1_pre", "elo2_pre", "qbelo1_pre", "qbelo2_pre", "qb1_value_pre", "qb2_value_pre", "qb1_adj", "qb2_adj", "score1", "score2"]
combine(groupby(df, [:team, :season]), cumul_vars .=> cumsum)
```
Actually, I think you want to keep existing columns, right? Then you would do
```julia
transform(groupby(df, [:team, :season]), cumul_vars .=> cumsum)
```
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [September 9, 2020, 2:19am UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299/3 "2020-09-09T02:19:33Z")
> [@clibassi](#):
>
> 1. the colon in the name indexing of columns makes things tricky
Did you know that you can use strings as column names? So this would work
```julia
#To Pull the Data
using HTTP, CSV, DataFrames
url = "https://projects.fivethirtyeight.com/nfl-api/nfl_elo.csv"
f38 = CSV.read(HTTP.get(url).body)
cumul_vars = ["elo1_pre", "elo2_pre", "qbelo1_pre", "qbelo2_pre", "qb1_value_pre", "qb2_value_pre",
"qb1_adj", "qb2_adj", "score1", "score2"];
combine(groupby(f38, ["team1", "season"]), cumul_vars .=> cumsum)
```
---
### Author:  [@clibassi](https://discourse.julialang.org/u/clibassi)
#### Post date: [September 9, 2020, 1:31pm UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299/4 "2020-09-09T13:31:41Z")
I am indeed coming from Stata - trying to shed those old habits, but not succeeding. Many thanks for this! Is it possible to do a version that is lagged by one time unit?
And thanks @xiaodai - that’s super helpful to know.
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 9, 2020, 1:39pm UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299/5 "2020-09-09T13:39:15Z")
you can use `ShiftedArrays` to get a `lag` so presumably you want
```julia
transform(groupby(df, [:team, :season]), cumul_vars .=> cumsum ∘ lag)
```
---
### Author:  [@clibassi](https://discourse.julialang.org/u/clibassi)
#### Post date: [September 9, 2020, 1:40pm UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299/6 "2020-09-09T13:40:39Z")
Great - I will look into that. Thank you!
---
### Author:  [@clibassi](https://discourse.julialang.org/u/clibassi)
#### Post date: [September 12, 2020, 8:51pm UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299/7 "2020-09-12T20:51:29Z")
This works great - just two (hopefully) quick follow-ups on this:
1. I did this successfully for team 1, but I’d like to do it as well for their opponent (team 2), but modifying the code for team2 would produce the same variable names (it’s iterating over the same column set, just for different grouped dfs). I know I can add another =\> to the transform statement to specify a new column name, but that doesn’t seem to be possible for a list of variables like I have in cumul\_vars here. Any suggestions for how to modifying the resulting column names and be able to execute this for the opposing team as well?
2. To do the lag, I went simple and just subtracted the current observation’s value from the cumulative variable with the following code, which works just fine but issues a syntax warning and is _definitely_ a very Stata way of doing things. So in the interest of retraining my brain and to learn better coding practices, I wonder if you have advice on a better way to write it.
```julia
for var in cumul_vars
full_data["$(var)_cumsum"] = full_data[!, "$(var)_cumsum"] .- full_data[!, "$(var)"]
end
```
---
### Author:  [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)
#### Post date: [September 12, 2020, 9:28pm UTC](https://discourse.julialang.org/t/create-running-total-columns-in-a-data-frame-for-multiple-variables-with-dynamic-column-name-creation/46299/8 "2020-09-12T21:28:31Z")
> [@clibassi](#):
>
> I did this successfully for team 1, but I’d like to do it as well for their opponent (team 2), but modifying the code for team2 would produce the same variable names (it’s iterating over the same column set, just for different grouped dfs). I know I can add another =\> to the transform statement to specify a new column name, but that doesn’t seem to be possible for a list of variables like I have in cumul\_vars here. Any suggestions for how to modifying the resulting column names and be able to execute this for the opposing team as well?
you can keep broadcasting the `=>` operator.
```julia
julia> df = DataFrame(rand(2,2));
julia> input_vars = ["x1", "x2"];
julia> fun(x) = x .+ 1;
julia> output_vars = [xi * "_new_variable_fun" for xi in input_vars];
julia> transform(df, input_vars .=> fun .=> output_vars)
2×4 DataFrame
│ Row │ x1 │ x2 │ x1_new_variable_fun │ x2_new_variable_fun │
│ │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼──────────┼──────────┼─────────────────────┼─────────────────────┤
│ 1 │ 0.516845 │ 0.606987 │ 1.51685 │ 1.60699 │
│ 2 │ 0.453556 │ 0.965991 │ 1.45356 │ 1.96599 │
```
> [@clibassi](#):
>
> To do the lag, I went simple and just subtracted the current observation’s value from the cumulative variable with the following code, which works just fine but issues a syntax warning and is _definitely_ a very Stata way of doing things. So in the interest of retraining my brain and to learn better coding practices, I wonder if you have advice on a better way to write it.
Please read the syntax warning! You are getting it from the first indexing, you need a `[:,"$(var)_cumsum"]`, notice the new `:`.
I’m not sure I have a better way of doing that `cumsun` thing. You could certainly write a small function and use `transform`, but then you have to have an input like `[[:x1, :x2], [:y1, :y2]] .=> f` which is complicated.
The indexing behavior is definitely a bit ugly… perhaps that can be fixed with metaprogramming.