I do have a problem, that I can't seem to solve efficiently.
Say the result of my loop is the following (which is made up).
library(tidyverse)
mytib <- tribble(
~year, ~month, ~shop_id, ~inventory,
2019, 01, "A", 200,
2019, 01, "B", 300,
2019, 01, "C", 240,
2019, 05, "A", 250,
2019, 05, "B", 400,
2019, 05, "D", 400,
2019, 08, "A", 300,
2019, 08, "B", 250,
2019, 08, "C", 200,
2019, 11, "A", 300,
2019, 11, "E", 250,)
That is, I only get results for January, May, August and November (because data is only available for these dates). However, I would like to carry values forward for February, March, June(those would get the January values. June and July will get the values from May and so on. I can't use "fill", since R doesn't "know" that there is February, March...etc (and I cannot "mutate" these months into "mytib".)
I came up with a solution which unfortunately is highly inefficient and prone to errors. The solution (in words) is the following: Create a grid with months from 1:12, and all unique shop_ids (see code below), then create separate vectors with the shops I observe in January, May, August and November.
Then group_split the grid (into a list) by month and year. Then reduce all unique shopids to those I observe. Join them alltogher (using dplyr::join_all), then left-join the original tibble to the reduced grid, and finally carry forward (.direction="down") observed values, so that I end up with a tibbe "result", which is what I want.
Although I reach my desired goal, I wonder whether there is a solution that is much more efficient and less prone to errors than my bumbling approach. Any help or hint is highly appreciated.
Ps. Please don't be too harsh to me, as I'm still pretty new to R.
Here is the complete code:
mytib <- tribble(
~year, ~month, ~shop_id, ~inventory,
2019, 01, "A", 200,
2019, 01, "B", 300,
2019, 01, "C", 240,
2019, 05, "A", 250,
2019, 05, "B", 400,
2019, 05, "D", 400,
2019, 08, "A", 300,
2019, 08, "B", 250,
2019, 08, "C", 200,
2019, 11, "A", 300,
2019, 11, "E", 250,)
grid <- expand.grid(year = 2019,
month = 1:12,
shop_id = unique(mytib$shop_id))
grid
jan2019 <- mytib %>% filter(year == 2019 & month ==01)
jan2019 <- jan2019$shop_id
may2019 <- mytib %>% filter(year == 2019 & month == 05)
may2019 <- may2019$shop_id
aug2019 <- mytib %>% filter(year == 2019 & month == 08)
aug2019 <-aug2019$shop_id
nov2019 <- mytib %>% filter(year == 2019 & month == 11)
nov2019 <- nov2019$shop_id
my_list <- grid %>% group_by(year, month) %>% group_split()
my_list
my_list[[1]] <- my_list[[1]][my_list[[1]]$shop_id %in% jan2019,] ; my_list[[1]]
my_list[[2]] <- my_list[[2]][my_list[[2]]$shop_id %in% jan2019,] ; my_list[[2]]
my_list[[3]] <- my_list[[3]][my_list[[3]]$shop_id %in% jan2019,] ; my_list[[3]]
my_list[[4]] <- my_list[[4]][my_list[[4]]$shop_id %in% jan2019,] ; my_list[[4]]
my_list[[5]] <- my_list[[5]][my_list[[5]]$shop_id %in% may2019,] ; my_list[[5]]
my_list[[6]] <- my_list[[6]][my_list[[6]]$shop_id %in% may2019,] ; my_list[[6]]
my_list[[7]] <- my_list[[7]][my_list[[7]]$shop_id %in% may2019,] ; my_list[[7]]
my_list[[8]] <- my_list[[8]][my_list[[8]]$shop_id %in% aug2019,] ; my_list[[8]]
my_list[[9]] <- my_list[[9]][my_list[[9]]$shop_id %in% aug2019,] ; my_list[[9]]
my_list[[10]]<- my_list[[10]][my_list[[10]]$shop_id %in% aug2019,];my_list[[10]]
my_list[[11]]<- my_list[[11]][my_list[[11]]$shop_id %in% nov2019,];my_list[[11]]
my_list[[12]]<- my_list[[12]][my_list[[12]]$shop_id %in% nov2019,];my_list[[12]]
result <- plyr::join_all(my_list, type="full")
result
result <- left_join(result, mytib, by=c("year", "month", "shop_id"))
result %>% group_by(shop_id) %>% fill(inventory,.direction = "down") %>% print(n=35)