Supercharge Your Analysis of Large Datasets in R with data.table

If you‘ve ever tried to work with a large dataset containing millions of rows in R, you‘ve probably run into performance issues. Reading in the data, filtering, aggregating, and manipulating it can be painfully slow or even cause your R session to crash due to insufficient memory.

The reason is that R‘s built-in data frame is not designed for large datasets. Under the hood, data frames store each column separately in memory, which allows for flexibility but is not memory efficient. Operations on data frames also tend to make copies of the data, further increasing memory usage.

Fortunately, the data.table package provides an enhanced version of the data frame that is optimized for large datasets. Not only does it have a more efficient internal representation, but it also uses a powerful and concise syntax that avoids unnecessary copies and intermediate results.

In this article, we‘ll dive into how data tables work and see examples of using them to supercharge your analysis of large datasets in R. We‘ll cover:

• The key differences between data tables and data frames
• Reading in large datasets efficiently
• Filtering, selecting, aggregating, and joining data tables
• Converting between data tables and data frames
• Best practices and tips for using data tables

Whether you‘re a beginner or an experienced R user, learning data.table will take your data analysis capabilities to the next level. Let‘s get started!

Data Tables vs Data Frames

A data table looks and behaves a lot like a data frame, but has some key differences under the hood:

• Data tables store data more efficiently in memory by using a contiguous array of values for each column. This avoids the overhead of the data frame‘s list structure.
• Data table operations modify data by reference, avoiding copies. In contrast, most data frame operations make a copy of the data.
• Data tables extend data frames, so they can do everything a data frame can do and more.

Here‘s a quick example to illustrate the speed difference when selecting a subset of columns from a large dataset:

library(data.table)

# Generate a large dataset 
n_rows <- 1e7
n_cols <- 100
set.seed(1)
DF <- data.frame(matrix(rnorm(n_rows*n_cols),ncol=n_cols)) 

# Convert to a data table
DT <- as.data.table(DF)

# Select a subset of columns
system.time(DF[, 1:10])
#>    user  system elapsed 
#>   0.791   0.404   1.276

system.time(DT[, 1:10, with=FALSE])
#>    user  system elapsed 
#>   0.007   0.000   0.005

The data table is over 200x faster at selecting the first 10 columns from a dataset of 10 million rows and 100 columns!

This speedup is possible because:

1. The data table does not make a copy, it just returns a new data table pointing to the same underlying data
2. The data table‘s internal structure allows for faster column subsetting

We‘ll see many more examples of data tables outperforming data frames as we go through the key operations.

Reading Large Datasets

The first step to analyzing a large dataset is reading it into R. Using read.csv() can be slow for very large files.

Data table provides an optimized function fread() for reading in large tabular data. It‘s significantly faster than read.csv() and also more flexible, automatically detecting the file format and delimiters.

Let‘s compare reading in a 500 MB csv file:

# Generate a large CSV file
write.csv(DF, "large_file.csv", row.names=FALSE)

# Read using read.csv
system.time(read.csv("large_file.csv"))
#>    user  system elapsed 
#>  11.224   1.361  12.605

# Read using fread
system.time(fread("large_file.csv"))
#>    user  system elapsed 
#>   1.423   0.269   1.699  

fread() is about 8x faster than read.csv() on this 500MB file. The larger the file, the bigger the speed difference will be.

Some useful options to customize how fread reads in the data include:

• select: Specify the columns to read
• drop: Specify columns to skip
• stringsAsFactors: Convert string columns to factors
• na.strings: Specify values to interpret as missing

See ?fread for the full list of options.

Data Manipulation with data.table

Now let‘s see data tables in action on some common data manipulation tasks. We‘ll use the the New York City flights dataset which contains information on all 336,776 flights departing NYC in 2013.

library(data.table)
library(nycflights13)

# Convert flights data frame to a data table
flights <- as.data.table(nycflights13::flights)

Filtering Rows

Filtering a data table follows a similar syntax to filtering a data frame with square brackets []. The main difference is that we don‘t need to prefix column names with the data table name.

# Data frame syntax
flights[flights$month == 1 & flights$day == 1, ]

# Data table syntax
flights[month == 1 & day == 1]

For more complex queries, data table allows using an expression in the i argument:

flights[origin == "JFK" & carrier %in% c(‘AA‘,‘DL‘) & !is.na(arr_delay), 
        .(m_arr_delay = mean(arr_delay), n = .N), 
        by = .(origin,dest,month)] 

This query finds the number of flights n and mean arrival delay m_arr_delay for each origin-destination-month combination, filtering for flights from JFK on carriers AA or DL with a non-missing arrival delay.

Selecting Columns

We can select columns using an expression in the j argument of []:

# Select specific columns
flights[, .(origin, dest, tailnum)]

# Select column by name as a character vector 
flights[, c("origin", "dest", "tailnum")]

# Select columns by index
flights[, 1:5]

# Drop columns 
flights[, !c("origin", "dest", "tailnum")]

Adding and Modifying Columns

To add a new column, use := in the j argument:

flights[, dist_km := distance * 1.60934]

This adds a new column dist_km calculating the distance in kilometers.

We can add multiple columns at once and also perform operations based on other columns:

flights[, `:=`(speed = distance / air_time,
               delay_diff = arr_delay - dep_delay)]

To modify an existing column, just assign to it with :=:

flights[, tail_num := tolower(tailnum)]

Aggregating and Summarizing

Aggregating and summarizing data is very efficient with data tables. We use an expression in j to compute summary statistics and can group by one or more columns with the by argument.

# Compute the mean and median departure delay by origin airport
flights[, .(m_dep_delay = mean(dep_delay),
            med_dep_delay = median(dep_delay)), 
        by = origin]

# Count the number of flights to each destination
flights[, .(.N), by = dest]

# Find the maximum departure delay by origin and month 
flights[, .(max_dep_delay = max(dep_delay)), by = .(origin,month)]

The .N is a special variable that counts the number of rows in each group. It‘s equivalent to n() in dplyr.

Chaining Operations

We can chain together a sequence of operations on a data table using []. This allows us to perform complex data manipulations in a concise and readable way.

flights[origin == "JFK" & month == 6,                # Filter  
        .(m_arr_delay = mean(arr_delay, na.rm=TRUE), # Summarize
          m_dep_delay = mean(dep_delay, na.rm=TRUE), 
          n_flights = .N),
        by = .(carrier, dest)][order(-m_arr_delay)]   # Sort

This query filters for flights from JFK in June, computes the mean arrival delay, departure delay, and number of flights by carrier and destination, and sorts the result by decreasing arrival delay.

Chaining is both fast and memory efficient since data tables only make copies when absolutely necessary. Intermediate results are not stored, reducing memory overhead.

Merging and Joining

Data table has rich functionality for merging and joining datasets. The syntax is reminiscent of SQL and very feature-rich. See ?data.table for the full documentation.

Here are some examples of common joins:

# Inner join on single column
flights[airports, on="origin"] 

# Left join on multiple columns
flights[planes, on=.(tailnum = tailnum), nomatch=0]

# Semi join (filter)
flights[weather, on=.(origin, time_hour), nomatch=0]

# Anti join
flights[!planes, on="tailnum"]

# Non-equi join
flights[planes, on=.(tailnum<tailnum), allow.cartesian=TRUE]

The flexibility of data table‘s join syntax allows for easy filtering, mapping, rolling joins, and non-equi joins that are difficult to do with base R or dplyr.

Converting Between data.table and data.frame

To convert a data frame to a data table, use as.data.table():

DT <- as.data.table(DF)

To convert a data table back to a data frame, use as.data.frame():

DF <- as.data.frame(DT)

In most cases, you won‘t need to convert back to a data frame. Data tables can be used seamlessly with most R functions and packages that expect a data frame.

Best Practices and Tips

Here are some best practices to keep in mind when using data tables:

• Use setDT() instead of <- to convert a data frame to a data table in place. This avoids an unnecessary copy.
• Use := for efficient assignment by reference. Avoid <- for modifying data tables as it makes a copy.
• Compute on columns, not individual values. Data tables are optimized for column-wise operations.
• Use built-in functions (e.g. mean(), sum()) instead of sapply() or lapply() for better performance.
• Subset using i and combine with j instead of chaining []. This limits the overhead of doing multiple subsets.
• Use cross-platform file formats like csv and fst for better fread performance.

Finally, some lesser known but useful tips:

• set() and setindex() allow you to do fast key-based subsets.
• melt() and dcast() provide data reshaping capabilities.
• shift() and lag() are useful for working with time series data.
• You can extend a data table with custom methods using setattr().

Alternative Packages

Data table is not the only option for working with large datasets in R. Some other popular packages include:

• dplyr: A grammar of data manipulation providing a consistent set of functions that can work with data frames, data tables, and SQL databases.
• dtplyr: A data table backend for dplyr, allowing you to use dplyr syntax with data tables.
• disk.frame: A package for working with larger-than-RAM datasets using fast chunked processing.
• arrow: An interface to the Apache Arrow C++ library, providing tools for working with large, multi-file datasets.

Each package has its own strengths and use cases. Data table excels in raw speed and memory efficiency for in-memory datasets, while the others may be better suited for different workflows or out-of-memory datasets.

Conclusion

We‘ve seen how the data.table package can supercharge your analysis of large datasets in R. With its fast and memory efficient operations, concise syntax, and flexible features like reshaping and joins, it‘s a valuable tool to add to your data analysis toolkit.

The best way to learn data.table is through practice and continuous use. Try replacing your data frame code with data.table equivalents and explore the features we didn‘t cover here. You may be surprised at how much more productive and efficient you can be!

As always, make sure to profile your code and benchmark different approaches to see what works best for your specific use case. And don‘t forget to take advantage of other packages in the R ecosystem that can complement data.table for different types of large-scale data analysis.

Do you use data.table in your work? What are your favorite tips or features? Share your thoughts in the comments below!