The unequalled joy of non-equi joins

Motivating example

A famous YouTuber is testing out a new marketing strategy, promoting specific videos on social media. They collect daily view counts of old videos and want to summarise these counts in the days following each promotion.

The tables promos and views are as follows. The data span a two-week period, but for certain days and certain videos, the view counts are missing.

What are the mean view counts on videos in the three days immediately following promotions for those videos?

I will show how you might accomplish this task using either non-equi joins or rolling joins in R.

Crossing + filter with dplyr

The package dplyr has no function for joining on anything other than an equality relation. However, you can get the same results (possibly less efficiently) using an outer join or Cartesian product, followed by a filtering operation.

library(dplyr)
views %>%
  # Crossing
  full_join(promos) %>%
  # Filter
  filter(view_date >= promo_date,
         view_date <= promo_date + 3) %>%
  # Aggregate
  group_by(video) %>%
  summarise(mean(views), sd(views), `n days` = n())

It would also be possible to compare promoted days with non-promoted days for each video, either by creating a binary indicator (instead of a filter) or by rejoining the table with the original dataset after the filter step.

Non-equi joins with sqldf

The sqldf package lets you query R data frames with SQL, as if you were working with a relational database. The result of the query is another data frame, and performance is sometimes better than equivalent R functions.

library(sqldf)
sqldf('SELECT v.video, view_date, views
       FROM views v
       JOIN promos p
       ON v.video = p.video AND
          view_date BETWEEN promo_date AND promo_date + 3'
      ) %>%
  group_by(video) %>%
  summarise(mean(views), sd(views), `n days` = n())

The aggregation step could also be written in SQL, but it makes sense only to use SQL where it is absolutely needed, and to use native R functions for everything else.

Having access to this functionality is very powerful, but has the obvious disadvantage that you need to learn a bit of SQL to understand the syntax.

Non-equi joins with data.table

The high-performance data manipulation package data.table now (as of v1.9.8) supports non-equi joins.

Non-equi joins are made possible with the X[i] merging syntax and the on argument. It’s slightly less flexible than the equivalent SQL, because you can’t just write promo_date + 3 in the inequality: instead it needs to be an explicit column in the table. You also can’t use the infix %between% operator, so two inequalities have to do instead. Otherwise the syntax is similar. Like in SQL, a prefix is used to disambiguate the column names: here it’s x.name.

library(data.table)
setDT(views)
setDT(promos)[, promo_end := promo_date + 3]

views[promos,
      .(video, views, x.view_date),
      # Non equi join:
      on = .(video,
             view_date >= promo_date,
             view_date <= promo_end)
      ][, # Chain into aggregate:
        .(mean = mean(views), sd = sd(views), .N),
        by = video]

Rolling joins with data.table

This particular example, since it involves a time variable, is even simpler using a rolling join. The concept is a bit confusing, but essentially it attributes records in one table with the most recent preceding records in the second table. You can read more about rolling joins in this blog post by Robert Norberg.

When performing rolling joins in data.table, one of the joining time columns gets dropped, which can make it hard to identify your records if they don’t have an explicit ID. To mitigate this, we will copy each date column to the name join_date and join on that.

views[, join_date := view_date]
promos[, join_date := promo_date]

setkey(views, video, join_date)
setkey(promos, video, join_date)
promos[views, roll = TRUE]

The syntax promos[views, roll=TRUE] means “which promotion immediately preceded each viewing date?” Conversely, views[promos, roll=TRUE] means “which viewing dates immediately preceded each promotion?”

In this case, we want something close to the former, but we’re only interested in promos in the past 3 days, whereas by default it’ll extend back into the depths of time looking for the last one, regardless of how long ago.

By changing roll = TRUE to roll = 3 the join will fail to match when the join_date differs by more than three days between the two tables. If we wanted to go in the opposite direction in time, we could use roll = -Inf to search forwards in time for future promotions, and roll = -3 for only those in the following three days.

promos[views, roll = 3]

Since the default in data.table’s X[i] merge syntax is nomatch = NA, we get all of the views back, with the column promo_date equal to the date of the last promotion (in the last three days), or NA if no such promotion was found. If we set nomatch = 0 then these non-matching values are dropped from the result.

So the full operation to calculate the summary figures is

promos[views, roll = 3, nomatch = 0
       ][j = .(mean = mean(views), sd = sd(views), .N),
         by = video]

If the intervals needed to be different lengths for each of the campaigns (i.e. not all equal to 3), then you would probably want a non-equi join rather than a rolling join in this case.

Extensions

Arguably, for these examples you’d want to compare the view counts in promotional periods with those outside promotional periods. This does not require a special kind of join; rather you perform the non-equi or rolling join as above and then wrangle the output accordingly.

If the non-matching rows are filtered out, you need to re-join with the original dataset. Otherwise, you need to use some sort of indicator variable for whether the viewing date falls within a promotional period or not.

In dplyr:

promos %>%
  full_join(views) %>%
  mutate(promo = between(view_date - promo_date, 0, 3)) %>%
  group_by(video, view_date) %>%
  summarise(promo = any(promo),
            views = unique(views)) %>%
  group_by(promo) %>%
  summarise(mean(views), sd(views), n = n())

In data.table:

promos[views, roll = 3][
         j = .(mean = mean(views), sd = sd(views), .N),
         by = .(promo = !is.na(promo_date))]