CODEWITHSUNDEEP
rdatedivisionpopulate
Nicodemus Sigit Sutanto
Nicodemus Sigit Sutanto

Reputation: 151

how to do math operation in data frame and make a new column of its results?

I want to use some basic math to know how much stock we need per day. i would like to divide stock by difference of start date and end date, so i could get stock per day. I currently have data like this

 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

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

Does anyone know how to do this?

Upvotes: 2

Views: 3555

Answers (1)

tmfmnk
tmfmnk

Reputation: 39858

One tidyverse possibility could be:

df %>%
 mutate(date = map2(start_date, end_date, seq, by = "1 day")) %>%
 select(-start_date, -end_date) %>%
 unnest() %>%
 group_by(item) %>%
 mutate(stock_day = stock/n())

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

Upvotes: 1

Related Questions