How to populate data based on date period

Question

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

Please write out exactly what you want to do in words. For example are you asking how to drop columns, change column names or something else? — Elin, May 23 '19 at 07:55
Similar question - https://stackoverflow.com/questions/28122708/generate-rows-between-two-dates-into-a-data-frame-in-r — thelatemail, May 23 '19 at 07:56

score 2 · Accepted Answer · answered May 23 '19 at 07:48

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))

How to populate data based on date period

1 Answers1