Benchmarking Data Tables

When I started learning R, I heard vague tales of the use of Data Tables. Really just whisperers, of something to consider in the future after I’ve become more proficient. Well now is the time to learn what if anything I’ve been missing out on.

Introduction

Data Tables are a potential replacement for the common dataframe. It seeks to perform that same role but with improved performance. I would like to see the speed comparison between Data Frames, Data Tables and Tibbles. I will use the microbenchmark package to perform the actual benchmarking.

library(tidyverse)
library(data.table)
library(microbenchmark)
library(farff)

For the benchmark, I will use the ‘credit-g’ dataset, which can be found on the open ml website. I’m pretty sure the last open ml dataset I used was a csv file, but they seem to have moved to a ARFF format. I will need to use the farff package to load the data.

df <- farff::readARFF('dataset_31_credit-g.arff')
dt <- setDT(df)
ti <- tibble(df)

Syntax

The syntax for Data Tables is a little different:

DT[i,j,by]

In this manner, a data table can be subset by i, to calculate j when grouped with a by. Along with the special syntax, there are some common functions that add some additional simplification.

.()

The ‘.()’ function can be used as a placeholder for ‘list()’. The list function is useful for subsetting.

Grouped Aggregate

Aggregating data in Data Tables is simple by using the j and by parameters in the syntax. Again, multiple functions or even multiple groupings can be passed with the ‘.()’ function. For this comparison, we will look at the performance of finding the average age of the credit holders grouped by the class or credit rating.

group <- microbenchmark(Data_Frame = df %>% 
                                 group_by(class) %>%
                       summarise(avg = mean(age)),
               Data_Table = dt[,.(avg = mean(age)), by = class],
               Tibble = ti %>% 
                       group_by(class) %>%
                       summarise(avg = mean(age)))
print(group)

Unit: microseconds
       expr    min     lq     mean  median      uq     max neval
 Data_Frame 5457.8 5916.5 8281.596 6602.00 8040.75 35931.3   100
 Data_Table  654.2  894.3 2715.814  983.90 1222.40 43101.0   100
     Tibble 5587.6 6108.6 7880.329 6605.75 7981.15 48245.3   100

Taking counts

Another function of interest is the ‘.N’ function. This function will return the count of rows. The test looks are the number of people with over 5000 in credit and younger than 35.

counts <- microbenchmark(Data_Frame = df %>% 
                                 filter(credit_amount > 5000, age <35) %>%
                       nrow(),
               Data_Table = dt[credit_amount > 5000 & age < 35, .N ,],
               Tibble = ti %>% 
                       filter(credit_amount > 5000, age <35) %>%
                       nrow())
print(counts)

Unit: microseconds
       expr    min     lq      mean   median       uq     max neval
 Data_Frame 8465.8 8789.4 11712.951  9627.60 11767.20 48873.5   100
 Data_Table  285.9  336.7   500.222   463.95   510.25  2196.7   100
     Tibble 9031.1 9421.9 13459.711 10219.80 12669.95 64585.3   100

Creating new columns

Data Tables also contain a very simple syntax for creating a new column with ‘:=’. I compare this to the tidyverse mutate function. Using the base R to create a column is still the fastest method, taking about half the time of the Data Table method.

new <- microbenchmark(Data_Frame = df %>% mutate(property = paste(property_magnitude, housing)),
               Data_Table = dt[,property := paste(property_magnitude, housing)],
               Tibble = ti %>% mutate(property = paste(property_magnitude, housing)))
print(new)

Unit: microseconds
       expr    min      lq     mean  median      uq     max neval
 Data_Frame 2074.8 2186.05 3169.626 2318.60 2627.85 29837.9   100
 Data_Table  502.3  563.00  843.560  668.55  776.45 11603.2   100
     Tibble 2576.8 2838.50 4705.342 3051.55 3986.85 41499.7   100

Chaining Data Tables

Another point of exploration is that Data Tables can be chained together to create more complicated structures

dt[credit_amount > 1000, .(age = mean(age)),by = .(purpose, class)][class == "good" & age < mean(age)]

               purpose class      age
1:            radio/tv  good 35.44865
2: furniture/equipment  good 33.21930
3:            used car  good 36.91860
4:            business  good 34.50000
5:  domestic appliance  good 35.50000
6:          retraining  good 34.00000

I don’t think this is the most useful feature, as you can already create some very complicated transformation with a single call. Chaining also makes it more difficult to understand.

Conclusions

It is clear that there are significant performance improvements when using Data Tables versus Data Frames (an average decrease of time by -67%). There are also insignificant differences between Data Frames and Tibbles. Also, the syntax for Data Tables is fairly simple and straight forward and yet extremely powerful.

So, to answer the most important question, should you change to Data Tables from Data Frames? Probably, they present a significant performance gain and their structure is very flexible.

Photo by Tyler Clemmensen on Unsplash