General Data Transforms With cdata

John Mount, Nina Zumel

2021-06-11

One of the design goals of the cdata R package is that data occurs in records, and records may be a pattern of cells in a groups of rows.

The allows cdata to support very powerful and arbitrary record transforms in one or two steps. Using “row records” (that is records that are exactly one row) as an intermediate lets us take just about any record shape to just about any record shape: first convert to row-records, then re-block the data into arbitrary record shapes (please see here and here for the concepts).

But as with all general ideas, it is much easier to see what we mean by the above with a concrete example. Let’s consider the following artificial (but simple) example. Suppose we have the following data.

library("cdata")

data <- wrapr::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     )

knitr::kable(data)
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

In the above the records are the triples of rows with matching record_id and the different rows within the record are identified by the value in the row column. So The data items are named by the triplet record_id, row and renaming column name (col1, col2, or col2). This sort of naming of values is essentially Codd’s “guaranteed access rule”.

Suppose we want to transpose each of the records- swapping the row and column notions. With cdata this is easy. First you design a transform to flatten each complex record into a single wide row (using the design steps taught here). Essentially that is just specifying the following control variables. We define how to identify records (the key columns) and the structure of the records (giving the interior of the record arbitrary names we will re-use later).

recordKeys = 'record_id'

incoming_shape <- wrapr::qchar_frame(
   "row"   , "col1", "col2", "col3" |
     "row1", v11   , v12   , v13    |
     "row2", v21   , v22   , v23    |
     "row3", v31   , v32   , v33    )

And we specify (using the same principles) the desired final record shape, re-using the interior names from the first step to show where values are to be mapped.

outgoing_shape <- wrapr::qchar_frame(
   "column_label"  , "c_row1", "c_row2", "c_row3" |
     "rec_col1"    , v11     , v21     , v31      |
     "rec_col2"    , v12     , v22     , v32      |
     "rec_col3"    , v13     , v23     , v33      )

Once you have done this you specify the overall transform by building a layout specifying the incoming and outgoing record shapes.

layout <- layout_specification(
  incoming_shape = incoming_shape,
  outgoing_shape = outgoing_shape,
  recordKeys = recordKeys)

print(layout)
#> {
#>  in_record <- wrapr::qchar_frame(
#>    "record_id"  , "row" , "col1", "col2", "col3" |
#>      .          , "row1", v11   , v12   , v13    |
#>      .          , "row2", v21   , v22   , v23    |
#>      .          , "row3", v31   , v32   , v33    )
#>  in_keys <- c('record_id', 'row')
#> 
#>  # becomes
#> 
#>  out_record <- wrapr::qchar_frame(
#>    "record_id"  , "column_label", "c_row1", "c_row2", "c_row3" |
#>      .          , "rec_col1"    , v11     , v21     , v31      |
#>      .          , "rec_col2"    , v12     , v22     , v32      |
#>      .          , "rec_col3"    , v13     , v23     , v33      )
#>  out_keys <- c('record_id', 'column_label')
#> 
#>  # args: c(checkNames = TRUE, checkKeys = TRUE, strict = FALSE, allow_rqdatatable = FALSE)
#> }

This layout specification or controller can then perform the transform.

data %.>%
  layout %.>%
  knitr::kable(.)
record_id column_label c_row1 c_row2 c_row3
1 rec_col1 1 4 7
1 rec_col2 2 5 8
1 rec_col3 3 6 9
2 rec_col1 11 14 17
2 rec_col2 12 15 18
2 rec_col3 13 16 19

And the transform is done, each record has been transposed.

The principle is “draw a picture.” First we draw a picture of the block record structure we have, and then we draw a picture of the block record structure we want.

As you have seen, we have complete freedom to re-name columns and record-piece labels (the labels that tell us which portion of a block-record each row fits into).

If you don’t want to use pipe notation, you can use the method layout_by() (which takes a layout specification as an argument) or the method convert_records() (which takes the components of the transform specification as separate arguments).

lr <- layout_by(layout, data)
knitr::kable(lr)
record_id column_label c_row1 c_row2 c_row3
1 rec_col1 1 4 7
1 rec_col2 2 5 8
1 rec_col3 3 6 9
2 rec_col1 11 14 17
2 rec_col2 12 15 18
2 rec_col3 13 16 19 

cr <- convert_records(
  data,
  keyColumns = recordKeys,
  incoming_shape = incoming_shape,
  outgoing_shape = outgoing_shape)

knitr::kable(cr)
record_id column_label c_row1 c_row2 c_row3
1 rec_col1 1 4 7
1 rec_col2 2 5 8
1 rec_col3 3 6 9
2 rec_col1 11 14 17
2 rec_col2 12 15 18
2 rec_col3 13 16 19

A nifty bonus is: if the transformation is “faithful” (preserves enough cells and labels), then it is invertible and in fact easy to invert (by the t() transpose/adjoint function).

inv_layout <- t(layout)

print(inv_layout)
#> {
#>  in_record <- wrapr::qchar_frame(
#>    "record_id"  , "column_label", "c_row1", "c_row2", "c_row3" |
#>      .          , "rec_col1"    , v11     , v21     , v31      |
#>      .          , "rec_col2"    , v12     , v22     , v32      |
#>      .          , "rec_col3"    , v13     , v23     , v33      )
#>  in_keys <- c('record_id', 'column_label')
#> 
#>  # becomes
#> 
#>  out_record <- wrapr::qchar_frame(
#>    "record_id"  , "row" , "col1", "col2", "col3" |
#>      .          , "row1", v11   , v12   , v13    |
#>      .          , "row2", v21   , v22   , v23    |
#>      .          , "row3", v31   , v32   , v33    )
#>  out_keys <- c('record_id', 'row')
#> 
#>  # args: c(checkNames = TRUE, checkKeys = TRUE, strict = FALSE, allow_rqdatatable = FALSE)
#> }

data %.>%
  layout %.>%
  inv_layout %.>%
  knitr::kable(.)
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

Also these conversions can also be translated into rquery operators, and therefore saved to be run either in memory or directly on a database.

table_desciption <- rquery::local_td(data)
ops <- table_desciption %.>%
  layout

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_label", CASE  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col1' THEN a."v11"  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col2' THEN a."v12"  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col3' THEN a."v13" ELSE NULL END AS "c_row1", CASE  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col1' THEN a."v21"  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col2' THEN a."v22"  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col3' THEN a."v23" ELSE NULL END AS "c_row2", CASE  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col1' THEN a."v31"  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col2' THEN a."v32"  WHEN CAST(b."column_label" AS VARCHAR) = 'rec_col3' THEN a."v33" ELSE NULL END AS "c_row3" FROM "IN" a CROSS JOIN "rrtbl_95737923310576013696_0000000002" b )

rquery::column_names(ops) 
#> [1] "record_id"    "column_label" "c_row1"       "c_row2"       "c_row3"

if(requireNamespace("DBI", quietly = TRUE) &&
   requireNamespace("RSQLite", quietly = TRUE)) {
  raw_connection <- DBI::dbConnect(RSQLite::SQLite(), 
                                   ":memory:")
  RSQLite::initExtension(raw_connection)
  db <- rquery::rquery_db_info(
    connection = raw_connection,
    is_dbi = TRUE,
    connection_options = rquery::rq_connection_tests(raw_connection))
  
  db_td <- rquery::rq_copy_to(db, "data", data)
  
  ops %.>% 
    db %.>% 
    knitr::kable(.) %.>%
    print(.)
  
  DBI::dbDisconnect(raw_connection)
}
#> 
#> 
#> | record_id|column_label | c_row1| c_row2| c_row3|
#> |---------:|:------------|------:|------:|------:|
#> |         1|rec_col1     |      1|      4|      7|
#> |         1|rec_col2     |      2|      5|      8|
#> |         1|rec_col3     |      3|      6|      9|
#> |         2|rec_col1     |     11|     14|     17|
#> |         2|rec_col2     |     12|     15|     18|
#> |         2|rec_col3     |     13|     16|     19|

And that is some of the generality of cdata transforms.