Cross Join in Dplyr in R

Cross Join in dplyr in R

You just need a dummy column to join on:

cust_time$k <- 1
cust_time %>% 
  inner_join(cust_time, by='k') %>%
  select(-k)

Or if you don't want to modify your original dataframe:

cust_time %>%
  mutate(k = 1) %>%
  replicate(2, ., simplify=FALSE) %>%
  Reduce(function(a, b) inner_join(a, b, by='k'), .) %>%
  select(-k)

Cartesian product with dplyr

Use crossing from the tidyr package:

x <- data.frame(x=c("a","b","c"))
y <- data.frame(y=c(1,2,3))

crossing(x, y)

Result:

   x y
 1 a 1
 2 a 2
 3 a 3
 4 b 1
 5 b 2
 6 b 3
 7 c 1
 8 c 2
 9 c 3

How to do cross join in R?

Is it just all=TRUE?

x<-data.frame(id1=c("a","b","c"),vals1=1:3)
y<-data.frame(id2=c("d","e","f"),vals2=4:6)
merge(x,y,all=TRUE)

From documentation of merge:

If by or both by.x and by.y are of length 0 (a length zero vector or NULL), the result, r, is the Cartesian product of x and y, i.e., dim(r) = c(nrow(x)*nrow(y), ncol(x) + ncol(y)).

How to Cross Join in R

We can use crossing

library(tidyr)
crossing(table1, table2)

Generate CROSS JOIN queries with dbplyr

According to the dbplyr NEWS file, since version 1.10, if you use a full_join(..., by = character()), it will "promote" the join to a cross join. This doesn't seem to be documented anywhere else yet, but searching the dbplyr Github repo for "cross" turned it up in both code and the NEWS file.

This syntax does not seem to work for local data frames, only via SQL.

Cross join in R with filtering/matching

Columns value.x and value.y are what you need

   table1 <- data.frame(id= c(1,1,2,3,3,3), value=c(1,2,1,1,3,4))
   table2 <- data.frame(id=c(1,1,2,3,3), value=c(5,4,1,4,3))
   merge(table1,table2,by="id",all.y=TRUE)

   id    value.x  value.y
1   1       1       5
2   1       1       4
3   1       2       5
4   1       2       4
5   2       1       1
6   3       1       4
7   3       1       3
8   3       3       4
9   3       3       3
10  3       4       4
11  3       4       3

R data.table cross-join by three variables

You can also do this:

data[, .(date=dates_wanted), .(group,id)]

Output:

     group     id       date
  1:     A  frank 2020-01-01
  2:     A  frank 2020-01-02
  3:     A  frank 2020-01-03
  4:     A  frank 2020-01-04
  5:     A  frank 2020-01-05
 ---                        
120:     B edward 2020-01-27
121:     B edward 2020-01-28
122:     B edward 2020-01-29
123:     B edward 2020-01-30
124:     B edward 2020-01-31

---

Related Topics