Extracting Text and Tables in Semi-Structured .txt

I have a .txt file that serves as the codebook for a large dataset that looks similar to this

==============================                                                
VAR V960922                                                                    
              NUMERIC                                                         
                                                                              
         Admin.48                                                             
                                                                              
         SUMMARY - POST MODE ASSIGNMENT AND ADMINISTRATION                    
         -----------------------------------------------------------          
                                                                              
              Post mode in this variable refers to beginning mode             
              (question Admin.47).                                            
                                                                              
        749      1.   Assigned to personal, administered as                   
                      personal IW                                             
          7      2.   Assigned to personal, administered as                   
                      telephone IW                                            
         28      3.   Assigned to telephone, administered as                  
                      personal IW                                             
        750      4.   Assigned to telephone, administered as                  
                      telephone IW                                            
                                                                              
                 0.   Inap, no Post IW                                        
                                                                              
============================== 

I would like to be able to convert this structure into a data frame to help with cleaning and labeling the dataset for use later. My ideal end result would be a table like this

| Var Name | Freqeuncies | Value Labels
| -------- | --------    | ---------------------------------------------------
| V960922  |        749  | 1. Assigned to personal, administered as personal IW
| V960922  |          7  | 2. Assigned to personal, administered as telephone IW
| V960922  |         28  | 3. Assigned to telephone, administered as personal IW
| V960922  |        750  | 4. Assigned to telephone, administered as telephone IW
| V960922  |         NA  | 0. Inap, no Post IW
     

Repeating for each of the variables included in the txt file. Each variable in the file follows a similar structure but has variations in the number of values or length of the summary for instance.

My main strategy so far has been to read in the txt file with readLines and then use str_subset to break off lines of the text that meet the criteria I need with the goal of then appending these together to create a data frame.

nes <- readLines("nes1996var.txt")
 
vars <- str_subset(nes, "^VAR", )
vars


numbers <- str_subset(nes,"\\d?\\.")
numbers

The first instance of just grabbing variable names worked okay since I ended up with a vector of all the variables like I wanted.

However, trying to pull the tables has been trickier. I've seen other threads on StackOverflow suggest to filter off of the rows that start with numbers, but in the text file there's a lot of deadspace before the numbers so I can't pull just the rows that begin with numbers because technically there aren't any.

So instead I've pulled all the rows that have any numbers at all that are then followed by a period, hoping to catch on the value labels formatting. This was better but not perfect, both because it captured a lot of rows from summaries that included years or other numbers and the fact that some of the rows in the tables actually go over and fill in the second row, meaning sometimes the necessary text got cut off.

Even after that I couldn't find a way to separate the frequency number from the value label strings since they were placed on the same row.

Is there a more efficient/effective method of achieving what I want? I'm somewhat experienced with R but I am also still learning a lot if that helps also.

Edit: The solution provided by Dave did what I needed once I made a few tweaks. Here is the code that worked for me in case anyone happens to be in a similar situation.

rl <- readLines(.txt file path)


## trim the white space from the front and back of each string 
## this will put the frequencies as the first characters in their lines. 
rl <- trimws(rl)

## find the variable delimiters
delims <- grep("==============================", rl)

## initialize the output as a list
out <- vector(mode="list", length=length(delims)-1)
    ## loop over the delimiters
for (i in 1:(length(delims) - 1)) {
  ## find the text between adjacent delimiters and call that vbl
  vbl <- rl[(delims[i] + 1):(delims[(i + 1)] - 1)]
  ## capture the varname as the stuff after "VAR " in the first row of vbl
  varname <- gsub("VAR (.*)", "\", vbl[1])
  ## identify the lines that start with a number
  resps <- grep("^\\d", vbl)
  
  if (length(resps) > 0) {
    ## identify the closest blank line to the last last response value and treat 
    ## that as the delimiter for the end of the last response category
    blanks <- which(vbl == "")
    resps <- c(resps, blanks[min(which(blanks > max(resps)))])
    ## grab the frequencies and remove the last one because the last one should be blank
    freqs <- gsub("^(\\d+).*", "\", vbl[resps])
    ## thanks to use padding out resps with the blank line after the last response category
    freqs <- freqs[-length(freqs)]
    ## for each identified response, paste together the text between the identified response row 
    ## and everything that comes before the next identifies response row.
    vlabs <- sapply(1:(length(resps) - 1), function(j) {
      paste(vbl[resps[j]:(resps[(j + 1)] - 1)], collapse = " ")
    })
    ## remove the frequencies and white space from the start of the variable labels
    ## trim the white space around variable labels as well
    vlabs <- trimws(gsub("^\\d+\\s+(.*)", "\", vlabs))
    ## collect all the information in one place
    out[[i]] <- data.frame(`Var Name` = varname, Frequencies = freqs, `Value Labels` = vlabs)
  } else {
    out[[i]] <- data.frame(`Var Name` = character(0), Frequencies = character(0), `Value Labels` = character(0))
  }
}

Here's an example. Comments through identify what each piece of code does. My assumption is that the delisting rows of equals signs separate each variable.

rl <- readLines(textConnection("==============================                                                
VAR V960922                                                                    
              NUMERIC                                                         
                                                                              
         Admin.48                                                             
                                                                              
         SUMMARY - POST MODE ASSIGNMENT AND ADMINISTRATION                    
         -----------------------------------------------------------          
                                                                              
              Post mode in this variable refers to beginning mode             
              (question Admin.47).                                            
                                                                              
        749      1.   Assigned to personal, administered as                   
                      personal IW                                             
          7      2.   Assigned to personal, administered as                   
                      telephone IW                                            
         28      3.   Assigned to telephone, administered as                  
                      personal IW                                             
        750      4.   Assigned to telephone, administered as                  
                      telephone IW                                            
                                                                              
                 0.   Inap, no Post IW                                        
                                                                              
============================== "))

## trim the white space from the front and back of each string 
## this will put the frequencies as the first characters in their lines. 
rl <- trimws(rl)

## find the variable delimiters
delims <- grep("==============================", rl)

## initialize the output as a list
out <- vector(mode="list", length=length(delims)-1)

## loop over the delimiters
for(i in 1:(length(delims)-1)){
  ## find the text between adjacent delimiters and call that vbl
  vbl <- rl[(delims[i]+1):(delims[(i+1)]-1)]
  ## capture the varname as the stuff after "VAR " in the first row of vbl
  varname <- gsub("VAR (.*)", "\\1", vbl[1])
  ## identify the lines that start with a number 
  resps <- grep("^\\d", vbl)
  ## identify the closest blank line to the last last response value and treat 
  ## that as the delimiter for the end of the last response category
  blanks <- which(vbl == "")
  resps <- c(resps, blanks[min(which(blanks > max(resps)))])
  ## grab the frequencies and remove the last one because the last one should be blank 
  freqs <- gsub("^(\\d+).*", "\\1", vbl[resps])
  ## thanks to use padding out resps with the blank line after the last response category
  freqs <- freqs[-length(freqs)]
  ## for each identified response, paste together the text between the identified response row 
  ## and everything that comes before the next identifies response row. 
  vlabs <- sapply(1:(length(resps)-1), function(i){
    paste(vbl[resps[i]:(resps[(i+1)]-1)], collapse=" ")
  })
  ## remove the frequencies and white space from the start of the variable labels
  ## trim the white space around variable labels as well
  vlabs <- trimws(gsub("^\\d+\\s+(.*)", "\\1", vlabs))
  ## collect all the information in one place
  out[[i]] <- data.frame(`Var Name` = varname, 
                    Frequencies = freqs, 
                    `Value Labels` = vlabs)  
  
}
## make all the variables into a markdown table
lapply(out, knitr::kable)
#> [[1]]
#> 
#> 
#> |Var.Name |Frequencies |Value.Labels                                             |
#> |:--------|:-----------|:--------------------------------------------------------|
#> |V960922  |749         |1.   Assigned to personal, administered as personal IW   |
#> |V960922  |7           |2.   Assigned to personal, administered as telephone IW  |
#> |V960922  |28          |3.   Assigned to telephone, administered as personal IW  |
#> |V960922  |750         |4.   Assigned to telephone, administered as telephone IW |
#> |V960922  |0           |0.   Inap, no Post IW                                    |

^{Created on 2023-06-08 with reprex v2.0.2}

If you are on a *nix system, doing the heavy lifting with awk to "tidy" the data, then use read.fwf ("f"ixed "w"idth "f"ormat). Maybe set the last width (100) to a higher value if you see truncated lines.

bash/shell

% awk '/^VAR .*[[:digit:]]/{x = $2}
$1 ~ /^[[:digit:]]+$/{printf("%s %s", x,$0); set = 1} 
$1 ~ /^[[:alpha:]]+$/ && set == 1{print $0; set = 0}  
$1 == "0."{print x,$0}' file > file_new

R

library(stringr) # for str_squish()
dat <- read.fwf("file_new", widths=c(7, 13, 100), 
col.names=c("Var Name", "Freqeuncies", "Value Labels"), check.names=F)
dat$'Value Labels' <- str_squish(dat$'Value Labels')
dat
Var Name Freqeuncies                                           Value Labels
1   V960922         749   1. Assigned to personal, administered as personal IW
2   V960922           7  2. Assigned to personal, administered as telephone IW
3   V960922          28  3. Assigned to telephone, administered as personal IW
4   V960922         750 4. Assigned to telephone, administered as telephone IW
5   V960922          NA                                    0. Inap, no Post IW
6   V360925         754   1. Assigned to personal, administered as personal IW
7   V360925          11  2. Assigned to personal, administered as telephone IW
8   V360925           6  3. Assigned to telephone, administered as personal IW
9   V360925          NA                                    0. Inap, no Post IW
10  V360925         699 4. Assigned to telephone, administered as telephone IW

Data

% cat file
==============================
VAR V960922
NUMERIC
Admin.48
SUMMARY - POST MODE ASSIGNMENT AND ADMINISTRATION
-----------------------------------------------------------
Post mode in this variable refers to beginning mode
(question Admin.47).
749      1.   Assigned to personal, administered as
personal IW
7      2.   Assigned to personal, administered as
telephone IW
28      3.   Assigned to telephone, administered as
personal IW
750      4.   Assigned to telephone, administered as
telephone IW
0.   Inap, no Post IW
==============================
VAR V360925
NUMERIC
Admin.48
SUMMARY - POST MODE ASSIGNMENT AND ADMINISTRATION
-----------------------------------------------------------
Post mode in this variable refers to beginning mode
(question Admin.47).
754      1.   Assigned to personal, administered as
personal IW
11      2.   Assigned to personal, administered as
telephone IW
6      3.   Assigned to telephone, administered as
personal IW
0.   Inap, no Post IW
699      4.   Assigned to telephone, administered as
telephone IW
==============================