New features in DataFrames.jl 1.3: part 4

Introduction

This post continues the presentation of new features added in DataFrames.jl 1.3. This time I will discuss the leftjoin! function.

The post was written under Julia 1.7.0 and DataFrames.jl 1.3.1. For performance comparison I have used R 4.1.2 and data.table 0.14.2. Both in R and Julia I run the computations on 4 threads.

An in place left join

Before release 1.3, DataFrames.jl already offered a rich set of efficient join functions: innerjoin, leftjoin, rightjoin, outerjoin, semijoin, antijoin, and crossjoin.

However, they all have a common limitation: their result is a freshly allocated data frame.

A common usage scenario in practice is that we would like to add some new columns to an existing table in-place. This is more efficient and uses less memory (which is relevant if we work with very large data frames).

Since DataFrames.jl 1.3 this option is available with the addition of the leftjoin! function. if you run leftjoin!(df1, df2; on=...) then the contract is that the df1 data frame is updated in-place with columns coming from df2 based on matching rows of both data frames using the columns passed in the on keyword argument.

It is important to remember that the design of leftjoin! assumes that the columns of df1 are left unchanged (this is crucial for performance of the operation). However, this implies that each row in df1 must have at most one match in df2. Otherwise, leftjoin! would not be able to execute the operation in-place since new rows would need to be added to df1. If you have matching duplicate rows in df2 then just use leftjoin.

Here are two minimal examples of leftjoin!.

julia> using DataFrames

julia> using Random

julia> df1 = DataFrame(a=1:6, b=1:6)
6×2 DataFrame
 Row │ a      b
     │ Int64  Int64
─────┼──────────────
   1 │     1      1
   2 │     2      2
   3 │     3      3
   4 │     4      4
   5 │     5      5
   6 │     6      6

julia> df2 = DataFrame(a=[2, 4, 6], c=1:3)
3×2 DataFrame
 Row │ a      c
     │ Int64  Int64
─────┼──────────────
   1 │     2      1
   2 │     4      2
   3 │     6      3

julia> leftjoin!(df1, df2, on=:a)
6×3 DataFrame
 Row │ a      b      c
     │ Int64  Int64  Int64?
─────┼───────────────────────
   1 │     1      1  missing
   2 │     2      2        1
   3 │     3      3  missing
   4 │     4      4        2
   5 │     5      5  missing
   6 │     6      6        3

julia> Random.seed!(1234);

julia> df1 = DataFrame(a=randperm(6), b=1:6)
6×2 DataFrame
 Row │ a      b
     │ Int64  Int64
─────┼──────────────
   1 │     3      1
   2 │     2      2
   3 │     6      3
   4 │     5      4
   5 │     1      5
   6 │     4      6

julia> df2 = DataFrame(a=shuffle!([2, 4, 6]), c=1:3)
3×2 DataFrame
 Row │ a      c
     │ Int64  Int64
─────┼──────────────
   1 │     4      1
   2 │     2      2
   3 │     6      3

julia> leftjoin!(df1, df2, on=:a)
6×3 DataFrame
 Row │ a      b      c
     │ Int64  Int64  Int64?
─────┼───────────────────────
   1 │     3      1  missing
   2 │     2      2        2
   3 │     6      3        3
   4 │     5      4  missing
   5 │     1      5  missing
   6 │     4      6        1

Performance benchmarks

Now let me run two performance benchmarks of DataFrames.jl against data.table. In the benchmarks I use 32-bit integers to ensure comparability of memory footprint of objects between R and Julia.

The first test is on sorted key column. We start with Julia:

julia> df1 = DataFrame(a=Int32.(1:10^8));

julia> df2 = DataFrame(a=Int32.(1:10^8), x = true);

julia> @time leftjoin!(df1, df2, on=:a);
  2.867632 seconds (221.45 k allocations: 2.433 GiB, 6.47% gc time, 3.98% compilation time)

julia> df1 = DataFrame(a=Int32.(1:10^8));

julia> @time leftjoin!(df1, df2, on=:a);
  2.934633 seconds (150 allocations: 2.421 GiB, 8.19% gc time)

And now the data.table:

> library(data.table)
> df1 = data.table(a=1:10^8)
> df2 = data.table(a=1:10^8, x=TRUE)
> system.time(df1[df2, on = 'a', x := i.x])
   user  system elapsed
  8.067   1.106   5.679
> df1 = data.table(a=1:10^8)
> system.time(df1[df2, on = 'a', x := i.x])
   user  system elapsed
  9.305   1.184   6.652

As you can see for sorted data DataFrames.jl timings are competitive. Let us now check shuffled data.

We start with DataFrames.jl:

julia> df1 = DataFrame(a=shuffle!(Int32.(1:10^8)));

julia> df2 = DataFrame(a=shuffle!(Int32.(1:10^8)), x = true);

julia> @time leftjoin!(df1, df2, on=:a);
 23.881552 seconds (175 allocations: 3.167 GiB, 1.43% gc time)

julia> df1 = DataFrame(a=Int32.(1:10^8));

julia> @time leftjoin!(df1, df2, on=:a);
 18.909113 seconds (175 allocations: 3.167 GiB, 1.40% gc time)

and now the timing for data.table:

> df1 = data.table(a=sample(1:10^8))
> df2 = data.table(a=sample(1:10^8), x=TRUE)
> system.time(df1[df2, on = 'a', x := i.x])
   user  system elapsed
 30.778   1.791  23.153
> df1 = data.table(a=sample(1:10^8))
> system.time(df1[df2, on = 'a', x := i.x])
   user  system elapsed
 30.586   1.695  22.893

Again the timing of DataFrames.jl is competitive.

(let me stress here that this is just one set of examples and relative performance of different packages can vary depending on the data and the operating environment; the point of these tests is to show that currently DataFrmes.jl is not much worse than data.table in execution of joins, as this was a performance bottleneck of DataFrames.jl in the past)

Conclusions

This time in the post I have focused on a single function: the leftjoin!. The reason is that I believe that addition of an in-place left join to DataFrames.jl is quite significant since it is needed in many data processing scenarios, especially when working with large tables.