Translating data.table to dplyr with table.express

table-express_demo.R

# https://www.johnmackintosh.com/2019-06-30-datatable-by-a-dummy/

# |- install latest version of table.express ----
devtools::install_github('asardaes/table.express')

# |- load library ----
library(dplyr)
library(table.express)
library(data.table)

# |- raw data ----
data <- rio::import("https://github.com/leungi/datasets/raw/master/RedGreenGreyDots.xlsx")

# |- dplyr ----
plot_data <- data %>%
  mutate(Movement15 = lubridate::floor_date(MovementDateTime, "15 minutes")) %>%
  group_by(IN_OUT, Movement_Type, Staging_Post, Movement15) %>%
  mutate(counter = case_when(
    IN_OUT == "IN" ~ 1,
    IN_OUT == "OUT" ~ -1
  )) %>%
  mutate(Movement_15_SEQNO = cumsum(counter)) %>%
  ungroup()

# Change "Tranfer In"  or "Transfer Out" to "Transfer"
plot_data$Movement_Type <- gsub("Transfer.*", "Transfer", x = plot_data$Movement_Type)

# |- table.express ----
DT <- as.data.table(data)
DT %>% 
  start_expr() %>% 
  mutate(Movement15 = lubridate::floor_date(MovementDateTime, "15 minutes")) %>% 
  mutate(counter = case_when(
    IN_OUT == "IN" ~ 1,
    IN_OUT == "OUT" ~ -1
  )) %>%
  # need to chain() to capture the counter column computed in previous step
  chain() %>% 
  group_by(IN_OUT, Movement_Type, Staging_Post, Movement15) %>%
  mutate(Movement_15_SEQNO = cumsum(counter)) %>% 
  mutate(Movement_Type =  if_else(stringr::str_detect(Movement_Type, 'Transfer.*'),
                                  'Transfer',
                                  Movement_Type)) %>%
  end_expr() %>% {
    invisible(print(.))
  }

# |- compare output ----
setdiff(as_tibble(DT), plot_data)