代码之家 › 专栏 › 技术社区 › mr.T

如何获取实行索引R箭头包

apache-arrow dplyr r

0

mr.T · 技术社区 · 2 年前

让我们想象一下,我有一个巨大的文件,分成许多部分,我可以用箭头阅读

X <- iris
library(arrow)
# bigX a large file that is stored in chunks on disk out of memory
bigX <- arrow_table(X)

按某个值过滤后 Sepal.Width == 3.8 ,我得到了一些我需要的数据

library(dplyr)
 bigX %>% 
  filter(Sepal.Width == 3.8) %>% 
  collect()

。。

 Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
1          5.7         3.8          1.7         0.3    setosa
2          5.1         3.8          1.5         0.3    setosa
3          5.1         3.8          1.9         0.4    setosa
4          5.1         3.8          1.6         0.2    setosa
5          7.7         3.8          6.7         2.2 virginica
6          7.9         3.8          6.4         2.0 virginica

我的问题是,我还想从一个巨大的选择中获得真正的行索引。就像在这个例子中一样。

X[X$Sepal.Width==3.8,]

。。

    Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
19           5.7         3.8          1.7         0.3    setosa
20           5.1         3.8          1.5         0.3    setosa
45           5.1         3.8          1.9         0.4    setosa
47           5.1         3.8          1.6         0.2    setosa
118          7.7         3.8          6.7         2.2 virginica
132          7.9         3.8          6.4         2.0 virginica

1 回复 | 直到 2 年前

1

3

r2evans 2 年前

无法使用 row_number() 直接地

library(dplyr)
arrow::arrow_table(iris) %>%
  mutate(rn = row_number()) %>%
  filter(Sepal.Width == 3.8) %>%
  collect()
# Warning: Expression row_number() not supported in Arrow; pulling data into R
#   Sepal.Length Sepal.Width Petal.Length Petal.Width   Species  rn
# 1          5.7         3.8          1.7         0.3    setosa  19
# 2          5.1         3.8          1.5         0.3    setosa  20
# 3          5.1         3.8          1.9         0.4    setosa  45
# 4          5.1         3.8          1.6         0.2    setosa  47
# 5          7.7         3.8          6.7         2.2 virginica 118
# 6          7.9         3.8          6.4         2.0 virginica 132

但是添加 duckdb 对于惰性管道,我们可以执行一些简单的窗口函数(例如 row_number() ),如所述 here :

arrow::arrow_table(iris) %>%
  arrow::to_duckdb() %>%
  mutate(rn = row_number()) %>%
  filter(Sepal.Width == 3.8) %>%
  collect()
# # A tibble: 6 Ã 6
#   Sepal.Length Sepal.Width Petal.Length Petal.Width Species      rn
#          <dbl>       <dbl>        <dbl>       <dbl> <chr>     <dbl>
# 1          5.7         3.8          1.7         0.3 setosa       19
# 2          5.1         3.8          1.5         0.3 setosa       20
# 3          5.1         3.8          1.9         0.4 setosa       45
# 4          5.1         3.8          1.6         0.2 setosa       47
# 5          7.7         3.8          6.7         2.2 virginica   118
# 6          7.9         3.8          6.4         2   virginica   132

这也适用于多文件数据集,例如镶木地板文件的目录(嵌套或不嵌套):

arrow::write_dataset(mtcars, "~/Downloads/tempmt", partitioning = "cyl")
arrow::open_dataset("~/Downloads/tempmt/") %>%
  arrow::to_duckdb() %>%
  mutate(rn = row_number()) %>%
  filter(disp > 300) %>%
  collect()
# # A tibble: 11 Ã 12
#      mpg  disp    hp  drat    wt  qsec    vs    am  gear  carb   cyl    rn
#    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <dbl>
#  1  18.7   360   175  3.15  3.44  17.0     0     0     3     2     8    19
#  2  14.3   360   245  3.21  3.57  15.8     0     0     3     4     8    20
#  3  10.4   472   205  2.93  5.25  18.0     0     0     3     4     8    24
#  4  10.4   460   215  3     5.42  17.8     0     0     3     4     8    25
#  5  14.7   440   230  3.23  5.34  17.4     0     0     3     4     8    26
#  6  15.5   318   150  2.76  3.52  16.9     0     0     3     2     8    27
#  7  15.2   304   150  3.15  3.44  17.3     0     0     3     2     8    28
#  8  13.3   350   245  3.73  3.84  15.4     0     0     3     4     8    29
#  9  19.2   400   175  3.08  3.84  17.0     0     0     3     2     8    30
# 10  15.8   351   264  4.22  3.17  14.5     0     1     5     4     8    31
# 11  15     301   335  3.54  3.57  14.6     0     1     5     8     8    32

尽管在管道的早期完成这一步非常重要。。。如果在过滤后进行,则不会得到实际的行号,因为它们是相对于添加点行号的数据而言的:

arrow::open_dataset("~/Downloads/tempmt/") %>%
  arrow::to_duckdb() %>%
  filter(disp > 300) %>%
  mutate(rn = row_number()) %>%
  collect()
# # A tibble: 11 Ã 12
#      mpg  disp    hp  drat    wt  qsec    vs    am  gear  carb   cyl    rn
#    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <dbl>
#  1  18.7   360   175  3.15  3.44  17.0     0     0     3     2     8     1
#  2  14.3   360   245  3.21  3.57  15.8     0     0     3     4     8     2
#  3  10.4   472   205  2.93  5.25  18.0     0     0     3     4     8     3
#  4  10.4   460   215  3     5.42  17.8     0     0     3     4     8     4
#  5  14.7   440   230  3.23  5.34  17.4     0     0     3     4     8     5
#  6  15.5   318   150  2.76  3.52  16.9     0     0     3     2     8     6
#  7  15.2   304   150  3.15  3.44  17.3     0     0     3     2     8     7
#  8  13.3   350   245  3.73  3.84  15.4     0     0     3     4     8     8
#  9  19.2   400   175  3.08  3.84  17.0     0     0     3     2     8     9
# 10  15.8   351   264  4.22  3.17  14.5     0     1     5     4     8    10
# 11  15     301   335  3.54  3.57  14.6     0     1     5     8     8    11

注意,我真的很想把这个和 add_filename() 伪功能,

arrow::open_dataset("~/Downloads/tempmt/") %>%
  mutate(fn = add_filename()) %>%
  slice_head(n=3) %>%
  collect()
#    mpg disp  hp drat   wt qsec vs am gear carb cyl                                             fn
# 1 21.0  160 110 3.90 2.62 16.5  0  1    4    4   6 /home/r2/Downloads/tempmt/cyl=6/part-0.parquet
# 2 21.0  160 110 3.90 2.88 17.0  0  1    4    4   6 /home/r2/Downloads/tempmt/cyl=6/part-0.parquet
# 3 21.4  258 110 3.08 3.21 19.4  1  0    3    1   6 /home/r2/Downloads/tempmt/cyl=6/part-0.parquet

但遗憾的是,它没有得到duckdb的支持,

arrow::open_dataset("~/Downloads/tempmt/") %>%
  arrow::to_duckdb() %>%
  mutate(fn = add_filename(), rn = row_number()) %>%
  filter(disp > 400) %>%
  collect()
# Error in `collect()`:
# ! Failed to collect lazy table.
# Caused by error:
# ! rapi_prepare: Failed to prepare query SELECT *
# FROM (
#   SELECT *, add_filename() AS fn, ROW_NUMBER() OVER () AS rn
#   FROM arrow_044
# ) q01
# WHERE (disp > 400.0)
# Error: Catalog Error: Scalar Function with name add_filename does not exist!
# Did you mean "add"?
# Run `rlang::last_trace()` to see where the error occurred.

和 to_duckdb() 对类的对象无效 "arrow_dplyr_query" :

arrow::open_dataset("~/Downloads/tempmt/") %>%
  mutate(fn = add_filename()) %>%
  arrow::to_duckdb() %>%
  mutate(rn = row_number()) %>%
  filter(disp > 400) %>%
  collect()
# Error in `collect()`:
# ! Failed to collect lazy table.
# Caused by error:
# ! Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result
# Error: Invalid Error: std::exception
# Run `rlang::last_trace()` to see where the error occurred.