title: Faster way to read fixed-width files
body: <p>I work with a lot of fixed width files (i.e., no separating character) that I need to read into R.  So, there is usually a definition of the column width to parse the string into variables.  I can use <code>read.fwf</code> to read in the data without a problem.  However, for large files, this can take a <strong>long</strong> time.  For a recent dataset, this took 800 seconds to read in a dataset with ~500,000 rows and 143 variables. </p>
  
  <pre><code>seer9 &lt;- read.fwf("~/data/rawdata.txt", 
    widths = cols,
    header = FALSE,
    buffersize = 250000,
    colClasses = "character",
    stringsAsFactors = FALSE))
  </code></pre>
  
  <p><code>fread</code> in the <code>data.table</code> package in R is awesome for solving most data read problems, except it doesn't parse fixed width files.  However, I can read each line in as a single character string (~500,000 rows, 1 column).  This takes 3-5 seconds.  (I love data.table.)</p>
  
  <pre><code>seer9 &lt;- fread("~/data/rawdata.txt", colClasses = "character",
                 sep = "\n", header = FALSE, verbose = TRUE)
  </code></pre>
  
  <p>There are a number of good posts on SO on how to parse text files.  See JHoward's suggestion <a href="https://stackoverflow.com/questions/20667086/is-there-a-faster-method-to-split-string-into-substrings-of-a-given-length">here</a>, to create a matrix of start and end columns, and <code>substr</code> to parse the data.  See GSee's suggestion <a href="https://stackoverflow.com/questions/11619616/how-to-split-a-string-into-substrings-of-a-given-length/11619681#11619681">here</a> to use <code>strsplit</code>. I couldn't figure out how to make that work with this data.  (Also, Michael Smith made some suggestions on the data.table mailing list involving <code>sed</code> that were beyond my ability to <a href="http://lists.r-forge.r-project.org/pipermail/datatable-help/2014-April/002496.html" rel="noreferrer">implement.</a>) Now, using <code>fread</code> and <code>substr()</code> I can do the whole thing in about 25-30 seconds.  Note that coercing to a data.table at end takes a chunk of time (5 sec?).</p>
  
  <pre><code>end_col &lt;- cumsum(cols)
  start_col &lt;- end_col - cols + 1
  start_end &lt;- cbind(start_col, end_col) # matrix of start and end positions
  text &lt;- lapply(seer9, function(x) {
          apply(start_end, 1, function(y) substr(x, y[1], y[2])) 
          })
  dt &lt;- data.table(text$V1)
  setnames(dt, old = 1:ncol(dt), new = seervars)
  </code></pre>
  
  <p>What I am wondering is whether this can be improved any further?  I know I am not the only one who has to read fixed width files, so if this could be made faster, it would make loading even larger files (with millions of rows) more tolerable.  I tried using <code>parallel</code> with <code>mclapply</code> and <code>data.table</code>instead of <code>lapply</code>, but those didn't change anything. (Likely due to my inexperience in R.)  I imagine that an Rcpp function could be written to do this really fast, but that is beyond my skill set.  Also, I may not be using lapply and apply appropriately.</p>
  
  <p>My data.table implementation (with <code>magrittr</code> chaining) takes the same time:</p>
  
  <pre><code>text &lt;- seer9[ , apply(start_end, 1, function(y) substr(V1, y[1], y[2]))] %&gt;% 
    data.table(.)
  </code></pre>
  
  <p>Can anyone make suggestions to improve the speed of this?  Or is this about as good as it gets?</p>
  
  <p>Here is code to create a similar data.table within R (rather than linking to actual data). It should have 331 characters, and 500,000 rows.  There are spaces to simulate missing fields in the data, but this is <strong>NOT</strong> space delimited data. (I am reading raw SEER data, in case anyone is interested.) Also including column widths (cols) and variable names (seervars) in case this helps someone else.  These are the actual column and variable definitions for SEER data.</p>
  
  <pre><code>seer9 &lt;-
    data.table(rep((paste0(paste0(letters, 1000:1054, " ", collapse = ""), " ")),
                   500000))
  
  cols = c(8,10,1,2,1,1,1,3,4,3,2,2,4,4,1,4,1,4,1,1,1,1,3,2,2,1,2,2,13,2,4,1,1,1,1,3,3,3,2,3,3,3,3,3,3,3,2,2,2,2,1,1,1,1,1,6,6,6,2,1,1,2,1,1,1,1,1,2,2,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,7,5,4,10,3,3,2,2,2,3,1,1,1,1,2,2,1,1,2,1,9,5,5,1,1,1,2,2,1,1,1,1,1,1,1,1,2,3,3,3,3,3,3,1,4,1,4,1,1,3,3,3,3,2,2,2,2)
  seervars &lt;- c("CASENUM", "REG", "MAR_STAT", "RACE", "ORIGIN", "NHIA", "SEX", "AGE_DX", "YR_BRTH", "PLC_BRTH", "SEQ_NUM", "DATE_mo", "DATE_yr", "SITEO2V", "LATERAL", "HISTO2V", "BEHO2V", "HISTO3V", "BEHO3V", "GRADE", "DX_CONF", "REPT_SRC", "EOD10_SZ", "EOD10_EX", "EOD10_PE", "EOD10_ND", "EOD10_PN", "EOD10_NE", "EOD13", "EOD2", "EOD4", "EODCODE", "TUMOR_1V", "TUMOR_2V", "TUMOR_3V", "CS_SIZE", "CS_EXT", "CS_NODE", "CS_METS", "CS_SSF1", "CS_SSF2", "CS_SSF3", "CS_SSF4", "CS_SSF5", "CS_SSF6", "CS_SSF25", "D_AJCC_T", "D_AJCC_N", "D_AJCC_M", "D_AJCC_S", "D_SSG77", "D_SSG00", "D_AJCC_F", "D_SSG77F", "D_SSG00F", "CSV_ORG", "CSV_DER", "CSV_CUR", "SURGPRIM", "SCOPE", "SURGOTH", "SURGNODE", "RECONST", "NO_SURG", "RADIATN", "RAD_BRN", "RAD_SURG", "SS_SURG", "SRPRIM02", "SCOPE02", "SRGOTH02", "REC_NO", "O_SITAGE", "O_SEQCON", "O_SEQLAT", "O_SURCON", "O_SITTYP", "H_BENIGN", "O_RPTSRC", "O_DFSITE", "O_LEUKDX", "O_SITBEH", "O_EODDT", "O_SITEOD", "O_SITMOR", "TYPEFUP", "AGE_REC", "SITERWHO", "ICDOTO9V", "ICDOT10V", "ICCC3WHO", "ICCC3XWHO", "BEHANAL", "HISTREC", "BRAINREC", "CS0204SCHEMA", "RAC_RECA", "RAC_RECY", "NHIAREC", "HST_STGA", "AJCC_STG", "AJ_3SEER", "SSG77", "SSG2000", "NUMPRIMS", "FIRSTPRM", "STCOUNTY", "ICD_5DIG", "CODKM", "STAT_REC", "IHS", "HIST_SSG_2000", "AYA_RECODE", "LYMPHOMA_RECODE", "DTH_CLASS", "O_DTH_CLASS", "EXTEVAL", "NODEEVAL", "METSEVAL", "INTPRIM", "ERSTATUS", "PRSTATUS", "CSSCHEMA", "CS_SSF8", "CS_SSF10", "CS_SSF11", "CS_SSF13", "CS_SSF15", "CS_SSF16", "VASINV", "SRV_TIME_MON", "SRV_TIME_MON_FLAG", "SRV_TIME_MON_PA", "SRV_TIME_MON_FLAG_PA", "INSREC_PUB", "DAJCC7T", "DAJCC7N", "DAJCC7M", "DAJCC7STG", "ADJTM_6VALUE", "ADJNM_6VALUE", "ADJM_6VALUE", "ADJAJCCSTG")
  </code></pre>
  
  <p><strong>UPDATE:</strong>
  LaF did the entire read in just under 7 seconds from the raw .txt file.  Maybe there is an even faster way, but I doubt anything could do appreciably better.  Amazing package.</p>
  
  <p><strong>27 July 2015 Update</strong>
  Just wanted to provide a small update to this.  I used the new readr package, and I was able to read in the entire file in 5 seconds using readr::read_fwf.</p>
  
  <pre><code>seer9_readr &lt;- read_fwf("path_to_data/COLRECT.TXT",
    col_positions = fwf_widths(cols))
  </code></pre>
  
  <p>Also, the updated stringi::stri_sub function is at least twice as fast as base::substr().  So, in the code above that uses fread to read the file (about 4 seconds), followed by apply to parse each line, the extraction of 143 variables took about 8 seconds with stringi::stri_sub compared to 19 for base::substr.  So, fread plus stri_sub is still only about 12 seconds to run.  Not bad.</p>
  
  <pre><code>seer9 &lt;-  fread("path_to_data/COLRECT.TXT",     
    colClasses = "character", 
    sep = "\n", 
    header = FALSE)
  text &lt;- seer9[ , apply(start_end, 1, function(y) substr(V1, y[1], y[2]))] %&gt;% 
    data.table(.)
  </code></pre>
  
  <h1>10 Dec 2015 update:</h1>
  
  <p>Please also see the <a href="https://stackoverflow.com/questions/24715894/faster-way-to-read-fixed-width-files-in-r#34190156">answer below</a> by @MichaelChirico who has added some great benchmarks and the iotools package.</p>