How to do a dplyr inner_join col1 > col2

user_7306323 0
Viewed 557

I am having difficulty with getting dplyr joins to work when I am not using the standard "col1" = "col2" join. Here are two examples of what I am experiencing.

Firstly:

library(dplyr)

tableA <- data.frame(col1= c("a","b","c","d"),
                     col2 = c(1,2,3,4))

inner_join(tableA, tableA, by = c("col1"!="col1")) %>% 
  select(col1, col2.x) %>% 
  arrange(col1, col2.x)

Error: by must be a (named) character vector, list, or NULL for natural joins (not recommended in production code), not logical

When I replicate this code but using sql I get the following:

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

copy_to(con, tableA)

tbl(con, sql("select a.col1, b.col2
              from 
              tableA as a
              inner join 
              tableA as b
              on a.col1 <> b.col1")) %>% 
  arrange(col1, col2)

Results from sql query:

# Source:     SQL [?? x 2]
# Database:   sqlite 3.19.3 [:memory:]
# Ordered by: col1, col2
     col1  col2
     <chr> <dbl>
 1     a     2
 2     a     3
 3     a     4
 4     b     1
 5     b     3
 6     b     4
 7     c     1
 8     c     2
 9     c     4
10     d     1
# ... with more rows

Second part is similar to the last:

inner_join(tableA, tableA, by = c("col1" > "col1")) %>% 
   select(col1, col2.x) %>% 
   arrange(col1, col2.x)

Error: by must be a (named) character vector, list, or NULL for natural joins (not recommended in production code), not logical

Sql equivalent:

tbl(con, sql("select a.col1, b.col2
              from tableA as a
              inner join tableA as b
              on a.col1 > b.col1")) %>% 
   arrange(col1, col2)

Results from second sql query:

# Source:     SQL [?? x 2]
# Database:   sqlite 3.19.3 [:memory:]
# Ordered by: col1, col2
   col1  col2
  <chr> <dbl>
1     b     1
2     c     1
3     c     2
4     d     1
5     d     2
6     d     3

Does anyone know how to create these sql examples but using dplyr code?

dbplyr dplyr r
2 Answers

For your first case:

library(dplyr)
library(tidyr)

expand(tableA, col1, col2) %>% 
  left_join(tableA, by = 'col1') %>% 
  filter(col2.x != col2.y) %>% 
  select(col1, col2 = col2.x)

The result:

# A tibble: 12 x 2
     col1  col2
   <fctr> <dbl>
 1      a     2
 2      a     3
 3      a     4
 4      b     1
 5      b     3
 6      b     4
 7      c     1
 8      c     2
 9      c     4
10      d     1
11      d     2
12      d     3

For your second case:

expand(tableA, col1, col2) %>% 
  left_join(tableA, by = 'col1') %>% 
  filter(col2.x < col2.y) %>% 
  select(col1, col2 = col2.x)

The result:

# A tibble: 6 x 2
    col1  col2
  <fctr> <dbl>
1      b     1
2      c     1
3      c     2
4      d     1
5      d     2
6      d     3
user_35268320

A solution using dplyr and tidyr. The idea is to expand the data frame and then perform join to the original data frame. After that, use fill from tidyr to fill in NA to previous records. Finally, filter out records with the same values and NA.

library(dplyr)
library(tidyr)

tableB <- tableA %>%
  complete(col1, col2) %>%
  left_join(tableA %>% mutate(col3 = col2), by = c("col1", "col2")) %>%
  group_by(col1) %>%
  fill(col3, .direction = "up") %>%
  filter(col2 != col3, !is.na(col3)) %>%
  select(-col3) %>%
  ungroup()
tableB
# # A tibble: 6 x 2
#    col1  col2
#   <chr> <dbl>
# 1     b     1
# 2     c     1
# 3     c     2
# 4     d     1
# 5     d     2
# 6     d     3

DATA

tableA <- data.frame(col1= c("a","b","c","d"),
                     col2 = c(1,2,3,4), stringsAsFactors = FALSE)
user_76698090
Related