Adding new calculated columns in dataframe using formula from another dataframe

Question

Below are two dataframes, df and fmla. The fmla dataframe stores the variable name and the formula expression.

Using fmla dataframe, two calculated variables need to be added to df (purrr::map()?).

df <- tibble::tribble(
  ~a,   ~b,  ~c,  ~d,
  1000, 500, 300, 100,
  1100, 560, 330, 110,
  1200, 600, 360, 120,
  1300, 650, 390, 130
  )

fmla <- tibble::tribble(
  ~variable,  ~formula,
        "e", "(a+b)/c",
        "f",     "e*d"
  )

If the calculated columns would have been manually added, the result would be similar to below calculation:

(result <- df %>% 
  mutate(e = (a+b)/c, f =  e*d )
)


result <- tibble::tribble(
       ~a,  ~b,  ~c,  ~d, ~e,     ~f,
     1000, 500, 300, 100,  5,    500,
     1100, 560, 330, 110,  5.03, 553.33,
     1200, 600, 360, 120,  5,    600,
     1300, 650, 390, 130,  5,    650
     )

Ronak Shah · Accepted Answer · 2021-05-22 13:49:29Z

2

Dynamically doing this in base R -

for(i in seq(nrow(fmla))) {
  df[fmla$variable[i]] <- with(df, eval(parse(text = fmla$formula[i])))
}

#    a     b     c     d     e     f
#  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1  1000   500   300   100  5     500 
#2  1100   560   330   110  5.03  553.
#3  1200   600   360   120  5     600 
#4  1300   650   390   130  5     650

answered May 22, 2021 at 13:49

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anoushiravan R · Accepted Answer · 2021-05-22 13:08:22Z

2

I hope this is what you have in mind, perhaps a little more automated:

library(dplyr)
library(tidyr)
library(rlang)


fmla %>%
  pivot_wider(names_from = variable, values_from = formula) %>%
  bind_cols(df) %>%
  rowwise() %>%
  mutate(e = eval(parse_expr(e)),
         f = eval(parse_expr(f)))


# A tibble: 4 x 6
# Rowwise: 
      e     f     a     b     c     d
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1  5     500   1000   500   300   100
2  5.03  553.  1100   560   330   110
3  5     600   1200   600   360   120
4  5     650   1300   650   390   130

edited May 22, 2021 at 13:08

answered May 22, 2021 at 12:39

Anoushiravan R

22k3 gold badges22 silver badges44 bronze badges

2 Comments

Ajit Singh Over a year ago

Many thanks. Can there be one more way? Being non-technical, coding is bit challenging. But after reading about tidyeval package, can the solution be something on the line of: * for each row of fmla dataframe, invoke a anonymous function on the df dataframe, using appropriate purrr function. * The anonymous function would take variable and formula as text argument, convert to symbol, and quote it (enquo()?) * the function would prepare the mutate statement by unquoting the variable and formula. * the function would execute the mutate statement to add the calculated column.

Anoushiravan R Over a year ago

Your welcome. I edited my codes to use parse_expr from rlang package and now I'm thinking of a way to more automate this. I totally got your point. I will update it as soon as I come up with something. Just wanted to propose an effective solution maybe not the most efficient one but I will update asap.

akrun · Accepted Answer · 2021-05-22 19:57:18Z

2

Using Map from base R

Map(function(x, y) df[[x]] <<- eval(parse(text= y), envir = df),
   fmla$variable, fmla$formula)

-output

df
# A tibble: 4 x 6
#      a     b     c     d     e     f
#  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1  1000   500   300   100  5     500 
#2  1100   560   330   110  5.03  553.
#3  1200   600   360   120  5     600 
#4  1300   650   390   130  5     650

answered May 22, 2021 at 19:57

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Adding new calculated columns in dataframe using formula from another dataframe

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related