Separate dates by month and add rows if necessary

Question

I have a table:

Name| Start     | Finish    |
----|-----------|-----------|
A   |2015-01-22 |2015-02-04 |
B   |2015-01-02 |2015-01-10 |
A   |2015-01-22 |2015-02-14 |
B   |2015-01-02 |2015-02-10 |

I need to interrupt the periods by month. If the period starts in a month and ends in the next, then I need to split it into two periods. If the period begins and ends in the same month, then it must be what it is. Let the estimated period may not contain more than one first day of the month. In other words, each line can be divided into no more than two lines. Finish (end of period) is always greater than Start.

What I want to get:

Name| Start     | Finish    |
----|-----------|-----------|
A   |2015-01-22 |2015-01-31 |
A   |2015-02-01 |2015-02-04 |
A   |2015-01-22 |2015-01-31 |
A   |2015-02-01 |2015-02-14 |
B   |2015-01-02 |2015-01-10 |
B   |2015-01-02 |2015-01-31 |
B   |2015-02-01 |2015-02-10 |

The order of the output lines is not a question.

Here is the table code:

Name = c("A", "B", "A", "B")
Start = c(as.Date("2015-01-22"), as.Date("2015-01-02"), as.Date("2015-01-22"), as.Date("2015-01-02"))
Finish = c(as.Date("2015-02-04"), as.Date("2015-01-10"), as.Date("2015-02-14"), as.Date("2015-02-10"))
df = data.frame(Name, Start, Finish)

Any suggestion how to do this?

Answer 1

. Name , . , , .. 1:nrow(df), df$Name by. .

by, , . (eom) "", "" . rbind.

library(zoo)

do.call("rbind", by(df, 1:nrow(df), function(x) with(x, {
   eom <- as.Date(as.yearmon(Start), frac = 1)
   if (eom < Finish)
      data.frame(Name, Start = c(Start, eom+1), Finish = c(eom, Finish))
   else x
})))

:

    Name      Start     Finish
1.1    A 2015-01-22 2015-01-31
1.2    A 2015-02-01 2015-02-04
2      B 2015-01-02 2015-01-10
3.1    A 2015-01-22 2015-01-31
3.2    A 2015-02-01 2015-02-14
4.1    B 2015-01-02 2015-01-31
4.2    B 2015-02-01 2015-02-10

Answer 2

Edit:

:

require(dplyr)
require(zoo)

df %>%
  filter(Finish>as.Date(as.yearmon(Start),frac=1)) %>%
  group_by(Name) %>%
  do(rbind(.,c(.$Name,
               paste(as.Date(as.yearmon(.$Start),frac=1)+1),
               .$Finish))) %>%
  mutate(Finish:=ifelse(as.Date(as.yearmon(Start),frac=1)<Finish,
                        paste(as.Date(as.yearmon(Start),frac=1)),Finish))

:

   Name      Start     Finish
1     A 2015-01-22 2015-01-31
2     A 2015-02-01 2015-02-04
3     B 2015-03-02 2015-03-31
4     B 2015-04-01 2015-04-10

:

require(data.table)
df <- fread("Name Start      Finish    
             A    2015-01-22 2015-02-01 
             B    2015-03-02 2015-04-01")

Answer 3

R:

idx <- with(df, format(Finish, "%Y-%m") > format(Start, "%Y-%m"))

rbind(df[!idx,],
   transform(df[idx,], Finish = as.Date(paste0(format(Finish, "%Y-%m"), "-01"))-1),
   transform(df[idx,], Start  = as.Date(paste0(format(Finish, "%Y-%m"), "-01"))))

#   Name      Start     Finish
#2     B 2015-01-02 2015-01-10
#1     A 2015-01-22 2015-01-31
#3     A 2015-01-22 2015-01-31
#4     B 2015-01-02 2015-01-31
#11    A 2015-02-01 2015-02-04
#31    A 2015-02-01 2015-02-14
#41    B 2015-02-01 2015-02-10