replyr (version 0.8.2)

moveValuesToRowsQ: Map a set of columns to rows (query based).

Description

Transform data facts from columns into additional rows using SQL and controlTable.

Usage

moveValuesToRowsQ(controlTable, wideTableName, my_db, ...,
  columnsToCopy = NULL,
  tempNameGenerator = replyr::makeTempNameGenerator("mvtrq"), strict = TRUE,
  checkNames = TRUE, showQuery = FALSE)

Arguments

controlTable

table specifying mapping (local data frame)

wideTableName

name of table containing data to be mapped (db/Spark data)

my_db

db handle

...

force later arguments to be by name.

columnsToCopy

character list of column names to copy

tempNameGenerator

a tempNameGenerator from replyr::makeTempNameGenerator()

strict

logical, if TRUE check control table contents for uniqueness

checkNames

logical, if TRUE check names

showQuery

if TRUE print query

Value

long table built by mapping wideTable to one row per group

Details

This is using the theory of "fluid data"n (https://github.com/WinVector/cdata), which includes the principle that each data cell has coordinates independent of the storage details and storage detail dependent coordinates (usually row-id, column-id, and group-id) can be re-derived at will (the other principle is that there may not be "one true preferred data shape" and many re-shapings of data may be needed to match data to different algorithms and methods).

The controlTable defines the names of each data element in the two notations: the notation of the tall table (which is row oriented) and the notation of the wide table (which is column oriented). controlTable[ , 1] (the group label) cross colnames(controlTable) (the column labels) are names of data cells in the long form. controlTable[ , 2:ncol(controlTable)] (column labels) are names of data cells in the wide form. To get behavior similar to tidyr::gather/spread one build the control table by running an appropiate query over the data.

Some discussion and examples can be found here: https://winvector.github.io/replyr/articles/FluidData.html and here https://github.com/WinVector/cdata.

See Also

moveValuesToRows, buildUnPivotControlTable, moveValuesToColumnsQ

Examples

# NOT RUN {
my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
wideTableName <- 'dat'
d <- dplyr::copy_to(my_db,
      dplyr::tribble(
        ~ID,          ~c1,          ~c2,          ~c3,          ~c4,
      'id1', 'val_id1_c1', 'val_id1_c2', 'val_id1_c3', 'val_id1_c4',
      'id2', 'val_id2_c1', 'val_id2_c2', 'val_id2_c3', 'val_id2_c4',
      'id3', 'val_id3_c1', 'val_id3_c2', 'val_id3_c3', 'val_id3_c4' ),
             wideTableName, overwrite = TRUE, temporary=TRUE)
controlTable <- dplyr::tribble(~group, ~col1, ~col2,
                                 'aa',  'c1',  'c2',
                                 'bb',  'c3',  'c4')
columnsToCopy <- 'ID'
moveValuesToRowsQ(controlTable,
                  wideTableName,
                  my_db,
                  columnsToCopy = columnsToCopy)
# # Source:   table<mvtrq_tnl6kueh5givlkobcl54_0000000001> [?? x 4]
# # Database: sqlite 3.19.3 [:memory:]
#      ID group       col1       col2
#   <chr> <chr>      <chr>      <chr>
# 1   id1    aa val_id1_c1 val_id1_c2
# 2   id1    bb val_id1_c3 val_id1_c4
# 3   id2    aa val_id2_c1 val_id2_c2
# 4   id2    bb val_id2_c3 val_id2_c4
# 5   id3    aa val_id3_c1 val_id3_c2
# 6   id3    bb val_id3_c3 val_id3_c4

# }