CODEWITHSUNDEEP
rdataframetextdata-ingestion
ulfelder
ulfelder

Reputation: 5335

How can I read a table in a loosely structured text file into a data frame in R?

Take a look at the "Estimated Global Trend daily values" file on this NOAA web page. It is a .txt file with something like 50 header lines (identified with leading #s) followed by several thousand lines of tabular data. The link to download the file is embedded in the code below.

How can I read this file so that I end up with a data frame (or tibble) with the appropriate column names and data?

All the text-to-data functions I know get stymied by those header lines. Here's what I just tried, riffing off of this SO Q&A. My thought was to read the file into a list of lines, then drop the lines that start with # from the list, then do.call(rbind, ...) the rest. The downloading part at the top works fine, but when I run the function, I'm getting back an empty list.

temp <- paste0(tempfile(), ".txt")
download.file("ftp://aftp.cmdl.noaa.gov/products/trends/co2/co2_trend_gl.txt",
              destfile = temp, mode = "wb")

processFile = function(filepath) {
  dat_list <- list()
  con = file(filepath, "r")
  while ( TRUE ) {
    line = readLines(con, n = 1)
    if ( length(line) == 0 ) {
      break
    }
    append(dat_list, line)
  }

  close(con)

  return(dat_list)

}

dat_list <- processFile(temp)

Upvotes: 3

Views: 337

Answers (2)

camille
camille

Reputation: 16881

Here are a few options that bypass your function and that you can mix & match.

In the easiest (albeit unlikely) scenario where you know the column names already, you can use read.table and enter the column names manually. The default option of comment.char = "#" means those comment lines will be omitted.

read.table(temp, col.names = c("year", "month", "day", "cycle", "trend"))

More likely is that you don't know those column names, but can get them by figuring out how many comment lines there are, then reading just the last of those lines. That saves you having to read more of the file than you need; this is a small enough file that it shouldn't make a huge difference, but in a larger file it might. I'm doing the counting by accessing the command line, only because that's the way I know how. Note also that I saved the file at an easier path; you could instead paste the command together with the temp variable.

Again, the comments are omitted by default.

n_comments <- as.numeric(system("grep '^# ' co2.txt | wc -l", intern = TRUE))
hdrs <- scan(temp, skip = n_comments - 1, nlines = 1, what = "character")[-1]
read.table(temp, col.names = hdrs)

Or with dplyr and stringr, read all the lines, separate out the comments to extract column names, then filter to remove the comment lines and separate into fields, assigning the column names you've just pulled out. Again, with a bigger file, this could become burdensome.

library(dplyr)

lines <- data.frame(text = readLines(temp), stringsAsFactors = FALSE)
comments <- lines %>%
  filter(stringr::str_detect(text, "^#"))

hdrs <- strsplit(comments[nrow(comments), 1], "\\s+")[[1]][-1]

lines %>%
  filter(!stringr::str_detect(text, "^#")) %>%
  mutate(text = trimws(text)) %>%
  tidyr::separate(text, into = hdrs, sep = "\\s+") %>%
  mutate_all(as.numeric)

Upvotes: 2

MrFlick
MrFlick

Reputation: 206586

Here's a possible alternative

processFile = function(filepath, header=TRUE, ...) {

  lines <- readLines(filepath)
  comments <- which(grepl("^#", lines))
  header_row <- gsub("^#","",lines[tail(comments,1)])
  data <- read.table(text=c(header_row, lines[-comments]), header=header, ...)

  return(data)

}

processFile(temp)

The idea is that we read in all the lines, find the ones that start with "#" and ignore them except for the last one which will be used as the header. We remove the "#" from the header (otherwise it's usually treated as a comment) and then pass it off to read.table to parse the data.

Upvotes: 2

Related Questions