Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Most efficient way to read key value pairs where values span multiple lines?

Tags:

dataframe

r

What is the fastest way to parse a text file such as the example below into a two column data.frame which then then be transformed into a wide format?

FN Thomson Reuters Web of Science™
VR 1.0
PT J
AU Panseri, Sara
   Chiesa, Luca Maria
   Brizzolari, Andrea
   Santaniello, Enzo
   Passero, Elena
   Biondi, Pier Antonio
TI Improved determination of malonaldehyde by high-performance liquid
   chromatography with UV detection as 2,3-diaminonaphthalene derivative
SO JOURNAL OF CHROMATOGRAPHY B-ANALYTICAL TECHNOLOGIES IN THE BIOMEDICAL
   AND LIFE SCIENCES
VL 976
BP 91
EP 95
DI 10.1016/j.jchromb.2014.11.017
PD JAN 22 2015
PY 2015

Using readLines is problematic because the multi-line fields don't have the keys. Reading as fixed width table also doesn't work. Suggestions? If not for the multiline issue, this would be easily accomplished with a function that operates on each row/record like so:

x <- "FN Thomson Reuters Web of Science"
re <- "^([^\\s]+)\\s*(.*)$"
key <- sub(re, "\\1", x, perl=TRUE)
value <- sub(re, "\\2", x, perl=TRUE)
data.frame(key, value)
key                          value
1  FN Thomson Reuters Web of Science

Notes: The fields will always be uppercase and two characters. The entire title and list of authors can be concatenated into a single cell.

like image 688
Maiasaura Avatar asked Sep 05 '26 15:09

Maiasaura


2 Answers

This should work:

library(zoo)

x <- read.fwf(file="tempSO.txt",widths=c(2,500),as.is=TRUE)

x$V1[x$V1=="  "] <- NA
x$V1 <- na.locf(x$V1)

res <- aggregate(V2 ~ V1, data = x, FUN = paste, collapse = "")
like image 138
zx8754 Avatar answered Sep 08 '26 05:09

zx8754


Here's another idea, that might be useful if you want to stay in base R:

parseEntry <- function(entry) {
    ## Split at beginning of each line that starts with a non-space character    
    ll <- strsplit(entry, "\\n(?=\\S)", perl=TRUE)[[1]]
    ## Clean up empty characters at beginning of continuation lines
    ll <- gsub("\\n(\\s){3}", "", ll)
    ## Split each field into its two components
    read.fwf(textConnection(ll), c(2, max(nchar(ll))))
}

## Read in and collapse entry into one long character string.
## (If file contained more than one entry, you could preprocess it accordingly.)
ee <- paste(readLines("egFile.txt"), collapse="\n")
## Parse the entry
parseEntry(ee)
like image 27
Josh O'Brien Avatar answered Sep 08 '26 05:09

Josh O'Brien



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!