# Adding a NamedTuple to DataFrame row

**URL:** <https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [September 20, 2021, 9:22am UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447 "2021-09-20T09:22:47Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![bernhard](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bernhard/32/2619_2.png) [@bernhard](https://discourse.julialang.org/u/bernhard)\
**Post date:** [September 20, 2021, 9:22am UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/1 "2021-09-20T09:22:47Z")

</div>

I have a simulation model that iteratively produces NamedTuples which I want to aggregate (summing) to a DataFrame. Only a portion of the columns are updated, as I keep statistics for different simulation periods (years).  
I [read](https://discourse.julialang.org/t/best-way-to-iteratively-add-to-a-dataframe/21084/4) that I may want to avoid creating intermediate DataFrames, thus I work with a NamedTuple.

The MWE below is a bit lengthy, but my question is simple, how can I improve `aggregate_yearly_metrics`. It does not seem type stable right now…

```julia
using DataFrames 

aggregationTuple = (p_volume=0, s_volume=0, floatvar=0.0, intvar=0)
aggDf = DataFrames.DataFrame()

yearlist = collect(1:2)
#define a dict for colnames lookup
colNamesOfAggDf = Dict{Int,Dict{Symbol,Symbol}}()
    for yr in yearlist
        hv = Dict{Symbol,Symbol}()
        for (symb, val) in pairs(aggregationTuple)
            nm = Symbol(string(symb, "_y", yr))
            hv[symb] = nm            
        end
        colNamesOfAggDf[yr] = deepcopy(hv)
    end
    
n=100 
aggDf[!, :iteration] = collect(1:n)
#define dataframe
    for yr in yearlist
        for (tplsymb,dfsymb) in colNamesOfAggDf[yr]
            aggDf[!, dfsymb] = zero(one(getindex(aggregationTuple, tplsymb)):n)
        end
    end

function aggregate_yearly_metrics!(aggDfRowNumber::Int, colNamesOfAggDf, yr::Int, aggDf::DataFrames.DataFrame, aggregationTuple::NamedTuple)
    for k in keys(aggregationTuple)
        v = getindex(aggregationTuple,k)
        dfcolname = colNamesOfAggDf[yr][k]
        aggregate_yearly_metrics_internal!(aggDf[!,dfcolname],aggDfRowNumber,v)
    end
    return nothing
end

function aggregate_yearly_metrics_internal!(dfcol,aggDfRowNumber,v)
    dfcol[aggDfRowNumber] = dfcol[aggDfRowNumber] + v
    return nothing
end

function sim(n_sims,iterations,colNamesOfAggDf,aggDf)
    for i = 1:iterations
        for j = 1:n_sims
            yr = ifelse(rand()<.5,1,2)
            #generate some data (originally stemming form a simulation model)
                aggregationTuple = (p_volume=1, s_volume=rand(Int), floatvar=rand(), intvar=0)
            #aggregate data 
            aggregate_yearly_metrics!(i, colNamesOfAggDf, yr, aggDf, aggregationTuple)
        end
    end 
    return aggDf 
end

aggDf
sim(20_000,n,colNamesOfAggDf,aggDf)

@code_warntype aggregate_yearly_metrics!(3, colNamesOfAggDf, 1, aggDf, aggregationTuple)

using BenchmarkTools
@benchmark sim(20_000,n,colNamesOfAggDf,aggDf)

```

---

<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:** [September 20, 2021, 9:54am UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/2 "2021-09-20T09:54:04Z")

</div>

You cannot make this code type-stable easily even if you switched from `DataFrame` to `NamedTuple` as a container.

The reason is that `dfcolname = colNamesOfAggDf[yr][k]` cannot be resolved by the compiler at compile time.

You potentially could do it with a generated function but then you would have to pass `yr` as `Val` and also keep `colNamesOfAggDf` as a structure which has column names embedded in its type. Then you would need to unroll the loop within a generated function. All this would be quite complex to do correctly.

---

<div class="post-metadata">

**Author:** ![bernhard](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bernhard/32/2619_2.png) [@bernhard](https://discourse.julialang.org/u/bernhard)\
**Post date:** [September 20, 2021, 9:58am UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/3 "2021-09-20T09:58:49Z")

</div>

> [@bkamins](#):
>
> You cannot make this code type-stable easily even if you switched from `DataFrame` to `NamedTuple` as a container.
> 
> The reason is that `dfcolname = colNamesOfAggDf[yr][k]` cannot be resolved by the compiler at compile time.

Thanks @bkamins .The aggregation tuple will always have the same structure though.

What if I create an aggDfYr1 and aggDfYr2. Then during the simulation I would need to **efficiently** add data to one of the rows of aggDfYr1 or aggDfYr2.

How can I add two DataFrames rows: ?

```julia
df = DataFrame(a=[1,2],b=[1.1,3.1])
data_to_add_to_rowX = df[1,:] # this would be something else than the first row.
df[1,:] = df[1,:] .+ data_to_add_to_rowX

```

---

<div class="post-metadata">

**Author:** ![bernhard](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bernhard/32/2619_2.png) [@bernhard](https://discourse.julialang.org/u/bernhard)\
**Post date:** [September 20, 2021, 10:02am UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/4 "2021-09-20T10:02:58Z")

</div>

Put differently: I want an in-place aggregation for my DataFrame, iteratively adding data to a certain row.  
I guess `combine/groupby` would do this, but calling these functions (in an inner loop) for my use case seems overkill.

---

<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:** [September 20, 2021, 10:10am UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/5 "2021-09-20T10:10:04Z")

</div>

> [@bernhard](#):
>
> I want an in-place aggregation for my DataFrame, iteratively adding data to a certain row.

With `DataFrame` it will always be type unstable. Use a `NamedTuple` with STATIC reference to column names (i.e. column names must be explicitly hard-coded in your source code).

---

<div class="post-metadata">

**Author:** ![bernhard](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bernhard/32/2619_2.png) [@bernhard](https://discourse.julialang.org/u/bernhard)\
**Post date:** [September 20, 2021, 11:31am UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/6 "2021-09-20T11:31:00Z")

</div>

Thanks. I will try and see if I can make this work.  
The namedtuple version is about 10 times faster for me.

```julia
using DataFrames 

aggregationTuple = (p_volume=0, s_volume=0, floatvar=0.0, intvar=0)
#aggNt = DataFrames.DataFrame()

yearlist = collect(1:2)
#define a dict for colnames lookup
colNamesOfAggDf = Dict{Int,Dict{Symbol,Symbol}}()
    for yr in yearlist
        hv = Dict{Symbol,Symbol}()
        for (symb, val) in pairs(aggregationTuple)
            nm = Symbol(string(symb, "_y", yr))
            hv[symb] = nm            
        end
        colNamesOfAggDf[yr] = deepcopy(hv)
    end
    
n=100 
aggDf = (iteration = collect(1:n), p_volume_y1 = zeros(Int,n),s_volume_y1 = zeros(Int,n),floatvar_y1 = zeros(Float64,n),intvar_y1 = zeros(Int,n), p_volume_y2 = zeros(Int,n),s_volume_y2 = zeros(Int,n),floatvar_y2 = zeros(Float64,n),intvar_y2 = zeros(Int,n))

function aggregate_yearly_metrics!(aggDfRowNumber::Int, colNamesOfAggDf, yr::Int, aggDf, p_volume,s_volume,floatvar,intvar)
    if yr ==1
        aggDf.p_volume_y1[aggDfRowNumber] = aggDf.p_volume_y1[aggDfRowNumber] + p_volume
        aggDf.s_volume_y1[aggDfRowNumber] = aggDf.s_volume_y1[aggDfRowNumber] + s_volume
        aggDf.floatvar_y1[aggDfRowNumber] = aggDf.floatvar_y1[aggDfRowNumber] + floatvar
        aggDf.intvar_y1[aggDfRowNumber] = aggDf.intvar_y1[aggDfRowNumber] + intvar
    else 
        @assert yr ==2
        aggDf.p_volume_y2[aggDfRowNumber] = aggDf.p_volume_y2[aggDfRowNumber] + p_volume
        aggDf.s_volume_y2[aggDfRowNumber] = aggDf.s_volume_y2[aggDfRowNumber] + s_volume
        aggDf.floatvar_y2[aggDfRowNumber] = aggDf.floatvar_y2[aggDfRowNumber] + floatvar
        aggDf.intvar_y2[aggDfRowNumber] = aggDf.intvar_y2[aggDfRowNumber] + intvar
    end
    return nothing
end

function sim(n_sims,iterations,colNamesOfAggDf,aggDf)
    for i = 1:iterations
        for j = 1:n_sims
            yr = ifelse(rand()<.5,1,2)
            #generate some data (originally stemming form a simulation model)
                aggregationTuple = (p_volume=1, s_volume=rand(Int), floatvar=rand(), intvar=0)
            #aggregate data
            aggregate_yearly_metrics!(i, colNamesOfAggDf, yr, aggDf, rand(Int),rand(Int),rand(),rand(Int))
        end
    end 
    return aggDf 
end

aggDf
sim(20_000,n,colNamesOfAggDf,aggDf)

@code_warntype aggregate_yearly_metrics!(3, colNamesOfAggDf, 1, aggDf, rand(Int),rand(Int),rand(),rand(Int))

using BenchmarkTools
@benchmark sim(20_000,n,colNamesOfAggDf,aggDf)
#36ms for 20k

```

---

<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:** [September 20, 2021, 12:04pm UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/7 "2021-09-20T12:04:31Z")

</div>

This is expected. DataFrames.jl are efficient if you work on whole columns.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [September 20, 2021, 12:24pm UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/8 "2021-09-20T12:24:40Z")

</div>

DataFramesMeta’s `@eachrow` macro may be helpful here. It works as though you are working with a row, but actually is a for-loop with a function barrier acting on columns, so it’s fast.

---

<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:** [September 20, 2021, 1:03pm UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/9 "2021-09-20T13:03:40Z")

</div>

I always keep forgetting about it 😄. Still the column names need to be known in advance to make it work - right?

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [September 20, 2021, 1:47pm UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/10 "2021-09-20T13:47:43Z")

</div>

I’m not sure I follow the code in the example, but you can do interpolation with `$`

```julia
julia> using DataFramesMeta

julia> df = DataFrame(a = rand(10), b = rand(10));

julia> c_str = "c";

julia> @eachrow df begin 
           @newcol $c_str::Vector{Float64}
           $c_str = :a + :b
       end
10×3 DataFrame
 Row │ a b c         
     │ Float64 Float64 Float64   
─────┼────────────────────────────────
   1 │ 0.47983 0.554476 1.03431
   2 │ 0.682568 0.532267 1.21484
   3 │ 0.712391 0.869989 1.58238
   4 │ 0.020835 0.0237198 0.0445548
   5 │ 0.89675 0.596729 1.49348
   6 │ 0.784122 0.337654 1.12178
   7 │ 0.149992 0.401756 0.551748
   8 │ 0.823635 0.03158 0.855215
   9 │ 0.107912 0.845048 0.95296
  10 │ 0.139757 0.147285 0.287042

```

---

<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:** [September 20, 2021, 1:54pm UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/11 "2021-09-20T13:54:30Z")

</div>

In the OP `@eachrow` would be within a function and `c_str` would be from a vector holding many column identifiers.

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [September 20, 2021, 1:59pm UTC](https://discourse.julialang.org/t/adding-a-namedtuple-to-dataframe-row/68447/12 "2021-09-20T13:59:02Z")

</div>

Ah. You _cannot_ do the following, then

```julia
cs = ["c1", "c2", "c3"]
@eachrow df begin 
    for c in cs
        @newcol $c::Vector{Float64} = :a + :b
    end
end

```

In general that approach is impossible given metaprogramming.

You _can_ do the following

```julia
julia> function make_cols(df, cs)
           for c in cs
               @eachrow! df begin 
                   @newcol $c::Vector{Float64}
                   $c = :a + :b + rand()
               end
           end
           df
       end;

julia> make_cols(df, ["c1", "c2"])
10×4 DataFrame
 Row │ a b c1 c2       
     │ Float64 Float64 Float64 Float64  
─────┼─────────────────────────────────────────
   1 │ 0.47983 0.554476 1.41844 1.70928
   2 │ 0.682568 0.532267 2.19252 1.59863
   3 │ 0.712391 0.869989 1.96144 2.35732
   4 │ 0.020835 0.0237198 0.23945 0.122738
   5 │ 0.89675 0.596729 2.39555 2.35703
   6 │ 0.784122 0.337654 2.06556 1.38156
   7 │ 0.149992 0.401756 0.876493 1.08352
   8 │ 0.823635 0.03158 1.62591 0.991938
   9 │ 0.107912 0.845048 1.51362 1.16172
  10 │ 0.139757 0.147285 0.505221 0.326369

```

Though this code is hard to reason about I suppose.

Creating new columns inside `@eachrow` is something I might want to turn my attention to soon.