Using slider to get next months prices in dataset

[Ambiflextrous]

I have a dataset with columns: date, price, has_sale.

How to:
On the days that have sale (has_sale == TRUE) get the prices of the next month and store it in next_month_sales

I've written the code:

library (tidyverse)
library(lubridate)
library(slider)


dataset %>%
group_by(year = year(date),month = month(date)) %>%
mutate(
next_month = ifelse(month != 12, month + 1, 1),
next_year = ifelse(month == 12, year + 1, year),
next_month_sales = slide(high, ~ .x[next_month], . before = -1, .after = Inf)
)

What I want is something like this:
year month price close next_month_sales
1 2019 1 16.179 16.207, 15.999, 15.930
2 2019 2 16.207 15.629, 15.576
3 2019 2 15.999 15.629, 15.576
4 2019 2 15.930 15.629, 15.576
5 2019 3 15.629 NA
6 2019 3 15.576 NA

Any help is appreciated

[Grageas]

Hi, Ambiflextrous. I don't know if you've solved your problem, but as the book "R For Data Science" says, we can use our tools, so I will try to tell you what AI answers to your problem:

RCopy code:

library(tidyverse)
library(lubridate)
library(slider)

# Assuming 'dataset' is the name of your dataset

dataset %>%
  filter(has_sale == TRUE) %>%
  group_by(year = year(date), month = month(date)) %>%
  mutate(
    next_month = if_else(month != 12, month + 1, 1),
    next_year = if_else(month == 12, year + 1, year)
  ) %>%
  ungroup() %>%
  left_join(dataset %>% 
              filter(has_sale == FALSE) %>%
              mutate(
                year = if_else(month == 12, year(date) + 1, year(date)),
                month = if_else(month == 12, 1, month(date))
              ) %>%
              select(-has_sale),
            by = c("next_year" = "year", "next_month" = "month")) %>%
  group_by(year, month, price) %>%
  summarize(next_month_sales = list(na.omit(price))) %>%
  mutate(next_month_sales = if_else(length(next_month_sales) == 0, NA, next_month_sales)) %>%
  ungroup()

This code first filters rows where has_sale == TRUE, then groups the data by year and month. Next, it calculates the next month and year. After that, it performs a left join with the original data, but only with the rows where has_sale == FALSE, using the previously calculated next months and years. It then regroups the data by year, month, and price, and summarizes the next month sales as a list. Finally, it adjusts rows with null future sales and removes the grouping.Preformatted text