Richard Cotton introduces data.table, an R package designed to speed up programming and computation for large datasets. The core I-J-by query syntax maps to SQL's WHERE-SELECT-GROUP BY, letting you filter rows, select columns, and group results in one concise statement. Data.table handles datasets from tens to hundreds of gigabytes, common in finance, genomics, and time-series work.
Chapters
So let's get started. What is a data table? Think data frame is just a set of columns in memory. Every column is the same length but different type. The first and primary goal of Data Table is to reduce programming time via easier syntax and more natural syntax. And the second goal really is to reduce compute time. And it's always been that way around, since the beginning in 2008 when I released the package. Currently it's in memory. So 64 bit and 100 gigabytes of RAM are quite routine. And these days you can get a quarter of a terabyte of RAM on Amazon EC2 for less than a few dollars per hour. And the other main feature in my mind is audit joins. So this is useful in finance and time series, which is my background. But other fields, such as genomics, also have large data which is ordered, but not necessarily by time. Let's have a look at a full example and all three parts of the data table query, there's three parts to it in blue, green and orange. And the way I would read this is as follows. So we're selecting from the mtcars data table all the rows where the MPG column is greater than 20, and for those rows we're selecting the average horsepower and the minimum weight and doing that, grouping the rows by cylinder and whether the cars have five gears or not. And those three parts of the query map to SQL. So the first part in blue, called I in R, but that's analogous to where in SQL. The second part in green maps to select in SQL and the third part in orange is the group by clause. And notice we can lay it out nicely like this, and we can put little comments alongside each of the calculations with a hash mark before it. So the general form of a data table query is I J by. And the way we read that out loud is take dt subset rows using I and then calculate J grouped by by. So here's creating a data table, four columns and six rows, just like a data frame. So here we notice that there's recycling in the B column. So the A, B, C is repeated once and recycling in D as well. So a single logical value is repeated six times. And there's three main things to remember about types in R. The first point is that you need to add L after a single one to make it type integer. One by itself is type numeric. The second is that NA is by default logical in R and you need to add integer afterwards to make it integer. And the final point is that character vectors in R are quite efficient and we like them a lot in datatable. So if you have the same long character string repeated many times in the vector, R won't actually store those letters over and over again in memory. It'll cache the unique string values globally and just have pointers in the vector to that global cache. And we like character vectors in datatable a lot. So here we're going to give some examples of using integers in I to subset the data table. So we pass 3 to 5 into the I argument before the comma, and we get those rows, row numbers 3, 4, and 5. But if we forget that comma, then it doesn't matter, we just get the same data back. Contrast that to dataframe, where we pass 3 to 5 into I again and the comma, that's the same as datatable. But if we forget that comma in dataframe, then it's an error. So is datatable compatible with dataframe? The first thing is that datatable is a dataframe too, so it's accepted by all base R packages. But if a base R package then calls dataframe syntax on the datatable, then how does it work? Well, datatable switches at the top of its method the square bracket, and it looks to see where it was called from. And if it's being called from a package which doesn't know about datatable, then it'll switch to the base R dataframe method for square bracket, and that package will still work. So now it's time for you to do some exercises.
