8 replies ›

1. The following is an alternative method using only the tidy verse packages:

   library(tidyverse)
   
   d = iris %>%
     mutate(row = seq(1, n())) %>%
     group_by(row) %>%
     nest() %>%
     mutate(keep = map(data, function(x) {
       x %>%
         gather('type', 'value', contains('.')) %>%
         separate('type', c('flower_part', 'measure'), sep = '[.]') %>%
         spread(measure, value)
     })) %>%
     dplyr::select(-row, -data) %>%
     unnest(keep)
   
   ggplot(d, aes(x=Length, y=Width)) +
     geom_point(aes(color=Species, shape=Species)) +
     facet_wrap(~flower_part, labeller = label_both, scale = "free") +
     ggtitle("Iris dimensions") +
     scale_color_brewer(palette = "Dark2")
   

   LikeLike

2. This example can be done with one rbind and creating one trivial new column. I can also see the value in having a programmatic way of doing it using cdata to write a specification. But that mess of percentage sandwiches masquerading as “tidy”? It has pipes within functions within pipes. Where’s the value in that?

   LikeLike

   1. The cdata system actually gives one extra additional ability to reason about these transforms.

      For example: for this direction (rowrecs_to_blocks()) any two row control table is an rbind in disguise (as this is), and any two column control table is a tidyr::gather() (so this isn’t a single tidyr::gather()).

      In fact the control table is literally the column re-namings you would do to solve this with an an rbind; but written in a very orderly and concise format, check out how the colored arguments match in the diagram comparing two solutions, below.

      Also in terms of Codd-style data manipulation algebra: rowrecs_to_blocks() is analogous to joining with the control table, and blocks_to_rowrecs() is its inverse (and looks a lot like a table widening followed by a summary/coalesce).

      Some articles and video developing the theory:

      Coordinatized Data: A Fluid Data Specification
      Fluid Data
      Fluid data reshaping with cdata
      Data Shaping with cdata

      LikeLike

3. There is indeed a tidyverse solution, but it is easier than what Justin proposes:

   library(dplyr)
   library(tidyr)
   library(ggplot2)
   
   out <- iris %>% 
     mutate(Key=seq_len(n())) %>%                       # add key (for 'spread')
     gather(flower_part, Value, -Species,-Key) %>%      # numeric data in long form
     separate(flower_part,into=c("Part","Measure")) %>% # Separate column with Length/width
     spread(Measure, Value)                             # put Length/Width in columns 
   
   out %>% ggplot(aes(x=Width, y=Length,col=Species, shape=Species)) +
     facet_grid(~Part) + geom_point()
   

   LikeLike

   1. Ahh. I felt there was a simpler method I just was not coming up with it. Thanks for the example.

      LikeLike

4. We now have an article up detailing more on the systematic method to derive the transform control table for a given desired transform here.

   LikeLike

5. Thanks for the post. Also could be accomplished with 3 sqldf statements

   septal = sqldf(‘select Species, “Septal” as flower_part, “Sepal.Length” as length, “Sepal.Width” as width from iris ‘)

   petal = sqldf(‘select Species, “Petal” as flower_part, “Petal.Length” as length, “Petal.Width” as width from iris ‘)

   iris_aug = sqldf(“select * from septal
   union all
   select * from petal”)

   But good to have other tools available and I have not used cdata much. The concept of a control table is nice.

   LikeLike

   1. In fact cdata itself can translate the control table into SQL code realizing the transform (and thus be used with databases).

      library("cdata")
      library("rquery")
      
      controlTable <- wrapr::build_frame(
        "flower_part", "Length"      , "Width"       |
        "Petal"      , "Petal.Length", "Petal.Width" |
        "Sepal"      , "Sepal.Length", "Sepal.Width" )
      
      columnsToCopy = "Species"
      
      db <- DBI::dbConnect(RSQLite::SQLite(), 
                           ":memory:")
      
      iris_r <- rquery::rq_copy_to(db, "iris", iris)
      
      res_tab <- rowrecs_to_blocks_q(
        iris_r$table_name,
        controlTable = controlTable,
        my_db = db,
        columnsToCopy = columnsToCopy,
        showQuery = TRUE)
      
      # [1] "CREATE  TABLE `mvtrq_03508568885469357450_0000000001` AS  SELECT a.`Species`, b.`flower_part`, CASE  WHEN b.`flower_part` = 'Petal' THEN a.`Petal.Length`  WHEN b.`flower_part` = 'Sepal' THEN a.`Sepal.Length` ELSE NULL END AS `Length`, CASE  WHEN b.`flower_part` = 'Petal' THEN a.`Petal.Width`  WHEN b.`flower_part` = 'Sepal' THEN a.`Sepal.Width` ELSE NULL END AS `Width` FROM `iris` a CROSS JOIN `mvtrq_03508568885469357450_0000000000` b "
      
      rquery::rstr(db, res_tab)
      
      # table `mvtrq_03508568885469357450_0000000001` SQLiteConnection 
      #  nrow: 300 
      #  NOTE: "obs" below is count of sample, not number of rows of data.
      # 'data.frame':   10 obs. of  4 variables:
      #  $ Species    : chr  "setosa" "setosa" "setosa" "setosa" ...
      #  $ flower_part: chr  "Petal" "Sepal" "Petal" "Sepal" ...
      #  $ Length     : num  1.4 5.1 1.4 4.9 1.3 4.7 1.5 4.6 1.4 5
      # $ Width      : num  0.2 3.5 0.2 3 0.2 3.2 0.2 3.1 0.2 3.6
      
      # # or execute as a an rquery pipeline
      # iris_r %.>%
      #   rowrecs_to_blocks(
      #     .,
      #     controlTable = controlTable,
      #     columnsToCopy = columnsToCopy) %.>%
      #   execute(db, .)
      

      LikeLike