Flagging a row in a data set referring to conditions in another data set

Question

My scenario is as follows:

I have two sales activities, A and B If A is succesful it leads to a new activity B

A and B have their own data sets with matching structure. However, there is no information stored that which of the A's has been succesful and has led to which B. This is what I am trying to flag.

The rules that I am trying to apply are as follows: A$datedone = B$datecreated AND A$organization = B$organization

in other words: If date when A has been done = date when B has been created AND If Organization A = Organization on B

How I can check these conditions between the two data sets and then store the TRUE/FALSE to a new variable in data set A for each record there?

Please provide a reproducible example, i.e. a small example of A and B https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Pierre Lapointe, Jun 20 '17 at 11:26

score 0 · Answer 1 · answered Jun 20 '17 at 13:04

It's difficult to come up with a solid answer without knowing your data structure for each dataframe, but you may be able to leverage dplyr's left_join function to get the results you're after. Something like this may help you get what you are looking for.

library(dplyr)

# Create test dataframes
A <- data.frame(datedone = c("1/1/2017", "2/2/2017","3/3/2017", "4/4/2017"),
                organization = c("org1","org1","org2","org3"),
                someotherdata = c("d1","d2","d3","d4"),
                stringsAsFactors = FALSE)


B <- data.frame(datecreated = c("1/1/2017", "2/4/2017","3/3/2017", "4/4/2017"),
                organization = c("org1","org1","org2","org3"),
                someotherdata = c("d1","d2","d3","d4"),
                stringsAsFactors = FALSE)

# Add column to each dataframe to act as an identifier
A$fromdf <- "A"
B$fromdf <- "B"

# Left join dataframe B onto dataframe A
AB <- A %>%
      left_join(B, by = c("datedone" = "datecreated", "organization" = "organization")) %>%
      # Create a new column to show if there was a match, based on whether a value is present in fromdf.y
      mutate(ABmatch = ifelse(is.na(fromdf.y), FALSE, TRUE)) %>%
      # Select whichever columns are needed from the dataframe
      select(datedone, organization, someotherdata = someotherdata.x, ABmatch)

Flagging a row in a data set referring to conditions in another data set

1 Answers1