Smartly select and mutate data frame columns, using dict

• Motivation
• Column operations
  • Add
  • Modify
  • Remove
• Benchmark
• Summary

Motivation

The dplyr functions select and mutate nowadays are commonly applied to perform data.frame column operations, frequently combined with magrittrs forward %>% pipe. While working well interactively, however, these methods often would require additional checking if used in “serious” code, for example, to catch column name clashes.

In principle, the container package provides a dict-class (resembling Pythons dict type), which allows to cover these issues more easily. In its very recent update, the container package for this reason gained an S3 method interface plus functions to convert back and forth between dict and data.frame. This can be used to extend the set of data.frame column operations and in this post I will show how and when they can serve as a useful alternative to mutate and select.

To keep matters simple, we use a tiny data table.

data <- data.frame(x = c(0.2, 0.5), y = letters[1:2])
data
##     x y
## 1 0.2 a
## 2 0.5 b

Column operations

library(dplyr)
n <- nrow(data)

data %>% 
    mutate(ID = 1:n)
##     x y ID
## 1 0.2 a  1
## 2 0.5 b  2

library(container)
data %>% 
    as.dict() %>% 
    add("ID", 1:n) %>%
    as.data.frame()
##     x y ID
## 1 0.2 a  1
## 2 0.5 b  2

data %>% 
    mutate(y = rnorm(n))
##     x         y
## 1 0.2 1.5295729
## 2 0.5 0.4821652

if ("y" %in% colnames(data)) {
    stop("column y already exists")
} else {
    data %>% 
        mutate(y = rnorm(n))
}
## Error in eval(expr, envir, enclos): column y already exists

data %>% 
    as.dict() %>% 
    add("y", rnorm(n)) %>%
    as.data.frame()
## Error in x$add(key, value): key 'y' already in Dict

data %>% 
    as.dict() %>% 
    setval("y", rnorm(n)) %>%
    as.data.frame()
##     x          y
## 1 0.2  0.5315501
## 2 0.5 -0.1573612

if ("ID" %in% colnames(data)) {
    data %>% 
        mutate(ID = 1:n)    
} else {
    stop("column ID not in data.frame")
}
## Error in eval(expr, envir, enclos): column ID not in data.frame

data %>% 
    as.dict() %>% 
    setval("ID", 1:n) %>%
    as.data.frame()
## Error in x$set(key, value, add): key 'ID' not in Dict

data %>% 
    as.dict() %>% 
    setval("ID", 1:n, add=TRUE) %>%
    as.data.frame()
##     x y ID
## 1 0.2 a  1
## 2 0.5 b  2

data %>% 
        select(-"y")
##     x
## 1 0.2
## 2 0.5

data %>% 
    as.dict() %>% 
    remove("y") %>% 
    as.data.frame()
##     x
## 1 0.2
## 2 0.5

data %>%
    select(-"ID")
## Error: Unknown column `ID`

data %>% 
    as.dict() %>% 
    remove("ID") %>% 
    as.data.frame()
## Error in x$remove(key): key 'ID' not in Dict

if ("ID" %in% colnames(data)) {
    data %>%
        select(-"ID")    
}

data %>% 
    as.dict() %>% 
    discard("ID") %>% 
    discard("y") %>%
    add("z", c("Hello", "World")) %>%
    as.data.frame()
##     x     z
## 1 0.2 Hello
## 2 0.5 World

Benchmark

The required additional code lines are limited but what about the computational overhead? To examine this, we benchmark some column operations using the famous ‘iris’ data set. As a hallmark reference we will also bring the data.table framework to the competition.

set.seed(123)
library(microbenchmark)
library(ggplot2)
library(data.table)
data <- iris
n <- nrow(data)
head(iris)
##   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1          5.1         3.5          1.4         0.2  setosa
## 2          4.9         3.0          1.4         0.2  setosa
## 3          4.7         3.2          1.3         0.2  setosa
## 4          4.6         3.1          1.5         0.2  setosa
## 5          5.0         3.6          1.4         0.2  setosa
## 6          5.4         3.9          1.7         0.4  setosa

For the benchmark, we add one, transform one and finally delete one column.

bm <- microbenchmark(control = list(order="inorder"), times = 100,
    dplyr = data %>% 
        mutate(ID = 1:n) %>%
        mutate(Species = as.character(Species)) %>%
        select(-Sepal.Width),
    
    dict = data %>%
        as.dict() %>%
        add("ID", 1:n) %>%
        setval(., "Species", as.character(.$get("Species"))) %>%
        remove("Sepal.Width") %>%
        as.data.frame(),
    
    `[.data.table` = data %>% 
        as.data.table() %>%
        .[, ID := 1:n] %>%
        .[, Species := as.character(Species)] %>%
        .[, Sepal.Width := NULL]
)
autoplot(bm) + theme_bw()

Somewhat surprisingly maybe, the dict-implementation is closer to the data.table than to the dplyr performance. Let’s examine each operation in more detail.

bm <- microbenchmark(control = list(order="inorder"), times = 100,
    tbl <- mutate(data, ID = 1:n),
    tbl <- mutate(tbl, Species = as.character(Species)),
    tbl <- select(tbl, -Sepal.Width),
    
    dic <- as.dict(data),
    add(dic, "ID", 1:n),
    setval(dic, "Species", as.character(dic$get("Species"))),
    remove(dic, "Sepal.Width"),
    dic <- as.data.frame(dic),
    
    dt <- as.data.table(data),
    dt[, ID := 1:n],
    dt[, Species := as.character(Species)],
    dt[, Sepal.Width := NULL]
)
autoplot(bm) + theme_bw()

Apparently, the mutate and select operations are the slowest in comparison, I think, because both the dict and data.table approach work by reference while probably some copying is done in the dplyr pipe. We also see that the dict-approach spends most of the computation time for the transformation back and forth between a dict and a data.frame while the actual column operations seem very efficient, even more efficient than that of data.table. This certainly came as a surprise to me, as the focus when developing the container package has never been on speed but rather on providing a concise data structure. Internally the dict simply consists of a named list, so I guess this speaks for the efficiency of base R list operations. Having said that, I found that the data.table code can be further improved by avoiding the overhead of the [.data.table operator and instead use the built-in set function:

bm <- microbenchmark(control = list(order="inorder"), times = 100,
    dplyr = data %>% 
        mutate(ID = 1:n) %>%
        mutate(Species = as.character(Species)) %>%
        select(-Sepal.Width),
    
    dict = data %>%
        as.dict() %>%
        add("ID", 1:n) %>%
        setval(., "Species", as.character(.$get("Species"))) %>%
        remove("Sepal.Width") %>%
        as.data.frame(),
    
    `[.data.table` = data %>% 
        as.data.table() %>%
        .[, ID := 1:n] %>%
        .[, Species := as.character(Species)] %>%
        .[, Sepal.Width := NULL],
    
    data.table.set = data %>% 
        as.data.table() %>%
        set(., j= "ID", value = 1:n) %>%
        set(., j = "Species", value = as.character(.[["Species"]])) %>%
        set(., j = "Sepal.Width", value = NULL)
)
autoplot(bm) + theme_bw()

This puts things back into perspective, I guess 🙂 It might also be interesting to know, how much of the computation time is spent on the non-standard evaluation part of the dplyr and [.data.table implementation, but that’s probably a topic on its own.

Summary

Accidently overwriting existing data columns leads to nasty bugs. The presented workflow allows to increase both reliability and precision of standard data frame column manipulation at very little cost. The intended column operations can be expressed more clearly and, in case of failures, informative error messages are provided by default. As a result, the dict-framework may serve as a useful supplement to “interactive piping”.