R reads data with records, sometimes on two lines

Question

I need to read data from text files (many of them are very large), which usually look like this:

---

# 2013 # 3090050010 # CCOU # 01 # 022 # 1 # # N 03/03/2015 # 2013 # 3090050010 # CCOU # 01 # 023 # 1 ## 03/16/2015 # 2013 # 3090050010 # CCOU # # 005 02 # 1 # 1692528 # 03/16/2015 # 2013 # 3090430110 # CCOU # 15 # 504 # 2 # blablablablablablablablablablablablablab labla # 10/10/2014

# 2013 # 3090430110 # CCOU # 15 # 505 # 2 ## 10/01/2014

---

So, "#" is a separator, and sometimes long lines use two lines. I have a workaround when I ignore lines that don't start with "#" using grep:

x<-readLines("data.txt")
y <- grep("^#",x)
app<-x[y]
NamesForCols<-c("..",...) 
myDat<-read.table(text=app,header =F,sep="#",quote="",col.names = NamesForCols, colClasses=c("NULL", "factor", NA,NA,NA,NA,NA,"character","NULL"), fill=T,blank.lines.skip=T,comment.char = "",allowEscapes = T)

But I am not happy with this decision (there is a loss of significant data). Is there a way to read the data.txt file so that each record necessarily entails matching the "#" symbol exactly 8 times, although this sometimes involves visiting two lines? Any other suggestion would be welcome. Thank!

Answer 1

You can do the following:

text <- "#2013#3090050010#CCOU#01#022#1#N#16/03/2015 
#2013#3090050010#CCOU#01#023#1##16/03/2015 
#2013#3090050010#CCOU#02#005#1#1692528#16/03/2015 
#2013#3090430110#CCOU#15#504#2#blablablablablablablablablablablablablab 
labla#01/10/2014

#2013#3090430110#CCOU#15#505#2##01/10/2014"

Now combine the columns that do not start with #the previous one:

x <- strsplit(text, "\n")[[1]]
# starts with # or is empty
ind <- cumsum(pmax(grepl("^#",x), x==""))
x_collapsed <- vapply(split(x, ind), paste0, character(1), collapse = "")
x_collapsed <- paste(x_collapsed, collapse = "\n")

Now you can read it, for example. via:

require(readr)
read_delim(x_collapsed, delim = "#", col_names = FALSE,
       col_types = cols(X9 = col_date("%d/%m/%Y")))[, -1]

Result:

# A tibble: 5 × 8
     X2         X3    X4    X5    X6    X7                                             X8         X9
  <int>      <dbl> <chr> <chr> <chr> <int>                                          <chr>     <date>
1  2013 3090050010  CCOU    01   022     1                                              N 2015-03-16
2  2013 3090050010  CCOU    01   023     1                                           <NA> 2015-03-16
3  2013 3090050010  CCOU    02   005     1                                        1692528 2015-03-16
4  2013 3090430110  CCOU    15   504     2 blablablablablablablablablablablablablab labla 2014-10-01
5  2013 3090430110  CCOU    15   505     2                                           <NA> 2014-10-01

Answer 2

text <- readLines("data.txt")
text_string <- paste0(text, collapse="")

# assuming every line ends in a date, put back line breaks
# by matching and capturing
result <- gsub("(\\d{2}/\\d{2}/\\d{4})\\s?", "\\1\n", text_string, perl = TRUE)

# read from string
df <- read.delim(text = result, header=FALSE, sep = "#")[2:9]

df

gives

    V2         V3   V4 V5  V6 V7                                              V8         V9

1 2013 3090050010 CCOU  1  22  1                                              N 16/03/2015
2 2013 3090050010 CCOU  1  23  1                                                16/03/2015
3 2013 3090050010 CCOU  2   5  1                                        1692528 16/03/2015
4 2013 3090430110 CCOU 15 504  2 blablablablablablablablablablablablablab labla 01/10/2014
5 2013 3090430110 CCOU 15 505  2                                                01/10/2014

Answer 3

floo0 epi99, , :

text <- readLines("data.txt")
text_string <- paste0(text, collapse="")
result <- gsub("(#[^#]*#[^#]*#[^#]*#[^#]*#[^#]*#[^#]*#[^#]*#[^#]*)","\\1\n", text_string, perl = TRUE)
df <- read.delim(text = result, header=FALSE, sep = "#")[2:9]

, epi99, , "#", , .