How to use dplyr left_join with multiple conditions?

Village.Idyot 0
Viewed 28

I'm trying to join two data frames, nCode and index, as shown in the below image. The code shown at the bottom adds the concat column from index to nCode by matching the eleCnt column, but I am trying to add the condition that concat is only added (joined) if the condition is met that Group <> 0 or the grpID between the two data frames match. Is there a clean, easy way to do this in dplyr or base R? I'm avoiding data.table for now as I'm newish to R and prefer keeping it simpler for now. I've been fooling around with dplyr's filter() function to add this condition but no luck yet.

This type of question is addressed in other posts like dplyr left_join by less than, greater than condition, and I like Jon Spring's solution to use the development version of left_join() where you could use left_join(x, y, join_by(a >= b, c < d)) for example, but I am wary of using a dev version for fear of bugs etc.

enter image description here

Code:

library(dplyr)

myDF5 <- 
  data.frame(
    Name = c("B","R","R","R","B","X","X"),
    Group = c(0,0,1,1,0,2,2)
    ) 

nCode <- myDF5 %>%
  mutate(origOrder = row_number()) %>%
  group_by(Name) %>%
  mutate(eleCnt = row_number()) %>%
  ungroup() %>%
  mutate(seqBase = ifelse(Group == 0 | Group != lag(Group), eleCnt,0)) %>%
  mutate(seqBase = na_if(seqBase, 0)) %>%
  group_by(Name) %>%
  fill(seqBase) %>%
  mutate(seqBase = match(seqBase, unique(seqBase))) %>%
  ungroup()

grpRnk <- nCode %>% select(Name,Group,eleCnt) %>% 
  filter(Group > 0) %>% 
  group_by(Name,Group) %>% 
  slice(which.min(Group)) %>% 
  ungroup() %>%
  arrange(eleCnt) %>%
  mutate(grpRnk = dense_rank(eleCnt)) %>% 
  select(-eleCnt) 

nCode <- left_join(nCode,grpRnk, by = c("Name", "Group")) %>%
  mutate(subGrp = ifelse(Group > 0, 
            sapply(1:n(), function(x) sum(Name[1:x]==Name[x]& 
            Group[1:x] == Group[x])), 0)) %>%
  mutate(grpID = sapply(1:n(), function(x) sum(eleCnt[(Group[1:n()] == Group[x]) & 
            (Name[1:n()] == Name[x]) & 
            (Group[1:n()]!= 0)])))

i = 1

index <- 
  filter(nCode, grpRnk == i) %>%
  distinct(eleCnt, .keep_all = TRUE) %>%
  mutate(grpID = sapply(1:n(), function(x) sum(eleCnt))) %>%
  mutate(concat = seqBase + subGrp/10) %>%
  select(eleCnt,grpID,concat)

index %>%
  select(eleCnt,concat) %>%
  left_join(nCode, ., by = "eleCnt")
left-join dplyr match r
1 Answers

One way to do this, sort of my typical fall back by overwriting, is to replace the index %>% select(...) at the bottom of the OP with the below:

index %>%
   select(eleCnt,concat) %>%
   left_join(nCode, ., by = "eleCnt") %>% 
   mutate(concat = ifelse(Group != 0 & grpID != max(index$grpID),NA,concat))

Which gives the correct answer of:

  Name  Group origOrder eleCnt seqBase grpRnk subGrp grpID concat
  <chr> <dbl>     <int>  <int>   <int>  <int>  <dbl> <int>  <dbl>
1 B         0         1      1       1     NA      0     0    1.1
2 R         0         2      1       1     NA      0     0    1.1
3 R         1         3      2       2      2      1     5   NA  
4 R         1         4      3       2      2      2     5   NA  
5 B         0         5      2       2     NA      0     0    1.2
6 X         2         6      1       1      1      1     3    1.1
7 X         2         7      2       1      1      2     3    1.2

But there must be a cleaner way to do this other than by overwriting the concat column.

Village.Idyot0
Related