How to subtract a column of date values by sys.Date() using mutate

I have this dataframe I am working with.

data <- data.frame(id = c(123,124,125,126,127,128,129,130),
           date = c("10/7/2021","10/6/2021","9/13/2021","10/18/2021","8/12/2021","9/6/2021","10/29/2021","9/6/2021"))

My goal is create a new column that tells me how many days have passed since that recorded date for each row. I'm trying to use this code but I keep getting NA days in my new column.

 data %>% 
   select(id,date) %>% 
   mutate("days_since" = as.Date(Sys.Date()) - as.Date(date,format="%Y-%m-%d"))

   id       date days_since
1 123  10/7/2021    NA days
2 124  10/6/2021    NA days
3 125  9/13/2021    NA days
4 126 10/18/2021    NA days
5 127  8/12/2021    NA days
6 128   9/6/2021    NA days
7 129 10/29/2021    NA days
8 130   9/6/2021    NA days

What am I doing wrong? Thank you for any feedback.

CodePudding user response：

We can use the lubridate package. It makes type conversion and operations with dates much easier. In your code, the as.Date(date) step was problematic because the format was wrong.

library(dplyr)
library(lubridate)

data %>% mutate("days_since" = Sys.Date() - mdy(date))

   id       date days_since
1 123  10/7/2021         28
2 124  10/6/2021         29
3 125  9/13/2021         22
4 126 10/18/2021         17
5 127  8/12/2021         23
6 128   9/6/2021         29
7 129 10/29/2021          6
8 130   9/6/2021         29

Thanks, @Karthik S for the simplification

CodePudding user response：

it is also easily done, using base r and a simple "-". This gives back the difference in days:

data <- data.frame(id = c(123,124,125,126,127,128,129,130),
                   date = c("2021-10-10","2021-10-06","2021-09-13","2021-10-18","2021-08-12","2021-09-06","2021-10-29","2021-09-06"))

data$date <- as.Date(data$date)
data$sys_date <- Sys.Date()

data$sysDate_to_date <- data$sys_date -data$date