Date char and date column class mixed after IF

tidyverse
1

Hi there
I have a problem of date column after doing IF_ELSE to calculate number of days in Quantity.
Desired outcomes = sd, ed and Quantity (start date, end date and quantity of days by substracting)
I created with lubridate dmy to get date class in sdate and edate.
IF_ELSE gave me an error for FALSE argument must be charactor, not date.
So, sdate3 was the result of char from start to get sdate3 where patient 5, 6, 7, 8, 18 and 19 are char, but else are date.
Since they are mixed date and char, I am unable to substract to get days in Quaantity.
Look forward to receiving your advice.

Repex is here.
Thanks you so much.
G

tpn.query <- tibble::tribble(
               ~PatientNumber, ~ProgramStatus,       ~start,         ~end,          ~sd,          ~ed, ~Quantity,
                           1L,          "COM", "01/07/2018", "01/07/2022",  "1/07/2022",  "1/07/2022",        1L,
                           2L,          "COM", "28/05/2021", "20/07/2022",  "1/07/2022", "20/07/2022",       19L,
                           3L,          "COM", "14/12/2021", "29/07/2022",  "1/07/2022", "29/07/2022",       28L,
                           4L,          "COM", "28/05/2021", "23/12/2022",  "1/07/2022", "23/12/2022",      175L,
                           5L,          "COM", "18/07/2022", "23/12/2022", "18/07/2022", "23/12/2022",      158L,
                           6L,          "COM", "01/07/2022", "03/02/2023",  "1/07/2022",  "3/02/2023",      217L,
                           7L,          "COM", "21/10/2022", "30/04/2023", "21/10/2022", "30/04/2023",      191L,
                           8L,          "COM", "12/01/2023", "03/05/2023", "12/01/2023",  "3/05/2023",      111L,
                           9L,          "ACT", "01/07/2018",           NA,  "1/07/2022", "30/06/2023",      364L,
                          10L,          "ACT", "01/07/2018",           NA,  "1/07/2022", "30/06/2023",      364L,
                          11L,          "ACT", "10/08/2018",           NA,  "1/07/2022", "30/06/2023",      364L,
                          12L,          "ACT", "14/06/2019",           NA,  "1/07/2022", "30/06/2023",      364L,
                          13L,          "ACT", "28/05/2019",           NA,  "1/07/2022", "30/06/2023",      364L,
                          14L,          "ACT", "22/11/2019",           NA,  "1/07/2022", "30/06/2023",      364L,
                          15L,          "ACT", "11/02/2020",           NA,  "1/07/2022", "30/06/2023",      364L,
                          16L,          "ACT", "01/09/2021",           NA,  "1/07/2022", "30/06/2023",      364L,
                          17L,          "ACT", "08/11/2021",           NA,  "1/07/2022", "30/06/2023",      364L,
                          18L,          "ACT", "11/11/2022",           NA, "11/11/2022", "30/06/2023",      231L,
                          19L,          "ACT", "03/03/2023",           NA,  "3/03/2023", "30/06/2023",      119L
               )

tpn.query
#> # A tibble: 19 × 7
#>    PatientNumber ProgramStatus start      end        sd         ed      Quantity
#>            <int> <chr>         <chr>      <chr>      <chr>      <chr>      <int>
#>  1             1 COM           01/07/2018 01/07/2022 1/07/2022  1/07/2…        1
#>  2             2 COM           28/05/2021 20/07/2022 1/07/2022  20/07/…       19
#>  3             3 COM           14/12/2021 29/07/2022 1/07/2022  29/07/…       28
#>  4             4 COM           28/05/2021 23/12/2022 1/07/2022  23/12/…      175
#>  5             5 COM           18/07/2022 23/12/2022 18/07/2022 23/12/…      158
#>  6             6 COM           01/07/2022 03/02/2023 1/07/2022  3/02/2…      217
#>  7             7 COM           21/10/2022 30/04/2023 21/10/2022 30/04/…      191
#>  8             8 COM           12/01/2023 03/05/2023 12/01/2023 3/05/2…      111
#>  9             9 ACT           01/07/2018 <NA>       1/07/2022  30/06/…      364
#> 10            10 ACT           01/07/2018 <NA>       1/07/2022  30/06/…      364
#> 11            11 ACT           10/08/2018 <NA>       1/07/2022  30/06/…      364
#> 12            12 ACT           14/06/2019 <NA>       1/07/2022  30/06/…      364
#> 13            13 ACT           28/05/2019 <NA>       1/07/2022  30/06/…      364
#> 14            14 ACT           22/11/2019 <NA>       1/07/2022  30/06/…      364
#> 15            15 ACT           11/02/2020 <NA>       1/07/2022  30/06/…      364
#> 16            16 ACT           01/09/2021 <NA>       1/07/2022  30/06/…      364
#> 17            17 ACT           08/11/2021 <NA>       1/07/2022  30/06/…      364
#> 18            18 ACT           11/11/2022 <NA>       11/11/2022 30/06/…      231
#> 19            19 ACT           03/03/2023 <NA>       3/03/2023  30/06/…      119

library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

tpn.query$sdate <- dmy(tpn.query$start)
tpn.query$edate <- dmy(tpn.query$end)

tpn.query
#> # A tibble: 19 × 9
#>    PatientNumber ProgramStatus start      end    sd    ed    Quantity sdate     
#>            <int> <chr>         <chr>      <chr>  <chr> <chr>    <int> <date>    
#>  1             1 COM           01/07/2018 01/07… 1/07… 1/07…        1 2018-07-01
#>  2             2 COM           28/05/2021 20/07… 1/07… 20/0…       19 2021-05-28
#>  3             3 COM           14/12/2021 29/07… 1/07… 29/0…       28 2021-12-14
#>  4             4 COM           28/05/2021 23/12… 1/07… 23/1…      175 2021-05-28
#>  5             5 COM           18/07/2022 23/12… 18/0… 23/1…      158 2022-07-18
#>  6             6 COM           01/07/2022 03/02… 1/07… 3/02…      217 2022-07-01
#>  7             7 COM           21/10/2022 30/04… 21/1… 30/0…      191 2022-10-21
#>  8             8 COM           12/01/2023 03/05… 12/0… 3/05…      111 2023-01-12
#>  9             9 ACT           01/07/2018 <NA>   1/07… 30/0…      364 2018-07-01
#> 10            10 ACT           01/07/2018 <NA>   1/07… 30/0…      364 2018-07-01
#> 11            11 ACT           10/08/2018 <NA>   1/07… 30/0…      364 2018-08-10
#> 12            12 ACT           14/06/2019 <NA>   1/07… 30/0…      364 2019-06-14
#> 13            13 ACT           28/05/2019 <NA>   1/07… 30/0…      364 2019-05-28
#> 14            14 ACT           22/11/2019 <NA>   1/07… 30/0…      364 2019-11-22
#> 15            15 ACT           11/02/2020 <NA>   1/07… 30/0…      364 2020-02-11
#> 16            16 ACT           01/09/2021 <NA>   1/07… 30/0…      364 2021-09-01
#> 17            17 ACT           08/11/2021 <NA>   1/07… 30/0…      364 2021-11-08
#> 18            18 ACT           11/11/2022 <NA>   11/1… 30/0…      231 2022-11-11
#> 19            19 ACT           03/03/2023 <NA>   3/03… 30/0…      119 2023-03-03
#> # ℹ 1 more variable: edate <date>

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

tpn.query$sdate2 <- if_else(tpn.query$sdate < '2022-07-01', '2022-07-01', tpn.query$sdate)
#> Error in `if_else()`:
#> ! Can't combine `true` <character> and `false` <date>.
#> Backtrace:
#>      ▆
#>   1. ├─dplyr::if_else(tpn.query$sdate < "2022-07-01", "2022-07-01", tpn.query$sdate)
#>   2. │ └─dplyr:::vec_case_when(...)
#>   3. │   └─vctrs::vec_ptype_common(!!!everything, .ptype = ptype, .call = call)
#>   4. └─vctrs (local) `<fn>`()
#>   5.   └─vctrs::vec_default_ptype2(...)
#>   6.     ├─base::withRestarts(...)
#>   7.     │ └─base (local) withOneRestart(expr, restarts[[1L]])
#>   8.     │   └─base (local) doWithOneRestart(return(expr), restart)
#>   9.     └─vctrs::stop_incompatible_type(...)
#>  10.       └─vctrs:::stop_incompatible(...)
#>  11.         └─vctrs:::stop_vctrs(...)
#>  12.           └─rlang::abort(message, class = c(class, "vctrs_error"), ..., call = call)

tpn.query
#> # A tibble: 19 × 9
#>    PatientNumber ProgramStatus start      end    sd    ed    Quantity sdate     
#>            <int> <chr>         <chr>      <chr>  <chr> <chr>    <int> <date>    
#>  1             1 COM           01/07/2018 01/07… 1/07… 1/07…        1 2018-07-01
#>  2             2 COM           28/05/2021 20/07… 1/07… 20/0…       19 2021-05-28
#>  3             3 COM           14/12/2021 29/07… 1/07… 29/0…       28 2021-12-14
#>  4             4 COM           28/05/2021 23/12… 1/07… 23/1…      175 2021-05-28
#>  5             5 COM           18/07/2022 23/12… 18/0… 23/1…      158 2022-07-18
#>  6             6 COM           01/07/2022 03/02… 1/07… 3/02…      217 2022-07-01
#>  7             7 COM           21/10/2022 30/04… 21/1… 30/0…      191 2022-10-21
#>  8             8 COM           12/01/2023 03/05… 12/0… 3/05…      111 2023-01-12
#>  9             9 ACT           01/07/2018 <NA>   1/07… 30/0…      364 2018-07-01
#> 10            10 ACT           01/07/2018 <NA>   1/07… 30/0…      364 2018-07-01
#> 11            11 ACT           10/08/2018 <NA>   1/07… 30/0…      364 2018-08-10
#> 12            12 ACT           14/06/2019 <NA>   1/07… 30/0…      364 2019-06-14
#> 13            13 ACT           28/05/2019 <NA>   1/07… 30/0…      364 2019-05-28
#> 14            14 ACT           22/11/2019 <NA>   1/07… 30/0…      364 2019-11-22
#> 15            15 ACT           11/02/2020 <NA>   1/07… 30/0…      364 2020-02-11
#> 16            16 ACT           01/09/2021 <NA>   1/07… 30/0…      364 2021-09-01
#> 17            17 ACT           08/11/2021 <NA>   1/07… 30/0…      364 2021-11-08
#> 18            18 ACT           11/11/2022 <NA>   11/1… 30/0…      231 2022-11-11
#> 19            19 ACT           03/03/2023 <NA>   3/03… 30/0…      119 2023-03-03
#> # ℹ 1 more variable: edate <date>

tpn.query$sdate3 <- if_else(tpn.query$sdate < '2022-07-01', '2022-07-01', tpn.query$start)

tpn.query
#> # A tibble: 19 × 10
#>    PatientNumber ProgramStatus start      end    sd    ed    Quantity sdate     
#>            <int> <chr>         <chr>      <chr>  <chr> <chr>    <int> <date>    
#>  1             1 COM           01/07/2018 01/07… 1/07… 1/07…        1 2018-07-01
#>  2             2 COM           28/05/2021 20/07… 1/07… 20/0…       19 2021-05-28
#>  3             3 COM           14/12/2021 29/07… 1/07… 29/0…       28 2021-12-14
#>  4             4 COM           28/05/2021 23/12… 1/07… 23/1…      175 2021-05-28
#>  5             5 COM           18/07/2022 23/12… 18/0… 23/1…      158 2022-07-18
#>  6             6 COM           01/07/2022 03/02… 1/07… 3/02…      217 2022-07-01
#>  7             7 COM           21/10/2022 30/04… 21/1… 30/0…      191 2022-10-21
#>  8             8 COM           12/01/2023 03/05… 12/0… 3/05…      111 2023-01-12
#>  9             9 ACT           01/07/2018 <NA>   1/07… 30/0…      364 2018-07-01
#> 10            10 ACT           01/07/2018 <NA>   1/07… 30/0…      364 2018-07-01
#> 11            11 ACT           10/08/2018 <NA>   1/07… 30/0…      364 2018-08-10
#> 12            12 ACT           14/06/2019 <NA>   1/07… 30/0…      364 2019-06-14
#> 13            13 ACT           28/05/2019 <NA>   1/07… 30/0…      364 2019-05-28
#> 14            14 ACT           22/11/2019 <NA>   1/07… 30/0…      364 2019-11-22
#> 15            15 ACT           11/02/2020 <NA>   1/07… 30/0…      364 2020-02-11
#> 16            16 ACT           01/09/2021 <NA>   1/07… 30/0…      364 2021-09-01
#> 17            17 ACT           08/11/2021 <NA>   1/07… 30/0…      364 2021-11-08
#> 18            18 ACT           11/11/2022 <NA>   11/1… 30/0…      231 2022-11-11
#> 19            19 ACT           03/03/2023 <NA>   3/03… 30/0…      119 2023-03-03
#> # ℹ 2 more variables: edate <date>, sdate3 <chr>

Created on 2023-07-14 with reprex v2.0.2

2

Does this work for you?

library(tidyverse)

tpn.query <- tibble::tribble(
  ~PatientNumber, ~ProgramStatus,       ~start,         ~end,          ~sd,          ~ed, ~Quantity,
  1L,          "COM", "01/07/2018", "01/07/2022",  "1/07/2022",  "1/07/2022",        1L,
  2L,          "COM", "28/05/2021", "20/07/2022",  "1/07/2022", "20/07/2022",       19L,
  3L,          "COM", "14/12/2021", "29/07/2022",  "1/07/2022", "29/07/2022",       28L,
  4L,          "COM", "28/05/2021", "23/12/2022",  "1/07/2022", "23/12/2022",      175L,
  5L,          "COM", "18/07/2022", "23/12/2022", "18/07/2022", "23/12/2022",      158L,
  6L,          "COM", "01/07/2022", "03/02/2023",  "1/07/2022",  "3/02/2023",      217L,
  7L,          "COM", "21/10/2022", "30/04/2023", "21/10/2022", "30/04/2023",      191L,
  8L,          "COM", "12/01/2023", "03/05/2023", "12/01/2023",  "3/05/2023",      111L,
  9L,          "ACT", "01/07/2018",           NA,  "1/07/2022", "30/06/2023",      364L,
  10L,          "ACT", "01/07/2018",           NA,  "1/07/2022", "30/06/2023",      364L,
  11L,          "ACT", "10/08/2018",           NA,  "1/07/2022", "30/06/2023",      364L,
  12L,          "ACT", "14/06/2019",           NA,  "1/07/2022", "30/06/2023",      364L,
  13L,          "ACT", "28/05/2019",           NA,  "1/07/2022", "30/06/2023",      364L,
  14L,          "ACT", "22/11/2019",           NA,  "1/07/2022", "30/06/2023",      364L,
  15L,          "ACT", "11/02/2020",           NA,  "1/07/2022", "30/06/2023",      364L,
  16L,          "ACT", "01/09/2021",           NA,  "1/07/2022", "30/06/2023",      364L,
  17L,          "ACT", "08/11/2021",           NA,  "1/07/2022", "30/06/2023",      364L,
  18L,          "ACT", "11/11/2022",           NA, "11/11/2022", "30/06/2023",      231L,
  19L,          "ACT", "03/03/2023",           NA,  "3/03/2023", "30/06/2023",      119L
)
tpn.query$sdate <- dmy(tpn.query$start)
tpn.query$edate <- dmy(tpn.query$end)

tpn.query$sdate2 <- if_else(tpn.query$sdate < ymd("2022-07-01"), ymd("2022-07-01"), tpn.query$sdate)

tpn.query |> select(sdate, sdate2)
#> # A tibble: 19 × 2
#>    sdate      sdate2    
#>    <date>     <date>    
#>  1 2018-07-01 2022-07-01
#>  2 2021-05-28 2022-07-01
#>  3 2021-12-14 2022-07-01
#>  4 2021-05-28 2022-07-01
#>  5 2022-07-18 2022-07-18
#>  6 2022-07-01 2022-07-01
#>  7 2022-10-21 2022-10-21
#>  8 2023-01-12 2023-01-12
#>  9 2018-07-01 2022-07-01
#> 10 2018-07-01 2022-07-01
#> 11 2018-08-10 2022-07-01
#> 12 2019-06-14 2022-07-01
#> 13 2019-05-28 2022-07-01
#> 14 2019-11-22 2022-07-01
#> 15 2020-02-11 2022-07-01
#> 16 2021-09-01 2022-07-01
#> 17 2021-11-08 2022-07-01
#> 18 2022-11-11 2022-11-11
#> 19 2023-03-03 2023-03-03

Created on 2023-07-13 with reprex v2.0.2

1 Like
3

@EconProf Sweetness.
That works like a charm.
I have been awaiting answers since posted.
Now all done and thank you so much.
Appreciated.
G

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.