在R语言数据框中查找同类别分组的上一日期对应值

Solution Approach & Code #

Let's break down how to solve both requirements efficiently using tidyverse tools:

Step 1: Understand the Data & Correct DayCategory Assignment

First, ensure the DayCategory is assigned correctly based on your rules (Monday = LU, Tue-Wed-Thu = MA-MI-JU, Fri = VI, Sat = SA, Sun = DO). We'll use case_when for readability.

Step 2: Add PreviousDaySameDayCategory Column

This column needs the most recent prior date that belongs to the same DayCategory (regardless of Holidays). We can do this by:

• Creating a unique date-DayCategory dataframe
• Grouping by DayCategory and using lag() to get the prior date in each group
• Joining this back to the original dataframe

Step 3: Add PreviousMatchingValue Column

This requires the value from the most recent prior date that matches same Hour, same DayCategory, and same Holidays. The key insight here is grouping by all three attributes—this ensures we only consider rows that meet all conditions, and lag() will pull the value from the last valid entry in that group.

Full R Code #

library(tidyverse)
library(lubridate)

# Generate the dataset as provided
Date.POSIXct <- seq(as.POSIXct("2018-05-01"), as.POSIXct("2018-05-31"), "hour")
mydf <- as.data.frame(Date.POSIXct)
mydf$Date <- as.Date(substr(as.character(mydf$Date.POSIXct), 1, 10))
mydf$WeekDay <- substr(toupper(weekdays(mydf$Date)), 1, 2)

# Correctly assign DayCategory per your rules
mydf$DayCategory <- case_when(
  mydf$WeekDay == "LU" ~ "LU",
  mydf$WeekDay %in% c("MA", "MI", "JU") ~ "MA-MI-JU",
  mydf$WeekDay == "VI" ~ "VI",
  mydf$WeekDay == "SA" ~ "SA",
  mydf$WeekDay == "DO" ~ "DO"
)

mydf$Hour <- hour(mydf$Date.POSIXct)
mydf$Holidays <- c(rep(0, 24*7), rep(1, 24*7), rep(0, 24*16 + 1))
set.seed(123)
mydf$myvalue <- sample.int(101, size = nrow(mydf), replace = TRUE)

# 1. Add PreviousDaySameDayCategory column
dates_summary <- mydf %>%
  distinct(Date, DayCategory) %>%
  arrange(Date) %>%
  group_by(DayCategory) %>%
  mutate(PreviousDaySameDayCategory = lag(Date)) %>%
  ungroup()

mydf <- mydf %>%
  left_join(dates_summary, by = c("Date", "DayCategory"))

# 2. Add PreviousMatchingValue column (matches Hour, DayCategory, Holidays)
mydf <- mydf %>%
  arrange(Date.POSIXct) %>%
  group_by(DayCategory, Holidays, Hour) %>%
  mutate(PreviousMatchingValue = lag(myvalue)) %>%
  ungroup()

# Inspect the first 10 rows to verify
head(mydf, 10)

Explanation #

• PreviousDaySameDayCategory: By grouping unique dates by DayCategory, lag(Date) gives the last date before the current one that falls into the same category. This works because we sorted the dates chronologically first.
• PreviousMatchingValue: Grouping by DayCategory, Holidays, and Hour ensures we only look at rows that meet all three conditions. lag(myvalue) pulls the value from the most recent prior entry in that group—automatically skipping dates in the same category but different holidays, which aligns with your requirement to "keep looking back" until a matching holiday status is found.

This method is efficient and scalable, even for larger datasets, since it uses optimized window functions instead of iterative lookups.

内容的提问来源于stack exchange，提问作者alvaropr