# Most efficient way to add new columns in each SubDataFrame of a GroupDataFrame

**URL:** <https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272>\
**Category:** Performance\
**Tags:** question, dataframes\
**Created:** [October 26, 2022, 6:49am UTC](https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272 "2022-10-26T06:49:46Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [October 26, 2022, 6:49am UTC](https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272/1 "2022-10-26T06:49:46Z")

</div>

Sorry kind of a beginners question again. Suppose I have a large GroupDataFrame and I would like to add columns to each SubDataFrame in the Group.

```julia
function build(GDF)
    for k = eachindex(GDF)
    GDF[k].newcol1 = function1(GDF[k].col1)
    GDF[k].newcol2 = function2(GDF[k].col2)
end 

```

Is there a better way to go about this? The performance I am seeing is quite slow and I am not sure if this is the bottleneck or if it is an issue with the function themselves.

According to the documentation pre-allocating output should help sometimes but when I tried this out with a DataFrame performance actually slowed.

```julia
df3 = DataFrame(X = [1, 2, 3, 4], Y = [0, 1, 2, 4])

```

```julia
julia> @benchmark df3.A = df3.X + df3.Y
BenchmarkTools.Trial: 10000 samples with 960 evaluations.
 Range (min … max): 84.158 ns … 1.026 μs ┊ GC (min … max): 0.00% … 89.01%
 Time (median): 89.800 ns ┊ GC (median): 0.00%
 Time (mean ± σ): 94.999 ns ± 39.546 ns ┊ GC (mean ± σ): 1.77% ± 4.00%

  █▇▅▂▄▆▇▆▅▃▃▃▃▃▃▃▃▃▃▂▂▂▂▂▂▂▂▁▂▁▁ ▁▁ ▁ ▂
  █████████████████████████████████████████████▇▇▆▇▇▇▇▆▇▇▆▆▆▇ █
  84.2 ns Histogram: log(frequency) by time 137 ns <

 Memory estimate: 96 bytes, allocs estimate: 1.

```

Whereas preallocating a vector before hand yielded

```julia
julia> function f1(df)
       df.G = Vector{Int}(undef,4)
       df.G = df.X+df.Y
       end

f1 (generic function with 1 method)

julia> @benchmark f1(df3)
BenchmarkTools.Trial: 10000 samples with 919 evaluations.
 Range (min … max): 109.675 ns … 2.028 μs ┊ GC (min … max): 0.00% … 92.06%
 Time (median): 111.353 ns ┊ GC (median): 0.00%
 Time (mean ± σ): 124.812 ns ± 108.725 ns ┊ GC (mean ± σ): 5.75% ± 6.19%

  █▅▃▁▃▄▄▄▃▃▂▂▁▁▁▁▁▁▁▁▁ ▁ ▁
  █████████████████████████████▇▇▇▇▇▇█▇▇▆▆▆▆▆▆▄▆▆▆▅▄▅▃▄▅▂▄▄▅▅▃▅ █
  110 ns Histogram: log(frequency) by time 178 ns <

 Memory estimate: 224 bytes, allocs estimate: 3.

```

and using `transform` seems to be even slower

```julia
@benchmark @transform(df3, :Q = :X .+:Y)
BenchmarkTools.Trial: 10000 samples with 1 evaluation.
 Range (min … max): 11.625 μs … 4.450 ms ┊ GC (min … max): 0.00% … 99.20%
 Time (median): 12.875 μs ┊ GC (median): 0.00%
 Time (mean ± σ): 14.912 μs ± 61.162 μs ┊ GC (mean ± σ): 5.75% ± 1.40%

  ▄▆▇█▇▇▆▅▄▄▃▃▂▂▂▃▂▂▂▂▁▁▁ ▁▁▂▁▁▁ ▁▁ ▂
  █████████████████████████████████████████▇▇█▇▇▇▇▇▆▆▆▆▆▆▅▆▆▆ █
  11.6 μs Histogram: log(frequency) by time 26.1 μs <

 Memory estimate: 9.20 KiB, allocs estimate: 168.

```

Any tips on what I’m doing wrong would be greatly appreciated thanks!

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [October 26, 2022, 7:40am UTC](https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272/2 "2022-10-26T07:40:18Z")

</div>

I’m a little bit confused - your question is about adding columns to a `SubDataFrame`, but your benchmarking code is all about adding columns to a regular `DataFrame`. Are you concerned about a slowdown when adding columns to a `SubDataFrame` compared to working with a `DataFrame` or about the speed of adding a column to a `DataFrame` in general?

Also your first attempt runs in 84 nano(!)seconds and has one allocation, do you think that this is somehow “too slow”? If so, what would you expect? Preallocation is a useful strategy when you can re-use memory in a calculation, but in this case you want to create a new vector, so you will have to make at least one allocation.

---

<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:** [October 26, 2022, 10:07am UTC](https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272/3 "2022-10-26T10:07:05Z")

</div>

> [@phantom](#):
>
> ```julia
> function build(GDF)
> for k = eachindex(GDF)
> GDF[k].newcol1 = function1(GDF[k].col1)
> GDF[k].newcol2 = function2(GDF[k].col2)
> end 
> 
> ```

A standard way to write it would be:

```julia
transform!(gdf, :col1 => function1 => :newcol1, :col2 => function2 => :newcol2)

```

and this should be efficient.

Of course this assumes that your data frame is large enough. For very small data frames (as in your example) compilation and bookkeeping will be much more expensive than computations themselves.

---

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [October 26, 2022, 11:03am UTC](https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272/4 "2022-10-26T11:03:10Z")

</div>

Thanks so much for pointing this out! I was concerned about the speed of adding columns to a SubDataFrame but I used the DataFrame as a bench mark because the Group Data Frame I am working with is quite large and I assumed that the most efficient method to add columns to a DataFrame would apply equally to a Sub Data Frame. Is this an incorrect assumption? Also thanks for explaining the performance with respect to pre-allocation. Going over the documentation makes a lot more sense now. So is it safe to say that it is not the pre-allocation that improves performance but just preventing of re-allocating memory that improves performance?

---

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [October 26, 2022, 11:20am UTC](https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272/5 "2022-10-26T11:20:11Z")

</div>

Thanks so much Bogumił ! Sorry I’m still having a little difficulty understanding the pair notation even though I’ve seen it used quite often. Is it the case that for some column x in a DataFrame df the pairing operator `=>` here runs the function on the column such that

```julia
:x => function === function(df.x)

```

whereas in the second instance of the pairing operator, it is acting as an assignment for :newcol? what happens if the function involves more than one column? Also thanks for clarifying the discrepancy in the performance of transform!

---

<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:** [October 26, 2022, 1:47pm UTC](https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272/6 "2022-10-26T13:47:56Z")

</div>

The operator works as follows:

```julia
[:source_column] => [function] => [:target_column]

```

which is (simlifying a bit but I understand you want a mental model) the same as:

```julia
df.target_column = function(df.source_column)

```

So as you can see the `=>` operator shows the ETL data flow (if you happened to use data bases):

- Extract a column
- Transform data
- Load the result into a data frame

Function can involve both many input columns and many output columns. Let me give you a concrete example:

```julia
julia> df = DataFrame(a=[1, 2, 3, 4], b=[4, 3, 2, 1])
4×2 DataFrame
 Row │ a b
     │ Int64 Int64
─────┼──────────────
   1 │ 1 4
   2 │ 2 3
   3 │ 3 2
   4 │ 4 1

julia> f(a, b) = extrema.(zip(a, b))
f (generic function with 1 method)

julia> f(df.a, df.b)
4-element Vector{Tuple{Int64, Int64}}:
 (1, 4)
 (2, 3)
 (2, 3)
 (1, 4)

julia> transform!(df, [:a, :b] => f => [:min, :max])
4×4 DataFrame
 Row │ a b min max
     │ Int64 Int64 Int64 Int64
─────┼────────────────────────────
   1 │ 1 4 1 4
   2 │ 2 3 2 3
   3 │ 3 2 2 3
   4 │ 4 1 1 4

```

As you can see the “extract” part can fetch multiple columns and the “load part” can store back multiple columns. The details here are more complex (and are related to `AsTable` wrapper). I recommend you to either read the documentation for explanation or my [DataFrames.jl minilanguage explained | Blog by Bogumił Kamiński](https://bkamins.github.io/julialang/2020/12/24/minilanguage.html) post.

---

<div class="post-metadata">

**Author:** ![phantom](https://avatars.discourse-cdn.com/v4/letter/p/e0b2c6/32.png) [@phantom](https://discourse.julialang.org/u/phantom)\
**Post date:** [October 27, 2022, 2:08am UTC](https://discourse.julialang.org/t/most-efficient-way-to-add-new-columns-in-each-subdataframe-of-a-groupdataframe/89272/7 "2022-10-27T02:08:14Z")

</div>

Thanks the explanation and blog post is really helpful! looking forward to the hardcopy of your book in December!