r-如何根据有条件的日期时间(第一条记录或最后一条记录)和一段时间内的事件计数对数据进行分组

Question

我得到了两个数据帧：

数据框A:具有服装店的购买信息，变量为：客户名称、购买日期、代理商和一段时间内购买的产品。

产品KarenM_14>td style="text-align:center；">X_18-25-2021 18:21:28JeanJeanJeanKeithAlbaM_50>X_18-28-20021 20:21:03AlbaM_43>X_38-29-2021 20:21:04AlexM_36>X_28-25-2021 20:21:05

Answer 1

这应该做到，但肯定还有改进的空间：

library(tidyverse)
df1 <- tibble::tribble(
~NAME, ~PRODUCT, ~AGENT,        ~DATE_PURCHASE,
"Karen",   "M_14",  "X_1", "8-25-20021 18:21:28",
"Jean",   "M_78",  "X_3", "8-26-20021 18:11:06",
"Jean",   "M_71",  "X_4", "8-26-20021 18:21:01",
"Jean",   "M_64",  "X_4", "8-27-20021 20:21:59",
"Keith",   "M_57",  "X_4", "8-27-20021 20:21:02",
"Alba",   "M_50",  "X_1", "8-28-20021 20:21:03",
"Alba",   "M_43",  "X_3", "8-29-20021 20:21:04",
"Alex",   "M_36",  "X_2", "8-25-20021 20:21:05"
)
df2 <- tibble::tribble(
~NAME,        ~TYPE,         ~DATE_OF_CALL,        ~DATE_PURCHASE,
"Karen",   "COMPLAIN", "8-26-20021 18:21:28", "8-25-20021 18:21:28",
"Jean", "CX_SERVICE", "8-27-20021 18:11:06", "8-26-20021 18:11:06",
"Jean",   "COMPLAIN", "8-28-20021 18:21:01", "8-26-20021 18:21:01",
"Jean", "CX_SERVICE", "8-29-20021 20:21:59", "8-27-20021 20:21:59",
"Keith", "CX_SERVICE", "8-29-20021 20:21:02", "8-27-20021 20:21:02",
"Alba",   "COMPLAIN", "8-30-20021 20:21:03", "8-28-20021 20:21:03",
"Alex", "CX_SERVICE", "8-25-20021 21:21:05", "8-29-20021 20:21:04"
)
joined_df <- dplyr::full_join(df1, df2, by = "NAME")
solution <- joined_df %>% 
group_by(NAME) %>% 
select(-c(DATE_PURCHASE.y)) %>%
top_n(n = 1, wt = DATE_PURCHASE.x) %>% 
top_n(n = 1, wt = DATE_OF_CALL) %>% 
rename("LAST_PRODUCT_PURCHASED" = "PRODUCT",
"DATE_PURCHASE" = "DATE_PURCHASE.x")
solution
# A tibble: 5 x 6
# Groups:   NAME [5]
#  NAME  LAST_PRODUCT_PURCHASED AGENT DATE_PURCHASE     TYPE       DATE_OF_CALL       
#  <chr> <chr>                  <chr> <chr>               <chr>      <chr>              
#1 Karen M_14                   X_1   8-25-20021 18:21:28 COMPLAIN   8-26-20021 18:21:28
#2 Jean  M_64                   X_4   8-27-20021 20:21:59 CX_SERVICE 8-29-20021 20:21:59
#3 Keith M_57                   X_4   8-27-20021 20:21:02 CX_SERVICE 8-29-20021 20:21:02
#4 Alba  M_43                   X_3   8-29-20021 20:21:04 COMPLAIN   8-30-20021 20:21:03
#5 Alex  M_36                   X_2   8-25-20021 20:21:05 CX_SERVICE 8-25-20021 21:21:05