This document will go over the many, many ways data can be stored and how to read them into R. This will be a living document, growing as time goes on to explore topics in more depth, add new methods of reading in data, and add new data formats that can be read in.

Text: Free-form

Free-form text files are those that don’t have a systematic structure. They may share a structure if the same process or software generates them, but there is rarely similarities between processes.

Often, these are meant for visual presentation of data, such as statistical results or listings, rather than data storage, but occasionally these contain important information that needs to be parsed out.

There is not much advice I can give here that applies to every free-form text file because each file will be different.

There may be some standardization if particular software is writing the files (e.g., modeling software writing standard outputs), in which case it would be best to write a custom function, or find one online if it exists, to parse those files.

Start with one of the functions below:

readLines()

#or

readr::read_lines()

I usually just use readLines() because it reduces the need for a dependency (the {readr} package), but read_lines() does have some additional functionality such as the ability to skip a certain number of rows.

One example is parsing information from a text file that is the output of some statistical software. Here, I’ve just run a quick ANOVA with some assumption tests and written the output to a text file.

lines <- readLines("./data_storage_formats/mtcars_test_results.txt")

cat(lines,sep = "\n")

Statistical Test Results - mtcars dataset
Response: mpg | Grouping factor: gear

=== SHAPIRO-WILK NORMALITY TEST (mpg) ===

    Shapiro-Wilk normality test

data:  mtcars$mpg
W = 0.94756, p-value = 0.1229

=== BARTLETT TEST OF HOMOGENEITY OF VARIANCES ===

    Bartlett test of homogeneity of variances

data:  mpg by gear
Bartlett's K-squared = 3.8253, df = 2, p-value = 0.1477

=== ONE-WAY ANOVA ===
            Df Sum Sq Mean Sq F value   Pr(>F)    
gear         2  483.2  241.62    10.9 0.000295 ***
Residuals   29  642.8   22.17                     
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

=== END OF RESULTS ===

Now, let’s say I just want the F statistic and p-value from the ANOVA. I need a way to extract just that information.

Here is how I would tackle this, assuming a somewhat standard output from whatever created this file.

I’d start by identifying the line number that contains the section header for the ANOVA. From there, I know the F statistic and p-value will be two lines below it.

line_num_aov <- which(grepl(pattern = "ANOVA", lines)) 
line_num_targ <- line_num_aov + 2

line_num_targ

[1] 20

Now I need a way to extract just the components from that line. There is nothing within that line to tell me where the values I want are. For example, the Bartlett’s K-squared value follows a label that identifies it and precedes a comma. I could use those as clear rules for extracting that value. We don’t have that for the ANOVA’s F statistic and p-value.

Instead, I think I’m going to have to base it on pieces of the line. If we split that line into parts based on the spaces between values, and the structure is always the same, I know the F statistic and p-value are the 5th and 6th values [1 = variable label, 2 = Df, 3 = Sum Sq, 4 = Mean Sq, 5 = F value, 6 = Pr(>F), 7 = Signif code].

So I’ll split on spaces and extract the parts I need.

line_split <- strsplit(lines[line_num_targ], split = "\\s+")[[1]]
f_val <- as.numeric(line_split[5])
p_val <- as.numeric(line_split[6])

f_val

[1] 10.9

p_val

[1] 0.000295

There you have it: the F statistic and p-value from the ANOVA.

Two special cases of free-form text files are loggers and listings, depending on how they have been set up.

Loggers

Some loggers output free-form text, but others can be fixed-width or even delimited. For the free-form versions, again, you are going to need a custom parser. I do find these easier than listings because they usually don’t wrap records onto multiple lines as there are no width restrictions like those in listings.

I have some water quality logger data for a project I worked on from which I’ve removed any identifying information (although the data is out in the public). This will be a pared down version without some of the checks and processing steps that may not be needed in your logger data.

library(stringr)
lines <- readLines("./data_storage_formats/ex_free_form_text.txt")

cat(head(lines, n = 20), sep = "\n")
cat("...\n")
cat(tail(lines, n = 20), sep = "\n")

Some intro text

A key to quality codes for this data set appears at the end of this file.

Site information

DATE         TIME    STAGE (ft.)   QUALITY
----------   -----   -----------   -------
10/01/2001   00:00            ""       255
10/02/2001   00:00            ""       255
10/03/2001   00:00            ""       255
10/04/2001   00:00            ""       255
10/05/2001   00:00            ""       255
10/06/2001   00:00            ""       255
10/07/2001   00:00            ""       255
10/08/2001   00:00            ""       255
10/09/2001   00:00            ""       255
10/10/2001   00:00            ""       255
10/11/2001   00:00            ""       255
10/12/2001   00:00            ""       255
...
09/16/2002   00:00        -23.08        77
09/17/2002   00:00        -23.01        77
09/18/2002   00:00        -22.91        77
09/19/2002   00:00        -22.93         1
09/20/2002   00:00        -22.97         1
09/21/2002   00:00        -22.96         1
09/22/2002   00:00        -22.97         1
09/23/2002   00:00        -22.99         1
09/24/2002   00:00        -22.99         1
09/25/2002   00:00        -22.99         1
09/26/2002   00:00        -22.98         1
09/27/2002   00:00        -22.98         1
09/28/2002   00:00        -22.97         1
09/29/2002   00:00        -22.97         1
09/30/2002   00:00        -22.93         1

Quality Codes Key:
1 - Good quality REVIEWED data
77 - Estimated from another station, same variable
255 - No Data

The first step I took was to identify the different parts in the top section using the line numbers.

date_pattern <- "\\b\\d{2}/\\d{2}/\\d{4}\\b"
   
# find the first row with the date as this is the first row with data 
header <- which(grepl(date_pattern, lines))[1] - 2
# get the variables
headers <- toupper(stringr::str_split(lines[header], "\\s{2,}")[[1]])

# get the dashed line that separates the variable name from the data
dash_line <- lines[header + 1]

# Find the start and end of each series of dashes
# These define the edges of the columns
matches <- stringr::str_match_all(dash_line, "(-+)")
dash_spans <- matches[[1]]
# Use gregexpr to get start positions of each dash group
starts <- unlist(gregexpr("-+", dash_line))
breaks <- c(starts[-1]-1, nchar(dash_line))

Next, I grabbed the quality codes at the end of the file.

# get first line of quality section
quality_start <- which(stringr::str_detect(lines, "Quality Codes Key:")) - 1

# get all rows that had a number, dash, then something following
quality_lines <- which(stringr::str_detect(lines, "\\d+\\s-\\s.*"))
quality_lines <- quality_lines[quality_lines > quality_start]
quality_lines <- lines[quality_lines]

# split into two columns based on the dash
# and make into a data frame
quality_lines <- stringr::str_split(quality_lines, 
                                    pattern = "\\s-\\s", 
                                    n = 2, 
                                    simplify = TRUE)
quality_lines <- as.data.frame(quality_lines)
colnames(quality_lines) <- c("QUALITY", "QUALITY_KEY")

Next, I reduced the text to just the lines containing the data and split them based on the positions identified in the previous step.

lines <- lines[grepl(date_pattern, lines)]
lines <- lines[!grepl("---{4,}", lines)]

df <- purrr::map(lines, function(line){
        
        x <- purrr::map2(starts, breaks, ~ str_trim(str_sub(line, .x, .y))) %>% 
          as.data.frame()
        colnames(x) <- headers
        return(x)
        
      }) %>% 
        dplyr::bind_rows()

rownames(df) <- NULL

Then I added the quality codes to the dataset and cleaned it up.

df <- dplyr::left_join(df, quality_lines, by = "QUALITY") %>% 
   dplyr::mutate(dplyr::across(dplyr::where(is.character), ~stringr::str_replace(.x, "\"\"", ""))) %>% 
  dplyr::mutate(QUALITY = as.numeric(QUALITY))

This gets us the final dataset:

        DATE  TIME STAGE (FT.) QUALITY QUALITY_KEY
1 10/01/2001 00:00                 255     No Data
2 10/02/2001 00:00                 255     No Data
3 10/03/2001 00:00                 255     No Data
4 10/04/2001 00:00                 255     No Data
5 10/05/2001 00:00                 255     No Data
6 10/06/2001 00:00                 255     No Data

...

          DATE  TIME STAGE (FT.) QUALITY                QUALITY_KEY
360 09/25/2002 00:00      -22.99       1 Good quality REVIEWED data
361 09/26/2002 00:00      -22.98       1 Good quality REVIEWED data
362 09/27/2002 00:00      -22.98       1 Good quality REVIEWED data
363 09/28/2002 00:00      -22.97       1 Good quality REVIEWED data
364 09/29/2002 00:00      -22.97       1 Good quality REVIEWED data
365 09/30/2002 00:00      -22.93       1 Good quality REVIEWED data

Listings

Listings are a whole different discussion. I really do not understand how, in the 21st century, the best way to communicate record- or even subject-level data is to print those values in a report or document. I don’t believe anyone is actually looking through each value. Providing the actual data in a format that could be actually explored (yes, even Excel, which has filters) would be better. If it were just printing the data, I would not have such a problem with it, but on occasion, I’ve been told a listing was the only format in which someone had the data…

Here are several reasons I dislike listings as data:

Data is too large to print in one document, with too many columns and rows. The data has to be split into panels. These panels have to be parsed out and recombined in both directions.
Sometimes, longer text within a “cell” is wrapped so it is technically in multiple rows.
Columns can vary in width, so parsing based on fixed width is not possible.
Generally, there are footnotes or counts on every page which has to be ignored and breaks up the tabular structure.
Values in columns are often grouped, similar to merged cells in Excel rather than on every row they apply to.
Since a listing is usually visual, decoded values or placeholders are included rather than the actual values in the data. This is not usually a huge issue, but can cause problems if it mixed data types.
Formatting is never consistent. Every tool that produces a listing produces a different structure. This means there is no one way that can be used to parse out the data.

Honestly, R is not my first attempt at parsing these into something usable. If it is a PDF, the export to Excel works half decently and you can then manually deal with all of the other issues. For Word, copy into Excel and, again, deal with it there. Since every listing you get will have a different structure, I’m not sure it is worth the effort to code up a parsing solution.

Text: Self-describing

JSON

YAML

XML

HTML

Text: Delimited

CSV

TSV

Other separated text files

Text: Layout-dependent

Fixed-width

Spreadsheets

Excel (.xlsx, .xls)

.ods

Google Sheets

R-native

RDS

RData

QS/QMS

Statistical software

SAS7BDAT/SAS7BCAT

XPT

SPSS

Stata

Documents

Word

RTF

PDF (text-based or scanned)

Images and Videos

Photos

Video frames

EXIF metadata

Databases

DuckDB

Other DBs

SQLite

MS Access

Cloud warehouses/lakes

Databricks

Snowflake

Spatial

Raster

NetCDF

Shapefiles

GeoPackage

GeoJSON

KML

Binary/columnar

Parquet

Feather/Arrow

HDF5

APIs

Raw API pulls

R packages that hit APIs and format

Other languages’ formats

MATLAB

Python pickle/NumPy

Domain specific

There are so many formats that are used only in specific domains and are less common.

These include signal/audio data, DICOM, genomics formats.