主页 / coding / 问题 / 76995634
Accepted
mr.T
Asked: 2023-08-29 03:44:03 +0800 CST

如何获取真实的行索引 R arrow 包

  • 772

假设我有一个巨大的文件，分为许多部分，我可以用箭头读取

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 个回答

  1. Best Answer

    直接用是不行的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()），如下所述：

    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

    这也适用于多文件数据集，例如 parquet 文件的目录（嵌套或非嵌套）：

    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.
    • 3

相关问题