cross join of 2 datasets break by particular column value

General
1

I have 2 datasets as shown below and I want to achieve below result in R
table 1

rbd carrier
A EK
B EK
C EK
W QR
Y QR
J QR

table 2

carrier flight leg
EK 1 DXBBOM
EK 2 BOMLHR
EK 3 DXBDEL
QR 7 DXBLON
QR 8 JFKLHR
QR 9 MXPDWE

desired o/p is
for every carrier flight leg from table 2 should be mapped to every rbd carrier same carriers combination

carrier flight leg rbd
EK 1 DXBBOM A
EK 1 DXBBOM B
EK 1 DXBBOM C
EK 2 BOMLHR A
EK 2 BOMLHR B
EK 2 BOMLHR C
EK 3 DXBDEL A
EK 3 DXBDEL B
EK 3 DXBDEL C
QR 7 DXBLON W
QR 7 DXBLON J
QR 7 DXBLON Y
QR 8 JFKLHR W
QR 8 JFKLHR J
QR 8 JFKLHR Y
.
.

EK cxr with all ek ebds, QR carrier with all qr rbd

Please advise

2

any suggestions on achieving the required output

3

You can do this with inner_join() from the dplyr package. I'll demonstrate with a reprex (for future reference, please use reprex, or some form of reproducible example in the future when including data — that way, it isn't incumbent on those answering to get your data formatted for R).

library(tidyverse)
tab1 <- tibble::tribble(
  ~rbd, ~carrier,
   "A",     "EK",
   "B",     "EK",
   "C",     "EK",
   "W",     "QR",
   "Y",     "QR",
   "J",     "QR"
  )

tab2 <- tibble::tribble(
  ~carrier, ~flight,     ~leg,
      "EK",       1, "DXBBOM",
      "EK",       2, "BOMLHR",
      "EK",       3, "DXBDEL",
      "QR",       7, "DXBLON",
      "QR",       8, "JFKLHR",
      "QR",       9, "MXPDWE"
  )

tab2 %>%
  inner_join(tab1, by = "carrier")
#> # A tibble: 18 x 4
#>    carrier flight leg    rbd  
#>    <chr>    <dbl> <chr>  <chr>
#>  1 EK           1 DXBBOM A    
#>  2 EK           1 DXBBOM B    
#>  3 EK           1 DXBBOM C    
#>  4 EK           2 BOMLHR A    
#>  5 EK           2 BOMLHR B    
#>  6 EK           2 BOMLHR C    
#>  7 EK           3 DXBDEL A    
#>  8 EK           3 DXBDEL B    
#>  9 EK           3 DXBDEL C    
#> 10 QR           7 DXBLON W    
#> 11 QR           7 DXBLON Y    
#> 12 QR           7 DXBLON J    
#> 13 QR           8 JFKLHR W    
#> 14 QR           8 JFKLHR Y    
#> 15 QR           8 JFKLHR J    
#> 16 QR           9 MXPDWE W    
#> 17 QR           9 MXPDWE Y    
#> 18 QR           9 MXPDWE J

Created on 2018-10-09 by the reprex package (v0.2.1.9000)

1 Like
4

Thanks a lot Mara ..does exactly what was expected. I have a question though, how does inner join return Cartesian and resets the results based on carrier. Thanks again for your help

5

I'm not totally sure what you mean here. But, regardless, I think you'll find the detail you're looking for in the Mutating joins section in R for Data Science:

There's also the dplyr vignette on Two-table verbs.

If you're asking how, as in the code, much of it is in C++, so I'm not a great resource for that. Here's the section, though:
https://github.com/tidyverse/dplyr/blob/master/src/join_exports.cpp#L193-L226