Generate rows between two dates into a data frame in R [closed]

Question

I've just started learning R for a short time. I have the following table

Name      stDte      edDte  
A    2010-05-01 2014-12-01  
B    2013-06-01 2014-02-01  

I need to get it transformed into a table like this

Name   Dte
A      2010-05-01
A      2010-06-01
A      2010-07-01
...
A      2014-12-01
B      2013-06-01
B      2013-07-01
...
B      2014-02-01

I'm thinking about using a combination of a 'for' loop for together with rbind but I'm not sure how to go about it. Would appreciate any suggestions on how to do that. Thanks in advance for the guidance

Answer 1

Since you don't state otherwise, this answer assumes the stDte and edDte columns are both of "Date" class.

In base R you could use Map() to create the sequence of dates, then data.frame to bring the new data frame together after creating the new Name column with rep.int().

M <- Map(seq, df$stDte, df$edDte, by = "month")
df2 <- data.frame(
    Name = rep.int(df$Name, vapply(M, length, 1L)), 
    Dte = do.call(c, M)
)    
str(df2)
# 'data.frame':    65 obs. of  2 variables:
#  $ Name: Factor w/ 2 levels "A","B": 1 1 1 1 1 1 1 1 1 1 ...
#  $ Dte : Date, format: "2010-05-01" "2010-06-01" ...
head(df2, 3)
#   Name        Dte
# 1    A 2010-05-01
# 2    A 2010-06-01
# 3    A 2010-07-01
tail(df2, 3)
#    Name        Dte
# 63    B 2013-12-01
# 64    B 2014-01-01
# 65    B 2014-02-01

Or you can use the data.table package and do

library(data.table)
setDT(df)[, .(Dte = seq(stDte, edDte, by = "month")), by = Name]

Answer 2

You cn build a series of dataframes for each row and then rbind them together. The argument recycling property of the dataframe function will repeat the value of 'Names' as many times as necessary:

do.call(rbind, 
        lapply(seq(nrow(dat)), function(x){
            data.frame(Name=dat[x,"Name"], 
            Dte=seq(as.Date(dat[x,"stDte"]), 
                    as.Date(dat[x,"edDte"]) ,by="month") ) } ))