0

I have 2 dataframes as shown. The first (df1) has orders IDs, user IDS, and the time the user orders something. In df2, I have the orderIds and the time the order was responded to (timeResponse). What I need is a dataframe that takes these two dataframes and outputs each order ID, and if it was responded to, the time difference between the order time and the fastest order response. Thus, in the first order (order ID 1), there were 3 responses, with the first one being at 2pm - so it would be a 2 hour response.

I'm looking for a way to do this in R.

df1 <- data.frame(
    orderID = c(1,2,3,4,5),
    userID = c(101, 102, 103, 104, 105),
    timeOrdered = c("1/1/2020  12:00:00 PM", "1/2/20 1:00PM", "1/3/20 12:00 AM", "1/4/20  12:00 AM", "1/5/20 12:00 AM"))

df2 <- data.frame(responseID = c(1,2,3,4,5),
    orderID = c(101, 102, 103, 104, 105),
    timeResponse = c("1/1/20 2:00 PM", "1/1/20 3:00 PM", "1/1/20 4:00 PM", "1/4/20 2:00 PM", "1/5/20 2:00 PM"))
alistaire
  • 42,459
  • 4
  • 77
  • 117
Allen
  • 722
  • 4
  • 13
  • 31
  • 3
    See [the joins question](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) – alistaire Dec 14 '19 at 21:14
  • Thanks...actually, the second is. df2 <- data.frame(responseID = c(1,2,3,4,5), orderID = c(1, 2, 3, 4, 5), timeResponse = c("1/1/20 2:00 PM", "1/1/20 3:00 PM", "1/1/20 4:00 PM", "1/4/20 2:00 PM", "1/5/20 2:00 PM")) – Allen Dec 14 '19 at 21:21
  • Yes. I fixed it in my comment. Anyway the join worked for me. – Allen Dec 14 '19 at 22:10
  • Great to hear - feel free to provide your own solution as an answer. – Ben Dec 14 '19 at 22:12
  • Here is how I answered it. merge(x = df1, y = df2, by = "orderID", all.x = TRUE) %>% mutate(diff=difftime(timeResponse,timeOrdered)) %>% select(orderID,diff) %>% group_by(orderID) %>% slice(which.min(diff)) – Allen Dec 14 '19 at 22:13

0 Answers0