請查看檔案末尾的代碼段。我正在用箭頭和 R 做我的第一個小步驟來處理太大而無法加載到記憶體中的檔案。
我正在嘗試重現此處的步驟
https://francoismichonneau.net/2022/10/import-big-csv/
它說明了如何將大型 tsv 檔案轉換為 parquet 檔案而不將其加載到記憶體中。我知道使用指向您的資料的鏈接是不好的做法，但我別無選擇提供一個仍然失敗的最小示例。
您可以在此處下載 test.tsv 檔案（大檔案的一小部分）
https://mega.nz/file/V9FnhazJ#YXBvpuRVQ9s3nVkWCQEaWn0g9Ul6pmtXlap6X7Zkz1E
在 reprex 中我有一些錯誤，但我不明白它們來自哪里，而且它們似乎與資料不兼容。任何幫助深表感謝！
library(tidyverse)
library(arrow)
#>
#> Attaching package: 'arrow'
#> The following object is masked from 'package:utils':
#>
#> timestamp
data1<- read_tsv("test.tsv") ## data in memory
#> Rows: 10000 Columns: 29
#> ── Column specification ────────────────────────────────────────────────────────
#> Delimiter: "\t"
#> chr (27): AID_MEASURE_ID, DATE_CREATED, DATE_GRANTED, AA_PUBLISHED_DATE, SER...
#> dbl (2): GRANTED_AMOUNT_FROM_EUR, NOMINAL_AMOUNT_EUR_FROM
#>
#> ? Use `spec()` to retrieve the full column specification for this data.
#> ? Specify the column types or set `show_col_types = FALSE` to quiet this message.
data1
#> # A tibble: 10,000 × 29
#> AID_MEASURE_ID DATE_…1 DATE_…2 AA_PU…3 SERVE…? AM_TI…? AM_TI…? STATUS AM_PR…?
#> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 SA.41416 24/11/… 18/08/… 09/12/… TM-100… NGA Sc… NGA Sc… Publi… N
#> 2 SA.41416 24/11/… 18/08/… 09/12/… TM-100… NGA Sc… NGA Sc… Publi… N
#> 3 SA.41416 24/11/… 18/08/… 09/12/… TM-100… NGA Sc… NGA Sc… Publi… N
#> 4 SA.41416 24/11/… 18/08/… 09/12/… TM-100… NGA Sc… NGA Sc… Publi… N
#> 5 SA.41416 25/11/… 25/08/… 09/12/… TM-100… NGA Sc… NGA Sc… Publi… N
#> 6 SA.41416 25/11/… 29/08/… 09/12/… TM-100… NGA Sc… NGA Sc… Publi… N
#> 7 SA.45235 02/11/… 27/10/… 02/11/… TM-100… 2014–2… Measur… Publi… X
#> 8 SA.45235 02/11/… 27/10/… 02/11/… TM-100… 2014–2… Measur… Publi… X
#> 9 SA.33193 08/11/… 27/10/… 11/01/… TM-100… R&D&I-… R & D … Publi… N
#> 10 SA.42321 16/11/… 01/11/… 01/06/… TM-100… POTENC… POTENT… Publi… X
#> # … with 9,990 more rows, 20 more variables: COFINANCE <chr>, OBJECTIVE <chr>,
#> # OTHER_OBJECTIVE_EN <chr>, AID_INSTRUMENT <chr>,
#> # OTHER_AID_INSTRUMENT_EN <chr>, BENEFICIARY_NAME <chr>,
#> # BENEFICIARY_NAME_ENGLISH <chr>, BENEFICIARY_NATIONAL_ID <chr>,
#> # BENEFICIARY_NAT_ID_TYPE_SD <chr>, BENEFICIARY_TYPE_SD <chr>,
#> # COUNTRY_SD <chr>, REGION_SD <chr>, SECTOR_SD <chr>,
#> # GRANTED_AMOUNT_FROM_EUR <dbl>, NOMINAL_AMOUNT_EUR_FROM <dbl>, …
#> # ? Use `print(n = ...)` to see more rows, and `colnames()` to see all variable names
data2 <- open_dataset("test.tsv", format="tsv")
data2
#> FileSystemDataset with 1 csv file
#> 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: null
#> 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
#> GRANTING_AUTHORITY_NAME: string
#> GRANTING_AUTHORITY_NAME_EN: string
#> NUTS_CD: string
#> GRANTING_AUTHORITY_COUNTRY: string
data3 <- read_tsv_arrow("test.tsv",as_data_frame=F)
##This works
write_dataset(
data3,
format = "parquet",
path = ".",
max_rows_per_file = 1e7
)
print("Done with the first parquet file")
#> [1] "Done with the first parquet file"
## but this does not...why?
write_dataset(
data2,
format = "parquet",
path = ".",
max_rows_per_file = 1e7
)
#> Error: Invalid: In CSV column #11: Row #9107: CSV conversion error to null: invalid value 'SECURITY ARRANGEMENTS'
###Apparently there is an error with the string "Security Arrangements" on line
### 9107. Let us see
data_error <- data1[9107, ] |>
glimpse()
#> Rows: 1
#> Columns: 29
#> $ AID_MEASURE_ID <chr> "SA.33193"
#> $ DATE_CREATED <chr> "27/09/17"
#> $ DATE_GRANTED <chr> "14/09/17"
#> $ AA_PUBLISHED_DATE <chr> "27/09/17"
#> $ SERVER_REF <chr> "TM-10080819"
#> $ AM_TITLE <chr> "R&D&I-scheme Flanders. Prolongation of an …
#> $ AM_TITLE_EN <chr> "R & D & I-scheme Flanders. Extension of an…
#> $ STATUS <chr> "Published"
#> $ AM_PROC_TYPE_CD <chr> "N"
#> $ COFINANCE <chr> "No"
#> $ OBJECTIVE <chr> "Research and development"
#> $ OTHER_OBJECTIVE_EN <chr> NA
#> $ AID_INSTRUMENT <chr> "Direct grant"
#> $ OTHER_AID_INSTRUMENT_EN <chr> NA
#> $ BENEFICIARY_NAME <chr> "INDAVER"
#> $ BENEFICIARY_NAME_ENGLISH <chr> "INDAVER"
#> $ BENEFICIARY_NATIONAL_ID <chr> "0427.973.304"
#> $ BENEFICIARY_NAT_ID_TYPE_SD <chr> "KBO/BCE"
#> $ BENEFICIARY_TYPE_SD <chr> "Only large enterprises"
#> $ COUNTRY_SD <chr> "Belgium"
#> $ REGION_SD <chr> NA
#> $ SECTOR_SD <chr> "E.38.21-Treatment and disposal of non-haza…
#> $ GRANTED_AMOUNT_FROM_EUR <dbl> 644531
#> $ NOMINAL_AMOUNT_EUR_FROM <dbl> NA
#> $ GRANT_RANGE <chr> "Not Range"
#> $ GRANTING_AUTHORITY_NAME <chr> "VLAIO"
#> $ GRANTING_AUTHORITY_NAME_EN <chr> "VLAIO"
#> $ NUTS_CD <chr> NA
#> $ GRANTING_AUTHORITY_COUNTRY <chr> "Belgium"
### but I see nothing about that text on that line
sessionInfo()
#> R version 4.2.1 (2022-06-23)
#> Platform: x86_64-pc-linux-gnu (64-bit)
#> Running under: Debian GNU/Linux 11 (bullseye)
#>
#> Matrix products: default
#> BLAS: /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.9.0
#> LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.9.0
#>
#> locale:
#> [1] LC_CTYPE=en_GB.UTF-8 LC_NUMERIC=C
#> [3] LC_TIME=en_GB.UTF-8 LC_COLLATE=en_GB.UTF-8
#> [5] LC_MONETARY=en_GB.UTF-8 LC_MESSAGES=en_GB.UTF-8
#> [7] LC_PAPER=en_GB.UTF-8 LC_NAME=C
#> [9] LC_ADDRESS=C LC_TELEPHONE=C
#> [11] LC_MEASUREMENT=en_GB.UTF-8 LC_IDENTIFICATION=C
#>
#> attached base packages:
#> [1] stats graphics grDevices utils datasets methods base
#>
#> other attached packages:
#> [1] arrow_9.0.0.2 forcats_0.5.1 stringr_1.4.0 dplyr_1.0.9
#> [5] purrr_0.3.4 readr_2.1.2 tidyr_1.2.0 tibble_3.1.8
#> [9] ggplot2_3.3.6 tidyverse_1.3.1
#>
#> loaded via a namespace (and not attached):
#> [1] lubridate_1.8.0 assertthat_0.2.1 digest_0.6.29 utf8_1.2.2
#> [5] R6_2.5.1 cellranger_1.1.0 backports_1.4.1 reprex_2.0.1
#> [9] evaluate_0.15 httr_1.4.3 highr_0.9 pillar_1.8.0
#> [13] rlang_1.0.4 readxl_1.4.0 R.utils_2.12.0 R.oo_1.25.0
#> [17] rmarkdown_2.14 styler_1.7.0 bit_4.0.4 munsell_0.5.0
#> [21] broom_1.0.0 compiler_4.2.1 modelr_0.1.8 xfun_0.31
#> [25] pkgconfig_2.0.3 htmltools_0.5.2 tidyselect_1.1.2 fansi_1.0.3
#> [29] crayon_1.5.1 tzdb_0.3.0 dbplyr_2.2.0 withr_2.5.0
#> [33] R.methodsS3_1.8.2 grid_4.2.1 jsonlite_1.8.0 gtable_0.3.0
#> [37] lifecycle_1.0.1 DBI_1.1.3 magrittr_2.0.3 scales_1.2.0
#> [41] vroom_1.5.7 cli_3.3.0 stringi_1.7.8 fs_1.5.2
#> [45] xml2_1.3.3 ellipsis_0.3.2 generics_0.1.3 vctrs_0.4.1
#> [49] tools_4.2.1 bit64_4.0.5 R.cache_0.16.0 glue_1.6.2
#> [53] hms_1.1.1 parallel_4.2.1 fastmap_1.1.0 yaml_2.3.5
#> [57] colorspace_2.0-3 rvest_1.0.2 knitr_1.39 haven_2.5.0
由reprex 包于 2022-10-21 創建(v2.0.1)
uj5u.com熱心網友回復：
當您在未指定資料型別（架構）的情況下匯入資料時，它們是從您的檔案中推斷出來的。read_tsv_arrow()并open_dataset()使用不同的策略來做到這一點：
read_tsv_arrow()通過查看完整的資料集來做到這一點open_dataset()通過掃描資料集的前幾行來完成
您收到的錯誤訊息告訴您決議器無法轉換為null看起來像文本的內容。
報告的架構open_dataset()告訴您唯一具有null型別的列是OTHER_OBJECTIVE_EN. 如果您查看由 報告的架構read_tsv_arrow()，您將看到OTHER_OBJECTIVE_EN是string。這種差異將您指向錯誤訊息所暗示的內容。該列中實際上有文本資料，但它比決議器推斷資料型別所查看的內容更遠。您不能依賴open_dataset()于您的情況的默認行為，您需要手動指定架構：
data <- open_dataset("test.tsv",
format = "tsv",
skip_rows = 1,
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(),
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
)
請注意，在指定架構時，open_dataset()您需要跳過包含列名的第一行。
在錯誤訊息中，具有不正確資料型別的行號減一，因為標題包含在計數中。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/520945.html
標籤：rCSV阿帕奇箭头
下一篇：在awk的幾列上運行數學