CODEWITHSUNDEEP
rdplyr
JasonAizkalns
JasonAizkalns

Reputation: 20463

Converting / Translating SQL to dplyr Left Join with Multiple Criteria

Here's the setup (it's really not that complicated...):

Table JobTitles

| PersonID | JobTitle | StartDate | EndDate |
|----------|----------|-----------|---------|
| A        | A1       | 1         | 5       |
| A        | A2       | 6         | 10      |
| A        | A3       | 11        | 15      |
| B        | B1       | 2         | 4       |
| B        | B2       | 5         | 7       |
| B        | B3       | 8         | 11      |
| C        | C1       | 5         | 12      |
| C        | C2       | 13        | 14      |
| C        | C3       | 15        | 18      |

Table Transactions:

| PersonID | TransDate | Amt |
|----------|-----------|-----|
| A        | 2         | 5   |
| A        | 3         | 10  |
| A        | 12        | 5   |
| A        | 12        | 10  |
| B        | 3         | 5   |
| B        | 3         | 10  |
| B        | 10        | 5   |
| C        | 16        | 10  |
| C        | 17        | 5   |
| C        | 17        | 10  |
| C        | 17        | 5   |

Desired Output:

| PersonID | JobTitle | StartDate | EndDate | Amt |
|----------|----------|-----------|---------|-----|
| A        | A1       | 1         | 5       | 15  |
| A        | A2       | 6         | 10      | 0   |
| A        | A3       | 11        | 15      | 15  |
| B        | B1       | 2         | 4       | 15  |
| B        | B2       | 5         | 7       | 0   |
| B        | B3       | 8         | 11      | 5   |
| C        | C1       | 5         | 12      | 0   |
| C        | C2       | 13        | 14      | 0   |
| C        | C3       | 15        | 18      | 30  |

This SQL gets me the desired output:

select jt.PersonID, jt.JobTitle, jt.StartDate, jt.EndDate, coalesce(sum(amt), 0) as amt
from JobTitles jt left join
     Transactions t
     on jt.PersonId = t.PersonId and
        t.TransDate between jt.StartDate and jt.EndDate
group by jt.PersonID, jt.JobTitle, jt.StartDate, jt.EndDate;

Tables for R:

JobTitles <- structure(list(PersonID = structure(c(1L, 1L, 1L, 2L, 2L, 2L, 
3L, 3L, 3L), .Label = c("A", "B", "C"), class = "factor"), JobTitle = structure(1:9, .Label = c("A1", 
"A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3"), class = "factor"), 
    StartDate = c(1L, 6L, 11L, 2L, 5L, 8L, 5L, 13L, 15L), EndDate = c(5L, 
    10L, 15L, 4L, 7L, 11L, 12L, 14L, 18L)), .Names = c("PersonID", 
"JobTitle", "StartDate", "EndDate"), class = "data.frame", row.names = c(NA, 
-9L))
Transactions <- structure(list(PersonID = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 
2L, 3L, 3L, 3L, 3L), .Label = c("A", "B", "C"), class = "factor"), 
    TransDate = c(2L, 3L, 12L, 12L, 3L, 3L, 10L, 16L, 17L, 17L, 
    17L), Amt = c(5L, 10L, 5L, 10L, 5L, 10L, 5L, 10L, 5L, 10L, 
    5L)), .Names = c("PersonID", "TransDate", "Amt"), class = "data.frame", row.names = c(NA, 
-11L))

How do I translate the SQL into working dplyr code? I am getting stuck on the left_join:

left_join(JobTitles, Transactions, 
          by = c("PersonID" = "PersonID", 
                 "StartDate" < "TransDate",
                 "EndDate" >= "TransDate"))
# Error: cannot join on columns 'TRUE' x '' : index out of bounds

Upvotes: 0

Views: 885

Answers (1)

jazzurro
jazzurro

Reputation: 23574

Similar to the idea of @zx8754, I came up with the following. I tried to use between because it was in the SQL script. But, the outcome is basically identical to what @zx8754 has. I further did calculation and got an outcome (i.e., foo). Then, I merged it with two columns (i.e., PersonID JobTitle) from JobTitles in order to get the expected outcome.

foo <- left_join(JobTitles, Transactions) %>%
       rowwise() %>%
       mutate(check = between(TransDate, StartDate, EndDate)) %>%
       filter(check == TRUE) %>%
       group_by(PersonID, JobTitle) %>%
       summarise(total = sum(Amt))

### Merge the master frame including all combs of PersonID and JobTitle, and foo
foo2 <- left_join(JobTitles[,c(1,2)], foo)

### NA to 0 
foo2$total[which(foo2$total %in% NA)] <- 0

#  PersonID JobTitle total
#1        A       A1    15
#2        A       A2     0
#3        A       A3    15
#4        B       B1    15
#5        B       B2     0
#6        B       B3     5
#7        C       C1     0
#8        C       C2     0
#9        C       C3    30

Or slightly shorter and in one pipe:

left_join(JobTitles, Transactions) %>%
  filter(TransDate > StartDate & TransDate < EndDate) %>%
  group_by(PersonID, JobTitle) %>%
  summarise(total = sum(Amt)) %>%
  left_join(JobTitles[,c(1,2)], .) %>%
  mutate(total = replace(total, is.na(total), 0))

Upvotes: 4

Related Questions