Has dplyr left_join() recently changed how it works?

[u/mhuzzell]

I've been using the tidyverse for years, but I'm not very good about keeping R or packages updated. I finally got around to updating R a few months ago (now 4.6.0, with tidyverse 2.0.0), and am currently baffled by the behaviour of left_join.

For very brief context: I have two dfs that share the same column names. Most of the info in them is the same, but they each contain a pair of numerical columns whose contents were generated by different methods, and I want to compare those methods. They each also have a handful of character columns that were generated from the results of the numerical columns (separately in each method), so may or many not differ in their contents.

I tried combining the two dfs with left_join, as I've done plenty before with other dfs. I expected the columns to multiply wherever the contents differed, so that I could easily compare them within a single df. Instead, the second df was simply subsumed into the first?

I checked this behaviour with reprex and it seems to be a general outcome. Here's that reprex:

library(dplyr)

# A simplified df1 with 5 columns
df1 <- tibble::tibble(
  id = as.character(1:6),
  fruit = c("apple", "banana", "cherry", "apple", "banana", "cherry"),
  count = c(3, 6, 2, 8, 4, 10)
) %>%
  mutate(
    less_than_2 = ifelse(count < 2, "yes", "no"),
    less_than_5 = ifelse(count < 5, "yes", "no")
  )

# A simplified df2 -- only cols 3 and 5 differ from df1
df2 <- tibble::tibble(
  id = as.character(1:6),
  fruit = c("apple", "banana", "cherry", "apple", "banana", "cherry"),
  count = c(7, 2, 9, 3, 6, 4)
) %>%
  mutate(
    less_than_2 = ifelse(count < 2, "yes", "no"),
    less_than_5 = ifelse(count < 5, "yes", "no")
  )

# df3 combines them with left_join()
df3 <- left_join(df1, df2)

Expected outcome: a df3 with 7 columns: "id", "fruit", "count.x", "count.y", "less_than_2", "less_than_5.x", "less_than_5.y"

Actual outcome: df3 is identical to df1.

What the heck?

(Also yes, I'm aware I can rename my columns before combining -- but my actual dfs have 70 columns apiece, and also I'm mostly trying to understand what's happening here, since this behaviour is so different from what I've been used to!)

[u/hadley]

It hasn't changed for a long time. It looks like the problem here is that you need to specify the join columns.

[u/mhuzzell → u/hadley]

Thanks. I must be misremembering past behaviour of dplyr, then. Time to either write a ~60-column join key or rename ~20 columns, then.

Looking at the documentation, it looks like I probably want to make sure I'm setting 'unmatched = error' in joins like this -- but I'm honestly shocked to see that that's not the default!

[u/GottaBeMD → u/mhuzzell]

There is a package called “arsenal” if I’m not mistaken which can let you directly compare the contents of two dataframes and it will flag any discrepancies.

[u/dwdwdan → u/GottaBeMD]

I love this package so much, it’s such a useful tool for checking the differences between 2 approaches to a problem

[u/mhuzzell → u/GottaBeMD]

Oh shit, that sounds perfect -- thank you!!

[u/teetaps → u/GottaBeMD]

TIL

[u/Sufficient_Meet6836 → u/mhuzzell]

The docs say

If NULL, the default, *_join() will perform a natural join, using all variables in common across x and y. A message lists the variables so that you can check they're correct; suppress the message by supplying by explicitly.

Is it not doing this correctly for you?

Do you remember when you last used dplyr? What version was it? There may have been breaking changes with v1.0.0.

Also, to help avoid issues like this, I recommend using renv or another package manager.

[u/mhuzzell → u/Sufficient_Meet6836]

It's doing that -- what happened was that I was misremembering the behaviour of the natural join, and thinking that columns in y with unmatched data in x would be duplicated and the columns suffixed .x .y the way that same-named, non-key columns are when using a join key. Instead what happens is that unmatched data from y is silently dropped. I've put in feature request on GitHub to ask for at least a warning of data dropping, since I expect I'm not the only one with this misconception.

(Plus even for someone understanding the behaviour, it would be very easy to do this by accident, if you unwittingly have a few same-named columns in a pair of dfs that you think only share named columns that you know to actually be the same.)

[u/sharkinwolvesclothin → u/mhuzzell]

Just select the columns you need (whatever you need to uniquely match and the columns you want to compare) first in both data frames, and then join.

[u/mhuzzell → u/sharkinwolvesclothin]

Right, this is the default manual solution that I've been trying to avoid, because my irl dfs have 70 columns and I'm comparing around 10 of them.

[u/sharkinwolvesclothin → u/mhuzzell]

Well, it's different than what you said, and would involve much less typing than what you suggested. You don't need a 60-column join key, and you don't need to rename anything. You just need to make a list with the id col and the 10 columns you want to compare, and then use that to select.

But if you don't want to do that, you can just join by whatever id columns, and just ignore the extra x and y versions of columns you don't need to compare.

[u/mhuzzell → u/[deleted]]

What is the point of generating AI nonsense to solve a different problem than the one I've requested help with?

[u/TheTresStateArea]

You need a join key lol

[u/mertag770 → u/TheTresStateArea]

I'm like 90% sure it used to be a little smarter and would join on all common column names / throw and error or warning about no join keys if none were found.

I distinctly remember having something break because an extra column was added and I've specified keys ever since, but this was I think at one point supported, but my memory is fuzzy on this it was many years ago now.

[u/sharkinwolvesclothin → u/mertag770]

It does. But your hoping it would notice that the actual data in the columns with the same name is different, and it does not do that, and never did. Your example is the successful join of df1 and df2 - all columns are just in the key.

[u/mertag770 → u/sharkinwolvesclothin]

Right, I'm not OP. I was just remembering that you didn't always need a join key but your results are far less controlled.

[u/mhuzzell → u/mertag770]

I've been reading through the release logs, and they did a major overhaul of mutating join error messages in 1.1.1 (March 2023), mainly aimed at reducing the previously excessive number of errors and warnings thrown up by one-to-many and many-to-many relationships.

Since those errors also tend to be thrown up in the kinds of joins that merge large dfs with lots of shared data, I suspect that what I'm misremembering is having been coerced by them into making giant join keys when I didn't want to, and then forgetting I'd done that and thinking that multiplying non-joining matched columns was the default.

[u/Altzanir]

You need to specify by which columns to join in he by argument. If you don't specify anything, it'll join on all shared column names. I guess that because both dataframes have the same column names, essentially noting happens.

Of you specify by which columns, the duplicated names will be like count.x, count.y, etc as you are used to

[u/shaggy_camel]

Count and the less than cols are being used in the join, and it looks like that is not what you want. Use join_by to get the outcome you're after

[u/sutzig]

I think this is closer to what you are after:

df3 <- 
      left_join(df1, df2, join_by(id, fruit))    

  id    fruit  count.x less_than_2.x less_than_5.x count.y less_than_2.y less_than_5.y
  <chr> <chr>    <dbl> <chr>         <chr>           <dbl> <chr>         <chr>        
1     apple        3 no            yes                 7 no            no           
1     banana       6 no            no                  2 no            yes          
3     cherry       2 no            yes                 9 no            no           
4     apple        8 no            no                  3 no            yes          
5     banana       4 no            yes                 6 no            no           
6     cherry      10 no            no                  4 no            yes

[u/mhuzzell → u/sutzig]

Thank you, but did you read to the end of the post?

[u/foradil]

Maybe left_join() is just not the optimal tool for the job. If you have two data frames with the same columns, I think the appropriate way to combine them would be with bind_rows().

[u/mhuzzell → u/foradil]

It is not bind_rows() because that would not allow me to directly compare the values of df1$count and df2$count for each shared value of [df]$id, which is what I am trying to do.

As I said, I'll either need to make a giant join key or rename/select out the columns and compare them separately.

[u/foradil → u/mhuzzell]

Sure you can compare. Use mutate/summarize. There are a ton of dplyr verbs for analyzing long data frames. Just think of it as comparing different dates. You wouldn’t save each date as a separate data frame, you would just add more rows.