nrows apparently ignored on messy file
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 35/100
Research direction
Reproduce the report with OutputFile.txt and the shown fread calls, especially nrows=48L, nrows=50L, fill=TRUE, and skip=4L. Start at fread's parsing and row-limit handling; done means the interaction between malformed lines, separator detection, warnings, and nrows is understood and the reported erroneous behavior has a verified regression test.
Written by the indexing model from the issue text.
Description
Trying to download some inflation data via:
https://www.tablebuilder.singstat.gov.sg/publicfacing/createDataTable.action?refId=15092
The csv produced by Export > CSV is a huge mess (renamed to .txt so GH will allow me to upload)
I can't tell for sure exactly how to load the file, but this certainly seems wrong:
inflation = fread('OutputFile.csv')
Warning message:
In fread("OutputFile.csv") :
Stopped early on line 54. Expected 698 fields but found 0. Consider fill=TRUE and comment.char=. First discarded non-empty line: <<The weighting pattern of the Consumer Price Index (CPI) was derived from the expenditure values collected from the 2012/13 Household Expenditure Survey (HES) and updated to 2014 values by taking into account price changes between 2012/13 and 2014.>>
inflation[ , 2:10]
# 1961 Jan 1961 Feb 1961 Mar 1961 Apr 1961 May 1961 Jun 1961 Jul 1961 Aug 1961 Sep
# 1: 24.665 24.689 24.709 24.309 24.174 24.345 24.398 24.64 24.516
# 2: 23.995 24.011 24.058 23.345 23.118 23.421 23.511 23.926 23.712
# 3: na na na na na na na na na
# 4: na na na na na na na na na
# 5: na na na na na na na na na
# 6: na na na na na na na na na
# 7: na na na na na na na na na
# 8: na na na na na na na na na
# 9: na na na na na na na na na
# 10: na na na na na na na na na
# 11: na na na na na na na na na
# 12: na na na na na na na na na
# 13: na na na na na na na na na
# 14: na na na na na na na na na
# 15: na na na na na na na na na
# 16: na na na na na na na na na
# 17: na na na na na na na na na
# 18: na na na na na na na na na
# 19: 54.921 54.921 54.965 54.949 54.68 54.486 54.684 54.684 54.626
# 20: 23.391 23.493 23.391 23.476 23.545 23.545 23.545 23.545 23.545
# 21: na na na na na na na na na
# 22: na na na na na na na na na
# 23: 33.118 33.073 33.447 33.752 33.577 33.545 33.265 33.265 33.26
# 24: na na na na na na na na na
# 25: na na na na na na na na na
# 26: 19.863 19.863 19.863 19.863 19.863 19.863 19.863 19.863 19.863
# 27: na na na na na na na na na
# 28: na na na na na na na na na
# 29: 20.319 20.319 20.319 20.319 20.319 20.319 20.319 20.319 20.319
# 30: na na na na na na na na na
# 31: na na na na na na na na na
# 32: na na na na na na na na na
# 33: 87.887 87.887 87.887 87.887 87.887 87.887 87.887 87.887 87.887
# 34: 51.022 51.022 51.022 51.022 51.022 51.022 51.022 51.022 51.022
# 35: na na na na na na na na na
# 36: na na na na na na na na na
# 37: na na na na na na na na na
# 38: 17.211 17.211 17.211 17.211 17.211 17.211 17.211 17.211 17.211
# 39: na na na na na na na na na
# 40: na na na na na na na na na
# 41: 19.146 19.149 19.142 19.142 19.126 19.126 19.126 19.126 19.142
# 42: na na na na na na na na na
# 43: na na na na na na na na na
# 44: na na na na na na na na na
# 45: na na na na na na na na na
# 46: na na na na na na na na na
# 47: na na na na na na na na na
# 48: na na na na na na na na na
# 1961 Jan 1961 Feb 1961 Mar 1961 Apr 1961 May 1961 Jun 1961 Jul 1961 Aug 1961 Sep
There are a few rows of data missing, but verbose = TRUE suggests the reason:
omp_get_max_threads() = 8
omp_get_thread_limit() = 2147483647
DTthreads = 0
RestoreAfterFork = true
Input contains no \n. Taking this to be a filename to open
[01] Check arguments
Using 8 threads (omp_get_max_threads()=8, nth=8)
NAstrings = [<<NA>>]
None of the NAstrings look like numbers.
show progress = 1
0/1 column will be read as integer
[02] Opening the file
Opening file /Users/michael.chirico/Downloads/OutputFile.csv
File opened, size = 253.2KB (259299 bytes).
Memory mapped ok
[03] Detect and skip BOM
[04] Arrange mmap to be \0 terminated
\n has been found in the input and different lines can end with different line endings (e.g. mixed \n and \r\n in one file). This is common and ideal.
[05] Skipping initial rows if needed
Positioned on line 1 starting: << Subject: Prices and Price Ind>>
[06] Detect separator, quoting rule, and ncolumns
Detecting sep automatically ...
sep=',' with 49 lines of 698 fields using quote rule 0
Detected 698 columns on line 5. This line is either column names or first data row. Line starts as: << Variables , 1961 Jan , 1961 F>>
Quote rule picked = 0
fill=false and the most number of columns found is 698
[07] Detect column types, good nrow estimate and whether first row is column names
Number of sampling jump points = 1 because (257753 bytes from row 1 to eof) / (2 * 256625 jump0size) == 0
A line with too-few fields (0/698) was found on line 49 of sample jump 0.
Type codes (jump 000) : AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...7777777772 Quote rule 0
'header' determined to be true due to column 638 containing a string on row 1 and a lower type (float64) in the rest of the 48 sample rows
All rows were sampled since file is small so we know nrow=48 exactly
[08] Assign column names
[09] Apply user overrides on column types
After 0 type and 0 drop user overrides : AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...7777777772
[10] Allocate memory for the datatable
Allocating 698 column slots (698 - 0 dropped) with 48 rows
[11] Read the data
jumps=[0..1), chunk_size=1048576, total_size=250084
Restarting team from jump 0. nSwept==0 quoteRule==1
jumps=[0..1), chunk_size=1048576, total_size=250084
Restarting team from jump 0. nSwept==0 quoteRule==2
jumps=[0..1), chunk_size=1048576, total_size=250084
Restarting team from jump 0. nSwept==0 quoteRule==3
jumps=[0..1), chunk_size=1048576, total_size=250084
Read 48 rows x 698 columns from 253.2KB (259299 bytes) file in 00:00.007 wall clock time
[12] Finalizing the datatable
Type counts:
1 : bool8 '2'
60 : float64 '7'
637 : string 'A'
=============================
0.000s ( 3%) Memory map 0.000GB file
0.004s ( 54%) sep=',' ncol=698 and header detection
0.000s ( 2%) Column type detection using 48 sample rows
0.000s ( 4%) Allocation of 48 rows x 698 cols (0.000GB) of which 48 (100%) rows used
0.003s ( 37%) Reading 1 chunks (0 swept) of 1.000MB (each chunk 48 rows) using 1 threads
+ 0.001s ( 9%) Parse to row-major thread buffers (grown 1 times)
+ 0.002s ( 26%) Transpose
+ 0.000s ( 2%) Waiting
0.000s ( 0%) Rereading 0 columns due to out-of-sample type exceptions
0.007s Total****
In particular this part of the verbose output contradicts the warning message I got:
A line with too-few fields (0/698) was found on line 49 of sample jump 0.
Whereas the warning said it stopped on line 54 and told me that was the first discarded line...
In any case, the same warning is issued if we supply nrows = 50L:
fread('OutputFile.csv', nrows = 50)
Warning message:
In fread("OutputFile.csv", nrows = 50) :
Stopped early on line 54. Expected 698 fields but found 0. Consider fill=TRUE and comment.char=. First discarded non-empty line: <<The weighting pattern of the Consumer Price Index (CPI) was derived from the expenditure values collected from the 2012/13 Household Expenditure Survey (HES) and updated to 2014 values by taking into account price changes between 2012/13 and 2014.>>
However, it's clear that this warning is being issued erroneously... actually, the warning is being triggered by that 49th line, since if we supply nrows = 48L, there's no error
nrow(fread('OutputFile.csv', nrows = 48L))
# [1] 48
So what about fill? Well, plain fill doesn't work as the sep detector gets thrown off:
fread('~/Downloads/OutputFile.csv', fill = TRUE, verbose = TRUE)
omp_get_max_threads() = 8
omp_get_thread_limit() = 2147483647
DTthreads = 0
RestoreAfterFork = true
Input contains no \n. Taking this to be a filename to open
[01] Check arguments
Using 8 threads (omp_get_max_threads()=8, nth=8)
NAstrings = [<<NA>>]
None of the NAstrings look like numbers.
show progress = 1
0/1 column will be read as integer
[02] Opening the file
Opening file /Users/michael.chirico/Downloads/OutputFile.csv
File opened, size = 253.2KB (259299 bytes).
Memory mapped ok
[03] Detect and skip BOM
[04] Arrange mmap to be \0 terminated
\n has been found in the input and different lines can end with different line endings (e.g. mixed \n and \r\n in one file). This is common and ideal.
[05] Skipping initial rows if needed
Positioned on line 1 starting: << Subject: Prices and Price Ind>>
[06] Detect separator, quoting rule, and ncolumns
Detecting sep automatically ...
No sep and quote rule found a block of 2x2 or greater. Single column input.
Error in fread("OutputFile.csv", fill = TRUE, verbose = TRUE) :
Single column input contains invalid quotes. Self healing only effective when ncol>1
In fact, supplying nrows does not help the sep detector overcome the confusion introduced by fill
fread("OutputFile.csv", fill = TRUE, nrows = 48L)
Error in fread("OutputFile.csv", fill = TRUE, nrows = 48L) :
Single column input contains invalid quotes. Self healing only effective when ncol>1
Supplying sep = ',' doesn't help either.
In fact, we need to examine the first few lines of the file as well; since they're broken, we need to supply skip as well:
inflation = fread('OutputFile.csv', skip = 4L, nrows = 48L)
This works, but I think fread could do a better job of helping us get to here
- Dominant language
- R
- Stars
- 3.9k
- Forks
- 1.1k
- Avg merge
- 14h 4m
- Merged PRs (30d)
- 4
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from Rdatatable/data.table
-
as.data.table() recurses without end on a survival::Surv object (or any data.frame carrying one) Open
Difficulty 2/5 1-3 hours Newbie friendliness 88/100
Rdatatable/data.table#7887 ·
-
consistency tests
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
Rdatatable/data.table#7853 · 3 comments ·
-
internals
Difficulty 2/5 1-3 hours Newbie friendliness 65/100
Rdatatable/data.table#6938 · 1 comment ·
-
encoding fread
Difficulty 2/5 1-3 hours Newbie friendliness 65/100
Rdatatable/data.table#5179 · 8 comments ·
-
documentation programming
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
Rdatatable/data.table#3199 · 3 comments ·
All issues in Rdatatable/data.table
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
r-lib/pkgdepends#485 · 3 comments ·
-
Difficulty 1/5 Under an hour Newbie friendliness 92/100
-
beginners blocker
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
-
enviPathR OpenBuild Error Build OK Build Warning policies-accepted pre-review precheck-passed
Difficulty 1/5 Under an hour Newbie friendliness 84/100
Bioconductor/BiocContributions#207 · 6 comments ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
datacarpentry/semester-biology#1255 ·