r : calculate time interval to reference-CodePudding

I would like to ask your help.

tibble::tribble(
      ~Last.TP,  ~D.EndStudy,    ~EOTvisit,       ~D.END,        ~D.IP,
  "2021-02-08", "2020-08-13", "2020-08-13", "2020-08-13", "2019-07-24",
  "2021-04-19", "2021-04-26", "2021-04-26", "2021-04-26", "2020-04-06",
  "2022-01-24", "2022-02-10", "2022-02-10", "2022-02-10", "2021-01-11"
  )

Desired temporary output is below tibble. In below tibble, Days are calculated reference to D.IP (2021-02-08 - 2019-07-24) 1 = 566 (days) I would like to get below tibble, but in months. Thank you very much!

tibble::tribble(
  ~LastTP, ~DENDSTUDY, ~EOTVISIT, ~DEND, ~DIP,
     566L,       387L,      387L,  387L,   1L,
     636L,       643L,      643L,  643L, 258L,
     916L,       933L,      933L,  933L, 538L
  )

CodePudding user response：

Update: OP request:

library(lubridate)
library(dplyr)
df %>% 
  mutate(across(4:8, ~ . - D.IP[1]) 1,
         across(4:8, ~ as.numeric(round(./30.417, digit=1)))
         )

First answer: We can do it using tripple across:

First we transform character class to date class.
Do the calculation with across substracting all from first row of D.IP
Again across all columns we calculate the months from days:

library(dplyr)
library(lubridate)
df %>% 
  mutate(across(, ymd),
         across(, ~ . - D.IP[1]) 1,
         across(, ~ as.numeric(round(./30.417, digit=1)))
         )

 Last.TP D.EndStudy EOTvisit D.END  D.IP
    <dbl>      <dbl>    <dbl> <dbl> <dbl>
1    18.6       12.7     12.7  12.7   0  
2    20.9       21.1     21.1  21.1   8.5
3    30.1       30.7     30.7  30.7  17.7

CodePudding user response：

Another option using sapply with as.Date to make sure it is in date format. You can use the following code:

df[] <- sapply(df, \(x) {
  (as.Date(x) - as.Date("2019-07-24")   1)/30.414
})
df
#> # A tibble: 3 × 5
#>   Last.TP D.EndStudy EOTvisit D.END    D.IP
#>     <dbl>      <dbl>    <dbl> <dbl>   <dbl>
#> 1    18.6       12.7     12.7  12.7  0.0329
#> 2    20.9       21.1     21.1  21.1  8.48  
#> 3    30.1       30.7     30.7  30.7 17.7

^{Created on 2023-01-20 with reprex v2.0.2}

A different option like @TarJae mentioned in the comments (thanks!):

df[] <- sapply(df, \(x) {
  (as.Date(x) - as.Date(df$D.IP[1])   1)/30.414
})
df
#> # A tibble: 3 × 5
#>   Last.TP D.EndStudy EOTvisit D.END    D.IP
#>     <dbl>      <dbl>    <dbl> <dbl>   <dbl>
#> 1    18.6       12.7     12.7  12.7  0.0329
#> 2    20.9       21.1     21.1  21.1  8.48  
#> 3    30.1       30.7     30.7  30.7 17.7

^{Created on 2023-01-20 with reprex v2.0.2}

CodePudding user response：

Alternatively, please try the below code,

code

df2 <- df %>% mutate(across(c('Last.TP','D.EndStudy','EOTvisit','D.END','D.IP'), 
~ as.numeric((as.Date(.x, '%Y-%m-%d')-as.Date(df$D.IP[1],'%Y-%m-%d') 1)/30.4375), 
.names = 'x{col}'))

^{Created on 2023-01-20 with reprex v2.0.2}

output

# A tibble: 3 × 10
  Last.TP    D.EndStudy EOTvisit   D.END      D.IP       xLast.TP xD.EndStudy xEOTvisit xD.END   xD.IP
  <chr>      <chr>      <chr>      <chr>      <chr>         <dbl>       <dbl>     <dbl>  <dbl>   <dbl>
1 2021-02-08 2020-08-13 2020-08-13 2020-08-13 2019-07-24     18.6        12.7      12.7   12.7  0.0329
2 2021-04-19 2021-04-26 2021-04-26 2021-04-26 2020-04-06     20.9        21.1      21.1   21.1  8.48  
3 2022-01-24 2022-02-10 2022-02-10 2022-02-10 2021-01-11     30.1        30.7      30.7   30.7 17.7