R+Arrow：处理逗号小数分隔符的TSV转Parquet报错求助

问题背景 #

尝试在不加载全量数据到内存的情况下，将使用逗号作为小数分隔符的TSV文件转换为Parquet文件，但转换时因数值格式解析失败报错：

Error: Invalid: Could not open CSV input source '/home/lorenzo/mega_pcloud/work/COMP/stat_support/tam_arrow/new_test/test.tsv': Invalid: In CSV column #22: Row #5: CSV conversion error to double: invalid value '631135,74'

修复方案 #

无需修改原TSV文件，只需在open_dataset中添加CSV解析选项，指定小数分隔符为逗号即可。arrow包的csv_options()支持配置decimal_point参数，适配这种非标准数值格式。

修改后的完整代码 #

library(tidyverse)
library(arrow)

data <- open_dataset("test.tsv",
  format = "tsv",
  skip_rows = 1, 
  # 添加csv解析选项，指定小数分隔符为逗号
  csv_options = csv_options(decimal_point = ","),
  schema = schema(
    AID_MEASURE_ID = string(), 
    DATE_CREATED = string(), 
    DATE_GRANTED = string(), 
    AA_PUBLISHED_DATE = string(), 
    SERVER_REF = string(), 
    AM_TITLE = string(), 
    AM_TITLE_EN = string(), 
    STATUS = string(), 
    AM_PROC_TYPE_CD = string(), 
    COFINANCE = string(), 
    OBJECTIVE = string(), 
    OTHER_OBJECTIVE_EN = string(), 
    AID_INSTRUMENT = string(), 
    OTHER_AID_INSTRUMENT_EN = string(), 
    BENEFICIARY_NAME = string(), 
    BENEFICIARY_NAME_ENGLISH = string(), 
    BENEFICIARY_NATIONAL_ID = string(), 
    BENEFICIARY_NAT_ID_TYPE_SD = string(), 
    BENEFICIARY_TYPE_SD = string(), 
    COUNTRY_SD = string(), 
    REGION_SD = string(), 
    SECTOR_SD = string(), 
    GRANTED_AMOUNT_FROM_EUR = double(), 
    NOMINAL_AMOUNT_EUR_FROM = double(), 
    GRANT_RANGE = string(),
    GRANTED_AMOUNT_RANGE_DESC=string(),
    GRANTING_AUTHORITY_NAME = string(), 
    GRANTING_AUTHORITY_NAME_EN = string(), 
    NUTS_CD = string(), 
    GRANTING_AUTHORITY_COUNTRY = string()
  )
)

write_dataset(
  data,
  format = "parquet",
  path = ".",
  max_rows_per_file = 1e7
)

说明 #

• csv_options(decimal_point = ",")告诉arrow在解析数值型字段时，将逗号识别为小数分隔符，替代默认的点号
• 该方式依旧保持了open_dataset的分块读取特性，不会将全量数据加载到内存，适合处理大文件

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