Help with Replacing Values based on another Column and historical values

tidyverse
1

Hello,

I have a data where we are tracking figures for different models on monthly basis. These models are based on the month name. In my sample data here, Model column represents that and 202304 is the latest model and 202303 is the previous month's model and so on.

We are tracking forecasts on this table for next 24 months ahead. In this example, I am showing for next 3 months represented by column "Next Month" as 1, 2, and 3. 0 represents current month (202304) and -1, -2, -3, and so on represents previous months (limiting only to last 3 months for this example, however, it goes back to all historical values)

At the moment we are interested in replacing the current month's value ie where Next Month is 0 to the value of previous Model's next month forecast. So, for Model 202304 and for its Next Month Column at 0 and Account Date at 202304 should have values from previous model 202303 from its next month's forecast at 1 and Account Date at 202304. Ideally we would like to replace all the historical values where Next Month is 0, -1, -2, etc to its Previous Models of 202303, 202302, 202301 and so on. But for now, even if we can just replace current month's value at 0 to previous model's value, that would work for the time being.

# Sample Data
library(tidyverse)
library(lubridate)
library(datapasta)

vol <- data.frame(
  stringsAsFactors = FALSE,
                    Model = c(202303L,202302L,
                              202304L,202302L,202302L,202302L,202302L,
                              202302L,202302L,202303L,202303L,202303L,202303L,
                              202303L,202303L,202304L,202304L,202304L,
                              202304L,202304L,202304L,202303L,202303L,202303L,
                              202303L,202303L,202302L,202302L,202302L,
                              202302L,202304L,202304L,202304L,202304L,202303L,
                              202302L,202302L,202304L,202304L,202303L,202304L,
                              202302L,202304L,202304L,202304L,202304L,
                              202302L,202302L,202302L,202302L,202303L,202303L,
                              202303L,202303L,202302L,202303L,202304L,
                              202304L,202303L,202302L,202302L,202304L,202302L,
                              202302L,202302L,202304L,202302L,202303L,
                              202303L,202303L,202303L,202304L,202304L,202304L,
                              202302L,202303L,202303L,202304L,202304L,
                              202304L,202304L,202304L,202302L,202302L,202302L,
                              202302L,202303L,202303L,202303L,202303L,202304L,
                              202304L,202304L,202303L,202303L,202303L,
                              202302L,202302L,202302L,202302L,202302L,202302L,
                              202303L,202303L,202304L,202304L,202303L,
                              202304L,202302L,202302L,202303L,202303L,202304L,
                              202304L,202304L,202302L,202303L,202303L,
                              202303L,202303L,202302L,202302L,202302L,202304L,
                              202304L,202304L),
             Account.Date = c(202303L,202302L,
                              202304L,202211L,202212L,202301L,202303L,
                              202304L,202305L,202212L,202301L,202302L,202304L,
                              202305L,202306L,202301L,202302L,202303L,
                              202305L,202306L,202307L,202303L,202212L,202304L,
                              202305L,202306L,202212L,202303L,202304L,
                              202305L,202303L,202305L,202306L,202307L,202301L,
                              202211L,202301L,202301L,202304L,202302L,202302L,
                              202302L,202303L,202304L,202301L,202302L,
                              202212L,202302L,202211L,202301L,202212L,202302L,
                              202301L,202303L,202302L,202302L,202302L,
                              202304L,202301L,202301L,202211L,202301L,202212L,
                              202301L,202302L,202304L,202211L,202212L,
                              202302L,202301L,202303L,202302L,202301L,202303L,
                              202212L,202303L,202212L,202303L,202302L,
                              202303L,202301L,202304L,202301L,202212L,202302L,
                              202211L,202303L,202212L,202302L,202301L,202306L,
                              202307L,202305L,202304L,202306L,202305L,
                              202305L,202303L,202304L,202305L,202303L,202304L,
                              202306L,202304L,202305L,202307L,202305L,
                              202306L,202304L,202305L,202306L,202305L,202306L,
                              202305L,202307L,202303L,202304L,202305L,
                              202306L,202304L,202305L,202303L,202304L,202305L,
                              202306L,202307L),
                 Category = c("Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A",
                              "Category A","Category A","Category A",
                              "Category A","Category A","Category A","Category A"),
             Product.Type = c("Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A","Product A","Product A",
                              "Product A","Product A"),
                  Country = c("USA","USA",
                              "USA","USA","USA","USA","USA","USA","USA",
                              "USA","USA","USA","USA","USA","USA","USA","USA",
                              "USA","USA","USA","USA","Canada","Canada",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","USA",
                              "USA","USA","USA","USA","USA","USA","USA",
                              "USA","USA","USA","USA","Canada","Canada",
                              "Canada","Canada","USA","USA","USA","USA","USA",
                              "USA","USA","USA","USA","USA","USA","USA",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","USA","USA",
                              "USA","USA","USA","USA","USA","USA","USA",
                              "Canada","Canada","Canada","Canada","Canada",
                              "Canada","Canada","Canada","Canada","USA",
                              "USA","USA","USA","USA","USA","USA","USA",
                              "USA"),
                 Shipment = c("Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Shipped","Shipped","Shipped",
                              "Shipped","Shipped","Shipped","Shipped","Shipped",
                              "Shipped","Shipped","Shipped","Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Shipped","Shipped","Shipped",
                              "Shipped","Shipped","Shipped","Shipped",
                              "Shipped","Shipped","Shipped","Shipped","Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped","Shipped",
                              "Shipped","Shipped","Shipped","Shipped",
                              "Shipped","Shipped","Shipped","Shipped","Shipped",
                              "Shipped","Shipped","Shipped","Shipped","Shipped",
                              "Shipped","Shipped","Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped","Not Shipped","Not Shipped",
                              "Not Shipped"),
                   Volume = c(NA,NA,NA,NA,
                              NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                              NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                              NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                              90L,108L,105L,92L,113L,92L,166L,105L,113L,
                              92L,105L,90L,0L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,122L,93L,111L,104L,121L,110L,
                              111L,93L,105L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,0L),
                    Price = c(NA,NA,NA,NA,
                              NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                              NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                              NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
                              107L,103L,112L,111L,113L,111L,115L,112L,
                              113L,110L,112L,106L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,106L,111L,103L,102L,104L,
                              102L,103L,107L,103L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,
                              0L,0L,0L,0L,0L,0L,0L),
                     Date = c("3/1/2023 0:00",
                              "2/1/2023 0:00","4/1/2023 0:00","11/1/2022 0:00",
                              "12/1/2022 0:00","1/1/2023 0:00",
                              "3/1/2023 0:00","4/1/2023 0:00","5/1/2023 0:00",
                              "12/1/2022 0:00","1/1/2023 0:00","2/1/2023 0:00",
                              "4/1/2023 0:00","5/1/2023 0:00","6/1/2023 0:00",
                              "1/1/2023 0:00","2/1/2023 0:00","3/1/2023 0:00",
                              "5/1/2023 0:00","6/1/2023 0:00","7/1/2023 0:00",
                              "3/1/2023 0:00","12/1/2022 0:00","4/1/2023 0:00",
                              "5/1/2023 0:00","6/1/2023 0:00","12/1/2022 0:00",
                              "3/1/2023 0:00","4/1/2023 0:00","5/1/2023 0:00",
                              "3/1/2023 0:00","5/1/2023 0:00","6/1/2023 0:00",
                              "7/1/2023 0:00","1/1/2023 0:00","11/1/2022 0:00",
                              "1/1/2023 0:00","1/1/2023 0:00","4/1/2023 0:00",
                              "2/1/2023 0:00","2/1/2023 0:00",
                              "2/1/2023 0:00","3/1/2023 0:00","4/1/2023 0:00",
                              "1/1/2023 0:00","2/1/2023 0:00","12/1/2022 0:00",
                              "2/1/2023 0:00","11/1/2022 0:00","1/1/2023 0:00",
                              "12/1/2022 0:00","2/1/2023 0:00","1/1/2023 0:00",
                              "3/1/2023 0:00","2/1/2023 0:00","2/1/2023 0:00",
                              "2/1/2023 0:00","4/1/2023 0:00","1/1/2023 0:00",
                              "1/1/2023 0:00","11/1/2022 0:00","1/1/2023 0:00",
                              "12/1/2022 0:00","1/1/2023 0:00","2/1/2023 0:00",
                              "4/1/2023 0:00","11/1/2022 0:00","12/1/2022 0:00",
                              "2/1/2023 0:00","1/1/2023 0:00",
                              "3/1/2023 0:00","2/1/2023 0:00","1/1/2023 0:00",
                              "3/1/2023 0:00","12/1/2022 0:00","3/1/2023 0:00",
                              "12/1/2022 0:00","3/1/2023 0:00","2/1/2023 0:00",
                              "3/1/2023 0:00","1/1/2023 0:00","4/1/2023 0:00",
                              "1/1/2023 0:00","12/1/2022 0:00","2/1/2023 0:00",
                              "11/1/2022 0:00","3/1/2023 0:00","12/1/2022 0:00",
                              "2/1/2023 0:00","1/1/2023 0:00","6/1/2023 0:00",
                              "7/1/2023 0:00","5/1/2023 0:00","4/1/2023 0:00",
                              "6/1/2023 0:00","5/1/2023 0:00","5/1/2023 0:00",
                              "3/1/2023 0:00","4/1/2023 0:00","5/1/2023 0:00",
                              "3/1/2023 0:00","4/1/2023 0:00","6/1/2023 0:00",
                              "4/1/2023 0:00","5/1/2023 0:00",
                              "7/1/2023 0:00","5/1/2023 0:00","6/1/2023 0:00",
                              "4/1/2023 0:00","5/1/2023 0:00","6/1/2023 0:00",
                              "5/1/2023 0:00","6/1/2023 0:00","5/1/2023 0:00",
                              "7/1/2023 0:00","3/1/2023 0:00","4/1/2023 0:00",
                              "5/1/2023 0:00","6/1/2023 0:00","4/1/2023 0:00",
                              "5/1/2023 0:00","3/1/2023 0:00","4/1/2023 0:00",
                              "5/1/2023 0:00","6/1/2023 0:00","7/1/2023 0:00"),
               Next.Month = c(0L,0L,0L,-3L,
                              -2L,-1L,1L,2L,3L,-3L,-2L,-1L,1L,2L,3L,
                              -3L,-2L,-1L,1L,2L,3L,0L,-3L,1L,2L,3L,-2L,
                              1L,2L,3L,-1L,1L,2L,3L,-2L,-3L,-1L,-3L,
                              0L,-1L,-2L,0L,-1L,0L,-3L,-2L,-2L,0L,-3L,
                              -1L,-3L,-1L,-2L,0L,0L,-1L,-2L,0L,-2L,
                              -1L,-3L,-3L,-2L,-1L,0L,0L,-3L,-3L,-1L,-2L,
                              0L,-2L,-3L,-1L,-2L,0L,-3L,-1L,-2L,-1L,
                              -3L,0L,-1L,-2L,0L,-3L,0L,-3L,-1L,-2L,2L,
                              3L,1L,1L,3L,2L,3L,1L,2L,3L,1L,2L,3L,
                              1L,1L,3L,2L,2L,2L,3L,3L,2L,2L,1L,3L,1L,
                              1L,2L,3L,1L,3L,1L,2L,1L,2L,3L)
       )

image
image2102×307 207 KB

For instance, Following to be changed as follows:

image
image2112×319 215 KB

Thanks for your help in advance!!

Regards,
KS

2

How can we use if condition here as to for any selected Model, if Next Month = 0 and Account Date = Model, then replace values for Volume & Price with Previous Model's values where its Next Month's value is 1 and its Account Date = Model + 1

So, for Model 202304, when Next Month = 0, then Volume & Price for Model 202304 should have values from previous model 202303 where extracted values are coming from Volume & Price when Account Date = 202304 and Next Month = 1

And when Next Month = -1 for Model 202304, values should come from 202302 Model where its Next Month = 1 & Account Date = 202303.

And so on for -2, -3, etc.

We are trying to retain previously forecasted values from previous models.

This should happen for any selected Model. So, the output will be same table, but with replacements as suggested above.

Thanks!

3

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.