[SOLVED] Translating dplyr to data.table

Translating dplyr to data.table

So I am trying to translate some dplyr code. I have tried to get help from a package that translates dplyr to data.table but it still does not work. The error is with row_number from dplyr..

I need all the steps in the dplyr code (even though they don't make sense here with mtcars)

library(dplyr)
library(dtplyr) # from https://github.com/tidyverse/dtplyr
library(data.table)

mtcars %>% 
  distinct(mpg, .keep_all = TRUE) %>% 
  group_by(am) %>% 
  arrange(mpg, .by_group = TRUE) %>% 
  mutate(row_num = LETTERS[row_number()]) %>% 
  ungroup() 

# using dtplyr
dt <- lazy_dt(mtcars)

dt %>% 
  distinct(mpg, .keep_all = TRUE) %>% 
  group_by(am) %>% 
  arrange(mpg, .by_group = TRUE) %>% 
  mutate(row_num = LETTERS[row_number()]) %>% 
  ungroup() %>% 
  show_query()
#> unique(`_DT1`, by = "mpg")[order(am, mpg)][, `:=`(row_num = c("A", 
#> "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", 
#> "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")[row_number()]), 
#>     keyby = .(am)]

# I then use the query from dtplyr 
DT <- as.data.table(mtcars)
unique(DT, by = "mpg")[order(am, mpg)][, `:=`(row_num = c("A", 
                                                              "B", "C", "D", "E", "F", "G", 
                                                              "H", "I", "J", "K", "L", "M", 
                                                              "N", "O", "P", "Q", "R", "S", 
                                                              "T", "U", "V", "W", "X", "Y", 
                                                              "Z")[row_number()]), keyby = .(am)]

#> row_number() should only be called in a data context

^{Created on 2019-07-14 by the reprex package (v0.3.0)}

Solution

We can use seq_len(.N)

unique(DT, by = "mpg")[order(am, mpg)][, 
     `:=`(row_num = LETTERS[seq_len(.N)]), by = .(am)][]