CODEWITHSUNDEEP
rdatedatetime
PeatyBoWeaty
PeatyBoWeaty

Reputation: 31

R count difference in days based on column value

I'm trying to count the difference in dates from a single column, based on another columns value.

This is the result I'm looking for

enter image description here

Upvotes: 0

Views: 219

Answers (2)

Reza
Reza

Reputation: 2025

Try this

library('dplyr')
df <- data.frame(id = c(1, 2, 3, 1, 2, 3), 
                Date = c('1/1/2020', '1/3/2020','1/1/2020','1/7/2020','1/6/2020','1/5/2020'))

df %>% mutate(Date = as.Date(Date, format='%m/%d/%Y')) %>%
  group_by(id) %>%
  mutate(DIFF = Date - lag(Date))

Upvotes: 1

Ben Norris
Ben Norris

Reputation: 5747

Here is a way using dplyr and lubridate (needed to make the dates behave when subtracting). It looks like you want the calculation to determine the number of days between the dates in a group by ID and the earliest date for that ID.

library(dplyr)
library(lubridate)

df %>%
  mutate(Date = dmy(Date)) %>%
  group_by(ID) %>%
  mutate(Diff = Date - min(Date))

If you want to have NA instead of 0, you can do the following:

df %>%
  mutate(Date = dmy(Date)) %>%
  group_by(ID) %>%
  mutate(Diff = if_else(Date == min(Date), NA_integer_, Date - min(Date))

Upvotes: 0

Related Questions