ptt-r-20221124

(df <- data.frame(Product = c(rep("Product 1", 4), rep("Product 2", 4)),
                 Sku = c("S", rep("M", 3), "S", "M", "S", "S"),
                 Date = c("2022/10/26 00:58", "2022/10/25 23:18", "2022/10/25 04:16",
                          "2022/10/23 12:53", "2022/10/23 11:58", "2022/10/23 09:59",
                          "2022/10/23 09:48", "2022/10/23 09:26"),
                 Result1 = c("NG", "OK", rep("NG", 3), "OK", "OK", "NG"),
                 Result2 = c(rep("OK", 4), "NG", "OK", "OK", "NG"),
                 Result3 = c(rep("OK", 6), "NG", "NG"),
                 Result4 = c("NG", "OK", "NG", "NG", "OK", "OK", "NG", "NG")
                 ))

#>     Product Sku             Date Result1 Result2 Result3 Result4
#> 1 Product 1   S 2022/10/26 00:58      NG      OK      OK      NG
#> 2 Product 1   M 2022/10/25 23:18      OK      OK      OK      OK
#> 3 Product 1   M 2022/10/25 04:16      NG      OK      OK      NG
#> 4 Product 1   M 2022/10/23 12:53      NG      OK      OK      NG
#> 5 Product 2   S 2022/10/23 11:58      NG      NG      OK      OK
#> 6 Product 2   M 2022/10/23 09:59      OK      OK      OK      OK
#> 7 Product 2   S 2022/10/23 09:48      OK      OK      NG      NG
#> 8 Product 2   S 2022/10/23 09:26      NG      NG      NG      NG

# tidyverse

library(dplyr)
library(tidyr)

dates <- df %>%
  arrange(Product, Sku, desc(Date)) %>%
  group_by(Product, Sku) %>%
  select(Product, Sku, Date) %>%
  slice(1)

results <- pivot_longer(df, starts_with("Result"), names_to = "Result") %>%
  arrange(Product, Sku, Result, desc(value), desc(Date)) %>%
  select(-Date) %>%
  group_by(Product, Sku, Result) %>%
  slice(1)

results <- pivot_wider(results, names_from = "Result", values_from = "value")

(left_join(dates, results, by=c("Product", "Sku")) %>%
  arrange(Product, desc(Sku)))

#> # A tibble: 4 × 7
#> # Groups:   Product, Sku [4]
#>   Product   Sku   Date             Result1 Result2 Result3 Result4
#>   <chr>     <chr> <chr>            <chr>   <chr>   <chr>   <chr>
#> 1 Product 1 S     2022/10/26 00:58 NG      OK      OK      NG
#> 2 Product 1 M     2022/10/25 23:18 OK      OK      OK      OK
#> 3 Product 2 S     2022/10/23 11:58 OK      OK      OK      OK
#> 4 Product 2 M     2022/10/23 09:59 OK      OK      OK      OK


# data.table

library(data.table)

setDT(df)

dates <- df[order(Product, Sku, -Date), .(Date[1]), by=.(Product, Sku)]

results <- melt(df, measure = patterns("^Result"), variable.name = "Result")

setorder(results, Product, -Sku, Result, -value, -Date)

results <- results[, Date:=NULL][, .SD[1], by=.(Product, Sku, Result)]

results <- dcast(results, Product + Sku ~ Result, value.var = "value")

(dates[results, on=.(Product, Sku)][order(Product, -Sku)])

#>      Product    Sku               V1 Result1 Result2 Result3 Result4
#>       <char> <char>           <char>  <char>  <char>  <char>  <char>
#> 1: Product 1      S 2022/10/26 00:58      NG      OK      OK      NG
#> 2: Product 1      M 2022/10/25 23:18      OK      OK      OK      OK
#> 3: Product 2      S 2022/10/23 11:58      OK      OK      OK      OK
#> 4: Product 2      M 2022/10/23 09:59      OK      OK      OK      OK

<sup>Created on 2022-11-24 with [reprex v2.0.2](https://reprex.tidyverse.org)</sup>