Slow dplyr query in R

user_10609470 0
Viewed 567

I've got a bit of code in R:

library(dplyr)

df_temp <- df %>%
   group_by(policy_number, policy_year) %>% 
   summarise(term_start_date  = last(term_start_date),
             term_end_date    = last(term_end_date),
             on_cover_after   = last(on_cover_after),
             termination_code = last(termination_code),
             termination_date = last(termination_date))

The main table df is about 700,000 rows by 130 columns. Grouped by policy_number and policy_year there are about 300,000 (policy_number/policy_year) groupings.

4 of the 5 columns that I've referred to in last() are dates.

This query takes about 3 minutes to run, which is a nuisance because the rest of my code runs quite briskly. I'm hoping to speed it up. Is there anything I could try that might help please?

(ideally would supply a reprex but how could I do that here? not sure)

Thank you.

Edit: since I'm always using the last record for a given (policy_number/policy_year) pair, is there some code I could write along the lines of:

df_temp <- df %>%
   group_by(policy_number, policy_year) %>% 
   mutate(counter = 1:n()) %>%
   filter(counter == max(counter)) %>%
   select(term_start_date,
          term_end_date,
          on_cover_after,
          termination_code,
          termination_date)

?

dplyr r
3 Answers

Instead of summarise, use summarise_at

library(dplyr)
df %>%
   group_by(policy_number, policy_year) %>%
   summarise_at(vars(term_start_date, term_end_date,
       term_end_date,termination_code, termination_date), last)
user_37322710

There is a great source here about this. The author makes several great suggestions (see his comments section). I would consider aggregating your data with data.table, or if you stick with dplyr then consider defining a key. Some metrics of relative benchmarks:

enter image description here

From source

user_80725460

You can try either dtplyr package (data.table backend for dplyr) or data.table package directly to speed up the calculation. See the benchmark below for iris dataset

library(microbenchmark)
library(data.table)
library(dtplyr)
library(dplyr, warn.conflicts = FALSE)

# create a “lazy” data table for dtplyr
iris2 <- lazy_dt(iris)

# create a data table for data.table
dt <- setDT(copy(iris))

# dplyr calculation
dplyr_func <- function() {
  iris %>% 
    group_by(Species) %>% 
    summarise_at(vars(Sepal.Length:Petal.Width), last)      
}

# dtplyr calculation
dtplyr_func <- function() {
  iris2 %>% 
    group_by(Species) %>% 
    summarise_at(vars(Sepal.Length:Petal.Width), last)  %>% 
    as_tibble()      
}

# data.table calculation
dt_func <- function() {
  dt[ 
    , lapply(.SD, last)
    , Species]
}

Benchmark

microbenchmark(
  dplyr_func(),
  dtplyr_func(),
  dt_func(),
  times = 1000L
)

#> Unit: microseconds
#>           expr    min     lq   mean median     uq   max neval cld
#>   dplyr_func() 4605.0 4892.9 5180.3 4999.9 5155.1 23253  1000   c
#>  dtplyr_func() 2503.9 2633.6 2885.0 2714.3 2812.2 39490  1000  b 
#>      dt_func()  949.2 1155.3 1848.1 2036.0 2302.1  7251  1000 a

Created on 2020-04-02 by the reprex package (v0.3.0)

user_7865420
Related