R create new column based on data range at a certain time point

user_4848029 0
Viewed 456

I have large data frame (>50 columns). A sample of the relevant columns are here:

tb <- data.frame(RowID=c("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11", "A12", "A13", "A14", "A15"), 
                    Patient=c("001", "001", "001", "002", "002", "035", "035", "035", "035", "035", "100", "100", "105", "105", "105"),
                    Time=c(1,2,3,1,2,1,2,3,4,5,1,2,1,2,3),
                    Value=c(NA,10,23,100,30,10,15,NA,60,56.7,30,51,3,13,77))

I am trying to create a new column (Value_status) that ranks the initial value for each patient as either low or high (Value <50, Value >=50). The Value_status should be carried through to the other rows for that patient.

Here's what I have:

tb %>%
  group_by(Patient) %>%
  mutate(Value_status = if_else(Time == 1 & Value < 50, "low", "high"))

Incomplete if_else statement

I thought I had solved it by adding group_by, but it doesn't give the same value for each individual patient as I hoped. I think I need to nest the if_else with more conditions, something like this?

Note: If a patient is missing Value at a time point other than 1, then they can still be grouped according to high/low.

tb %>%
  group_by(Patient) %>%
  mutate(Value_status = if_else(Time == 1 & Value < 50, "low", 
                                if_else(Time == 1 & >= 50, "high",
                                if_else(#Apply the value from time point 1#))))

The output I am trying to get should look like this: It should group patients based on whether or not their baseline values are high

RowID Patient Time Value Value_status
1     A1     001    1    NA         <NA>
2     A2     001    2  10.0         <NA>
3     A3     001    3  23.0         <NA>
4     A4     002    1 100.0         high
5     A5     002    2  30.0         high
6     A6     035    1  10.0         low
7     A7     035    2  15.0         low
8     A8     035    3    NA         low
9     A9     035    4  60.0         low
10   A10     035    5  56.7         low
11   A11     100    1  30.0         low
12   A12     100    2  51.0         low
13   A13     105    1   3.0         low
14   A14     105    2  13.0         low
15   A15     105    3  77.0         low
dplyr grouping r
2 Answers

Instead of if_else nested, we could use case_when where we can have multiple conditions created, then do a group_by with 'Patient' and fill the 'Value_status' NA elements with the previous non-NA values

library(dplyr)
library(tidyr)
tb %>%
    mutate(Value_status = case_when(Time == 1 & Value < 50 ~ "low",
                        Time == 1 & Value >= 50 ~ "high"
                        )) %>%
   group_by(Patient) %>%
   fill(Value_status) %>%
   ungroup

-outupt

# A tibble: 15 x 5
   RowID Patient  Time Value Value_status
   <chr> <chr>   <dbl> <dbl> <chr>       
 1 A1    001         1  NA   <NA>        
 2 A2    001         2  10   <NA>        
 3 A3    001         3  23   <NA>        
 4 A4    002         1 100   high        
 5 A5    002         2  30   high        
 6 A6    035         1  10   low         
 7 A7    035         2  15   low         
 8 A8    035         3  NA   low         
 9 A9    035         4  60   low         
10 A10   035         5  56.7 low         
11 A11   100         1  30   low         
12 A12   100         2  51   low         
13 A13   105         1   3   low         
14 A14   105         2  13   low         
15 A15   105         3  77   low
user_37322710

Here a solution with a nested ifelse

tb %>% 
  mutate(Value_status = ifelse(Time != 1 & Value ==10, "medium", 
                                      ifelse(Time == 1 & Value < 50, "low", 
                                             ifelse(Time == 1 & Value >= 50, "high", NA)
                                             )
                                      ))

Output:

   RowID Patient Time Value Value_status
1     A1     001    1    NA         <NA>
2     A2     001    2    10       medium
3     A3     001    3    23         <NA>
4     A4     002    1   100         high
5     A5     002    2    30         <NA>
6     A6     035    1    10          low
7     A7     035    2    15         <NA>
8     A8     035    3    NA         <NA>
9     A9     035    4    60         <NA>
10   A10     035    5    57         <NA>
11   A11     100    1    30          low
12   A12     100    2    51         <NA>
13   A13     105    1     3          low
14   A14     105    2    13         <NA>
15   A15     105    3    77         <NA>
user_133216470
Related