CODEWITHSUNDEEP
rdatepopulate
Nicodemus Sigit Sutanto
Nicodemus Sigit Sutanto

Reputation: 151

How to populate data based on date period

I need to populate data based on date period and change the column formation, so my data can be read by Qlik Sense, a business intelligence program. So i have data below with file name "axn". This file name then will be fill in a new column named 'store'.

  promo  start date  end date  item   discount   stock
    a     30/08/19   02/09/19   ax      15%       200      
    a     30/08/19   02/09/19   bx      15%       200
    a     27/08/19   27/08/19   cx      25%       200
    a     27/08/19   27/08/19   dx      15%       200
    b     03/09/19   10/09/19   ex      15%       200
    b     04/09/19   04/09/19   fx      15%       200

to be like this:

  store promo   date    item   discount   stock
   axn   a    30/08/19   ax      15%       200      
   axn   a    31/08/19   ax      15%       200
   axn   a    01/09/19   ax      15%       200
   axn   a    02/09/19   ax      15%       200
   axn   a    30/08/19   bx      15%       200
   axn   a    31/08/19   bx      15%       200
   axn   a    01/09/19   bx      15%       200
   axn   a    02/09/19   bx      15%       200
   axn   a    27/08/19   cx      25%       200
   axn   a    27/08/19   dx      15%       200
   axn   b    03/09/19   ex      15%       200
   axn   b    04/09/19   ex      15%       200
    ... continue as above
   axn   b    09/09/19   ex      15%       200
   axn   b    10/09/19   ex      15%       200
   axn   b    04/09/19   fx      15%       200

anyone know how to do this? many thanks in advance

Upvotes: 1

Views: 106

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389155

Using tidyverse you can first convert startdate and enddate to actual dates and create a sequence between them using seq and create a new column with the file name. 

library(tidyverse)

df %>%
  mutate_at(vars(startdate, enddate), as.Date, format = "%d/%m/%y") %>%
  unnest(date = map2(startdate, enddate, seq, by = "day")) %>%
  mutate(store = 'axn')

#   promo  startdate    enddate item discount stock       date store
#1      a 2019-08-30 2019-09-02   ax      15%   200 2019-08-30   axn
#2      a 2019-08-30 2019-09-02   ax      15%   200 2019-08-31   axn
#3      a 2019-08-30 2019-09-02   ax      15%   200 2019-09-01   axn
#4      a 2019-08-30 2019-09-02   ax      15%   200 2019-09-02   axn
#5      a 2019-08-30 2019-09-02   bx      15%   200 2019-08-30   axn
#6      a 2019-08-30 2019-09-02   bx      15%   200 2019-08-31   axn
#7      a 2019-08-30 2019-09-02   bx      15%   200 2019-09-01   axn
#8      a 2019-08-30 2019-09-02   bx      15%   200 2019-09-02   axn
#9      a 2019-08-27 2019-08-27   cx      25%   200 2019-08-27   axn
#10     a 2019-08-27 2019-08-27   dx      15%   200 2019-08-27   axn
#11     b 2019-09-03 2019-09-10   ex      15%   200 2019-09-03   axn
#12     b 2019-09-03 2019-09-10   ex      15%   200 2019-09-04   axn
#13     b 2019-09-03 2019-09-10   ex      15%   200 2019-09-05   axn
#14     b 2019-09-03 2019-09-10   ex      15%   200 2019-09-06   axn
#15     b 2019-09-03 2019-09-10   ex      15%   200 2019-09-07   axn
#16     b 2019-09-03 2019-09-10   ex      15%   200 2019-09-08   axn
#17     b 2019-09-03 2019-09-10   ex      15%   200 2019-09-09   axn
#18     b 2019-09-03 2019-09-10   ex      15%   200 2019-09-10   axn
#19     b 2019-09-04 2019-09-04   fx      15%   200 2019-09-04   axn

data

df <- structure(list(promo = structure(c(1L, 1L, 1L, 1L, 2L, 2L), 
.Label = c("a", "b"), class = "factor"), startdate = structure(c(4L, 4L, 3L, 
3L, 1L, 2L), .Label = c("03/09/19", "04/09/19", "27/08/19", "30/08/19"
), class = "factor"), enddate = structure(c(1L, 1L, 4L, 4L, 3L, 
2L), .Label = c("02/09/19", "04/09/19", "10/09/19", "27/08/19"
), class = "factor"), item = structure(1:6, .Label = c("ax", 
"bx", "cx", "dx", "ex", "fx"), class = "factor"), discount = 
structure(c(1L, 1L, 2L, 1L, 1L, 1L), .Label = c("15%", "25%"), class = "factor"), 
stock = c(200L, 200L, 200L, 200L, 200L, 200L)), class = "data.frame", 
row.names = c(NA, -6L))

Upvotes: 2

Related Questions