Making a rowwise selection based on a specific column condition on a dataframe

Buczinski 0
Viewed 16

I am a little perplex based on a specific data manipulation I want to perform. I want to look for animals with a specific Id repeated over time t where they can be treated (variable Treat==1) or not (Treat==0). The dataframe bd I have is the following example:

bd =data.frame(t=c(1,2,3,4,5,6,1,2,3,4,5,6,1,2,3,4,5,6), #6 measurement periods for the individuals  
                 Id= rep(1:18, each=6, length.out=18), #3 individuals repeated over time
                 Treat=c(0,0,1,0,0,0, 0,0,0,0,1,0,0,0,0,0,0,0) # variable indicating if the animal was treated for this period of time yes if 1 and no if 0               
) # I want to keep rows of the Id starting from Treat==1.

In the end I want to keep :for individual 1 the rows starting where Treat=="1" (ie row of Id=1 and t from t=3 to t=6, for Id=2, the rows with t=5 and t=6 and no row for Id=3 because Treat==0.

Thanks for your help!!!

data-wrangling tidyverse dplyr
1 Answers

It sounds like Treat means a treatment was applied on that time t, and you wish to get only that data which was after each animal was treated. So for each Id, you want the row with Treat=1, and also every row after (where Treat=0`).

There are several ways to do it. I will show two.

One is to create a new column status which is 1 on the treatment day, and also 1 on every day thereafter. You can the easily filter by status = 1. Luckily tidyverse has a function for this called cummax (max of all rows up to that one) that we can use.

library(tidyverse)

result = bd %>% 
  group_by(Id) %>%
  mutate(status = cummax(Treat)) %>%
  filter(status == 1)

result

      t    Id Treat status
  <dbl> <int> <dbl>  <dbl>
1     3     1     1      1
2     4     1     0      1
3     5     1     0      1
4     6     1     0      1
5     5     2     1      1
6     6     2     0      1

The second is more correct but also more complicated. You create a new column called t_treatment, that shows what day the animal was treated. This will be replicated along the entire series, but that's okay. Then you filter on t >= t_treatment. This will work even if Treat is not something where max works well (like a factor or string). It's also more extensible if you later need to do more complex things with multiple treatments (eg. rows after first two treatments within a week of each other).

library(tidyverse)

# Get treatment dates
treatments = bd %>% 
  filter(Treat == 1) %>%
  mutate(t_treatment = t) %>%
  select(Id, t_treatment)

# Perform a join back to the original data and filter
result = bd %>% 
  merge(treatments) %>%
  filter(t >= t_treatment)

result

  Id t Treat t_treatment
1  1 3     1           3
2  1 4     0           3
3  1 5     0           3
4  1 6     0           3
5  2 5     1           5
6  2 6     0           5
Dommondke0
Related