conditional subsetting of two columns - r

Question

I have a couple problems that I'm interested in solving. I would like to sample and store the conc column in the array by a value e.g.:

newdata <- data[ which(data$conc > 8), ]

However, I would like to save the associated datetime stamp with it. Finally in another array, when the conc value exceeds 8.00 before falling below 8.00, I would like to store the duration of this episode. So for example, 21:30 would record as 15 minutes, and another time will be logged between 00:15 and 03:00 resulting in a stored value of 165 minutes.

    datetime            conc
    20/08/2012 21:00    7.29                                                                                    
    20/08/2012 21:15    7.35                                                                                    
    20/08/2012 21:30    35.23                                                                                   
    20/08/2012 21:45    7.44                                                                                    
    20/08/2012 22:00    13.30                                                                                   
    20/08/2012 22:15    7.60                                                                                    
    20/08/2012 22:30    7.65                                                                                    
    20/08/2012 22:45    7.70                                                                                    
    20/08/2012 23:00    7.83                                                                                    
    20/08/2012 23:15    8.07                                                                                    
    20/08/2012 23:30    8.30                                                                                    
    20/08/2012 23:45    22.44                                                                                   
    21/08/2012 00:00    7.81                                                                                    
    21/08/2012 00:15    10.67                                                                                   
    21/08/2012 00:30    11.07                                                                                   
    21/08/2012 00:45    8.29                                                                                    
    21/08/2012 01:00    8.17                                                                                    
    21/08/2012 01:15    8.29                                                                                    
    21/08/2012 01:30    8.26                                                                                    
    21/08/2012 01:45    8.93                                                                                    
    21/08/2012 02:00    9.74                                                                                    
    21/08/2012 02:15    9.69                                                                                    
    21/08/2012 02:30    9.15                                                                                    
    21/08/2012 02:45    9.52                                                                                    
    21/08/2012 03:00    9.10
    21/08/2012 03:15    7.10

Well I have used `newdata <- data[data$conc > 8, ]` and saved using `write.table(newdata, file="newdata.dat", row.names=FALSE, sep="\t", quote=FALSE)` but I cannot find out how to save the duration of periods where values exceed 8. — squishy, Jan 22 '14 at 16:36

score 0 · Answer 1 · answered Jan 22 '14 at 17:40

Maybe one form would be to add two more columns to your data, one indicating that the conc is above 8 and another calculating the cumulative time before it returns below 8.

 #generating data
 data <- read.table(text="datetime conc
'20/08/2012 21:00' 7.29
'20/08/2012 21:15' 7.35
'20/08/2012 21:30' 35.23
'20/08/2012 21:45' 7.44
'20/08/2012 22:00' 13.30
'20/08/2012 22:15' 7.60
'20/08/2012 22:30' 7.65
'20/08/2012 22:45' 7.70
'20/08/2012 23:00' 7.83
'20/08/2012 23:15' 8.07
'20/08/2012 23:30' 8.30
'20/08/2012 23:45' 22.44
'21/08/2012 00:00' 7.81
'21/08/2012 00:15' 10.67
'21/08/2012 00:30' 11.07
'21/08/2012 00:45' 8.29
'21/08/2012 01:00' 8.17
'21/08/2012 01:15' 8.29
'21/08/2012 01:30' 8.26
'21/08/2012 01:45' 8.93
'21/08/2012 02:00' 9.74
'21/08/2012 02:15' 9.69
'21/08/2012 02:30' 9.15
'21/08/2012 02:45' 9.52
'21/08/2012 03:00' 9.10
'21/08/2012 03:15' 7.10", sep=" ", header=TRUE, stringsAsFactors=FALSE)

#converting to date

data$datetime<-as.POSIXct(data$datetime, format="%d/%m/%Y %H:%M")

#creating stamps
data$stamp <- NA
data$stamp[which(data$conc<8)] <- "less.than.8"
data$stamp[which(data$conc>8)] <- "greater.than.8"

#calculating cumulative durationg in the episodes of sequencies of conc>8

for (i in 1:nrow(data)){
  if(data$stamp[i] =="less.than.8"){
    data$cum.duration[i] <- 0}
  if(data$stamp[i] =="greater.than.8"){
    data$cum.duration[i] <- (data$datetime[i]-data$datetime[i-1])+data$cum.duration[i-1]}
}

This will result in the following table, then you can do whatever you want with it:

              datetime  conc          stamp cum.duration
1  2012-08-20 21:00:00  7.29    less.than.8            0
2  2012-08-20 21:15:00  7.35    less.than.8            0
3  2012-08-20 21:30:00 35.23 greater.than.8           15
4  2012-08-20 21:45:00  7.44    less.than.8            0
5  2012-08-20 22:00:00 13.30 greater.than.8           15
6  2012-08-20 22:15:00  7.60    less.than.8            0
7  2012-08-20 22:30:00  7.65    less.than.8            0
8  2012-08-20 22:45:00  7.70    less.than.8            0
9  2012-08-20 23:00:00  7.83    less.than.8            0
10 2012-08-20 23:15:00  8.07 greater.than.8           15
11 2012-08-20 23:30:00  8.30 greater.than.8           30
12 2012-08-20 23:45:00 22.44 greater.than.8           45
13 2012-08-21 00:00:00  7.81    less.than.8            0
14 2012-08-21 00:15:00 10.67 greater.than.8           15
15 2012-08-21 00:30:00 11.07 greater.than.8           30
16 2012-08-21 00:45:00  8.29 greater.than.8           45
17 2012-08-21 01:00:00  8.17 greater.than.8           60
18 2012-08-21 01:15:00  8.29 greater.than.8           75
19 2012-08-21 01:30:00  8.26 greater.than.8           90
20 2012-08-21 01:45:00  8.93 greater.than.8          105
21 2012-08-21 02:00:00  9.74 greater.than.8          120
22 2012-08-21 02:15:00  9.69 greater.than.8          135
23 2012-08-21 02:30:00  9.15 greater.than.8          150
24 2012-08-21 02:45:00  9.52 greater.than.8          165
25 2012-08-21 03:00:00  9.10 greater.than.8          180
26 2012-08-21 03:15:00  7.10    less.than.8            0

To select only the end episodes, you can write:

 lines <- which(data$conc>8)
 lines <- lines[(lines[2:length(lines)] - lines[1:(length(lines)-1)])>1]
 data[lines,]

Which will give you:

          datetime  conc          stamp cum.duration
3  2012-08-20 21:30:00 35.23 greater.than.8           15
5  2012-08-20 22:00:00 13.30 greater.than.8           15
12 2012-08-20 23:45:00 22.44 greater.than.8           45
25 2012-08-21 03:00:00  9.10 greater.than.8          180

Thanks for this, however, when I run this on a large dataset, I get the error message: `Error in if (data$stamp[i] == "less.than.25") { : missing value where TRUE/FALSE needed` — squishy, Jan 24 '14 at 09:44
Could you post the whole code you have tried? I can't see why it should not work on a large dataset. — Carlos Cinelli, Jan 24 '14 at 15:50
Sorry @carloscineli, this worked after a bit of fiddling ! There was problem with using <- NA on my data set. — squishy, Feb 03 '14 at 13:44

conditional subsetting of two columns - r

1 Answers1