I have a data frame like DF below which will be imported directly from the database (as tibble).
library(tidyverse) library(lubridate) date_until <- dmy("31.05.2019") date_val <- dmy("30.06.2018") DF <- data.frame( date_bal = as.Date(c("2018-04-30", "2018-05-31", "2018-06-30", "2018-05-31", "2018-06-30")), department = c("A","A","A","B","B"), amount = c(10,20,30,40,50) ) DF <- DF %>% as_tibble() DF
It represents the amount of money spent by each department in a specific month. My task is to project how much money will be spent by each department in the following months until a specified date in the future (in this case date_until=31.05.2019)
I would like to use tidyverse in order to generate additional rows for each department where the first column date_bal would be a sequence of dates from the last one from "original" DF up until date_until which is predefined. Then I would like to add additional column called "DIFF" which would represent the difference between DATE_BAL and DATE_VAL, where DATE_VAL is also predefined. My final result would look like this:
I have managed to do this in the following way:
a) first filter data from DF for department A
b) Create another DF2 by populating it with date sequence from min(dat_bal) to date_until from a)
c) Merge data frames from a) and b) and then add calculated columns using mutate
Since I will have to repeat this procedure for many departments I wonder if it's possible to add rows (create date sequence) in existing DF (without creating a second DF and then merging).
Thanks in advance for your help and time.