在R的Arrow表中批量替换数值型变量的NA/null值（无需转成R数据框）

我在R中使用Arrow表处理大型数据集：该数据集约2400行（对应参与者信息）、950列，由2000个单文件含2-4k行的parquet文件合并为可查询数据库而来。但数据库中约400个个体的目标变量存在NA值，导致部分汇总变量输出大量NA。

数据示例 #

temp <- COI_tag |> filter(flag1 == 0, flag2 == 0) |> group_by(participantID) |> summarise(across(COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)][COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)]  %in% c("clean_concept_tags","clean_terms") == FALSE],sum)) |> compute()

> temp$concept_1
[
  [
    0,
    null,
    null,
    null,
    1,
    null,
    null,
    null,
    null,
    null,
    ...
    null,
    0,
    null,
    null,
    null,
    1,
    null,
    null,
    null,
    null
  ]
]

> unique(data.frame(temp$to_data_frame())$concept_1)
[1] NA  1  0  8
> length(data.frame(temp$to_data_frame())$concept_1)
[1] 4702
> sum(is.na(data.frame(temp$to_data_frame())$concept_1))
[1] 4657

核心问题：有没有办法在执行sum和compute前，直接在Arrow表中替换null/NA值，无需先将数据导入R？（因为replace_na似乎需要先导入数据到R环境）

尝试过的方法及报错 #

方法1：使用mutate_if #

temp <- COI_tag |> filter(flag1 == 0, flag2 == 0) |> group_by(participantID) |> mutate_if(is.na, across(where(is.na), ~ coalesce(.x, 0))) |> summarise(across(COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)][COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)]  %in% c("clean_concept_tags","clean_terms") == FALSE],sum)) |> compute()

报错信息：

Error in `mutate_if()`:
! `.p` is invalid.
✖ `.p` should return a single logical.
ℹ `.p` returns a size 3368621 <logical> for column `participantID`.
Run `rlang::last_trace()` to see where the error occurred

方法2：直接调用replace_na #

temp <- COI_tag |> filter(flag1 == 0, flag2 == 0) |> group_by(participantID) |> replace_na(0) |> summarise(across(COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)][COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)]  %in% c("clean_concept_tags","clean_terms") == FALSE],sum)) |> compute()

报错信息：

Error in `vec_size()`:
! `x` must be a vector, not a <arrow_dplyr_query> object.
Run `rlang::last_trace()` to see where the error occurred.

方法3：mutate结合across和replace_na #

temp <- COI_tag |> filter(flag1 == 0, flag2 == 0) |> group_by(participantID) |> mutate(across(where(is.numeric), ~replace_na(.x, 0))) |> summarise(across(COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)][COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)]  %in% c("clean_concept_tags","clean_terms") == FALSE],sum)) |> compute()

报错信息：

Error: Expression replace_na(participantID, 0) not supported in Arrow
Call collect() first to pull data into R.
In addition: Warning messages:
1: Invalid metadata$r 
2: Invalid metadata$r 

解决方案 #

方案1：汇总时直接跳过NA（更高效） #

Arrow的sum函数支持na.rm = TRUE参数，无需提前替换NA，直接在汇总时忽略即可，全程在Arrow引擎内执行：

# 先简化列名筛选逻辑
target_cols <- setdiff(
  COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)],
  c("clean_concept_tags", "clean_terms")
)

temp <- COI_tag |> 
  filter(flag1 == 0, flag2 == 0) |> 
  group_by(participantID) |> 
  summarise(across(all_of(target_cols), sum, na.rm = TRUE)) |> 
  compute()

方案2：汇总前用coalesce替换NA #

如果必须在汇总前替换NA，可使用Arrow支持的dplyr::coalesce函数，注意要排除非数值列（比如participantID）：

target_cols <- setdiff(
  COI_tag$schema$names[grepl("concept_|term_", COI_tag$schema$names)],
  c("clean_concept_tags", "clean_terms")
)

temp <- COI_tag |> 
  filter(flag1 == 0, flag2 == 0) |> 
  mutate(across(all_of(target_cols), ~coalesce(.x, 0))) |> 
  group_by(participantID) |> 
  summarise(across(all_of(target_cols), sum)) |> 
  compute()

说明：两种方案都无需将数据导入R环境，所有操作均在Arrow的列式存储引擎中完成，避免了内存压力。

内容的提问来源于stack exchange，提问作者TDeramus