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 (.*)", "\\1", 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+).*", "\\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(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+(.*)", "\\1", 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