Transmuting multiple columns based on columns outside .vars

user_8284887 0
Viewed 203

I have a dataframe where the first 5 columns are boolean, and the next 2 columns are a start and end date. Where the first 5 columns are equal to 1, I would like to transmute such that they are then assigned a date between the start and end dates. The 0 values can be left as 0.

Example dataframe:

df<- data.frame(A = c(1,1,0,0,1), B = c(0,1,0,0,1), C = c(0,0,1,1,0),D = c(1,0,0,0,1), E = c(1,1,1,0,0), 
                  StartDate = as.Date(c("2018-03-21","1999-02-06","2004-01-19", "2000-03-18", "1998-05-20")),
                  EndDate = as.Date(c("2020-08-02","2004-09-15","2009-07-27","2017-04-21", "2001-01-24"))

> A B C D E  StartDate    EndDate
  1 0 0 1 1 2018-03-21 2020-08-02
  1 1 0 0 1 1999-02-06 2004-09-15
  0 0 1 0 1 2004-01-19 2009-07-27
  0 0 1 0 0 2000-03-18 2017-04-21
  1 1 0 1 0 1998-05-20 2001-01-24

I would like an output where each of the 1 values are replaced by a random date between the StartDate and EndDate for each row, e.g:

> A          B          C          D          E           StartDate    EndDate
  2019-07-22 0          0          2020-01-30 2018-03-26 2018-03-21 2020-08-02
  1999-03-21 2002-04-04 0          0          1999-09-14 1999-02-06 2004-09-15
  0          0          2005-06-07 0          2008-05-28 2004-01-19 2009-07-27
  0          0          2015-10-11 0          0          2000-03-18 2017-04-21
  1999-02-05 2000-11-14 0          1999-12-19 0          1998-05-20 2001-01-24

So far I am thinking along the lines of:

For .vars = 1:5, run an ifelse() where any 1 value is replaced by a random date between StartDate and EndDate

df %>%
  transmute_at(.vars = 1:5,            
               .funs = ifelse(1,sample(seq(StartDate, EndDate, by = "day"), 1), 0) )

This however doesn't run as it cannot find StartDate and EndDate due to the code only running for cols 1:5

Any help would be appreciated!

dplyr r
2 Answers

There are a few issues you need to consider here:

  • seq() is not vectorised over from and to, so it will require that StartDate and EndDate are length 1. You can achieve this by using rowwise()
  • The replacement columns will have type date, so you will not be able to simply include zeros, as these will be coerced to 1970-01-01 (try lubridate::as_date(0)). The best alternative is probably to use NA here.
  • transmute() will drop the columns being used, i.e. it will drop StartDate and EndDate. If you want to keep them you should use mutate() instead
  • The main issue in your example is that your .funs argument is not a function. From the documentation:

A function fun, a quosure style lambda ~ fun(.) or a list of either form.

  • Scoped verbs, i.e. functions ending with _at, _if etc are superseded in favour of across() as of {dplyr} 1.0.0.

Here is an example that takes the above into account:

df %>% 
  rowwise() %>% 
  dplyr::mutate(
    across(1:5, ~lubridate::as_date(ifelse(
      . == 1, 
      sample(seq(StartDate, EndDate, by = "day"), 1), 
      NA
    )))
  ) %>% 
  ungroup()
#> # A tibble: 5 x 7
#>   A          B          C          D          E          StartDate  EndDate   
#>   <date>     <date>     <date>     <date>     <date>     <date>     <date>    
#> 1 2019-08-31 NA         NA         2019-09-30 2019-03-15 2018-03-21 2020-08-02
#> 2 2002-01-10 2001-11-25 NA         NA         2003-07-17 1999-02-06 2004-09-15
#> 3 NA         NA         2006-06-16 NA         2008-09-03 2004-01-19 2009-07-27
#> 4 NA         NA         2015-05-21 NA         NA         2000-03-18 2017-04-21
#> 5 1999-09-19 1999-08-30 NA         1999-11-04 NA         1998-05-20 2001-01-24
user_56330630

You can also use the following solution. It presents just another way of performing row-wise operations. We use pmap function from package purrr. It allows you to provide any number of arguments (here 7 as we have 7 variables) and apply a function on each row of the data frame. Just bear in mind that we refer to the value of every variable in a row by ..1 for example for the first variable A. As you might have noticed I also used ..6 and ..7 to refer to values of StartDate and EndDate in each row:

library(dplyr)
library(purrr)
library(lubridate)

df %>%
  mutate(pmap_df(., ~ ifelse(c(...)[1:5] == 1, 
                          as_date(sample(seq(..6, ..7, by = "days"))), 
                          NA))) %>%
  mutate(across(A:E, ~ as_date(.x)))


           A          B          C          D          E  StartDate    EndDate
1 2020-05-20       <NA>       <NA> 2020-03-07 2020-01-22 2018-03-21 2020-08-02
2 2000-05-22 2001-01-05       <NA>       <NA> 2002-05-29 1999-02-06 2004-09-15
3       <NA>       <NA> 2008-08-31       <NA> 2005-10-05 2004-01-19 2009-07-27
4       <NA>       <NA> 2012-07-05       <NA>       <NA> 2000-03-18 2017-04-21
5 1998-10-02 2000-05-06       <NA> 2000-09-01       <NA> 1998-05-20 2001-01-24
user_143145200
Related