Reading text file with multiple space as delimiter in R

Question

I have big data set which consist of around 94 columns and 3 Million rows. This file have single as well as multiple spaces as delimiter between columns. I need to read some columns from this file in R. For this I tried using read.table() with options which can be seen in the code below, the code is pasted below-

### Defining the columns to be read from the file, the first 5 column, then we do not read next 24, after this we read next 5 columns. Last 60 columns are not read in-

    col_classes = c(rep("character",2), rep("numeric", 3), rep("NULL",24), rep("numeric", 5), rep("NULL", 60))   

### Reading first 100 rows of the data

    data <- read.table(file, sep = " ",header = F, nrows = 100, na.strings ="", stringsAsFactors= F)

Since, the file which has to read in have more than one space as the delimiter between some of the column, the above method does not work. Is there any method using which we can read in this file efficiently.

Decompensation

Just remove the sep=" " argument. read.table by default knows how to handle multiple spaces. – Loading

I have a very similar problem, but I need a more general solution since I have single spaces within some fields. Meaning that I should be able to set the minimum number of consecutive spaces (in my case 2) to be considered as a separator, with no limit for it. – Personalize

Related post: #30955964 – Rippy

@HongOoi: yeah but only because read.table/read.csv's default is sep="", which means "multiple whitespace", which we might expect should be a regex "\w*" or "\w+" not "". – Hankhanke

Answer 1

You need to change your delimiter. " " refers to one whitespace character. "" refers to any length whitespace as being the delimiter

 data <- read.table(file, sep = "" , header = F , nrows = 100,
                     na.strings ="", stringsAsFactors= F)

From the manual:

If sep = "" (the default for read.table) the separator is ‘white space’, that is one or more spaces, tabs, newlines or carriage returns.

Also, with a large datafile you may want to consider data.table:::fread to quickly read data straight into a data.table. I was myself using this function this morning. It is still experimental, but I find it works very well indeed.

Diva

how does 'fread' handles multiple spaces?? This was the first read function which I tried using but for me it failed due to multiple spaces, any work around on this?? – Decompensation

@user2412678 Did you try fread(... , sep = "" ) or alternatively you could try fread( ... , sep = "\s" ) but I don't know if that will work. Could you try both and report back, then we can update the answer for fread if one of them works. – Commerce

fread( ...,sep ="") does not work in fread, we get the following error when we use this - Error in fread(file, sep = "", : 'sep' must be 'auto' or a single character fread(....,sep = "\s") does not work in fread, we get following error in this case Error: '\s' is an unrecognized escape in character string starting ""\s" However, fread(...,sep = " " ) work, but this does not solves the problem of multiple space as delimiter, it treats multiple space as column – Decompensation

Answer 2

If you want to use the tidyverse (or readr respectively) package instead, you can use read_table instead.

read_table(file, col_names = TRUE, col_types = NULL,
  locale = default_locale(), na = "NA", skip = 0, n_max = Inf,
  guess_max = min(n_max, 1000), progress = show_progress(), comment = "")

And see here in the description:

read_table() and read_table2() are designed to read the type of textual data where
each column is #' separate by one (or more) columns of space.

Inefficacious

Answer 3

If you field have a fixed width, you should consider using read.fwf() which might handle missing values better.

Scanties