CODEWITHSUNDEEP
rsubset
user3585829
user3585829

Reputation: 965

Subset rows occuring after a condition is met in a different column

I've searched around and can't seem to figure out how to solve this problem.

I have a data set of subjects and I'd like to subset all rows following an event taking place in a different column. Here is an example of what the data set looks like:

subject <- letters[rep(seq(from = 1, to = 5), each = 10)]
value1 <- rnorm(n = length(subject), mean = 20, sd = 5)
value2 <- rnorm(n = length(subject), mean = 30, sd = 10)
tag <- rep(NA, n = length(subject))
df <- data.frame(subject, value1, value2, tag)

# add random events

df[6,4] <- "event"
df[16,4] <- "event"
df[24,4] <- "event"
df[39,4] <- "event"
df[43,4] <- "event"

head(df, 20)
   subject   value1   value2   tag
1        a 29.48322 28.50112  <NA>
2        a 26.83034 32.61494  <NA>
3        a 19.03148 38.66233  <NA>
4        a 19.97549 36.09613  <NA>
5        a 22.04944 26.80911  <NA>
6        a 16.67589 37.07147 event
7        a 14.25538 32.94055  <NA>
8        a 18.29705 24.17948  <NA>
9        a 14.26047 23.94956  <NA>
10       a 23.91977 39.76018  <NA>
11       b 20.64587 38.93593  <NA>
12       b 20.72713 14.29013  <NA>
13       b 17.55487 27.63619  <NA>
14       b 14.18344 40.30682  <NA>
15       b 11.47055 22.01550  <NA>
16       b 24.60832 38.49901 event
17       b 15.10552 32.08878  <NA>
18       b 23.21466 28.17392  <NA>
19       b 20.59442 34.18078  <NA>
20       b 21.19128 33.50000  <NA>

Is there a way to subset out all rows starting at "event" and all rows after "event" by subject?

Upvotes: 0

Views: 49

Answers (2)

Anders Ellern Bilgrau
Anders Ellern Bilgrau

Reputation: 10233

Yes, this is an easy solution in base R:

indx <- unlist(lapply(which(df$tag == "event"), "+", 0:1))
df[indx, ]
#   subject    value1   value2   tag
#6        a 25.996706 15.65917 event
#7        a 20.336984 35.03734  <NA>
#16       b  9.825914 25.34336 event
#17       b 24.344257 30.15755  <NA>
#24       c 18.586266 33.82119 event
#25       c 25.879272 52.43784  <NA>
#39       d 24.366653 25.03767 event
#40       d 19.870183 36.61909  <NA>
#43       e 23.706029 43.46765 event
#44       e 15.091674 29.45431  <NA>

Here which returns all the row indicies for "event", and the lapply adds the vector 0:1 (i.e. 0 and 1) to all each of these indicies giving the 'event-row' and the row after.

There are multiple other ways to get it as well:

# Alternative 1
indx <- apply(expand.grid(which(df$tag == "event"), 0:1), 1, sum)

# Alternative 2
eindx <- which(df$tag == "event")
indx <- c(eindx, eindx + 1)

These indicies come in a different order, but one can always sort them.

To solve it by-subject, you can check that this the addition of one keeps it within the subject, and exclude if not: 

eindx <- which(df$tag == "event")
not_eq <- which(df$subject[eindx] != df$subject[eindx+1])
indx <- sort(c(eindx, setdiff(eindx, not_eq) + 1))
df[indx, ]

or you can wrap these approaches into an function and utilize the by or split functions:

get_event <- function(f) {
  eindx <- which(f$tag == "event")
  indx <- sort(c(eindx, eindx + 1))
  f[indx, ]
}

res <- do.call(rbind, by(df, subject, get_event))

or 

res <- do.call(rbind, lapply(split(df, subject), get_event))

Upvotes: 1

JasonAizkalns
JasonAizkalns

Reputation: 20473

Depending on what you want to do after the subset, this will probably work:

library(tidyverse)

df %>%
  group_by(subject) %>%
  mutate(event_grp = cumsum(!is.na(tag))) %>%
  group_by(subject, event_grp) %>%
  summarise(
    avg_val1 = mean(value1),
    avg_val2 = mean(value2)
  )

#    subject event_grp avg_val1 avg_val2
#    <fct>       <int>    <dbl>    <dbl>
#  1 a               0     22.7     38.6
#  2 a               1     20.5     30.5
#  3 b               0     21.1     25.0
#  4 b               1     21.4     21.2
#  5 c               0     19.5     35.8
#  6 c               1     18.6     23.9
#  7 d               0     18.7     31.1
#  8 d               1     19.4     42.0
#  9 e               0     18.5     25.7
# 10 e               1     20.7     30.2

For the subset, you'll just want:

df %>%
  group_by(subject) %>%
  mutate(event_grp = cumsum(!is.na(tag))) %>%
  filter(event_grp >= 1)

Upvotes: 3

Related Questions