I am struggling to understand how I can subtract the blood pressure data if the patient had anywhere from 1 measurement to 5 measurements. For example, my data

So some patients will have all 5 measurements (e.g. ID 1) when some patients will have only 1 measurement (e.g. ID 4).

I want to make a new variable that subtracts from the latest value to the earliest value. If the patient only has 1 measurement, the new variable will be NA. For example like this.

I am using R Studio for this. I would appreciate any coding help to achieve this!

CodePudding user response：

Assuming that the first value is always located in Value1 and that the dates are sorted correctly, the dplyr package makes it straight-forward.

Use coalesce to find the first non-missing value 2-5 (in reverse order), and substract value 1 from that.

library(dplyr)

mutate(df, NewVariable = coalesce(Value5, Value4, Value3, Value2) - Value1)

#> # A tibble: 4 × 12
#>      ID Date1      Value1 Date2      Value2 Date3      Value3 Date4      Value4 Date5      Value5 NewVariable
#>   <dbl> <chr>       <dbl> <chr>       <dbl> <chr>       <dbl> <chr>       <dbl> <chr>       <dbl>       <dbl>
#> 1     1 01/01/2022    160 01/02/2022    161 01/04/2022    159 01/05/2022    159 01/06/2022    130         -30
#> 2     2 08/02/2022    130 01/07/2022    120 <NA>           NA <NA>           NA <NA>           NA         -10
#> 3     3 01/04/2022    112 29/09/2022    161 10/10/2022    159 <NA>           NA <NA>           NA          47
#> 4     4 01/10/2022    182 <NA>           NA <NA>           NA <NA>           NA <NA>           NA          NA