匹配数据帧，排除上次非NA值并忽略订单-Java 学习之路

我有两个数据帧：

Partner<-c("Alpha","Beta","Zeta")
COL1<-c("A","C","M")
COL2<-c("B","D","K")
COL3<-c("C","F",NA)
COL4<-c("D",NA,NA)
df1<-data.frame(Partner,COL1,COL2,COL3,COL4)

lift<-c(9,10,11,12,12,23,12,24)
RULE1<-c("B","B","D","A","C","K","M","K")
RULE2<-c("A","A","C","B","A","M","T","M")
RULE3<-c("G","D","M","C" ,"M", "E",NA,NA)
RULE4<-c(NA,NA,"K","D" ,NA, NA,NA,NA)

df2<-data.frame(lift,RULE1,RULE2,RULE3,RULE4)

df1
Partner    COL1    COL2    COL3    COL4
Alpha         A       B       C       D
Beta          C       D       F      NA
Zeta          M       K      NA      NA

df2
lift    RULE1    RULE2     RULE3    RULE4
   9        B        A         G       NA
  10        B        A         D       NA
  11        D        C         M        K
  12        A        B         C        D
  12        C        A         M       NA
  23        K        M         E       NA
  12        M        T        NA       NA
  24        K        M        NA       NA

这是一个市场篮子分析 . df1是购买列出的每个项目的客户/合作伙伴：A，B，C ......等 .

df2是与过去购买的商品相关的推荐 .

每个df2行中的最后一个值代表建议 . 因此，最后一个非NA值的每一行中的前面的值是“篮子” .

因此，例如在df2的第一行中，它表明：如果B和A一起购买，建议G.

我希望能够弄清楚df1中的每个合作伙伴是否购买了每行中的所有值，不包括最终值，因为这是建议 . 然后将该建议添加到新数据帧的每一行的末尾 .

例如：对于合作伙伴：Alpha，从第一行推荐 Value G会不会很好？答案是肯定的，因为他们在df2（A和B）中从该行购买了所有值 .

对于合作伙伴：Beta，建议使用值G并不好，因为并非所有df2第一行的值都在Beta行中找到 .

最终产出：

Partner    COL1    COL2    COL3    COL4    lift   RULE1    RULE2    RULE3    RULE4   Does Last Non-NA Value Exist in Row?
Alpha         A       B       C       D       9       B        A        G       NA                                    No
Alpha         A       B       C       D      10       B        A        D       NA                                   Yes
Alpha         A       B       C       D      12       A        B        C        D                                   Yes
Alpha         A       B       C       D      12       C        A        M       NA                                    No
Zeta          M       K      NA      NA      23       K        M        E       NA                                    No
Zeta          M       K      NA      NA      12       M        T       NA       NA                                    No
Zeta          M       K      NA      NA      24       K        M       NA       NA                                   Yes

为清楚起见写出结果：

DF3

row1输出“No”，因为在Alpha Partner中找不到G而G中的所有值都出现在Alpha Partner（B，A）中

row2输出“Yes”，因为D在Alpha Partner中找到，D之前的所有值都出现在Alpha Partner（B，A）中

row3输出“Yes”，因为D在Alpha Partner中找到，D之前的所有值都出现在Alpha Partner（A，B，C）中

row4输出“No”，因为在Alpha Partner中找不到M，M中的所有值都出现在Alpha Partner（C，A）中

row5输出“No”，因为在Zeta Partner中找不到E，并且E之前的所有值都显示在Zeta Partner（K，M）中

row6输出“No”，因为在Zeta Partner中找不到T而T中的所有值都出现在Zeta Partner（M）中

row7输出“Yes”，因为在Zeta Partner中找到M，在Zeta Partner（K）中出现M之前的所有值

我认为这必须是某种联合或匹配，但无法弄清楚如何做到这一点 .

如果有人可以帮我解决这个问题，这将非常有用 .

谢谢 .

这是尝试：

df1<-cbind(df1_id=1:nrow(df1),df1)
df2 <- cbind(df2_id=1:nrow(df2),df2)
d11  <- df1 %>% gather(Col, Value,starts_with("C"))           #Long
d11 <- d11 %>% na.omit() %>%group_by(df1_id) %>% slice(-n()) #remove last non NA
d22  <- df2 %>%  gather(Rule, Value,starts_with("R"))
res <- inner_join(d11,d22)
rm(d22)
rm(d11)
final<-cbind(df1[res$df1_id,],df2[res$df2_id,])
final$Exist <- apply(final, 1, FUN = function(x) 
c("No", "Yes")[(anyDuplicated(x[!is.na(x) & x != "" ])!=0) +1])

但这不起作用，因为它没有考虑所有的 Value ，只要其中一个匹配......不是全部 .

1 回答

这非常棘手，因为必须将n个客户的购买与一组m个规则进行比较 . 除此之外，还有两点增加了复杂性：

df2 中的最后一个非NA RULE 列在语义上与其他列不同 . 不幸的是，给定的数据结构并未反映出这一点 . 因此， df2 缺少明确的推荐列 .
最后，必须确定合作伙伴是否已购买推荐商品 .

出于性能原因，下面的方法依赖于 melt() ， dcast() 和 data.table 包的连接操作 . 然而，为了避免产生n * m行的笛卡尔交叉积，使用循环 .

EDIT dcast() 已移出 lapply() 函数 .

准备n：m join的数据

library(data.table)
# convert to data.table and add row numbers
# here, a copy is used insteasd of setDT() in order to rename the data.tables
purchases <- as.data.table(df1)[, rnp := seq_len(.N)]
rules <- as.data.table(df2)[, rnr := seq_len(.N)]

# prepare purchases for joins
lp <- melt(purchases, id.vars = c("rnp", "Partner"), na.rm = TRUE)
wp <- dcast(lp, rnp ~ value, drop = FALSE)
wp
#   rnp  A  B  C  D  F  K  M
#1:   1  A  B  C  D NA NA NA
#2:   2 NA NA  C  D  F NA NA
#3:   3 NA NA NA NA NA  K  M


# prepare rules
lr <- melt(rules, id.vars = c("rnr", "lift"), na.rm = TRUE)
# identify last column of each rule which becomes the recommendation
rn_of_last_col <- lr[, last(.I), by = rnr][, V1]
# reshape from long to wide without recommendation
wr <- dcast(lr[-rn_of_last_col], rnr ~ value)
# add column with recommendations (kind of cbind, no join)
wr[, recommended := lr[rn_of_last_col, value]]
wr
#   rnr  A  B  C  D  K  M recommended
#1:   1  A  B NA NA NA NA           G
#2:   2  A  B NA NA NA NA           D
#3:   3 NA NA  C  D NA  M           K
#4:   4  A  B  C NA NA NA           D
#5:   5  A NA  C NA NA NA           M
#6:   6 NA NA NA NA  K  M           E
#7:   7 NA NA NA NA NA  M           T
#8:   8 NA NA NA NA  K NA           M

结合规则和购买

combi <- rbindlist(
  # implied loop over rules to find matching purchases for each rule
  lapply(seq_len(nrow(rules)), function(i) {
    # get col names except last col which is the recommendation
    cols <- lr[rnr == i, value[-.N]]
    # join single rule with all partners on relevant cols for this rule
    wp[wr[i, .SD, .SDcols = c(cols, "rnr", "recommended")], on = cols, nomatch = 0]
  })
)
# check if recommendation was purchased already
combi[, already_purchased := Reduce(`|`, lapply(.SD, function(x) x == recommended)), 
      .SDcols = -c("rnp", "rnr", "recommended")]
# clean up already purchased
combi[is.na(already_purchased), already_purchased := FALSE
      ][, already_purchased := ifelse(already_purchased, "Yes", "No")]
combi
#   rnp  A  B  C  D  F  K  M rnr recommended already_purchased
#1:   1  A  B  C  D NA NA NA   1           G                No
#2:   1  A  B  C  D NA NA NA   2           D               Yes
#3:   1  A  B  C  D NA NA NA   4           D               Yes
#4:   1  A  B  C  D NA NA NA   5           M                No
#5:   3 NA NA NA NA NA  K  M   6           E                No
#6:   3 NA NA NA NA NA  K  M   7           T                No
#7:   3 NA NA NA NA NA  K  M   8           M               Yes

在创建 combi 时，诀窍是只加入每个规则中包含的那些列 . 这就是为什么需要单独为每个规则进行连接 .

从本质上讲，我们现在已经完成了 . 但是，它看起来不像所需的输出 .

最终加入

tmp_rules <- rules[combi[, .(rnp, rnr, recommended, already_purchased)], on = "rnr"]
tmp_purch <- purchases[combi[, .(rnp, rnr)], on = "rnp"]
result <- tmp_purch[tmp_rules, on = c("rnp", "rnr")]
result[, (c("rnp", "rnr")) := NULL]
result
#   Partner COL1 COL2 COL3 COL4 lift RULE1 RULE2 RULE3 RULE4 recommend already_purchased
#1:   Alpha    A    B    C    D    9     B     A     G    NA         G                No
#2:   Alpha    A    B    C    D   10     B     A     D    NA         D               Yes
#3:   Alpha    A    B    C    D   12     A     B     C     D         D               Yes
#4:   Alpha    A    B    C    D   12     C     A     M    NA         M                No
#5:    Zeta    M    K   NA   NA   23     K     M     E    NA         E                No
#6:    Zeta    M    K   NA   NA   12     M     T    NA    NA         T                No
#7:    Zeta    M    K   NA   NA   24     K     M    NA    NA         M               Yes

回复于 2024-04-27T13:00:52+08:00

匹配数据帧，排除上次非NA值并忽略订单

1 回答

准备n：m join的数据

结合规则和购买

最终加入

相关问题