How to use .SD in the R data.table package

For some data.table users, “dot-SD” is a bit of a mystery. But data.table creator Matt Dowle told me that it’s actually quite simple: Just think of it as a symbol representing “each group.” Let’s go through a couple of examples.

I have a data set of daily cycling trips from the Boston area’s bicycle-share system. If you’d like to follow along, you can download the CSV file from the link at the bottom of this article.

I’ll load data.table and import my CSV file using data.table’s fread() function. In the code below, I’m saving the data into a data table called mydt.

library(data.table)
mydt <- fread("daily_cycling_trips_by_usertype.csv")

Next, I suggest printing the first six lines with head(mydt) to see what the data looks like. You’ll see that the data has columns for the date, the user type (subscriber or single-trip customer), number of trips, year, and month starting date to help with totals by month.

The first example Matt suggested: Print the first few rows of the data table grouped by user type. (We’re filtering for the first 12 rows just to make it easier to see the output). 

mydt[1:12, print(.SD), by = usertype]

print() iterated over each group and printed two separate times, one for each user type. The problem, though, is I don’t know which is the customer user group and which is the subscriber user group. The “by” column didn’t print out. Fortunately, Matt showed me a little trick for that.

If you’re familiar with mydt[i, j, by] data.table syntax, there are three parts to the bracket notation after the data table name: i, j, and by. i is for filtering rows, j is for what you want to do, and by is how you want to group your data.

For example: 

mydt[1:12, { print(.SD) }, by = usertype]

In the line of code above, I’ve just put curly braces around the j part. That’s going to let me add multiple R expressions inside the j argument. Now it’s still the same as before: no user type names.

But in this next line of code, look at the R statement I added (well, Matt told me to add): print(.BY).

mydt[1:12, { print(.BY); print(.SD) }, by = usertype]

.BY is a special data.table symbol that holds the value of by – what column or columns I’m grouping by.

If you run this code, you’ll have the name of each grouping variable along with the printout.

Sharon Machlis, IDG

So that’s a very basic example. I’m guessing you might want to do something a little more interesting with .SD than print, though. Next let’s look at summarizing the data by group, calculating which day had the most trips each month this year.

This line of code has it all:

mydt[Year == "2019", .SD[which.max(Trips)], by = MonthStarting]

The i first argument in the brackets filters for any rows where the year is 2019. The j argument is the interesting part for .SD. Think of .SD as referring to each group of your data. Or as Matt said, “You do j by by. Like a for loop.”

What if you want to see maximums for each month and user type? Just add another column to the by (third) argument:

mydt[Year == "2019", .SD[which.max(Trips)], 
    by = .(MonthStarting, usertype)]

There are several ways to express grouping by more than one column in data.table. One way is with the dot before the unquoted column names, as above. Another is to use list instead of the dot, for example: 

mydt[Year == "2019", .SD[which.max(Trips)], 
     by = list(MonthStarting, usertype)]

You can also use a conventional base R vector with quotation marks around each column name. 

mydt[Year == "2019", .SD[which.max(Trips)], 
by = c("MonthStarting", "usertype")]

For more R tips, head to the “Do More With R” video page on InfoWorld or check out the “Do More With R” YouTube playlist.

Hope to see you next episode!