Remap values in a set of columns.

map_column_values(source, colmap, ..., null_default = FALSE)

Arguments

source	optree relop node or data.frame.
colmap	data.frame with columns column_name, old_value, new_value.
...	force later arguments to bind by name.
null_default	logical, if TRUE map non-matching values to NULL (else they map to self).

Value

implementing optree or altered data.frame

Examples


if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) {
  my_db <- DBI::dbConnect(RSQLite::SQLite(),
                          ":memory:")

  d <- rq_copy_to(my_db, 'd',
                   data.frame(a = c("1", "2", "1", "3"),
                              b = c("1", "1", "3", "2"),
                              c = c("1", "2", "3", "4"),
                              stringsAsFactors = FALSE),
                   temporary = TRUE,
                   overwrite = TRUE)
  mp <- build_frame(
      "column_name", "old_value", "new_value" |
      "a"          , "1"        , "10"        |
      "a"          , "2"        , "20"        |
      "b"          , "1"        , "100"       |
      "b"          , "3"        , "300"       )

  # example
  op_tree <- d %.>%
    map_column_values(., mp)
  cat(format(op_tree))
  sql <- to_sql(op_tree, my_db)
  cat(sql)
  print(DBI::dbGetQuery(my_db, sql))

  # cleanup
  DBI::dbDisconnect(my_db)
}
#> mk_td("d", c(
#>   "a",
#>   "b",
#>   "c")) %.>%
#>  sql_node(.,
#>           map_column_values(., mp))
#> SELECT
#>  CASE  WHEN  `a`  =  '1'  THEN  '10' WHEN  `a`  =  '2'  THEN  '20'  ELSE  `a`  END AS `a`,
#>  CASE  WHEN  `b`  =  '1'  THEN  '100' WHEN  `b`  =  '3'  THEN  '300'  ELSE  `b`  END AS `b`,
#>  `c` AS `c`
#> FROM (
#>  SELECT
#>   `a`,
#>   `b`,
#>   `c`
#>  FROM
#>   `d`
#> ) tsql_07313838060301891726_0000000000
#>    a   b c
#> 1 10 100 1
#> 2 20 100 2
#> 3 10 300 3
#> 4  3   2 4