Indicate NULLs per row for given column set.

Source: R/work_with_NULLs.R

Build a query that creates NULL indicators for nulls in selected columns.

mark_null_cols(source, cols)

Arguments

source

incoming rel_op tree or data.frame.
cols

named character, values are columns to track, names are where to land indicators.

Value

rel_op node or data.frame (depending on input).

See also

null_replace, count_null_cols

Examples


# WARNING: example tries to change rquery.rquery_db_executor option to RSQLite and back.
if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) {
  my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  RSQLite::initExtension(my_db)
  old_o <- options(list("rquery.rquery_db_executor" = list(db = my_db)))

  d <- rq_copy_to(my_db, 'd',
                   data.frame(AUC = c(0.6, 0.5, NA),
                              R2 = c(1.0, 0.9, NA)))
  op_tree <- d %.>% mark_null_cols(., qc(AUC_NULL, R2_NULL) %:=%
                                     qc(AUC, R2))
  cat(format(op_tree))
  sql <- to_sql(op_tree, my_db)
  cat(sql)
  print(DBI::dbGetQuery(my_db, sql))

  # ad-hoc mode
  data.frame(AUC=c(1,NA,0.5), R2=c(NA,1,0)) %.>%
     op_tree %.>%
     print(.)

  # cleanup
  options(old_o)
  DBI::dbDisconnect(my_db)
}

#> mk_td("d", c(
#>   "AUC",
#>   "R2")) %.>%
#>  sql_node(.,
#>           mark_null_cols(c('AUC_NULL' = 'AUC', 'R2_NULL' = 'R2')))
#> SELECT
#>  `AUC` AS `AUC`,
#>  `R2` AS `R2`,
#>  `AUC` IS NULL AS `AUC_NULL`,
#>  `R2` IS NULL AS `R2_NULL`
#> FROM (
#>  SELECT
#>   `AUC`,
#>   `R2`
#>  FROM
#>   `d`
#> ) tsql_30342753490658846898_0000000000
#>   AUC  R2 AUC_NULL R2_NULL
#> 1 0.6 1.0        0       0
#> 2 0.5 0.9        0       0
#> 3  NA  NA        1       1
#>   AUC R2 AUC_NULL R2_NULL
#> 1 1.0 NA        0       1
#> 2  NA  1        1       0
#> 3 0.5  0        0       0