# data.table 解法,但道理應該不限於 data.table
# d1 處理 Result*。同 Product-Sku-Result 之下有任一 "O" 就給 "O"。預先 melt 為長表格較方便。
# d2 處理 Date。預先排除所有 Result 皆為 "N" 的列,再按 Date 排序,再挑出同 Product-Sku 的第一筆。
# d.out 輸出結果,即 merge d1 與 d2
library( magrittr)
library( data.table )
d <-
data.table (
Product = c( "p1" %>% rep( 4 ) , "p2" %>% rep( 4 ) ) ,
Sku = c( "s" , "m" %>% rep( 3 ) , "s" , "m" , "s" , "s" ) ,
Date = as.POSIXct ( 8 : 1 , origin = "2022-10-10" ) ,
Result1 = c( "N" , "O" , "N" %>% rep( 3 ) , "O" %>% rep( 2 ) , "N" ) ,
Result2 = c( "O" %>% rep( 4 ) , "N" , "O" %>% rep( 2 ) , "N" ) ,
Result3 = c( "O" %>% rep( 6 ) , "N" %>% rep( 2 ) ) ,
Result4 = c( "N" , "O" , "N" %>% rep( 2 ) , "O" %>% rep( 2 ) , "N" %>% rep( 2 ) )
)
print( d)
d1 <-
d %>%
melt( id.vars = c( "Product" , "Sku" , "Date" ) ) %>%
.[ , .( res.merge = ifelse( any( value == "O" ) , "O" , "N" ) ) , by = .( Product, Sku, variable) ] %>%
dcast( Product + Sku ~ variable, value.var = "res.merge" )
d2 <-
d %>%
melt( id.vars = c( "Product" , "Sku" , "Date" ) ) %>%
.[ , .( Date.kept = ifelse( any( value == "O" ) , T, F) ) , by = .( Product, Sku, Date) ] %>%
.[ ( Date.kept ) ] %>%
split( by = c( "Product" , "Sku" ) ) %>%
lapply( function ( x) x[ 1 ] ) %>%
do .call ( "rbind" , .)
d.out <- merge( d1, d2, by = c( "Product" , "Sku" ) )
d.out [ , Date.kept := NULL]
print( d.out )
IyBkYXRhLnRhYmxlIOino+azle+8jOS9humBk+eQhuaHieipsuS4jemZkOaWvCBkYXRhLnRhYmxlCiMgZDEg6JmV55CGIFJlc3VsdCrjgILlkIwgUHJvZHVjdC1Ta3UtUmVzdWx0IOS5i+S4i+acieS7u+S4gCAiTyIg5bCx57WmICJPIuOAgumgkOWFiCBtZWx0IOeCuumVt+ihqOagvOi8g+aWueS+v+OAggojIGQyIOiZleeQhiBEYXRl44CC6aCQ5YWI5o6S6Zmk5omA5pyJIFJlc3VsdCDnmobngrogIk4iIOeahOWIl++8jOWGjeaMiSBEYXRlIOaOkuW6j++8jOWGjeaMkeWHuuWQjCBQcm9kdWN0LVNrdSDnmoTnrKzkuIDnrYbjgIIKIyBkLm91dCDovLjlh7rntZDmnpzvvIzljbMgbWVyZ2UgZDEg6IiHIGQyCgoKbGlicmFyeShtYWdyaXR0cikKbGlicmFyeShkYXRhLnRhYmxlKQpkIDwtCiAgZGF0YS50YWJsZSgKICAgIFByb2R1Y3QgPSBjKCJwMSIgJT4lIHJlcCg0KSwgInAyIiAlPiUgcmVwKDQpKSwgCiAgICBTa3UgPSBjKCJzIiwgIm0iICU+JSByZXAoMyksICJzIiwgIm0iLCAicyIsICJzIiksIAogICAgRGF0ZSA9IGFzLlBPU0lYY3QoODoxLCBvcmlnaW4gPSAiMjAyMi0xMC0xMCIpLAogICAgUmVzdWx0MSA9IGMoIk4iLCAiTyIsICJOIiAlPiUgcmVwKDMpLCAiTyIgJT4lIHJlcCgyKSwgIk4iKSwKICAgIFJlc3VsdDIgPSBjKCJPIiAlPiUgcmVwKDQpLCAiTiIsICJPIiAlPiUgcmVwKDIpLCAiTiIpLAogICAgUmVzdWx0MyA9IGMoIk8iICU+JSByZXAoNiksICJOIiAlPiUgcmVwKDIpKSwKICAgIFJlc3VsdDQgPSBjKCJOIiwgIk8iLCAiTiIgJT4lIHJlcCgyKSwgIk8iICU+JSByZXAoMiksICJOIiAlPiUgcmVwKDIpKQogICkKcHJpbnQoZCkKCmQxIDwtIAogIGQgJT4lCiAgbWVsdChpZC52YXJzID0gYygiUHJvZHVjdCIsICJTa3UiLCAiRGF0ZSIpKSAlPiUKICAuWywgLihyZXMubWVyZ2UgPSBpZmVsc2UoYW55KHZhbHVlID09ICJPIiksICJPIiwgIk4iKSksIGJ5ID0gLihQcm9kdWN0LCBTa3UsIHZhcmlhYmxlKV0gJT4lCiAgZGNhc3QoUHJvZHVjdCArIFNrdSB+IHZhcmlhYmxlLCB2YWx1ZS52YXIgPSAicmVzLm1lcmdlIikKCmQyIDwtCiAgZCAlPiUKICBtZWx0KGlkLnZhcnMgPSBjKCJQcm9kdWN0IiwgIlNrdSIsICJEYXRlIikpICU+JQogIC5bLCAuKERhdGUua2VwdCA9IGlmZWxzZShhbnkodmFsdWUgPT0gIk8iKSwgVCwgRikpLCBieSA9IC4oUHJvZHVjdCwgU2t1LCBEYXRlKV0gJT4lCiAgLlsoRGF0ZS5rZXB0KV0gJT4lCiAgc3BsaXQoYnkgPSBjKCJQcm9kdWN0IiwgIlNrdSIpKSAlPiUKICBsYXBwbHkoZnVuY3Rpb24oeCl4WzFdKSAlPiUKICBkby5jYWxsKCJyYmluZCIsIC4pCgpkLm91dCA8LSBtZXJnZShkMSwgZDIsIGJ5ID0gYygiUHJvZHVjdCIsICJTa3UiKSkKZC5vdXRbLCBEYXRlLmtlcHQgOj0gTlVMTF0KcHJpbnQoZC5vdXQp