Expand data set row in R

Question

I've got a table like this:

| Activation Month | Disabled Month | Month.Fee | Custr
| 21/4/2018        | N/A            | 10        |     A    
| 21/3/2018        | 21/6/2018      | 20        |     B

I want to transform this table base on validity range, in column Activation and Disabled, in order to have 1 entry per months, (assuming today is 30/11/2018) like:

Month | Enrolled  |  Activation Month | Disabled Month | Month.Fee | Cust.
  1   |     N     | 21/4/2018         | N/A            | 10        | A
  2   |     N     | 21/4/2018         | N/A            | 10        | A
  3   |     N     | 21/4/2018         | N/A            | 10        | A
  4   |     Y     | 21/4/2018         | N/A            | 10        | A
  5   |     Y     | 21/4/2018         | N/A            | 10        | A
  6   |     Y     | 21/4/2018         | N/A            | 10        | A
  7   |     Y     | 21/4/2018         | N/A            | 10        | A
  8   |     Y     | 21/4/2018         | N/A            | 10        | A
  9   |     Y     | 21/4/2018         | N/A            | 10        | A
  10  |     Y     | 21/4/2018         | N/A            | 10        | A
  11  |     Y     | 21/4/2018         | N/A            | 10        | A
  12  |     Y     | 21/4/2018         | N/A            | 10        | A
  1   |     N     | 21/3/2018         | 21/6/2018      | 10        | B
  2   |     N     | 21/3/2018         | 21/6/2018      | 10        | B
  3   |     Y     | 21/3/2018         | 21/6/2018      | 10        | B
  4   |     Y     | 21/3/2018         | 21/6/2018      | 10        | B
  5   |     Y     | 21/3/2018         | 21/6/2018      | 10        | B
  6   |     Y     | 21/3/2018         | 21/6/2018      | 10        | B
  7   |     N     | 21/3/2018         | 21/6/2018      | 10        | B
  8   |     N     | 21/3/2018         | 21/6/2018      | 10        | B
  9   |     N     | 21/3/2018         | 21/6/2018      | 10        | B
  10  |     N     | 21/3/2018         | 21/6/2018      | 10        | B
  11  |     N     | 21/3/2018         | 21/6/2018      | 10        | B
  12  |     N     | 21/3/2018         | 21/6/2018      | 10        | B

Is there any way to manage this without loops?

Instead of whining, please use your hammer power to re-open if you disagree. — Henrik, Nov 30 '18 at 15:52
Enrolled is TRUE (Y) when month number falls in range from activation month and disabled month. N/A means still active. — Ilproff_77, Nov 30 '18 at 16:09

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

One option would be

library(tidyverse)
df1 %>% 
  mutate(Enrolled = map2(Activation.Month, Disabled.Month, ~ {
            x1 <- month(dmy(.x))
            x2 <- month(dmy(.y))
            ind <- if(is.na(x2)) x1:12 else x1:x2
            1:12 %in% ind})) %>%
  unnest %>%
  mutate(Month = rep(1:12, length.out = n()))
#    Activation.Month Disabled.Month Month.Fee Custr Enrolled Month
#1         21/4/2018           <NA>        10     A    FALSE     1
#2         21/4/2018           <NA>        10     A    FALSE     2
#3         21/4/2018           <NA>        10     A    FALSE     3
#4         21/4/2018           <NA>        10     A     TRUE     4
#5         21/4/2018           <NA>        10     A     TRUE     5
#6         21/4/2018           <NA>        10     A     TRUE     6
#7         21/4/2018           <NA>        10     A     TRUE     7
#8         21/4/2018           <NA>        10     A     TRUE     8
#9         21/4/2018           <NA>        10     A     TRUE     9
#10        21/4/2018           <NA>        10     A     TRUE    10
#11        21/4/2018           <NA>        10     A     TRUE    11
#12        21/4/2018           <NA>        10     A     TRUE    12
#13        21/3/2018      21/6/2018        20     B    FALSE     1
#14        21/3/2018      21/6/2018        20     B    FALSE     2
#15        21/3/2018      21/6/2018        20     B     TRUE     3
#16        21/3/2018      21/6/2018        20     B     TRUE     4
#17        21/3/2018      21/6/2018        20     B     TRUE     5
#18        21/3/2018      21/6/2018        20     B     TRUE     6
#19        21/3/2018      21/6/2018        20     B    FALSE     7
#20        21/3/2018      21/6/2018        20     B    FALSE     8
#21        21/3/2018      21/6/2018        20     B    FALSE     9
#22        21/3/2018      21/6/2018        20     B    FALSE    10
#23        21/3/2018      21/6/2018        20     B    FALSE    11
#24        21/3/2018      21/6/2018        20     B    FALSE    12

NOTE: Here, we are using TRUE/FALSE for the 'Enrolled' instead of 'Y/N' as it becomes easier to subset with a logical column

data

df1 <- structure(list(Activation.Month = c("21/4/2018", "21/3/2018"), 
Disabled.Month = c(NA, "21/6/2018"), Month.Fee = c(10L, 20L
), Custr = c("A", "B")), class = "data.frame", row.names = c(NA, 
-2L))

Enrolled is TRUE (Y) when month number falls in range from activation month and disabled month. N/A means still active. — Ilproff_77, Nov 30 '18 at 16:09
It tooks me more than 20 minuts to figure out what hour code does :). But works like a charms — Ilproff_77, Dec 04 '18 at 21:08

Expand data set row in R

1 Answers1

data