Repository navigation
fread problem with different number of columns #1812
Description
Activity
@christellacaze Use the
autostartoption so thatfreadcatches that there are "6" columns in the file, see?fread.Try
fread(..., autostart = 100)thanks Michael. I have hundreds of files that i need to import with thousands of lines each, and they may all have extra tabs in various places so i was hoping for a solution that would work for all of them. I guess that's not possible to do with fread so I'll use readr::read_delim instead.
thank you for all your help, I really appreciate it.
Christel
From: Michael Chirico [email protected]
Sent: 15 August 2016 19:07
To: Rdatatable/data.table
Cc: chrisjacques; Author
Subject: Re: [Rdatatable/data.table] fread problem with different number of columns (#1812)Use the autostart option so that fread catches that there are "6" columns in the file.
Try fread(..., autostart = 100)
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHubhttps://github.com//issues/1812#issuecomment-239897299, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AFovI4aZ_dbpXGIWiv_tAQYi88aCP8Y3ks5qgLjsgaJpZM4JkQRC.should be possible to set autostart to be x% of the length of the file, perhaps with command line tools. anyway I still think
sedis your best option.Please provide a minimal reproducible example.. code that we can copy/paste to reproduce the issue.
select=1:10should be able to ignore the rest of the line after the first 10 columns have been read, I suppose. That was the interesting idea from @christellacaze, iiuc. Similar tonrows=10ignoring invalid lines after row 10. We could do with an example file and some information on how the invalid file arose to justify the effort in supporting that.Reacted by Artem KlevtsovJust chiming in on this old request because I am running into the same issue. I am attaching an example file (~100k rows) which fails with the same issue. This is an extracted portion of a .sam file used for storing DNA sequence data. The file format specification (https://samtools.github.io/hts-specs/SAMv1.pdf) includes 11 mandatory columns which are fixed and other optional tags any of which may or may not be present in any given line. So having the ability to
select=1:11and ignore the optional tags would be useful in processing these type of files.@avinashs There are in theory unlimited number of issues that we can face in csv due it lack of constraints on its structure. While we still can add support for that to fread I would suggests to use command line tools for fixing csv before reading with fread. In the end any special case handling like this will slightly increase the maintenance cost of fread. It is always better to fix structure in the upstream tool which produce csv. Tools that you might want to look at are
awk,sed,grep. Then you just usefread(cmd="grep pattern file.csv").@jangorecki I completely agree with your idea in principle. I posted this particular file just as an illustrative example of a situation where the above feature would be useful. I already have a worked around for this right now, but I still think that the general idea of the feature, have fread skip / ignore consistency / error checks on columns that are not included by
select, is worth considering. I think it would make the behaviour of fread more intuitive and is in line with the feature set of other fast readers likereadr:read_csvandiotools:read.csv.raw. Not saying it should be a high priority addition, but it would be useful, provided it doesn't break / complicate other functionality.Reacted by Jan Gorecki1 remaining item
I did a reproducible example of this, and I notice that the problem happens when the "bad data" is after line 100.
So, all importing stops after reaching a problem after line 100.
library(data.table) # Example 01 - Working # It works if "new/bad column" appears in the first 100 lines. # So, a data.table is created with 11 columns using FILL temp <- tempfile() writeLines(c(rep("1,2,3,4,5,6,7,8,9,10", 99), "1,2,3,4,5,6,7,8,9,10,11", rep("1,2,3,4,5,6,7,8,9,10", 100)), con = temp) temp_data <- fread(temp, header = FALSE, fill = TRUE) unlink(temp) # Example 02 - Not working # It breaks if "new/bad column" appears after 100 lines # Note that error message tells to use fill = TRUE, even when it is already in use temp <- tempfile() writeLines(c(rep("1,2,3,4,5,6,7,8,9,10", 100), "1,2,3,4,5,6,7,8,9,10,11", rep("1,2,3,4,5,6,7,8,9,10", 100)), con = temp) temp_data <- fread(temp, header = FALSE, fill = TRUE) #> Warning in fread(temp, header = FALSE, fill = TRUE): Stopped early on line #> 101. Expected 10 fields but found 11. Consider fill=TRUE and comment.char=. #> First discarded non-empty line: <<1,2,3,4,5,6,7,8,9,10,11>> unlink(temp) # Example 03 - Not working # Using select = 1:10 as proposed, does not work either temp <- tempfile() writeLines(c(rep("1,2,3,4,5,6,7,8,9,10", 100), "1,2,3,4,5,6,7,8,9,10,11", rep("1,2,3,4,5,6,7,8,9,10", 100)), con = temp) temp_data <- fread(temp, header = FALSE, fill = TRUE, select = 1:10) #> Warning in fread(temp, header = FALSE, fill = TRUE, select = 1:10): Stopped #> early on line 101. Expected 10 fields but found 11. Consider fill=TRUE and #> comment.char=. First discarded non-empty line: <<1,2,3,4,5,6,7,8,9,10,11>> unlink(temp) # Example 04 - Not working # Using drop = 11, also does not work temp <- tempfile() writeLines(c(rep("1,2,3,4,5,6,7,8,9,10", 100), "1,2,3,4,5,6,7,8,9,10,11", rep("1,2,3,4,5,6,7,8,9,10", 100)), con = temp) temp_data <- fread(temp, header = FALSE, fill = TRUE, drop = 11) #> Warning in fread(temp, header = FALSE, fill = TRUE, drop = 11): Column #> number 11 (drop[1]) is out of range [1,ncol=10] #> Warning in fread(temp, header = FALSE, fill = TRUE, drop = 11): Stopped #> early on line 101. Expected 10 fields but found 11. Consider fill=TRUE and #> comment.char=. First discarded non-empty line: <<1,2,3,4,5,6,7,8,9,10,11>> unlink(temp) sessionInfo() #> R version 3.5.2 (2018-12-20) #> Platform: x86_64-w64-mingw32/x64 (64-bit) #> Running under: Windows 10 x64 (build 17763) #> #> Matrix products: default #> #> locale: #> [1] LC_COLLATE=English_United States.1252 #> [2] LC_CTYPE=English_United States.1252 #> [3] LC_MONETARY=English_United States.1252 #> [4] LC_NUMERIC=C #> [5] LC_TIME=English_United States.1252 #> #> attached base packages: #> [1] stats graphics grDevices utils datasets methods base #> #> other attached packages: #> [1] data.table_1.12.1 #> #> loaded via a namespace (and not attached): #> [1] compiler_3.5.2 magrittr_1.5 tools_3.5.2 htmltools_0.3.6 #> [5] yaml_2.2.0 Rcpp_1.0.0 stringi_1.3.1 rmarkdown_1.11 #> [9] highr_0.7 knitr_1.21 stringr_1.4.0 xfun_0.4 #> [13] digest_0.6.18 evaluate_0.12
Created on 2019-03-12 by the reprex package (v0.2.1)
In example 02, the expected result should be a data.table with 201 lines and 11 columns
In example 03 and 04, it should be a data.table with 201 lines and 10 columns.Another way of dealing with it, should be by ignoring all lines that does not meet the initial assumptions, in this case, 10 columns. This way, the expected value would be a data.table with 200 lines and 10 columns.
Maybe add a param
drop.bad = TRUEand just drop those lines besides stop the process?Hey folks. Also ran into this and ended up using an awk fix that may help the next person. Basically use the
cmdoption offreadand then useawk(on linux-like systems) to strip out just the columns you want. So if your file is called "test.csv" and you use a comma separator and you want the first 4 columns, you can do like:data.table::fread( cmd = "cat test.csv | awk -F ',' '{print $1 , $2 , $3 , $4}'")
It can get funky with the quotes and escapes but helped us do what we needed.
Also thanks for data.table! Helps get things done quicker for sure 👍
Having this problem right now reading a large number of csv-files with hundres of columns and where the error occurs in different places. Want to include rather than drop these additional columns. Had to switch to
read_csv.
hi there,
I've got a problem importing files where some lines end with double tab:
If the file's first line has extra columns and then other lines have missing columns, then fill=TRUE solves the problem.
However i have one file where the extra columns happen in line 82:
> test[80:84] [1] "1053982\t2014-11-30\t-1\ttimetak\t14" "1053982\t2014-11-30\t-1\tyearcompleted\t4" [3] "1053982\t24-11-2014\t-1\tAMOUNT OTHER WEBSITES\t6\t\t" "1053982\t24-11-2014\t-1\tBBC1_422\t1\t\t" [5] "1053982\t24-11-2014\t-1\tBBC1_501\t13\t\t"In this case fill=TRUE isn't enough to get around the problem, and i get an error message saying there's text after processing all cols:
Is there a way i could force fread to either only read the first 5 columns per line and dropping anything after that?
Failing that, can i specify the number of columns in advance to account for the lines with extra tabs, as this way fill=TRUE would work?