CODEWITHSUNDEEP
rdplyr
anup
anup

Reputation: 475

dplyr::mutate with dynamic variables created from column names

I referred to this and this, both of which did not work for me because they were both based on functions which call mutate and these functions are called probably in a loop, which is what I cannot do.

I have a df (can be reproduced by following code:) [[note: coincidentally, all "Y_****" columns here happen to have same value, but please ignore that. The main dataframe is actually very long, I have put only 6 rows here. ]] 

mainY <- structure(list(PolygonId = 0:5, Area = c(3.018892, 1.995702, 
2.277057, 1.176975, 1.983469, 4.533144), Perimeter = c(10.6415, 
8.6314, 9.2226, 6.1484, 10.2277, 12.0012), X0 = c(0.59, 0.654, 
0.51, 0.6, 0.622, 0.431), Y0 = c(1.4, 1.4, 1.4, 1.4, 1.4, 1.4
), phi = c(0.3, 0.3, 0.3, 0.3, 0.5, 0.3), J0 = c(0.49199, 0.33466, 
0.55057, 0.5076, 0.46434, 0.6574), h0 = c(1669.494, 1656.977, 
1683.435, 1660.62, 1670.445, 1707.416), mat0 = c(0.58, 0.74, 
0.39, 0.67, 0.47, 0.24), tc0 = c(0.4, 0.42, 0.37, 0.41, 0.38, 
0.35), z0 = c(0.8272, 0.8044, 0.744, 0.8505, 1.0288, 0.6703), 
    W0 = c(4764.9472, 3147.8891, 2859.4418, 1974.6163, 4127.504, 
    4670.4702), Y_a02 = c(1.4, 1.4, 1.4, 1.4, 1.4, 1.4), Y_a03 = c(1.4, 
    1.4, 1.4, 1.4, 1.4, 1.4), Y_a04 = c(1.4, 1.4, 1.4, 1.4, 1.4, 
    1.4), Y_b05 = c(1.4, 1.4, 1.4, 1.4, 1.4, 1.4), Y_b06 = c(1.4, 
    1.4, 1.4, 1.4, 1.4, 1.4)), .Names = c("PolygonId", "Area", 
"Perimeter", "X0", "Y0", "phi", "J0", "h0", "mat0", "tc0", "z0", 
"W0", "Y_a02", "Y_a03", "Y_a04", "Y_b05", "Y_b06"), row.names = c(NA, 
6L), class = "data.frame")

This is how it looks like:

enter image description here

The actual number of Y_**** columns is more than 20. It can increase when I decide to increase more data.

My code is as follow: (explanation is below the code)

calc.z <- function(x, y, phi, j){
  round(x * y * (phi + sqrt(j)),4)
}
calc.W <- function(tc, h, z, area){
  round(tc * h * pi * sqrt(z^3) * area, 4)
}

I need to calculate z and W for each row. The functions above show the formula for z and W.

Normally what I'd do is:

newdf<- dplyr::mutate(mainY,
       z_Y_a02  = calc.z(X0, Y_a02 , phi, J0), W_Y_a02  = calc.W(tc0, h0, z_Y_a02,  Area),
       z_Y_a03  = calc.z(X0, Y_a03 , phi, J0), W_Y_a03  = calc.W(tc0, h0, z_Y_a03,  Area))
# and so on 
# note here, for calculating all z_****, X0, phi, J0 are always used the same
# same for calculating W

But this is tedious, repeating same for 20+ columns and many more.

For the examples I mentioned above, I re wrote the code as follows::

newdf<- dplyr::mutate_at(mainY,
                      .vars = c("Y_a02","Y_a03","Y_a04","Y_b05","Y_b06"),
                      .funs = calc.z("X0",.vars,"phi","J0")
                      )
# This did not work. I again changed like this:

newdf<- dplyr::mutate_at(mainY,
                         .vars = c("Y_a02","Y_a03","Y_a04","Y_b05","Y_b06"),
                         .funs = calc.z("X0",.vars,"phi","J0")
                          )
# This does not work as well.

The following is the format of the result I expect. (( ## represents some number. ))

> newdf
  PolygonId     Area Perimeter    X0  Y0 phi      J0       h0 mat0  tc0     z0       W0 Y_a02 Y_a03 Y_a04 Y_b05 Y_b06   z_Y_a02   W_Y_a02   z_Y_a03   W_Y_a03   z_Y_a04   W_Y_a04   z_Y_b05   W_Y_b05   z_Y_b06   W_Y_b06
1         0 3.018892   10.6415 0.590 1.4 0.3 0.49199 1669.494 0.58 0.40 0.8272 4764.947   1.4   1.4   1.4   1.4   1.4      ##        ##         ##        ##       ##       ##        ##        ##         ##        ##
2         1 1.995702    8.6314 0.654 1.4 0.3 0.33466 1656.977 0.74 0.42 0.8044 3147.889   1.4   1.4   1.4   1.4   1.4      ##        ##         ##        ##       ##       ##        ##        ##         ##        ##
3         2 2.277057    9.2226 0.510 1.4 0.3 0.55057 1683.435 0.39 0.37 0.7440 2859.442   1.4   1.4   1.4   1.4   1.4      ##        ##         ##        ##       ##       ##        ##        ##         ##        ##
4         3 1.176975    6.1484 0.600 1.4 0.3 0.50760 1660.620 0.67 0.41 0.8505 1974.616   1.4   1.4   1.4   1.4   1.4      ##        ##         ##        ##       ##       ##        ##        ##         ##        ##
5         4 1.983469   10.2277 0.622 1.4 0.5 0.46434 1670.445 0.47 0.38 1.0288 4127.504   1.4   1.4   1.4   1.4   1.4      ##        ##         ##        ##       ##       ##        ##        ##         ##        ##
6         5 4.533144   12.0012 0.431 1.4 0.3 0.65740 1707.416 0.24 0.35 0.6703 4670.470   1.4   1.4   1.4   1.4   1.4      ##        ##         ##        ##       ##       ##        ##        ##         ##        ##

Upvotes: 2

Views: 2421

Answers (1)

missuse
missuse

Reputation: 19716

This should work:

library(tidyverse)
mainY %>%
  mutate_at(.vars = vars(Y_a02, Y_a03, Y_a04, Y_b05, Y_b06),
            .funs = funs(calc.z = calc.z(X0,.,phi,J0)))
#output

**  PolygonId     Area Perimeter    X0  Y0 phi      J0       h0 mat0  tc0     z0       W0 Y_a02 Y_a03 Y_a04
1         0 3.018892   10.6415 0.590 1.4 0.3 0.49199 1669.494 0.58 0.40 0.8272 4764.947   1.4   1.4   1.4
2         1 1.995702    8.6314 0.654 1.4 0.3 0.33466 1656.977 0.74 0.42 0.8044 3147.889   1.4   1.4   1.4
3         2 2.277057    9.2226 0.510 1.4 0.3 0.55057 1683.435 0.39 0.37 0.7440 2859.442   1.4   1.4   1.4
4         3 1.176975    6.1484 0.600 1.4 0.3 0.50760 1660.620 0.67 0.41 0.8505 1974.616   1.4   1.4   1.4
5         4 1.983469   10.2277 0.622 1.4 0.5 0.46434 1670.445 0.47 0.38 1.0288 4127.504   1.4   1.4   1.4
6         5 4.533144   12.0012 0.431 1.4 0.3 0.65740 1707.416 0.24 0.35 0.6703 4670.470   1.4   1.4   1.4
  Y_b05 Y_b06 Y_a02_calc.z Y_a03_calc.z Y_a04_calc.z Y_b05_calc.z Y_b06_calc.z
1   1.4   1.4       0.8272       0.8272       0.8272       0.8272       0.8272
2   1.4   1.4       0.8044       0.8044       0.8044       0.8044       0.8044
3   1.4   1.4       0.7440       0.7440       0.7440       0.7440       0.7440
4   1.4   1.4       0.8505       0.8505       0.8505       0.8505       0.8505
5   1.4   1.4       1.0288       1.0288       1.0288       1.0288       1.0288
6   1.4   1.4       0.6703       0.6703       0.6703       0.6703       0.6703

if you would like just to replace the Y_b** columns with the result: 

mainY %>%
      mutate_at(.vars = vars(Y_a02, Y_a03, Y_a04, Y_b05, Y_b06),
                .funs = funs(calc.z(X0, ., phi, J0)))

instead of typing all the columns in .vars you can also do something like this:

colnames(mainY)[grep("Y_.\\d{2}$", colnames(mainY))]

or "^Y_" as the pattern if non of the other columns start with "Y_".

where "Y_.\\d{2}$" depends works for the example in question but might need changing for your real table: 

mainY %>%
  mutate_at(.vars = colnames(.)[grep("Y_.\\d{2}$", colnames(.))],
            .funs = funs(calc.z = calc.z(X0, ., phi, J0)))

EDIT answer to the question in comments:

A way to pass the calculated calc.z values to calc.W. All columns will be kept: 

 mainY %>%
      mutate_at(.vars = colnames(.)[grep("Y_.\\d{2}$", colnames(.))],
                .funs = funs(calc.z = calc.z(X0, ., phi, J0))) %>%
      mutate_at(.vars = colnames(.)[grep("_calc.z$", colnames(.))],
                .funs = funs(calc.W = calc.z(tc0, h0, ., Area))

)

Upvotes: 3

Related Questions