CODEWITHSUNDEEP
rdplyr
RL_Pug
RL_Pug

Reputation: 867

How to subtract a column of date values by sys.Date() using mutate - tidyverse/dplyr? R

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.

Upvotes: 0

Views: 466

Answers (2)

LGe
LGe

Reputation: 516

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

Upvotes: 0

GuedesBF
GuedesBF

Reputation: 9868

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

Upvotes: 2

Related Questions