CODEWITHSUNDEEP
rdatedataframe
Omi Slash
Omi Slash

Reputation: 147

Fill dates and create new rows in dataframe

    rptdate       st
1   2/18/2017   2/12/2017
2   2/25/2017   2/19/2017
3   3/4/2017    2/26/2017
4   3/11/2017   3/5/2017
5   3/18/2017   3/12/2017
6   3/25/2017   3/19/2017
7   4/1/2017    3/26/2017
8   4/8/2017    4/2/2017
9   4/15/2017   4/9/2017
10  4/22/2017   4/16/2017
11  4/29/2017   4/23/2017
12  5/6/2017    4/30/2017
13  5/13/2017   5/7/2017
14  5/20/2017   5/14/2017
15  5/27/2017   5/21/2017
16  6/3/2017    5/28/2017
17  6/10/2017   6/4/2017

So basically rptdate is a bunch of Saturdays and st is each previous Sunday.

I would like to reshape this dataframe (the data is in date format) in this manner:

what I would like to do is this:

i=1
j=1
While (rptdate[i][j]>=st[i][j])
  {add a new row where rptdate[i][j+1]= rptdate[i][j] and st[i][j+1]=rptdate[i][j]+1}

So basically, my desired new dataframe should be like this:

     rptdate       st
1   2/18/2017   2/12/2017
    2/18/2017   2/13/2017
    2/18/2017   2/14/2017
    2/18/2017   2/15/2017
    2/18/2017   2/16/2017
    2/18/2017   2/17/2017
    2/18/2017   2/18/2017
2   2/25/2017   2/19/2017
    2/25/2017   2/20/2017
    2/25/2017   2/21/2017
    2/25/2017   2/22/2017
    2/25/2017   2/23/2017
    2/25/2017   2/24/2017
    2/25/2017   2/25/2017

Thank you very much for your time.

Upvotes: 2

Views: 69

Answers (3)

www
www

Reputation: 39154

Here is an example using functions from dplyr and lubridate. dt2 would be the final output.

# Create example data frame
dt <- read.table(text = "rptdate st
2/18/2017   2/12/2017
2/25/2017   2/19/2017
3/4/2017    2/26/2017
3/11/2017   3/5/2017
3/18/2017   3/12/2017
3/25/2017   3/19/2017
4/1/2017    3/26/2017
4/8/2017    4/2/2017
4/15/2017   4/9/2017
4/22/2017   4/16/2017
4/29/2017   4/23/2017
5/6/2017    4/30/2017
5/13/2017   5/7/2017
5/20/2017   5/14/2017
5/27/2017   5/21/2017
6/3/2017    5/28/2017
6/10/2017   6/4/2017",
                 header = TRUE, stringsAsFactors = FALSE)

# Load packages
library(dplyr)
library(lubridate)

# Process the data
dt2 <- dt %>%
  mutate(rptdate = mdy(rptdate), st = mdy(st)) %>%
  rowwise() %>%
  do(data_frame(rptdate = rep(.$rptdate[1], 7),
                st = seq(.$st[1], .$rptdate[1], by = 1))) %>%
  mutate(rptdate = format(rptdate, "%m/%d/%Y"),
         st = format(st, "%m/%d/%Y"))

Or you can use the map2 and unnest functions from tidyverse.

# Load packages
library(tidyverse)
library(lubridate)

# Process the data
dt2 <- dt %>%
  mutate(rptdate = mdy(rptdate), st = mdy(st)) %>%
  mutate(st = map2(st, rptdate, seq, by = 1)) %>%
  unnest() %>%
  mutate(rptdate = format(rptdate, "%m/%d/%Y"),
         st = format(st, "%m/%d/%Y"))

Upvotes: 0

quant
quant

Reputation: 4482

library(data.table)

dt <- data.table(V1=as.Date(c("2/18/2017","2/25/2017","3/4/2017","3/11/2017"),format = "%m/%d/%Y"),
                 V2=as.Date(c("2/12/2017","2/19/2017","2/26/2017","3/5/2017"),format = "%m/%d/%Y"))
for(i in 0:6){
  dt[,paste0("colomn_i",i):=V1-i]
}
dt[,V2:=NULL]
temp <- melt(dt,id.vars = "V1")
setorder(temp,V1,value)
temp[,variable:=NULL]

Even though, eventually V2, is not needed

Upvotes: 0

Sotos
Sotos

Reputation: 51582

Here is an idea via base R. You need to convert you variables to dates first. Then expand the data frame with extra 7 rows (1 week) for each date. Generate all the missing dates using seq and add them in your st variable.

d2[] <- lapply(d2, function(i) as.Date(i, format = '%m/%d/%Y'))
d3 <- d2[rep(row.names(d2), each = 7),]
d3$st<- do.call(c, Map(function(x, y)seq(x, y, by = 1), d2$st, d2$rptdate))

head(d3, 10)
#       rptdate         st
#1   2017-02-18 2017-02-12
#1.1 2017-02-18 2017-02-13
#1.2 2017-02-18 2017-02-14
#1.3 2017-02-18 2017-02-15
#1.4 2017-02-18 2017-02-16
#1.5 2017-02-18 2017-02-17
#1.6 2017-02-18 2017-02-18
#2   2017-02-25 2017-02-19
#2.1 2017-02-25 2017-02-20
#2.2 2017-02-25 2017-02-21
...

Upvotes: 1

Related Questions