CODEWITHSUNDEEP
rdate
kelvin
kelvin

Reputation: 65

Calculate the difference in dates based on the entries in another column

ID Date
1 2022-09-05
1 2022-09-07
1 2022-09-10
2 2022-09-16
2 2022-09-07
  1. The ID's are not unique.
  2. In the case where the ID appears more than twice then get the average of days (i.e for ID = 1 , the outcome will be mean(c(difftime(2022-09-10,2022-09-07,units="days"),mean(c(difftime(2022-09-07,2022-09-05,units="days"))))
  3. In the case where the ID has only 2 entries the just get the difference between the dates

The desired outcome for the above table should be

ID Days
1 2.5 days
2 9 days

Upvotes: 1

Views: 42

Answers (2)

Maël
Maël

Reputation: 51994

In base R:

aggregate(Date ~ ID, df, \(x) mean(abs(diff(x))))

  ID     Date
1  1 2.5 days
2  2 9.0 days

Upvotes: 3

Edward
Edward

Reputation: 18683

library(dplyr)

group_by(df, ID) %>%
  summarise(res=mean(difftime(Date, lag(Date), units="days"), na.rm=TRUE))

# A tibble: 2 × 2
     ID res      
  <dbl> <drtn>   
1     1  2.5 days
2     2 -9.0 days

Data

df <- tibble(ID=c(1,1,1,2,2),
             Date=c(as.Date("2022-09-05"),
                    as.Date("2022-09-07"),
                    as.Date("2022-09-10"),
                    as.Date("2022-09-16"),
                    as.Date("2022-09-07")))

Upvotes: 2

Related Questions