在R语言中查找列中大于指定日期的首个日期

Hey there! Let's get your code sorted to find the first Date2 value that's greater than each corresponding Date1. I'll offer two approaches that build on your existing work with dplyr and sqldf.

First, let's recap your core need:

For every Date1 in your monthly_sequence_03 table, find the smallest Date2 value from monthly_sequence_06 that's larger than Date1, and add this as a new column to your final dataset.

Approach 1: Using dplyr (Tidyverse Style) #

This approach uses tidyverse functions to join, filter, and select the first matching Date2 in a readable way:

library(dplyr)

# Generate your base datasets (cleaned up a bit for readability)
monthly_sequence_03 <- data.frame(
  Pnp = 'A100',
  Frequency = 3,
  Duration = 'Month',
  Date1 = seq(as.Date('2020-01-01'), as.Date('2025-06-30'), by = '3 months')
)

monthly_sequence_06 <- data.frame(
  Pnp = 'A100',
  Frequency = 6,
  Duration = 'Month',
  Date2 = seq(as.Date('2020-01-01'), as.Date('2025-06-30'), by = '6 months')
)

# Join, filter, and get the first greater Date2
new_df <- monthly_sequence_03 %>%
  # Join every Date1 with all matching Pnp Date2 values
  cross_join(monthly_sequence_06, by = "Pnp") %>%
  # Keep only rows where Date2 is larger than Date1
  filter(Date2 > Date1) %>%
  # Group by each Date1 to process individually
  group_by(Date1) %>%
  # Pick the smallest (first) Date2 that's larger than Date1
  slice_min(Date2, n = 1) %>%
  # Ungroup to avoid unexpected behavior later
  ungroup() %>%
  # Clean up column names and order to match your original structure
  select(Pnp, Frequency.x, Duration.x, Date1, Date2) %>%
  rename(Frequency = Frequency.x, Duration = Duration.x)

How this works: #

• cross_join links every Date1 to all Date2 values with the same Pnp
• filter(Date2 > Date1) removes any Date2 values that aren't larger than the current Date1
• slice_min(Date2, n=1) grabs the smallest (earliest) remaining Date2 for each Date1, which is exactly the first greater date you need

Approach 2: Using sqldf (Continuing Your Original Workflow) #

If you prefer to stick with SQL-style queries, this uses a correlated subquery to directly fetch the first greater Date2 for each row:

library(sqldf)

# Generate your base datasets (same as above)
monthly_sequence_03 <- data.frame(
  Pnp = 'A100',
  Frequency = 3,
  Duration = 'Month',
  Date1 = seq(as.Date('2020-01-01'), as.Date('2025-06-30'), by = '3 months')
)

monthly_sequence_06 <- data.frame(
  Pnp = 'A100',
  Frequency = 6,
  Duration = 'Month',
  Date2 = seq(as.Date('2020-01-01'), as.Date('2025-06-30'), by = '6 months')
)

# Use a correlated subquery to get the first greater Date2
new_df <- sqldf("
  SELECT a.*, 
         (SELECT MIN(b.Date2) 
          FROM monthly_sequence_06 b 
          WHERE b.Pnp = a.Pnp AND b.Date2 > a.Date1) AS first_greater_Date2
  FROM monthly_sequence_03 a
")

How this works: #

• For every row in monthly_sequence_03 (aliased as a), the subquery looks at monthly_sequence_06 (aliased as b)
• It finds the smallest Date2 where Pnp matches and Date2 is larger than the current Date1, then returns that value as first_greater_Date2

Example Output Check #

For a Date1 value like 2020-01-01, the first greater Date2 will be 2020-07-01. For 2020-04-01, it will also be 2020-07-01—which aligns perfectly with your 6-month Date2 sequence.

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