.TXT in long form to data.frame in wide form in R

Question

I am currently working with clinical assessment data that is scored and output by a software package in a .txt file. My goal is extract the data from the txt file into a long format data frame with a column for: Participant # (which is included in the file name), subtest, Score, and T-score.

An example data file is available here: https://github.com/AlexSwiderski/CatTextToData/blob/master/Example_data

I am running into a couple road blocks that I could use some input into how navigate.

1) I only need the information that corresponds to each subtest, these all have a number prior to the subtest name. Therefore, the rows that only have one to two words that are not necessary (eg cognitive screen) seem to be interfering creating new data frames because I have a mismatch in columns provided and columns wanted.

Some additional corks to the data: 1) the asteriks are NOT necessary 2) the cognitive TOTAL will never have a value

I am utilizing the readtext package to import the data at the moment and I am able to get a data frame with two columns. One being the file name (this includes the participant name) so that problem is fixed. However, the next column is a a giant character string with the columns data points for both Score and T-Score. Presumably I would then need to split these into the columns of interest, previously listed.

Next problem, when I view the data the T scores are in the correct order, however the "score" data no longer matches the true values.

Here is what I have tried:

# install.packages("readtext")
library(readtext)
library(tidyr)

pathTofile <- path.expand("/Users/Brahma/Desktop/CAT TEXT FILES/")
data <- readtext(paste0(pathTofile2, "CAToutput.txt"),
                  #docvarsfrom = "filenames",
                  dvsep = " ")

From here I do not know how to split the data, in my head I would do something like this

data2 <- separate(data2, text, sep = " ", into = c("subtest", "score", "t_score"))

This of course, gives the correct column names but removes almost all the data I actually am interested in.

Any help would be appreciated whether a solution or a direction you might suggest I look for more answers.

Sincerely,

Alex

Answer 1

Here is a way of converting that text file to a dataframe that you can do analysis on

library(tidyverse)

input <- read_lines('c:/temp/scores.txt')

# do the match and keep only the second column
header <- as_tibble(str_match(input, "^(.*?)\\s+Score.*")[, 2, drop = FALSE])
colnames(header) <- 'title'

# add index to the list so we can match the scores that come after
header <- header %>%
  mutate(row = row_number()) %>%
  fill(title)  # copy title down

# pull off the scores on the numbered rows
scores <- str_match(input, "^([0-9]+[. ]+)(.*?)\\s+([0-9]+)\\s+([0-9*]+)$")
scores <- as_tibble(scores) %>%
  mutate(row = row_number())

# keep only rows that are numbered and delete first column
scores <- scores[!is.na(scores[,1]), -1]

# merge the header with the scores to give each section
table <- left_join(scores,
                   header,
                   by = 'row'
)
colnames(table) <- c('index', 'type', 'Score', 'T-Score', 'row', 'title')
head(table, 10)

# A tibble: 10 x 6
   index  type               Score `T-Score`   row title           
   <chr>  <chr>              <chr> <chr>     <int> <chr>           
 1 "1. "  Line Bisection     9     53            3 Subtest/Section 
 2 "2. "  Semantic Memory    8     51            4 Subtest/Section 
 3 "3. "  Word Fluency       1     56*           5 Subtest/Section 
 4 "4. "  Recognition Memory 40    59            6 Subtest/Section 
 5 "5. "  Gesture Object Use 2     68            7 Subtest/Section 
 6 "6. "  Arithmetic         5     49            8 Subtest/Section 
 7 "7. "  Spoken Words       17    45*          14 Spoken Language 
 8 "9. "  Spoken Sentences   25    53*          15 Spoken Language 
 9 "11. " Spoken Paragraphs  4     60           16 Spoken Language 
10 "8. "  Written Words      14    45*          20 Written Language

Answer 2

What is the source for the code at the link provided?

https://github.com/AlexSwiderski/CatTextToData/blob/master/Example_data

This data is odd. I was able to successfully match patterns and manipulate most of the data, but two rows refused to oblige. Rows 17 and 20 refused to be matched. In addition, the data type / data structure are very unfamiliar.

This is what was accomplished before hitting a wall.

df <- read.csv("test.txt", header = FALSE, sep = ".", skip = 1)

df1 <- df %>% mutate(V2, Extract = str_extract(df$V2, "[1-9]+\\s[1-9]+\\*+\\s?"))
df2 <- df1 %>% mutate(V2, Extract2 = str_extract(df1$V2, "[0-9]+.[0-9]+$"))

head(df2)

When the data was further explored, the second column, V2, included data types that are completely unfamiliar. These included: Arithmetic, Complex Words, Digit Strings, and Function Words.

If anything, it would good to know something about those unfamiliar data types.

Answer 3

Took another look at this problem and found where it had gotten off track. Ignore my previous post. This solution works in Jupyter Lab using the data that was provided.

library(stringr)
library(dplyr)

df <- read.csv("test.txt", header = FALSE, sep = ".", skip = 1)

df1 <- df %>% mutate(V2, "Score" =  str_extract(df$V2, "\\d+")  ) 

df2 <- df1 %>% mutate(V2, "T Score" = str_extract(df$V2, "\\d\\d\\*?$")) 

df3 <- df2 %>% mutate(V2, "Subtest/Section" = str_remove_all(df2$V2, "\\\t+[0-9]+"))

df4 <- df3 %>% mutate(V1, "Sub-S" = str_extract(df3$V1, "\\s\\d\\d\\s*"))

df5 <- df4 %>% mutate(V1, "Sub-T" = str_extract(df4$V1,"\\d\\d\\*")) 

df6 <- replace(df5, is.na(df5), "") 


df7 <- df6 %>% mutate(V1, "Description" = str_remove_all(V1, "\\d\\d\\s\\d\\d\\**$"))   # remove digits, new variable 

df7$V1 <- NULL         # remove variable 
df7$V2 <- NULL         # remove variable 

df8 <- df7[, c(6,3,1,4,2,5)]       # re-align variables
head(df8,15)