Improve `pivot_longer()` performance.
Is there a way to send `pivot_longer()` to `UNPIVOT`?
I see a huge performance difference.
Reprex below:
``` r
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
```
<sup>Created on 2026-02-06 with [reprex v2.1.1](https://reprex.tidyverse.org)</sup>
0 条评论