This is a related question from my original post found here: How to create a new variable based on condition from different dataframe in R

I have 2 data frames from an experiment. The 1st df reads a (roughly) continuous signal over 40 mins. There are 5 columns, 1:3 are binary - saying whether a button was pushed. The 4th column is a binary of if either from column 2 or 3 was pushed. The 5th column is an approximate time in seconds. Example from df below:

etc.

The 2nd data frame is session info where each row is a trial, usually 100-150 rows depending on the day. I have a column that marks trial start time and another column that marks trial end time in seconds. I have another column that states whether or not the trial had an intervention. Example from df below (I omitted several irrelevant columns):

etc.

For the 1st data frame, I want to create a column that indicates whether or not the button was pushed within a trial. If the button was indeed pushed within a trial, I need to label it based on intervention. This is based on those start and end times in the 2nd df, along with the control info. In this table, 0 = intervention and 3 = control.

I would like it to look something like this (iti = inter-trial, wt_int = within trial & intervention, wt_control = within trial & control):

etc.

Going off previous recommendations, I've tried nested ifelse statements with no success. I can get it to label all of the trials as either "iti" or "wt_int" with different failed attempts, or an error at row 1037 (when it changes from iti to wt). From my original question I have a "trial" column now in my 1st df which I'm using for the following code. Perhaps there is a more straightforward approach that combines the original code?

Errors out part way through:

df %>% 
  rowwise() %>% 
  mutate(trial_type = ifelse(any(trial == "wt" & df2$control == 0,
                                 ifelse(trial == "wt" & df2$control == 3,
                                        "wt_omission", "iti"), "wt_odor")))

Also tried this, which labels all as wt_int:

df$trial_type <- ifelse(df$trial == 'wt' && df2$control == 0,
                        ifelse(df$trial == 'wt' && df2$control == 3, 
                               "wt_control", "iti"), "wt_int")

Thank you!

CodePudding user response：

You could use cut to create intervals and check, if a values falls into them:

library(dplyr)

df1 %>% 
  mutate(
    check_1 = cut(time, breaks = df2$t_start, labels = FALSE),
    check_2 = coalesce(cut(time, breaks = df2$t_end, labels = FALSE), 0),
    check_3 = df2$control[check_1],
    trial_type = case_when(
      check_1 - check_2 == 1 & check_3 == 0 ~ "wt_int",
      check_1 - check_2 == 1 & check_3 == 3 ~ "wt_control",
      TRUE ~ "iti"
      )
    ) %>% 
  select(-starts_with("check_"))

This returns

# A tibble: 7 x 6
  initiate  left right l_or_r   time trial_type
     <dbl> <dbl> <dbl>  <dbl>  <dbl> <chr>     
1        0     0     1      1   2.82 iti       
2        0     0     1      1   2.82 iti       
3        0     0     1      1   2.82 iti       
4        0     0     1      1   2.83 iti       
5        1     0     0      0  16.8  wt_int    
6        1     0     0      0  16.8  wt_int    
7        1     0     0      0 187.   wt_control

Data

df1 <- structure(list(initiate = c(0, 0, 0, 0, 1, 1, 1), left = c(0, 
0, 0, 0, 0, 0, 0), right = c(1, 1, 1, 1, 0, 0, 0), l_or_r = c(1, 
1, 1, 1, 0, 0, 0), time = c(2.8225, 2.82375, 2.825, 2.82625, 
16.82, 16.8212, 187.08)), class = c("spec_tbl_df", "tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -7L), spec = structure(list(
    cols = list(initiate = structure(list(), class = c("collector_double", 
    "collector")), left = structure(list(), class = c("collector_double", 
    "collector")), right = structure(list(), class = c("collector_double", 
    "collector")), l_or_r = structure(list(), class = c("collector_double", 
    "collector")), time = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1L), class = "col_spec"))

df2 <- structure(list(trial = c(1, 2, 3, 4, 11, 12, 30), control = c(0, 
0, 0, 0, 3, 0, 3), t_start = c(16.64709, 41.81843, 65.5451, 82.65743, 
187.0787, 200.0486, 415.171), t_end = c(35.49431, 57.74304, 71.16612, 
87.30914, 193.5898, 203.1883, 418.0405)), class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -7L), spec = structure(list(
    cols = list(trial = structure(list(), class = c("collector_double", 
    "collector")), control = structure(list(), class = c("collector_double", 
    "collector")), t_start = structure(list(), class = c("collector_double", 
    "collector")), t_end = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1L), class = "col_spec"))