Replacing Values based on previous Years value (Revisiting)

tidyverse
1

Hi All,

Referencing the previously worked out example here -
Replacing Values based on previous Years value - tidyverse - Posit Community (rstudio.com)

Solution works fine if we have the data available for entire column. However, if we have NAs in future months, we can't fill in values for all rows. Is there a way to apply the same logic that can carry on to the extended data with no figures originally. I would like to fill in the NAs with the same logic for the remaining figures. Yes, we can create another column and use Sales_Shift, but as the data gets extended more and more, it is an issue.

We would still like to go with previous 1 year value to be filled in all future rows for Sales_Shift column using original values in data and newly received values using tlag (or any other method). Basically, entire column needs to be filled using previous year's value.

In the example below, rows are NA for Sales_Shift column starting from row 145. I am looking to have 220 in row 145 for CA, Type A in 2021 Jan using value of 2020 Jan (achieved with tlag) and so on for the remaining NAs.

Any help here would be greatly appreciated. Thanks!

library(tidyverse)
library(lubridate)

vol <- data.frame(
  Date = c("2018 Jan","2018 Jan","2018 Jan","2018 Jan",
           "2018 Feb","2018 Feb","2018 Feb","2018 Feb",
           "2018 Mar","2018 Mar","2018 Mar","2018 Mar",
           "2018 Apr","2018 Apr","2018 Apr","2018 Apr",
           "2018 May","2018 May","2018 May","2018 May",
           "2018 Jun","2018 Jun","2018 Jun","2018 Jun",
           "2018 Jul","2018 Jul","2018 Jul","2018 Jul",
           "2018 Aug","2018 Aug","2018 Aug","2018 Aug",
           "2018 Sep","2018 Sep","2018 Sep","2018 Sep",
           "2018 Oct","2018 Oct","2018 Oct","2018 Oct",
           "2018 Nov","2018 Nov","2018 Nov","2018 Nov",
           "2018 Dec","2018 Dec","2018 Dec","2018 Dec",
           "2019 Jan","2019 Jan","2019 Jan","2019 Jan",
           "2019 Feb","2019 Feb","2019 Feb","2019 Feb",
           "2019 Mar","2019 Mar","2019 Mar","2019 Mar",
           "2019 Apr","2019 Apr","2019 Apr","2019 Apr",
           "2019 May","2019 May","2019 May","2019 May",
           "2019 Jun","2019 Jun","2019 Jun","2019 Jun",
           "2019 Jul","2019 Jul","2019 Jul","2019 Jul",
           "2019 Aug","2019 Aug","2019 Aug","2019 Aug",
           "2019 Sep","2019 Sep","2019 Sep","2019 Sep",
           "2019 Oct","2019 Oct","2019 Oct","2019 Oct",
           "2019 Nov","2019 Nov","2019 Nov","2019 Nov",
           "2019 Dec","2019 Dec","2019 Dec","2019 Dec",
           "2020 Jan","2020 Jan","2020 Jan","2020 Jan",
           "2020 Feb","2020 Feb","2020 Feb","2020 Feb",
           "2020 Mar","2020 Mar","2020 Mar","2020 Mar",
           "2020 Apr","2020 Apr","2020 Apr","2020 Apr",
           "2020 May","2020 May","2020 May","2020 May",
           "2020 Jun","2020 Jun","2020 Jun","2020 Jun",
           "2020 Jul","2020 Jul","2020 Jul","2020 Jul",
           "2020 Aug","2020 Aug","2020 Aug","2020 Aug",
           "2020 Sep","2020 Sep","2020 Sep","2020 Sep",
           "2020 Oct","2020 Oct","2020 Oct","2020 Oct",
           "2020 Nov","2020 Nov","2020 Nov","2020 Nov",
           "2020 Dec", "2020 Dec","2020 Dec", "2020 Dec",
           "2021 Jan","2021 Jan","2021 Jan","2021 Jan",
           "2021 Feb","2021 Feb","2021 Feb","2021 Feb",
           "2021 Mar","2021 Mar","2021 Mar","2021 Mar",
           "2021 Apr","2021 Apr","2021 Apr","2021 Apr",
           "2021 May","2021 May","2021 May","2021 May",
           "2021 Jun","2021 Jun","2021 Jun","2021 Jun",
           "2021 Jul","2021 Jul","2021 Jul","2021 Jul",
           "2021 Aug","2021 Aug","2021 Aug","2021 Aug",
           "2021 Sep","2021 Sep","2021 Sep","2021 Sep",
           "2021 Oct","2021 Oct","2021 Oct","2021 Oct",
           "2021 Nov","2021 Nov","2021 Nov","2021 Nov",
           "2021 Dec","2021 Dec", "2021 Dec","2021 Dec"),
  
  Country = c("CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US",
              "CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US",
              "CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US",
              "CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US"),
  
  Type = c("A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B",
           "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B",
           "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B",
           "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B"
  ),
  
  Sales = c(100,110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210,100,110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210,100,110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210,100,110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210,
            220, 230,240, 250, 260, 270, 280, 290, 300, 310, 320, 330,220, 230,240, 250, 260, 270, 280, 290, 300, 310, 320, 330,20, 230,240, 250, 260, 270, 280, 290, 300, 310, 320, 330,220, 230,240, 250, 260, 270, 280, 290, 300, 310, 320, 330,
            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, 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, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)
)

vol$Date <- ym(vol$Date)

# First Option

library(statar)

vol1 <- vol %>% 
  group_by(Country, Type) %>% 
  mutate(Sales_shift = tlag(Sales, n = years(1), time = Date))
2

The performs a fill over missing/NA values, based on common country and type organised around common month ( ie. jan for all years is one group, feb for all years another)


(result <- mutate(vol,
  rn = row_number()
) |>
  arrange(Country, Type, lubridate::month(Date)) |>
  group_by(Country, Type, lubridate::month(Date)) |>
  tidyr::fill(Sales, .direction = "down") |>
  ungroup() |>
  arrange(rn) |>
  select(-last_col(), -rn))

check row 145

result |> slice(145)

# A tibble: 1 × 4
  Date       Country Type  Sales
  <date>     <chr>   <chr> <dbl>
1 2021-01-01 CA      A       220
3

Thanks @nirgrahamuk!
This is great and works perfectly.

Question -
By filling it down and by grouping it by month(Date), we are getting previous years value.
If I was to take previous month's value instead, how would that apply here as its already grouped by month. I mean how can we modify this if we would like to get previous month's value in similar scenario.

Thanks a bunch in advance!

4

This topic was automatically closed 7 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.