This device uses expression-ifelse(,,) to simulate the more powerful per-row block-if(){}else{}. The difference is expression-ifelse(,,) can choose per-row what value to express, whereas block-if(){}else{} can choose per-row where to assign multiple values. By simulation we mean: a sequence of quoted mutate expressions are emitted that implement the transform. These expressions can then be optimized into a minimal number of no-dependency blocks by extend_se for efficient execution. The idea is the user can write legible code in this notation, and the translation turns it into safe and efficient code suitable for execution either on data.frames or at a big data scale using RPostgreSQL or sparklyr.

if_else_block(testexpr, thenexprs = NULL, elseexprs = NULL)

Arguments

testexpr

character containing the test expression.

thenexprs

named character then assignments (altering columns, not creating).

elseexprs

named character else assignments (altering columns, not creating).

Details

Note: ifebtest_* is a reserved column name for this procedure.

Examples

# Example: clear one of a or b in any row where both are set. # Land random selections early to avoid SQLite bug. my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") d <- dbi_copy_to( my_db, 'd', data.frame(i = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), a = c(0, 0, 1, 1, 1, 1, 1, 1, 1, 1), b = c(0, 1, 0, 1, 1, 1, 1, 1, 1, 1), r = runif(10), edited = 0), temporary=TRUE, overwrite=TRUE) program <- if_else_block( testexpr = qe((a+b)>1), thenexprs = c( if_else_block( testexpr = qe(r >= 0.5), thenexprs = qae(a := 0), elseexprs = qae(b := 0)), qae(edited := 1))) print(program)
#> $ifebtest_2 #> [1] "(a + b) > 1" #> #> $ifebtest_1 #> [1] "r >= 0.5" #> #> $a #> [1] "ifelse( ifebtest_2, ifelse( ifebtest_1, 0, a), a)" #> #> $b #> [1] "ifelse( ifebtest_2, ifelse( ! ifebtest_1, 0, b), b)" #> #> $edited #> [1] "ifelse( ifebtest_2, 1, edited)" #>
trf <- extend_se(d, program) cat(format(trf))
#> table('d') %.>% #> extend(., #> ifebtest_2 := ( a + b ) > 1, #> ifebtest_1 := r >= 0.5) %.>% #> extend(., #> a := ifelse(ifebtest_2, ifelse(ifebtest_1, 0, a), a), #> b := ifelse(ifebtest_2, ifelse(!( ifebtest_1 ), 0, b), b), #> edited := ifelse(ifebtest_2, 1, edited))
sql <- to_sql(trf, my_db) cat(sql)
#> SELECT #> `i`, #> `ifebtest_1`, #> `ifebtest_2`, #> `r`, #> ( CASE WHEN ( `ifebtest_2` ) THEN ( ( CASE WHEN ( `ifebtest_1` ) THEN ( 0 ) ELSE ( `a` ) END ) ) ELSE ( `a` ) END ) AS `a`, #> ( CASE WHEN ( `ifebtest_2` ) THEN ( ( CASE WHEN ( ( NOT ( `ifebtest_1` ) ) ) THEN ( 0 ) ELSE ( `b` ) END ) ) ELSE ( `b` ) END ) AS `b`, #> ( CASE WHEN ( `ifebtest_2` ) THEN ( 1 ) ELSE ( `edited` ) END ) AS `edited` #> FROM ( #> SELECT #> `i`, #> `a`, #> `b`, #> `r`, #> `edited`, #> ( `a` + `b` ) > 1 AS `ifebtest_2`, #> `r` >= 0.5 AS `ifebtest_1` #> FROM ( #> SELECT #> `d`.`i`, #> `d`.`a`, #> `d`.`b`, #> `d`.`r`, #> `d`.`edited` #> FROM #> `d` #> ) tsql_0000 #> ) tsql_0001
DBI::dbGetQuery(my_db, sql)
#> i ifebtest_1 ifebtest_2 r a b edited #> 1 1 1 0 0.68016292 0 0 0 #> 2 2 0 0 0.49884561 0 1 0 #> 3 3 1 0 0.64167935 1 0 0 #> 4 4 1 1 0.66028435 0 1 1 #> 5 5 0 1 0.09602416 1 0 1 #> 6 6 1 1 0.76560016 0 1 1 #> 7 7 1 1 0.76967480 0 1 1 #> 8 8 1 1 0.99071231 0 1 1 #> 9 9 1 1 0.97052090 0 1 1 #> 10 10 0 1 0.38918276 1 0 1
# Why we need to land the random selection early # for SQLIte: q <- "SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT * from ( VALUES(1),(2) ) ) a ) b" DBI::dbGetQuery(my_db, q)
#> r1 r2 #> 1 -6381369950381479477 407347571150895551 #> 2 5476848436928024646 -8836863401132307497
DBI::dbDisconnect(my_db)