Improve `pivot_longer()` performance.

[iangow]

Is there a way to send pivot_longer() to UNPIVOT?

I see a huge performance difference.

Reprex below:

system_time <- function (expr) {
  print(system.time(expr))
  expr
}

read_pq <- function(pq, dtype) {
  
  arrow_types <- c(
    float = arrow::float64(),
    int   = arrow::int32(),
    str   = arrow::string() ,
    date  = arrow::date32(),
    bool  = arrow::bool() 
  )
  
  arrow_type <- arrow_types[[dtype]]
  ds <- arrow::open_dataset(pq)
  sch <- ds$schema
  type_cols <- sch$names[vapply(sch$fields, \(f) f$type == arrow_type, 
                                logical(1))]
  
  key_cols <- c("IDRSSD", "date")
  type_cols <- setdiff(type_cols, key_cols)
  if (length(type_cols) == 0L) return(NULL)
  
  keep <- c(key_cols, type_cols)
  ds |> 
    dplyr::select(all_of(keep))
}

get_long <- function(pq, dtype = "float") {
  
  stopifnot(length(pq) == 1L)
  ds <- read_pq(pq, dtype)
  
  if (is.null(df)) return(NULL)
  
  sql <- paste0("
    SELECT IDRSSD, date, item, value
    FROM
      (UNPIVOT temp
      ON COLUMNS(* EXCLUDE (IDRSSD, date))
      INTO NAME item
           VALUE value
    ) AS u
    WHERE value IS NOT NULL")
  
  db <- DBI::dbConnect(duckdb::duckdb())
  
  out_db <- 
    ds |>
    arrow::to_duckdb(con = db, table_name = "temp")
  
  out <-
    dplyr::tbl(db, dplyr::sql(sql)) |> 
    arrow::to_arrow() |>
    dplyr::collect()
  
  DBI::dbDisconnect(db)
  
  out
}

get_long_dplyr <- function(pq, dtype = "float") {
  
  stopifnot(length(pq) == 1L)
  
  db <- DBI::dbConnect(duckdb::duckdb())
  
  out <-
    read_pq(pq, dtype) |> 
    arrow::to_duckdb(con = db) |>
    tidyr::pivot_longer(
      cols = -c(IDRSSD, date),
      names_to = "item",
      values_to = "value") |>
    dplyr::filter(!is.na(value)) |>
    arrow::to_arrow() |>
    dplyr::collect()
  
  DBI::dbDisconnect(db)
  
  out
}

url <- paste0("https://www.dropbox.com/scl/fi/n3o9ogfa7wuy2n0n1ylra/", 
              "rcr_20020630.parquet?rlkey=giq1pf0zamd948jknm1b4fb7t&dl=1")
t <- tempfile()
download.file(url, t)

get_long(t, dtype = "float") |> system_time()
#>    user  system elapsed 
#>   1.076   0.116   1.052
#> # A tibble: 1,521,603 × 4
#>    IDRSSD date       item     value
#>     <int> <date>     <chr>    <dbl>
#>  1     37 2002-06-30 RCON0010  1647
#>  2     37 2002-06-30 RCON1395     0
#>  3     37 2002-06-30 RCON1651     0
#>  4     37 2002-06-30 RCON1754 19232
#>  5     37 2002-06-30 RCON1773     0
#>  6     37 2002-06-30 RCON2170 69304
#>  7     37 2002-06-30 RCON2221     0
#>  8     37 2002-06-30 RCON3123  1074
#>  9     37 2002-06-30 RCON3128     0
#> 10     37 2002-06-30 RCON3210 12222
#> # ℹ 1,521,593 more rows
get_long_dplyr(t, dtype = "float") |> system_time()
#>    user  system elapsed 
#>  69.039   0.453  69.216
#> # A tibble: 1,521,603 × 4
#>    IDRSSD date       item        value
#>     <int> <date>     <chr>       <dbl>
#>  1  12311 2002-06-30 RCFD0010   895391
#>  2  14409 2002-06-30 RCFD0010   172166
#>  3  35301 2002-06-30 RCFD0010 27410474
#>  4  42541 2002-06-30 RCFD0010   755664
#>  5  44134 2002-06-30 RCFD0010    40762
#>  6  44602 2002-06-30 RCFD0010   938721
#>  7  53033 2002-06-30 RCFD0010    40714
#>  8  58243 2002-06-30 RCFD0010    53192
#>  9  60143 2002-06-30 RCFD0010  1109551
#> 10  63069 2002-06-30 RCFD0010   462482
#> # ℹ 1,521,593 more rows

url <- paste0("https://www.dropbox.com/scl/fi/b1hczq9n047579tn0mgud/",
              "ent_20141231.parquet?rlkey=wbzixev88198av6zr5v5x47bl&dl=1")
t <- tempfile()
download.file(url, t)

get_long(t, "str") |> system_time()
#>    user  system elapsed 
#>   0.035   0.008   0.041
#> # A tibble: 26,280 × 4
#>    IDRSSD date       item     value                             
#>     <int> <date>     <chr>    <chr>                             
#>  1     37 2014-12-31 RSSD9017 Bank of Hancock County            
#>  2     37 2014-12-31 RSSD9130 Sparta                            
#>  3     37 2014-12-31 RSSD9200 GA                                
#>  4     37 2014-12-31 RSSD9220 31087                             
#>  5    242 2014-12-31 RSSD9017 First Community  Bank, Xenia-Flora
#>  6    242 2014-12-31 RSSD9130 Xenia                             
#>  7    242 2014-12-31 RSSD9200 IL                                
#>  8    242 2014-12-31 RSSD9220 62899                             
#>  9    279 2014-12-31 RSSD9017 MINEOLA COMMUNITY BANK S. S. B.   
#> 10    279 2014-12-31 RSSD9130 MINEOLA                           
#> # ℹ 26,270 more rows
get_long_dplyr(t, "str") |> system_time()
#>    user  system elapsed 
#>   0.070   0.008   0.073
#> # A tibble: 26,280 × 4
#>    IDRSSD date       item     value                               
#>     <int> <date>     <chr>    <chr>                               
#>  1     37 2014-12-31 RSSD9017 Bank of Hancock County              
#>  2    242 2014-12-31 RSSD9017 First Community  Bank, Xenia-Flora  
#>  3    279 2014-12-31 RSSD9017 MINEOLA COMMUNITY BANK S. S. B.     
#>  4    354 2014-12-31 RSSD9017 Bison State Bank                    
#>  5    457 2014-12-31 RSSD9017 LOWRY STATE BANK                    
#>  6    505 2014-12-31 RSSD9017 BALLSTON SPA NATIONAL BANK          
#>  7   1155 2014-12-31 RSSD9017 First State Bank & Trust Company    
#>  8   1351 2014-12-31 RSSD9017 Bank of Grandin                     
#>  9   1454 2014-12-31 RSSD9017 Hilltop National Bank               
#> 10   1557 2014-12-31 RSSD9017 First State Bank, Kiowa, Kansas, The
#> # ℹ 26,270 more rows

^{Created on 2026-02-06 with reprex v2.1.1}