Is there an R function that reads text files with \n as a (column) delimiter?

user_1858667 0
Viewed 1068

The Problem

I'm trying to come up with a neat/fast way to read files delimited by newline (\n) characters into more than one column.

Essentially in a given input file, multiple rows in the input file should become a single row in the output, however most file reading functions sensibly interpret the newline character as signifying a new row, and so they end up as a data frame with a single column. Here's an example:

The input files look like this:

Header Info
2021-01-01
text
...
@
2021-01-02
text
...
@
...

Where the ... represents potentially multiple rows in the input file, and the @ signifies what should really be the end of a row in the output data frame. So upon reading this file, it should become a data frame like this (ignoring the header):

X1 X2 ... Xn
2021-01-01 text ... ...
2021-01-02 text ... ...
... ... ... ...

My attempt

I've tried base, data.table, readr and vroom, and they all have one of two outputs, either a data frame with a single column, or a vector. I want to avoid a for loop, and so my current solution is using base::readLines(), to read it as a character vector, then manually adding some "proper" column separators (e.g. ;), and then joining and splitting again.

# Save the example data to use as input
writeLines(c("Header Info", "2021-01-01", "text", "@", "2021-01-02", "text", "@"), "input.txt")

input <- readLines("input.txt")
input <- paste(input[2:length(input)], collapse = ";") # Skip the header
input <- gsub(";@;*", replacement = "\n", x = input)
input <- strsplit(unlist(strsplit(input, "\n")), ";")
input <- do.call(rbind.data.frame, input)

# Clean up the example input
unlink("input.txt")

My code above works and gives the desired result, but surely there's a better way?? Edit: This is internal in a function, so part (perhaps the larger part) of the intention of any simplification is to improve the speed.

Thanks in advance!

data.table readr r
5 Answers

1) Read in the data, locate the @ signs giving logical variable at and then create a grouping variable g which has distinct values for each desired line. Finally use tapply with paste to rework it into lines that can be read using read.table and read it. (If there are commas in the data then use some other separating character.)

L <- readLines("input.txt")[-1]
at <- grepl("@", L)
g <- cumsum(at)
read.table(text = tapply(L[!at], g[!at], paste, collapse = ","), 
  sep = ",", col.names = cnames)

giving this data frame:

          V1   V2
1 2021-01-01 text
2 2021-01-02 text

2) Another approach is to rework the data into dcf form by removing the @ sign and prefacing other lines with their column name and a colon. Then use read.dcf. cnames is a character vector of column names that you want to use.

cnames <- c("Date", "Text")

L <- readLines("input.txt")[-1]
LL <- sub("@", "", paste0(c(paste0(cnames, ": "), ""), L))
DF <- as.data.frame(read.dcf(textConnection(LL)))
DF[] <- lapply(DF, type.convert, as.is = TRUE)
DF

giving this data frame:

        Date Text
1 2021-01-01 text
2 2021-01-02 text

3) This approach simply reshapes the data into a matrix and then converts it to a data frame. Note that (1) converts numeric columns to numeric whereas this one just leaves them as character.

L <- readLines("input.txt")[-1]
k <- grep("@", L)[1]
as.data.frame(matrix(L, ncol = k, byrow = TRUE))[, -k]
##           V1   V2
## 1 2021-01-01 text
## 2 2021-01-02 text

Benchmark

The question did not mention speed as a consideration but in a comment it was later mentioned. Based on the data in the benchmark below (1) runs over twice as fast as the code in the question and (3) runs nearly 25x faster.

library(microbenchmark)

writeLines(c("Header Info", 
   rep(c("2021-01-01", "text", "@", "2021-01-02", "text", "@"), 10000)), 
   "input.txt")

library(microbenchmark)
writeLines(c("Header Info", rep(c("2021-01-01", "text", "@", "2021-01-02", "text", "@"), 10000)), "input.txt")

microbenchmark(times = 10,
ques = {
  input <- readLines("input.txt")
  input <- paste(input[2:length(input)], collapse = ";") # Skip the header
  input <- gsub(";@;*", replacement = "\n", x = input)
  input <- strsplit(unlist(strsplit(input, "\n")), ";")
  input <- do.call(rbind.data.frame, input)
},
ans1 = {
  L <- readLines("input.txt")[-1]
  at <- grepl("@", L)
  g <- cumsum(at)
  read.table(text = tapply(L[!at], g[!at], paste, collapse = ","), sep = ",")
},
ans3 = {
  L <- readLines("input.txt")[-1]
  k <- grep("@", L)[1]
  as.data.frame(matrix(L, ncol = k, byrow = TRUE))[, -k]
})
## Unit: milliseconds
##  expr     min      lq    mean  median      uq     max neval cld
##  ques 1146.62 1179.65 1188.74 1194.78 1200.11 1219.01    10   c
##  ans1  518.95  522.75  548.33  532.59  561.55  647.14    10  b 
##  ans3   50.47   51.19   51.68   51.69   52.25   52.52    10 a
user_5165480

You can get round some of the string manipulation with something along the lines of:

input <- readLines("input.txt")[-1] #Read in and remove header
ncol <- which(input=="@")[1]-1  #Number of columns of data
data.frame(matrix(input[input != "@"], ncol = ncol, byrow=TRUE)) #Convert to dataframe 
#          X1   X2
#1 2021-01-01 text
#2 2021-01-02 text
user_33796750

At this point, you might consider going the full mile and use a proper grammar to parse it. I don't know how big or complex the situation really is, but using pegr it might look something like this:


input <-
"Header Info
2021-01-01
text
multiple lines
of
text
@
2021-01-02
text
more
lines of text
@
"

library(pegr)
peg <- new.parser(commonRules,action=TRUE) +
    c("HEADER   <- 'Header Info' EOL" , "{}"  ) + # Rule to match literal 'Header Info' and a \n, then discard
    c("TYPE     <- 'text' EOL"        , "{-}" ) + # Rule to match literal 'text', store paste and store as $TYPE
    c("DATE     <- (!EOL .)* EOL"     , "{-}" ) + # Rule to match any character leading up to a new line. Could improve to look for a date format
    c("EOS      <- '@' EOL"           , "{}"  ) + # Rule to match end of section, then discard
    c("BODY     <- (!EOS .)*"         , "{-}" ) + # Rule to match body of text, including newlines
    c("SECTION  <- DATE TYPE BODY EOS"        ) + # Combining rules to match each section
    c("DOCUMENT <- HEADER SECTION*"           )   # Combining more rules to match the endire document

res <- peg[["DOCUMENT"]](input))

final <- matrix( value(res), ncol=3, byrow=TRUE ) %>%
    as.data.frame %>%
    setnames( names(value(res))[1:3])

final

Produces:

         DATE TYPE                       BODY
 1 2021-01-01 text multiple lines\nof\ntext\n
 2 2021-01-02 text      more\nlines of text\n

It might feel clunky if you don't know the syntax, but once you do, its a fire and forget solution. It'll run according to spec until the spec doesn't hold. You don't have to worry about fragile pretreatment and it is easy to adapt to changing formats in the future.

user_21659930

There's also the tidyverse way:

library(tidyr)
library(readr)
library(stringr)

max_columns <- 5

d <- {
  readr::read_file("file.txt") %>% 
  stringr::str_remove("^Header Info\n") %>% 
  tibble::enframe(name = NULL) %>% 
  separate_rows(value, sep = "@\n")  %>% 
  separate("value", into = paste0("X", 1:max_columns) , sep = "\n") 
}

Using your example input in a file called file.txt, the d looks like:

# A tibble: 3 x 5
  X1         X2    X3    X4    X5   
  <chr>      <chr> <chr> <chr> <chr>
1 2021-01-01 text  ...   ""    NA   
2 2021-01-02 text  ...   ""    NA   
3 ...        NA    NA     NA   NA   
Warning message:
Expected 5 pieces. Missing pieces filled with `NA` in 3 rows [1, 2, 3].

Note that the warning is simply to make sure you know you're getting NA's, this is inevitable if number of rows between @ varies

user_47208900

I am using the data similar to that provided by Sirius for demonstration. you can also do something like this to have variable number of columns in resulting data frame

example <- "Header Info
2021-01-01
text
multiple lines
of
text
@
2021-01-02
text
more
lines of text
@"
library(tidyverse)

example %>% as.data.frame() %>% setNames('dummy') %>%
  separate_rows(dummy, sep = '\\n') %>%
  filter(row_number() !=1) %>%
  group_by(rowid = rev(cumsum(rev(dummy == '@')))) %>%
  filter(dummy != '@') %>%
  mutate(name = paste0('X', row_number())) %>%
  pivot_wider(id_cols = rowid, names_from = name, values_from = dummy)
#> # A tibble: 2 x 6
#> # Groups:   rowid [2]
#>   rowid X1         X2    X3             X4            X5   
#>   <int> <chr>      <chr> <chr>          <chr>         <chr>
#> 1     2 2021-01-01 text  multiple lines of            text 
#> 2     1 2021-01-02 text  more           lines of text <NA>

Created on 2021-05-30 by the reprex package (v2.0.0)

user_28848590
Related