R Assigning New Variable based on data

Question

I have a data which includes rentals and searchs. If search is made by same customer who made rental, and if search made before rental then i want to assign as successful search.

Here is a part of my data.

time <- c("2019-03-13 14:43:00", "2019-03-13 14:34:00", "2019-03-13 14:23:00")
user <- c("A", "B", "A")
Type <- c("Rental","Search","Search")
data <- cbind(time, user, Type)

I need a new column that shows third row as successful.

But I have lots of data. So i need to do something like this:

If type is search and
If there is a rental up to 2 hours after search,
And if that rental's user name is equals search's user name

Then data$result <- "Successful"

"Something like this" is a bit vague. It would be better if you created a new dataset that shows exactly your expected output. Also, what have you tried? Is the use of external packages such as dplyr or data.table allowed? — s_baldur
– s_baldur, Commented Mar 14, 2019 at 8:50

MatthewR · Accepted Answer · 2019-03-14 09:29:39Z

1

I changed your data because it didn't make sense with your instructions. The time var you have is a point in time not a duration. So you either need a duration or two points. Also you said the rental's user name equals search's user name, but you only provided one name. Regardless this is how you would setup an if else as you describe.

time <- c(1:3)
username <- c("A", "B", "A")
rentalname <- c("A", "B", "A")
Type <- c("Rental","Search","Search")
data <- data.frame(time, username, rentalname, Type)


data$result <- ifelse( 
    data$Type %in% "Search" & 
    data$time > 2 &
    data$username %in% data$rentalname, "Successful" ,"Failure")

answered Mar 14, 2019 at 9:29

MatthewR

2,7806 gold badges29 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sargg · Accepted Answer · 2019-03-14 12:12:42Z

0

If I understand well what you want, this should work (it creates the new data frame "success" with the successful entries):

# create new data frame
success <- data.frame(time=character(), user=character(), Type=character(), result=character(), stringsAsFactors=F)

count <- 1

# loop around each user
for(us in unique(data[,"user"])){

  # subset data per user
  subdata <- data[data[,"user"] == us, ]

  # skips the user if there is only one entry for that user or if there is no "Rental" entry in "Type"
  if(is.null(dim(subdata))) next;
  if(!is.null(dim(subdata)) & !any(subdata[,"Type"] == "Rental")) next;

  # sort subdata chronologically
  subdata <- subdata[order(subdata[,"time"]),]

  # loop around rows in the subdata
  for(i in 2:nrow(subdata)){

    # calculate the time difference between entries i and i-1 if i is a rental and i-1 a search
    if(difftime(subdata[i,"time"], subdata[i-1, "time"], units="mins") < 120 & subdata[i-1, "Type"] == "Search" & subdata[i, "Type"] == "Rental"){
      success[count,] <- c(subdata[i,], "success")
      count <- count +1
    }
  }
}

It works for that small matrix you gave although you would need to try and make sure it works properly with a larger one.

answered Mar 14, 2019 at 12:12

sargg

3333 silver badges8 bronze badges

1 Comment

Ece Özçınar Over a year ago

Thank you so much this helped me a lot.

Collectives™ on Stack Overflow

R Assigning New Variable based on data

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related