Read the data efficiently with multiple separating lines in R

r

Solution

Its not clear what multiple separators refers to but here is a solution that addresses the data you actually showed.

Read in the data using using `fill=TRUE` to fill in empty fields. Keep track of which rows are headers using `is.hdr`. Convert `V2` to numeric (replacing `V2` with `NA` in the header rows so they do not generate a warning). Then replace non-header rows with NAs in the next two columns and use `na.locf` (link) to fill in the NAs with the headers. Finally, only keep non-header rows.

library(zoo)
DF <- read.table("jdtrial.txt", fill = TRUE, as.is = TRUE)

is.hdr <- DF$V3 != ""
transform(DF, 
    V2 = as.numeric(replace(V2, is.hdr, NA)),
    V3 = na.locf(ifelse(is.hdr, V2, NA)),
    name = na.locf(ifelse(is.hdr, V3, NA)))[!is.hdr, ]

The result of the last statement is:

         V1       V2       V3      name
2  250.0417 17.49966 02-Model (Minimum)
3  250.0833 17.50000 02-Model (Minimum)
4  250.1250 17.50089 02-Model (Minimum)
5  250.1667 17.50117 02-Model (Minimum)
6  250.2083 17.50138 02-Model (Minimum)
7  250.2500 17.50214 02-Model (Minimum)
8  250.2917 17.50256 02-Model (Minimum)
9  250.3333 17.50168 02-Model (Minimum)
11 250.0417 17.50206       03 (Maximum)
12 250.0833 17.50115       03 (Maximum)
13 250.1250 17.50113       03 (Maximum)
14 250.1667 17.50124       03 (Maximum)
15 250.2083 17.50160       03 (Maximum)
16 250.2500 17.50247       03 (Maximum)
17 250.2917 17.50432       03 (Maximum)
19 250.0417 17.50206 04-Model (Maximum)
20 250.0833 17.50115 04-Model (Maximum)
21 250.1250 17.50113 04-Model (Maximum)
22 250.1667 17.50124 04-Model (Maximum)
23 250.2083 17.50160 04-Model (Maximum)
24 250.2500 17.50247 04-Model (Maximum)
25 250.2917 17.50432 04-Model (Maximum)
26 250.3333 17.50558 04-Model (Maximum)

Problem

I have a sample dataset like this: ``` 8 02-Model (Minimum) 250.04167175293 17.4996566772461 250.08332824707 17.5000038146973 250.125 17.5008907318115 250.16667175293 17.5011672973633 250.20832824707 17.5013771057129 250.25 17.502140045166 250.29167175293 17.5025615692139 250.33332824707 17.5016822814941 7 03 (Maximum) 250.04167175293 17.5020561218262 250.08332824707 17.501148223877 250.125 17.501127243042 250.16667175293 17.5012378692627 250.20832824707 17.5016021728516 250.25 17.5024681091309 250.29167175293 17.5043239593506 ``` The first column on the data file means the number of rows for that particular data (i.e for 02-MOdel (minimum)). Then after 8 lines I have another line `7 03 (Maximum)` which means for 03 (Maximum) I will have 7 lines of data. The function I have written is as follows: ``` readts <- function(x) { path <- x # Read the first line of the file hello1 <- read.table(path, header = F, nrows = 1,sep="\t") tmp1 <- hello1$V1 # Read the data below first line hello2 <- read.table(path, header = F, nrows = (tmp1), skip = 1, col.names = c("Time", "value")) hello2$name <- c(as.character(hello1$V2)) # Read data for the second chunk hello3 <- read.table(path, header = F, skip = (tmp1 + 1), nrows = 1,sep="\t") tmp2 <- hello3$V1 hello4 <- read.table(path, header = F, skip = (tmp1 + 2), col.names = c("Time", "value"),nrows=tmp2) hello4$name <- c(as.character(hello3$V2)) # Combine data to create a dataframe df <- rbind(hello2, hello4) return(df) } ``` The output I get is as follows: ``` > readts("jdtrial.txt") Time value name 1 250.0417 17.49966 02-Model (Minimum) 2 250.0833 17.50000 02-Model (Minimum) 3 250.1250 17.50089 02-Model (Minimum) 4 250.1667 17.50117 02-Model (Minimum) 5 250.2083 17.50138 02-Model (Minimum) 6 250.2500 17.50214 02-Model (Minimum) 7 250.2917 17.50256 02-Model (Minimum) 8 250.3333 17.50168 02-Model (Minimum) 9 250.0417 17.50206 03 (Maximum) 10 250.0833 17.50115 03 (Maximum) 11 250.1250 17.50113 03 (Maximum) 12 250.1667 17.50124 03 (Maximum) 13 250.2083 17.50160 03 (Maximum) 14 250.2500 17.50247 03 (Maximum) 15 250.2917 17.50432 03 (Maximum) ``` jdtrial.txt is the data I have shown above. However, when I have large data with multiple separators, my function doesn't work and I need to add more lines which makes the function more messy. Is there any easier method to read a data file like this? Thanks. The expected data is the data that I got. The data you can try with: ``` 8 02-Model (Minimum) 250.04167175293 17.4996566772461 250.08332824707 17.5000038146973 250.125 17.5008907318115 250.16667175293 17.5011672973633 250.20832824707 17.5013771057129 250.25 17.502140045166 250.29167175293 17.5025615692139 250.33332824707 17.5016822814941 7 03 (Maximum) 250.04167175293 17.5020561218262 250.08332824707 17.501148223877 250.125 17.501127243042 250.16667175293 17.5012378692627 250.20832824707 17.5016021728516 250.25 17.5024681091309 250.29167175293 17.5043239593506 8 04-Model (Maximum) 250.04167175293 17.5020561218262 250.08332824707 17.501148223877 250.125 17.501127243042 250.16667175293 17.5012378692627 250.20832824707 17.5016021728516 250.25 17.5024681091309 250.29167175293 17.5043239593506 250.33332824707 17.5055828094482 ```

Original source