General transform from arbitrary record shape to arbitrary record shape.

convert_records(
  table,
  incoming_shape = NULL,
  outgoing_shape = NULL,
  ...,
  keyColumns = NULL,
  columnsToCopy_in = NULL,
  checkNames = TRUE,
  checkKeys = FALSE,
  strict = FALSE,
  incoming_controlTableKeys = colnames(incoming_shape)[[1]],
  outgoing_controlTableKeys = colnames(outgoing_shape)[[1]],
  tmp_name_source = wrapr::mk_tmp_name_source("crec"),
  temporary = TRUE,
  allow_rqdatatable_in = FALSE,
  allow_rqdatatable_out = TRUE
)

Arguments

table

data.frame or relop.

incoming_shape

data.frame, definition of incoming record shape.

outgoing_shape

data.frame, defintion of outgoing record shape.

...

force later arguments to bind by name.

keyColumns

character vector of column defining incoming row groups

columnsToCopy_in

character array of incoming column names to copy.

checkNames

logical, if TRUE check names.

checkKeys

logical, if TRUE check columnsToCopy form row keys (not a requirement, unless you want to be able to invert the operation).

strict

logical, if TRUE check control table name forms.

incoming_controlTableKeys

character, which column names of the incoming control table are considered to be keys.

outgoing_controlTableKeys

character, which column names of the outgoing control table are considered to be keys.

tmp_name_source

a tempNameGenerator from cdata::mk_tmp_name_source()

temporary

logical, if TRUE use temporary tables

allow_rqdatatable_in

logical, if TRUE allow rqdatatable shortcutting on simple conversions.

allow_rqdatatable_out

logical, if TRUE allow rqdatatable shortcutting on simple conversions.

Value

processing pipeline or transformed table

Examples

incoming_shape <- qchar_frame( "row", "col1", "col2", "col3" | "row1", v11, v12, v13 | "row2", v21, v22, v23 | "row3", v31, v32, v33 ) outgoing_shape <- qchar_frame( "column", "row1", "row2", "row3" | "col1", v11, v21 , v31 | "col2", v12, v22 , v32 | "col3", v13, v23 , v33 ) data <- build_frame( 'record_id', 'row', 'col1', 'col2', 'col3' | 1, 'row1', 1, 2, 3 | 1, 'row2', 4, 5, 6 | 1, 'row3', 7, 8, 9 | 2, 'row1', 11, 12, 13 | 2, 'row2', 14, 15, 16 | 2, 'row3', 17, 18, 19 ) print(data)
#> record_id row col1 col2 col3 #> 1 1 row1 1 2 3 #> 2 1 row2 4 5 6 #> 3 1 row3 7 8 9 #> 4 2 row1 11 12 13 #> 5 2 row2 14 15 16 #> 6 2 row3 17 18 19
convert_records( data, keyColumns = 'record_id', incoming_shape = incoming_shape, outgoing_shape = outgoing_shape)
#> record_id column row1 row2 row3 #> 1 1 col1 1 4 7 #> 2 1 col2 2 5 8 #> 3 1 col3 3 6 9 #> 4 2 col1 11 14 17 #> 5 2 col2 12 15 18 #> 6 2 col3 13 16 19
td <- rquery::local_td(data) ops <- convert_records( td, keyColumns = 'record_id', incoming_shape = incoming_shape, outgoing_shape = outgoing_shape) cat(format(ops))
#> mk_td("data", c( #> "record_id", #> "row", #> "col1", #> "col2", #> "col3")) %.>% #> non_sql_node(., CREATE TEMPORARY TABLE "OUT" AS SELECT a."record_id" "record_id", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row1' THEN a."col1" ELSE NULL END ) "v11", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row1' THEN a."col2" ELSE NULL END ) "v12", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row1' THEN a."col3" ELSE NULL END ) "v13", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row2' THEN a."col1" ELSE NULL END ) "v21", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row2' THEN a."col2" ELSE NULL END ) "v22", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row2' THEN a."col3" ELSE NULL END ) "v23", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row3' THEN a."col1" ELSE NULL END ) "v31", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row3' THEN a."col2" ELSE NULL END ) "v32", MAX( CASE WHEN CAST(a."row" AS VARCHAR) = 'row3' THEN a."col3" ELSE NULL END ) "v33" FROM "IN" a GROUP BY a."record_id") %.>% #> non_sql_node(., CREATE TEMPORARY TABLE "OUT" AS SELECT a."record_id", b."column", CASE WHEN CAST(b."column" AS VARCHAR) = 'col1' THEN a."v11" WHEN CAST(b."column" AS VARCHAR) = 'col2' THEN a."v12" WHEN CAST(b."column" AS VARCHAR) = 'col3' THEN a."v13" ELSE NULL END AS "row1", CASE WHEN CAST(b."column" AS VARCHAR) = 'col1' THEN a."v21" WHEN CAST(b."column" AS VARCHAR) = 'col2' THEN a."v22" WHEN CAST(b."column" AS VARCHAR) = 'col3' THEN a."v23" ELSE NULL END AS "row2", CASE WHEN CAST(b."column" AS VARCHAR) = 'col1' THEN a."v31" WHEN CAST(b."column" AS VARCHAR) = 'col2' THEN a."v32" WHEN CAST(b."column" AS VARCHAR) = 'col3' THEN a."v33" ELSE NULL END AS "row3" FROM "IN" a CROSS JOIN "crec_31812209643026056975_0000000004" b )
if(requireNamespace("rqdatatable", quietly = TRUE)) { library("rqdatatable") data %.>% ops %.>% print(.) }
#> record_id column row1 row2 row3 #> 1 1 col1 1 4 7 #> 2 1 col2 2 5 8 #> 3 1 col3 3 6 9 #> 4 2 col1 11 14 17 #> 5 2 col2 12 15 18 #> 6 2 col3 13 16 19