I have the following data:
Date Price
2-Jul-13 20
3-Jul-13 22
4-Jul-13 30
5-Jul-13 18
8-Jul-13 12
9-Jul-13 24
10-Jul-13 28
11-Jul-13 14
The output has to be:
Date Price day_diff week_diff
2-Jul-13 20 0 4
3-Jul-13 22 2
4-Jul-13 30 8
5-Jul-13 18 -12
8-Jul-13 12 -6
9-Jul-13 24 12 -4
10-Jul-13 28 4
11-Jul-13 14 -14
12-Jul-13 18 4
15-Jul-13 12 -6
16-Jul-13 20 8 ....
....
To calculate day_diff first value is taken as 0 and then 22-20 = 2 and so on. To calculate week_diff the next week starts from 9-Jul-13 so 24-20 = 4 similarly next week starts from 16-July-13 so 20-24 = -4 and so on...
Please help me with this.
Please provide your data with dput() in future.
The data:
read.table(
text = " Date Price
2-Jul-13 20
3-Jul-13 22
4-Jul-13 30
5-Jul-13 18
8-Jul-13 12
9-Jul-13 24
10-Jul-13 28
11-Jul-13 14
12-Jul-13 18
15-Jul-13 12
16-Jul-13 20 ",
header = T
) -> df
Solution:
library(tidyverse)
library(lubridate)
df %>%
mutate(
Date = dmy(Date),
day_diff = Price - lag(Price),
week_date = floor_date(Date, unit = 'week', week_start = 2),
week_number = ifelse(Date == week_date, Price, 0),
week_diff = lead(week_number, 5) - week_number
) %>%
select(Date, Price, day_diff, week_diff)-> output_df
Output
> output_df
Date Price day_diff week_diff
1 2013-07-02 20 NA 4
2 2013-07-03 22 2 0
3 2013-07-04 30 8 0
4 2013-07-05 18 -12 0
5 2013-07-08 12 -6 0
6 2013-07-09 24 12 -4
7 2013-07-10 28 4 NA
8 2013-07-11 14 -14 NA
9 2013-07-12 18 4 NA
10 2013-07-15 12 -6 NA
11 2013-07-16 20 8 NA