I need to write some code to process a tab delimited text files that has defined segments with variable lengths in R.

The table included in each segment should start 1 line below "Group:" and stop either 2 lines above "~End" if the group is a control or 6 lines above "~End" if if the group is a standard. The lengths of the tables themselves will be variable and can be empty like the segment "SpikedControl".

and example file looks like this:

Group: Controls 1
Sample Wells Values MeanValue CV Od-bkgd-blank
Anti-Hu Det A11 2.849 2.855 0.282 2.853
 A23 2.860 
Coat Control A12 0.161 0.160 0.530 0.159
 A24 0.160 
Diluent Standard 1 A9 0.114 0.113 1.379 0.104
 A21 0.112 
Diluent Standard 2 A8 0.012 0.013 2.817 0.012
 A20 0.013 

~End
Group: SpikedControl 1
Sample Wells Concentration Values MeanValue CV ODbkgdblank Conc %Expected

~End
Group: Standards 1
Sample ExpConc Wells OD CV OD ODblank MeanODBlank Result %Recovery
St001 2000.000 B1 2.939 1.4 2.932 2.904 Range? Range?
 B13 2.882 2.875 1153.779 57.689
St002 666.667 B2 2.820 0.9 2.812 2.829 456.435 68.465
 B14 2.855 2.847 670.358 100.554
St003 222.222 B3 2.677 0.9 2.669 2.686 208.849 93.982
 B15 2.709 2.702 237.852 107.033
St004 74.074 B4 2.215 1.4 2.205 2.226 72.185 97.449
 B16 2.258 2.248 77.452 104.560
St005 24.691 B5 1.406 1.3 1.397 1.410 24.296 98.397
 B17 1.433 1.424 25.153 101.868
St006 8.230 B6 0.669 2.9 0.658 0.672 7.781 94.536
 B18 0.697 0.686 8.240 100.115
St007 2.743 B7 0.357 5.8 0.348 0.334 3.143 114.579
 B19 0.329 0.320 2.759 100.579
St008 0.914 B8 0.198 3.7 0.191 0.186 1.029 112.551
 B20 0.188 0.181 0.895 97.891
St009 0.305 B9 0.163 7.8 0.154 0.146 0.532 174.477
 B21 0.146 0.137 0.296 97.190
St010 0.102 B10 0.130 5.1 0.123 0.119 0.096 94.087
 B22 0.121 0.114 Range? Range?
St011 0.034 B11 0.133 4.7 0.126 0.122 0.134 394.778
 B23 0.125 0.117 Range? Range?
St012 0.011 B12 0.117 0.7 0.105 0.104 Range? Range?
 B24 0.115 0.104 Range? Range?

EC50 = 28.085

AUC = 5565.432

~End

I am not very experienced with processing text files like this, and am looking on some advice on how to approach identifying these segments and reading the tables within.

Thanks!

Edit - Link to example file:

https://www.dropbox.com/s/4m0lmbbequmpd9b/ExampleFile.txt?dl=0

PS: these files are spit out from a spectrophotometer so I don't have any control over the format as the software is pretty antiquated.

Edit 2 - Making some progress:

Read in file and get start and end lines for each segment

inputtext <- readLines("ExampleFile.txt")

starts <- grep("Group:", inputtext)
ends <- grep("~End", inputtext)

which I can follow up with

test2 <- read.table("ExampleFile.txt", header = T, sep = "t", skip = 17, nrows = 24, blank.lines.skip = F)

now I am just trying to figure out how to accurately identify the number of rows to read.

So if the start row is 17, and the end row is 48. Then nrows needs to 24 which is 48 (end row indicated) - 17 (the first rows that are skipped) - 7 (to account for the header line and lines of fluff on the end of the table, which could also be 4 if it is a control table)

Now I just need to figure out how to loop this and properly identify whether the group is a control or standard to subtract the right amount of fluff.

I ended up doing the following:

library(tidyverse)

inputtext <- readLines("Test.txt")

starts <- grep("Group:", inputtext)
ends <- grep("~End", inputtext)

realend <- (ends - starts) - 2

dff <- list()

for (i in 1:4) 

 dff[[i]] <- read.table("Test.txt",
 header = T,
 sep = "t",
 skip = starts[i],
 nrows = realend[i],
 blank.lines.skip = F,
 row.names = NULL)



dff <- lapply(dff, function(x) x[!is.na(x$Values),])

dff <- dff[sapply(dff, function(x) dim(x)[1]) > 0]

names(dff) <- letters[1:length(dff)]

list2env(dff,.GlobalEnv)

You can use a nested data frame for that purpose. I detailed the method in 3 steps below.

library(tidyverse)
inputtext <- readLines("~/downloads/ExampleFile.txt")

1. Read data in a single data frame, create a column with the group name

dtf1 <- data_frame(input = inputtext) %>% 
 separate(input, c("x1", "x2", "x3", "x4", "x5", "x6", "x7", "x8", "x9"), sep = "t") %>% 
 mutate(group = ifelse(grepl("Group:", x1), x2, NA)) %>% 
 fill(group) %>% 
 filter(!is.na(x4)) 
head(dtf1)

2. Nest the data frame

dtf2 <- dtf1 %>% 
 group_by(group) %>% 
 nest() 
dtf2$data[[1]]

Give column names from the data to the first nested data frame

colnames1 <- dtf2$data[[1]] %>% slice(1) %>% unlist()
colnames1[is.na(colnames1)] <- names(colnames1[is.na(colnames1)])
colnames(dtf2$data[[1]]) <- colnames1

3. Give column names from the data to each sub data frame

 dtf3 <- dtf2 %>% 
 mutate(names = map(data, slice, 1),
 names = map(names, unlist),
 names = map(names,
 function(x) # Replace NA column names by the default x_ names
 x[is.na(x)] <- names(x[is.na(x)])
 return(x)
 ),
 data = map2(data, names, setNames), 
 data = map(data, slice, -1)) 

You now have a list of data frames. You can use the group name to call the corresponding data frame:

 > dtf3$data[dtf3$group=="Controls"][[1]]
# A tibble: 8 x 9
 Sample Wells Values MeanValue CV `Od-bkgd-blank` x7 x8 x9 
 <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 Anti-Hu Det A11 2.849 2.855 0.282 2.853 NA NA NA 
2 "" A23 2.860 "" "" "" NA NA NA 
3 Coat Control A12 0.161 0.160 0.530 0.159 NA NA NA 
4 "" A24 0.160 "" "" "" NA NA NA 
5 Diluent Standard 1 A9 0.114 0.113 1.379 0.104 NA NA NA 
6 "" A21 0.112 "" "" "" NA NA NA 
7 Diluent Standard 2 A8 0.012 0.013 2.817 0.012 NA NA NA 
8 "" A20 0.013 "" "" "" NA NA NA 
> dtf3$data[dtf3$group=="Standards"][[1]]
# A tibble: 24 x 9
 Sample ExpConc Wells OD `CV OD` ODblank MeanODBlank Result `%Recovery`
 <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> 
 1 St001 2000.000 B1 2.939 1.4 2.932 2.904 Range? Range? 
 2 "" "" B13 2.882 "" 2.875 "" 1153.779 57.689 
 3 St002 666.667 B2 2.820 0.9 2.812 2.829 456.435 68.465 
 4 "" "" B14 2.855 "" 2.847 "" 670.358 100.554 
 5 St003 222.222 B3 2.677 0.9 2.669 2.686 208.849 93.982 
 6 "" "" B15 2.709 "" 2.702 "" 237.852 107.033 
 7 St004 74.074 B4 2.215 1.4 2.205 2.226 72.185 97.449 
 8 "" "" B16 2.258 "" 2.248 "" 77.452 104.560 
 9 St005 24.691 B5 1.406 1.3 1.397 1.410 24.296 98.397 
10 "" "" B17 1.433 "" 1.424 "" 25.153 101.868 
# ... with 14 more rows
> 

Note: rename based on this answer.

Placed source data as a gist in case it gets lost:
spectrophotometer.txt