CODEWITHSUNDEEP
rdplyr
Francisco Colina
Francisco Colina

Reputation: 399

Calulcating consecutive months in R

I would like to apply the following logic in R:

  1. I already have grou_by the data by Donor.ID
  2. The first step is to identify the Firs.Date, which is the first donation
  3. What I need is to check on each donor if there has been a donation, meaning another row which a date that is within 3 months of the first.Date, so for the example below, I would use mutate to create another column, let's call it Drop.Out and then add the logic that detects if a donation happened within 3 months of the first donation, in that case Donor.ID 1 will have a 1, and Donor.ID 2 will have a 0, simply because there was not a donation (row enrtry) within 3 months from the first date.

I know that it should be surprisingly easy, but somehow I can't get it to work.

Data Frame

Upvotes: 0

Views: 52

Answers (1)

dcsuka
dcsuka

Reputation: 2997

I used lubridate to make a duration from the first time in every group to the current time, and some basic summarization to check if any values meet the criteria. Let me know if this works.

library(dplyr)
library(tibble)
library(lubridate)

df <- tribble(
  ~Donor.id, ~Date,
  1, "2019-01-01",
  1, "2019-02-03",
  1, "2019-03-03",
  1, "2019-06-12",
  1, "2019-06-23",
  2, "2019-03-01",
  2, "2019-07-01",
  2, "2019-08-01",
) %>%
  mutate(Date = parse_date_time(Date, "%y-%m-%d"))


df %>%
  group_by(Donor.id) %>%
  mutate(Drop.Out = (first(Date) %--% Date)/dmonths(1) != 0 
  & (first(Date) %--% Date)/dmonths(1) <= 3) %>%
  summarise(Drop.Out = any(Drop.Out))

Upvotes: 1

Related Questions