# Working with multiple columns in DataFramesMeta, programmatically

**URL:** <https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056>\
**Category:** General Usage\
**Tags:** dataframes, dataframesmeta\
**Created:** [November 30, 2022, 4:31pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056 "2022-11-30T16:31:58Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [November 30, 2022, 4:31pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/1 "2022-11-30T16:31:58Z")

</div>

Suppose I have the following DataFrame:

```julia
test = DataFrame(
    w = [10, 13, 5],
    a1 = [12, 9, 6],
    a2 = [9, 10, 8],
    a3 = [14, 10, 12]
)

3×4 DataFrame
 Row │ w a1 a2 a3    
     │ Int64 Int64 Int64 Int64 
─────┼────────────────────────────
   1 │ 10 12 9 14
   2 │ 13 9 10 10
   3 │ 5 6 8 12

```

I would like to create a new column `v` that is the sum of the squared differences between all of the “a” columns (i.e., a1 - a3) and `w`, but I want to refer to the “a” columns programmatically.

I’ve tried several variations that go something like this:

```julia
a_cols = [Symbol("a$i") for i in 1:3]

3-element Vector{Symbol}:
 :a1
 :a2
 :a3

@combine(test,
    :total = sum(:w),
    @byrow :se = sum([(AsTable(a_cols) .- :w)^2])
)

```

and I get:

```julia
ERROR: ArgumentError: Column references must be either all the same type or a a combination of `Symbol`s and strings
Stacktrace:
 [1] make_source_concrete(x::Vector{Any})
   @ DataFramesMeta C:\Users\mthel\.julia\packages\DataFramesMeta\BkRtJ\src\parsing.jl:398
 [2] top-level scope
   @ C:\Users\mthel\.julia\packages\DataFramesMeta\BkRtJ\src\macros.jl:1944

```

The desired output would be:

```julia
3×5 DataFrame
 Row │ w a1 a2 a3 v     
     │ Int64 Int64 Int64 Int64 Int64 
─────┼───────────────────────────────────
   1 │ 10 12 9 14 21
   2 │ 13 9 10 10 34
   3 │ 5 6 8 12 59

```

---

<div class="post-metadata">

**Author:** ![j\_verzani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/j_verzani/32/8551_2.png) [@j\_verzani](https://discourse.julialang.org/u/j_verzani)\
**Post date:** [November 30, 2022, 4:46pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/2 "2022-11-30T16:46:25Z")

</div>

Maybe this:

```julia
transform(test, All() => ByRow((w,as...) -> sum((a-w)^2 for a ∈ as)) => :v)

```

---

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [November 30, 2022, 4:51pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/3 "2022-11-30T16:51:10Z")

</div>

> [@j\_verzani](#):
>
> `transform(test, All() => ByRow((w,as...) -> sum((a-w)^2 for a ∈ as)) => :v)`

Well, it doesn’t throw an error but it doesn’t produce the desired result unfortunately:

```julia
3×5 DataFrame
 Row │ w a1 a2 a3 v     
     │ Int64 Int64 Int64 Int64 Int64 
─────┼───────────────────────────────────
   1 │ 10 12 9 14 142
   2 │ 13 9 10 10 475
   3 │ 5 6 8 12 2975

```

---

<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:** [November 30, 2022, 4:56pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/4 "2022-11-30T16:56:28Z")

</div>

For me it works correctly:

```julia
julia> transform(test, All() => ByRow((w,as...) -> sum((a-w)^2 for a ∈ as)) => :v)
3×5 DataFrame
 Row │ w a1 a2 a3 v
     │ Int64 Int64 Int64 Int64 Int64
─────┼───────────────────────────────────
   1 │ 10 12 9 14 21
   2 │ 13 9 10 10 34
   3 │ 5 6 8 12 59

```

(and it looks correct)

Maybe a more careful code would be e.g.

```julia
julia> transform(test, Cols(:w, r"a\d") => ByRow((w,as...) -> sum((a-w)^2 for a ∈ as)) => :v)
3×5 DataFrame
 Row │ w a1 a2 a3 v
     │ Int64 Int64 Int64 Int64 Int64
─────┼───────────────────────────────────
   1 │ 10 12 9 14 21
   2 │ 13 9 10 10 34
   3 │ 5 6 8 12 59

```

but it is a detail.

Regarding DataFramesMeta.jl - I think it is not possible currently there, but @pdeffebach is working on it.

On the other hand DataFrameMacros.jl should support this I think - @jules should know best.

---

<div class="post-metadata">

**Author:** ![mthelm85](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mthelm85/32/224164_2.png) [@mthelm85](https://discourse.julialang.org/u/mthelm85)\
**Post date:** [November 30, 2022, 4:57pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/5 "2022-11-30T16:57:51Z")

</div>

> [@bkamins](#):
>
> `transform(test, Cols(:w, r"a\d") => ByRow((w,as...) -> sum((a-w)^2 for a ∈ as)) => :v)`

The second version works great. Thanks to you both!

---

<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:** [November 30, 2022, 5:15pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/6 "2022-11-30T17:15:26Z")

</div>

A simple way:

```julia
df.v = sum((df[:,a] - df.w).^2 for a in Symbol.("a", 1:3))

```

NB: renamed `test`, `df`

---

<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:** [November 30, 2022, 5:55pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/7 "2022-11-30T17:55:57Z")

</div>

This is a good point. Here is a less elegant solution with DataFramesMeta

```julia
julia> @combine(test,
           :total = sum(:w),
           @byrow :se = begin 
               nt = AsTable([a_cols; :w])
               v_a = collect(nt[a_cols])
               w = nt.w
               sum( (v_a .- w) .^2 )
           end
       )

```

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [November 30, 2022, 6:11pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/8 "2022-11-30T18:11:49Z")

</div>

Another simple solution (good mostly for this example, StatsBase needed):

```julia
test.v = 3var(Matrix(test); mean=test.w, dims=2)

```

(I would also assume that the variance is actually needed and then the `3` in front isn’t needed).

---

<div class="post-metadata">

**Author:** ![j\_verzani](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/j_verzani/32/8551_2.png) [@j\_verzani](https://discourse.julialang.org/u/j_verzani)\
**Post date:** [November 30, 2022, 6:22pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/9 "2022-11-30T18:22:10Z")

</div>

Does `All()` have a guarantee on the order of variables that are selected? I was assuming it was `1:ncol(df)`, but it looks more complicated than that in the source.

---

<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:** [November 30, 2022, 6:22pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/10 "2022-11-30T18:22:57Z")

</div>

> [@j\_verzani](#):
>
> Does `All()` have a guarantee on the order of variables that are selected?

yes `All()` produces the same order as `names(test)`.

---

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.julialang.org/u/jules)\
**Post date:** [November 30, 2022, 7:27pm UTC](https://discourse.julialang.org/t/working-with-multiple-columns-in-dataframesmeta-programmatically/91056/11 "2022-11-30T19:27:11Z")

</div>

@bkamins thanks for the tag, indeed this is something that DataFrameMacros [can do via `{{ }}` syntax](https://jkrumbiegel.com/DataFrameMacros.jl/stable/#%7B%7B%7D). This makes a tuple of the referenced columns so we can do:

```julia
using DataFrameMacros
using DataFrames

test = DataFrame(
    w = [10, 13, 5],
    a1 = [12, 9, 6],
    a2 = [9, 10, 8],
    a3 = [14, 10, 12]
)

@transform test :v = sum((:w .- {{r"a"}}) .^ 2)

3×5 DataFrame
 Row │ w a1 a2 a3 v     
     │ Int64 Int64 Int64 Int64 Int64 
─────┼───────────────────────────────────
   1 │ 10 12 9 14 21
   2 │ 13 9 10 10 34
   3 │ 5 6 8 12 59

```

The contrast to `{ }` specifiers is that those broadcast the whole expression, so if there were more `w`s one could do:

```julia
test2 = DataFrame(
    w1 = [10, 13, 5],
    w2 = [13, 15, 7],
    w3 = [9, 3, 10],
    a1 = [12, 9, 6],
    a2 = [9, 10, 8],
    a3 = [14, 10, 12]
)

@transform test2 "v_{}" = sum(({r"w"} .- {{r"a"}}) .^ 2)

3×9 DataFrame
 Row │ w1 w2 w3 a1 a2 a3 v_w1 v_w2 v_w3  
     │ Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 
─────┼───────────────────────────────────────────────────────────────
   1 │ 10 13 9 12 9 14 21 18 34
   2 │ 13 15 3 9 10 10 34 86 134
   3 │ 5 7 10 6 8 12 59 27 24

```