Advertisement
hohiyan

ptt-r-20221124

Nov 24th, 2022 (edited)
1,005
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
R 3.28 KB | None | 0 0
  1. (df <- data.frame(Product = c(rep("Product 1", 4), rep("Product 2", 4)),
  2.                  Sku = c("S", rep("M", 3), "S", "M", "S", "S"),
  3.                  Date = c("2022/10/26 00:58", "2022/10/25 23:18", "2022/10/25 04:16",
  4.                           "2022/10/23 12:53", "2022/10/23 11:58", "2022/10/23 09:59",
  5.                           "2022/10/23 09:48", "2022/10/23 09:26"),
  6.                  Result1 = c("NG", "OK", rep("NG", 3), "OK", "OK", "NG"),
  7.                  Result2 = c(rep("OK", 4), "NG", "OK", "OK", "NG"),
  8.                  Result3 = c(rep("OK", 6), "NG", "NG"),
  9.                  Result4 = c("NG", "OK", "NG", "NG", "OK", "OK", "NG", "NG")
  10.                  ))
  11.  
  12. #>     Product Sku             Date Result1 Result2 Result3 Result4
  13. #> 1 Product 1   S 2022/10/26 00:58      NG      OK      OK      NG
  14. #> 2 Product 1   M 2022/10/25 23:18      OK      OK      OK      OK
  15. #> 3 Product 1   M 2022/10/25 04:16      NG      OK      OK      NG
  16. #> 4 Product 1   M 2022/10/23 12:53      NG      OK      OK      NG
  17. #> 5 Product 2   S 2022/10/23 11:58      NG      NG      OK      OK
  18. #> 6 Product 2   M 2022/10/23 09:59      OK      OK      OK      OK
  19. #> 7 Product 2   S 2022/10/23 09:48      OK      OK      NG      NG
  20. #> 8 Product 2   S 2022/10/23 09:26      NG      NG      NG      NG
  21.  
  22. # tidyverse
  23.  
  24. library(dplyr)
  25. library(tidyr)
  26.  
  27. dates <- df %>%
  28.   arrange(Product, Sku, desc(Date)) %>%
  29.   group_by(Product, Sku) %>%
  30.   select(Product, Sku, Date) %>%
  31.   slice(1)
  32.  
  33. results <- pivot_longer(df, starts_with("Result"), names_to = "Result") %>%
  34.   arrange(Product, Sku, Result, desc(value), desc(Date)) %>%
  35.   select(-Date) %>%
  36.   group_by(Product, Sku, Result) %>%
  37.   slice(1)
  38.  
  39. results <- pivot_wider(results, names_from = "Result", values_from = "value")
  40.  
  41. (left_join(dates, results, by=c("Product", "Sku")) %>%
  42.   arrange(Product, desc(Sku)))
  43.  
  44. #> # A tibble: 4 × 7
  45. #> # Groups:   Product, Sku [4]
  46. #>   Product   Sku   Date             Result1 Result2 Result3 Result4
  47. #>   <chr>     <chr> <chr>            <chr>   <chr>   <chr>   <chr>  
  48. #> 1 Product 1 S     2022/10/26 00:58 NG      OK      OK      NG    
  49. #> 2 Product 1 M     2022/10/25 23:18 OK      OK      OK      OK    
  50. #> 3 Product 2 S     2022/10/23 11:58 OK      OK      OK      OK    
  51. #> 4 Product 2 M     2022/10/23 09:59 OK      OK      OK      OK
  52.  
  53.  
  54. # data.table
  55.  
  56. library(data.table)
  57.  
  58. setDT(df)
  59.  
  60. dates <- df[order(Product, Sku, -Date), .(Date[1]), by=.(Product, Sku)]
  61.  
  62. results <- melt(df, measure = patterns("^Result"), variable.name = "Result")
  63.  
  64. setorder(results, Product, -Sku, Result, -value, -Date)
  65.  
  66. results <- results[, Date:=NULL][, .SD[1], by=.(Product, Sku, Result)]
  67.  
  68. results <- dcast(results, Product + Sku ~ Result, value.var = "value")
  69.  
  70. (dates[results, on=.(Product, Sku)][order(Product, -Sku)])
  71.  
  72. #>      Product    Sku               V1 Result1 Result2 Result3 Result4
  73. #>       <char> <char>           <char>  <char>  <char>  <char>  <char>
  74. #> 1: Product 1      S 2022/10/26 00:58      NG      OK      OK      NG
  75. #> 2: Product 1      M 2022/10/25 23:18      OK      OK      OK      OK
  76. #> 3: Product 2      S 2022/10/23 11:58      OK      OK      OK      OK
  77. #> 4: Product 2      M 2022/10/23 09:59      OK      OK      OK      OK
  78.  
  79. <sup>Created on 2022-11-24 with [reprex v2.0.2](https://reprex.tidyverse.org)</sup>
  80.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement