# Evaluate a formula inside a dataframes to create a new column

**URL:** <https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208>\
**Category:** Data\
**Tags:** question, dataframes, eval\
**Created:** [August 25, 2023, 10:03pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208 "2023-08-25T22:03:40Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![gaspardelanuit](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@gaspardelanuit](https://discourse.julialang.org/u/gaspardelanuit)\
**Post date:** [August 25, 2023, 10:03pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/1 "2023-08-25T22:03:40Z")

</div>

I have a dataset that includes a column which has a formula.  
The formula is a recipe to get a new column from existing columns.

Essentially the data looks like the following

```julia
> df = DataFrame(l=1:8, a=[1, 1, 1, 2, 2, 1, 1, 2],
   b=repeat([2, 1], outer=[4]),c=1:8,
   formula = [repeat([":b+:c"], 3); repeat([":a+:c"], 5)])
   
8×5 DataFrame
 Row │ l a b c formula
     │ Int64 Int64 Int64 Int64 String
─────┼─────────────────────────────────────
   1 │ 1 1 2 1 :b+:c
   2 │ 2 1 1 2 :b+:c
   3 │ 3 1 2 3 :b+:c
   4 │ 4 2 1 4 :a+:c
   5 │ 5 2 2 5 :a+:c
   6 │ 6 1 1 6 :a+:c
   7 │ 7 1 2 7 :a+:c
   8 │ 8 2 1 8 :a+:c

```

I am trying to create a new column which would have either the value a `b+c` or `a+c` as given by the column named formula.  
This column is a string and depending on preprocessing looks like `a+b` or `:a+:b` (could also be `:a+:b-:c`).

The result I am looking for is something like:

```julia
df = DataFrame(l=1:8, a=[1, 1, 1, 2, 2, 1, 1, 2],
           b=repeat([2, 1], outer=[4]),c=1:8,
           formula = [repeat([":b+:c"], 3); repeat([":a+:c"], 5)],
           value = [3,3,5,6,7,7,8,10])
8×6 DataFrame
 Row │ l a b c formula value
     │ Int64 Int64 Int64 Int64 String Int64
─────┼────────────────────────────────────────────
   1 │ 1 1 2 1 :b+:c 3
   2 │ 2 1 1 2 :b+:c 3
   3 │ 3 1 2 3 :b+:c 5
   4 │ 4 2 1 4 :a+:c 6
   5 │ 5 2 2 5 :a+:c 7
   6 │ 6 1 1 6 :a+:c 7
   7 │ 7 1 2 7 :a+:c 8
   8 │ 8 2 1 8 :a+:c 10

```

I have tried to use metaprogramming after splitting the table `groupby(df, :formula)`, but I run into the issue that the scope of eval is global, so the formula and the subdataframes are not properly evaluated.

This is related to this [SO post](https://stackoverflow.com/questions/21267962/how-can-i-eval-a-local-variable-in-julia).

Thanks for the help!

---

<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:** [August 25, 2023, 11:17pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/2 "2023-08-25T23:17:50Z")

</div>

```julia
ifelse.(dr.formula.== Symbol("a+b"), df.a .+ df.b, df.a .+ df.c)

```

---

<div class="post-metadata">

**Author:** ![gaspardelanuit](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@gaspardelanuit](https://discourse.julialang.org/u/gaspardelanuit)\
**Post date:** [August 26, 2023, 12:45am UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/3 "2023-08-26T00:45:55Z")

</div>

Thanks!  
Technically this could work but I am dealing with something like 50 different formulas which is why I am trying to use macros.

---

<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:** [August 26, 2023, 5:04am UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/4 "2023-08-26T05:04:46Z")

</div>

I don’t understand how macros could be of help here, the formulas are values but macros operate on expressions?

Do the formulas have some structure or could they be anything?

---

<div class="post-metadata">

**Author:** ![jd-foster](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jd-foster/32/35824_2.png) [@jd-foster](https://discourse.julialang.org/u/jd-foster)\
**Post date:** [August 26, 2023, 10:53am UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/5 "2023-08-26T10:53:02Z")

</div>

The formulas use existing column names represented as Strings or Symbols as the operands and simple binary operators (+, -,etc), as I understand it.

---

<div class="post-metadata">

**Author:** ![gaspardelanuit](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@gaspardelanuit](https://discourse.julialang.org/u/gaspardelanuit)\
**Post date:** [August 26, 2023, 2:11pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/6 "2023-08-26T14:11:04Z")

</div>

The equivalent in R using data.table would be to do:

```plaintext
R> dt = data.table(l=1:8, a=c(1, 1, 1, 2, 2, 1, 1, 2),
   b=rep(c(2, 1), 4), c=1:8,
   formula = c(rep("b+c", 3), rep("a+c", 5)))
   
R > dt[, value:=eval(parse(text=formula)), by = .(l)]   

```

where the formula is parsed and then evaluated.

My initial idea was to group the data.frame by formulas, parse the formula and create a macro to create the new value, but alas the subdataframe could not be evaluated in the macro because it was in local scope.

---

<div class="post-metadata">

**Author:** ![bertschi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bertschi/32/33462_2.png) [@bertschi](https://discourse.julialang.org/u/bertschi)\
**Post date:** [August 26, 2023, 2:16pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/7 "2023-08-26T14:16:01Z")

</div>

As your formula depends on runtime information, you will need to use `eval` one way or the other. Instead of trying to evaluate the formula code directly – which would happen in the global environment – you can build a function and call that later on. Here is a simple example (assuming that you know which data frame columns to use before hand):

```julia
using DataFrames, MacroTools
df = ... # copy your example here
function makeFun(formula)
    expr = MacroTools.postwalk(e -> if e isa QuoteNode; e.value else e end, Meta.parse(formula))
    eval(:((a, b, c) -> $expr))
end
# Now use as follows
funs = makeFun.(df.formula)
zip(df.a, df.b, df.c) .|> splat.(funs)

```

Obviously, you can extend the approach by further meta-programming to take the arguments from a given data frame …

---

<div class="post-metadata">

**Author:** ![gaspardelanuit](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@gaspardelanuit](https://discourse.julialang.org/u/gaspardelanuit)\
**Post date:** [August 27, 2023, 3:11pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/8 "2023-08-27T15:11:13Z")

</div>

Thanks!  
I think this works. I have adjusted it so that I don’t have to reference the arguments directly and can select specific columns that show up in the formulas.

```julia
using DataFrames, MacroTools
df = DataFrame(l=1:8, a=[1, 1, 1, 2, 2, 1, 1, 2],
   b=repeat([2, 1], outer=[4]),c=1:8,
   formula = [repeat([":b+:c"], 3); repeat([":a+:c"], 5)])

# some form of column selector that includes at least the columns that will show up in the formulas
col_arguments_idx = 1:4; 

function makeFun(formula)
    expr = MacroTools.postwalk(e -> if e isa QuoteNode; e.value else e end, Meta.parse(formula))
    eval(Meta.parse("(" * join(names(df)[col_arguments_idx], ",") * ") -> $expr"))
end

funs = makeFun.(df.formula)
df.value .= eachrow(df[:, 1:4]) .|> Tuple .|> splat.(funs);

```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [August 27, 2023, 4:35pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/9 "2023-08-27T16:35:23Z")

</div>

I wonder if this task can be done similarly to what DataFrameMacros uses, when using expressions like  
`@transform(df, :d=:a+:b)` to create new columns.  
If you want to use different expressions, you can proceed to a partition first, using the groupby() function.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [August 27, 2023, 4:54pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/10 "2023-08-27T16:54:25Z")

</div>

Would a brutal shortcut like this be acceptable for your cases?

```julia
function makeFun1(formula)
    expr = replace(formula, ':'=>"")
    eval(Meta.parse("(" * join(names(df)[col_arguments_idx], ",") * ") -> $expr"))
end

```

---

<div class="post-metadata">

**Author:** ![bertschi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bertschi/32/33462_2.png) [@bertschi](https://discourse.julialang.org/u/bertschi)\
**Post date:** [August 27, 2023, 7:09pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/11 "2023-08-27T19:09:26Z")

</div>

Good that you could make it work. Just a few remarks:

1. Please don’t use string manipulation for constructing expressions, Julia has dedicated data types for this purpose. Here is a classic why meta-programming should not be based on strings: [Why Lisp macros are cool, a Perl perspective](https://gist.github.com/ayosec/3121940)

2. Don’t have `makeFun` depend on global state, better pass all required information explicitly.

Here is how I would extend the function:

```julia
function makeFun(argnames, formula)
    expr = MacroTools.postwalk(e -> if e isa QuoteNode; e.value else e end,
                               Meta.parse(formula))
    args = Expr(:tuple, Symbol.(argnames)...)
    eval(:($args -> $expr))
end

col_arguments_idx = 1:4
funs = makeFun.(Ref(names(df)[col_arguments_idx]), df.formula)
eachrow(df[:, col_arguments_idx]) .|> Tuple .|> splat.(funs);
```

---

<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:** [August 27, 2023, 7:38pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/12 "2023-08-27T19:38:16Z")

</div>

A simple way:

```julia
df.out = [eval(Meta.parse(replace(df.formula[i], ":"=>"df[$i,:]."))) for i in 1:nrow(df)]

```

---

<div class="post-metadata">

**Author:** ![bertschi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bertschi/32/33462_2.png) [@bertschi](https://discourse.julialang.org/u/bertschi)\
**Post date:** [August 27, 2023, 8:08pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/13 "2023-08-27T20:08:14Z")

</div>

Yes, but again manipulating strings is brittle:

```julia
df = DataFrame(a = 1:2, b = 3:4, formula = "sum(:a:(:b+1))")
[eval(Meta.parse(replace(df.formula[i], ":"=>"df[$i,:]."))) for i in 1:nrow(df)]
[eval(MacroTools.postwalk(e -> if e isa QuoteNode; :(getindex(df, $i, $e)) else e end, Meta.parse(df.formula[i]))) for i in 1:nrow(df)]

```

---

<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:** [August 27, 2023, 8:13pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/14 "2023-08-27T20:13:49Z")

</div>

> [@bertschi](#):
>
> manipulating strings is brittle:

Yes, they are suitable for simple minds like mine 😓

---

<div class="post-metadata">

**Author:** ![gaspardelanuit](https://avatars.discourse-cdn.com/v4/letter/g/f475e1/32.png) [@gaspardelanuit](https://discourse.julialang.org/u/gaspardelanuit)\
**Post date:** [August 27, 2023, 8:28pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/15 "2023-08-27T20:28:30Z")

</div>

Thank you.

I guess this is a little mysterious to me.  
I am wondering how I can learn/understand this code to reapply it in different context. I find the macro documentation not particularly enlightening.

---

<div class="post-metadata">

**Author:** ![bertschi](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bertschi/32/33462_2.png) [@bertschi](https://discourse.julialang.org/u/bertschi)\
**Post date:** [August 27, 2023, 10:49pm UTC](https://discourse.julialang.org/t/evaluate-a-formula-inside-a-dataframes-to-create-a-new-column/103208/16 "2023-08-27T22:49:24Z")

</div>

TBH, I learned macros in Lisp where they are slightly easier as the data structures involved are simpler, i.e., reducing the impedance mismatch between the code you see and its internal representation. If you want to go this route, [On Lisp](http://www.paulgraham.com/onlisp.html) is still a great read.  
Anyways, let’s start with the famous REPL, i.e., read-eval-print-loop which quite accurately describes what is happening when you interact with Julia:

1. Your code is _read_ from the terminal (as text) and parsed into an internal data structure
2. This data (representing your code) is then _evaluated_ – which in Julia involves compiling and then executing the code
3. The result is _printed_ to you on the terminal, i.e., converted into a textual representation again.
4. Now _loop_, i.e., go back to step 1

A macro is basically a function that can act between steps 1 and 2, i.e., it can transform the data structure representing code before it is passed for evaluation. Let’s have a closer look at this using the Julia REPL itself:

```julia
julia> txt = "a = b + 1" # code as text
"a = b + 1"
julia> expr = Meta.parse(txt) # parse into data
:(a = b + 1)
julia> Meta.dump(expr) # Look at data in more detail => its a tree containing Expr nodes, Symbols, Numbers etc
Expr
  head: Symbol call
  args: Array{Any}((3,))
    1: Symbol +
    2: Symbol a
    3: Expr
      head: Symbol call
      args: Array{Any}((3,))
        1: Symbol *
        2: Symbol b
        3: Int64 2
julia> using MacroTools
julia> MacroTools.postwalk(e -> @show(e), expr) # walk that tree in post-order
e = :a
e = :+
e = :b
e = 1
e = :(b + 1)
e = :(a = b + 1)
:(a = b + 1)
# Now let's write a simple transformation which inserts code for printing whenever hitting a call expression
julia> function _tracecall(e)
           if e isa Expr && e.head == :call
               Expr(:block,
                    Expr(:println, "Calling ", string(e)),
                    e)
           else
               e
           end
       end
julia> _tracecall(expr) # not a call expr
:(a = b + 1)
julia> _tracecall(expr.args[2]) # but this one is
quote
    println("Calling ", "b + 1")
    b + 1
end
julia> MacroTools.postwalk(_tracecall, expr) # Transform every call in the tree
:(a = begin
          println("Calling ", "b + 1")
          b + 1
      end)
# Turn this into a macro
julia> macro tracecalls(e) MacroTools.postwalk(_tracecall, e) end
@tracecalls (macro with 1 method)
julia> @tracecalls a = b + 1
Calling b + 1
11

```

Now, fix this macro to work properly on multiple nested calls and simplify the construction of the expression using [Interpolation](https://docs.julialang.org/en/v1/manual/metaprogramming/#Expressions-and-evaluation) …