R语言：判断给定年度季度是否包含在日期区间内

Got it! Let's tackle this problem—you want to add columns to your tibble that show whether a given year-quarter falls within the open to close date range for each row. Here's how to do it cleanly with dplyr and lubridate (which makes date/quarter handling way easier):

1. 加载必要的包 #

First, make sure you have these packages installed and loaded:

library(dplyr)
library(lubridate)

2. 你的原始数据 #

Let's start with your existing data (I've tweaked it to use tibble() instead of the deprecated data_frame()):

a <- tibble(
  open = as.Date(c("12-31-2016","10-28-2016","08-01-2016"), format = "%m-%d-%Y"),
  close = as.Date(c("01-04-2017","01-05-2017","01-01-2017"), format = "%m-%d-%Y")
)

3. 手动指定季度的判断方法 #

If you know exactly which quarters you need to check (like 2016Q3, 2016Q4, 2017Q1 for your data), we can define each quarter's date interval, then use int_overlaps() to check for overlap with each row's open/close range:

# 定义要检查的季度及其起止日期区间
quarter_intervals <- list(
  "2016Q3" = interval(ymd("2016-07-01"), ymd("2016-09-30")),
  "2016Q4" = interval(ymd("2016-10-01"), ymd("2016-12-31")),
  "2017Q1" = interval(ymd("2017-01-01"), ymd("2017-03-31"))
)

# 为每个季度添加判断列
a_with_quarters <- a %>%
  mutate(
    `2016Q3` = int_overlaps(interval(open, close), quarter_intervals[["2016Q3"]]),
    `2016Q4` = int_overlaps(interval(open, close), quarter_intervals[["2016Q4"]]),
    `2017Q1` = int_overlaps(interval(open, close), quarter_intervals[["2017Q1"]])
  )

结果展示 #

When you print the updated tibble, you'll get clear TRUE/FALSE values for each quarter:

print(a_with_quarters)
#> # A tibble: 3 × 5
#>   open      close      `2016Q3` `2016Q4` `2017Q1`
#>   <date>    <date>     <lgl>    <lgl>    <lgl>   
#> 1 2016-12-31 2017-01-04 FALSE    TRUE     TRUE    
#> 2 2016-10-28 2017-01-05 FALSE    TRUE     TRUE    
#> 3 2016-08-01 2017-01-01 TRUE     TRUE     TRUE

4. 动态生成所有涉及季度的方法 #

If you don't want to manually list quarters (great for larger datasets), we can auto-detect all quarters covered by your open and close dates, then batch-add the columns:

# 提取数据中覆盖的所有年度季度
all_quarters <- a %>%
  mutate(
    open_quarter = quarter(open, with_year = TRUE),
    close_quarter = quarter(close, with_year = TRUE)
  ) %>%
  summarise(
    quarters = seq(min(open_quarter), max(close_quarter), by = 0.25)
  ) %>%
  pull(quarters) %>%
  as.character()

# 批量生成季度区间并添加判断列
a_dynamic_quarters <- a %>%
  mutate(
    across(
      all_of(all_quarters),
      ~ {
        # 解析季度的年份和季度数
        year_part <- substr(.y, 1, 4)
        qtr_part <- as.integer(substr(.y, 6, 6))
        # 生成季度的起始和结束日期
        qtr_start <- ymd(paste0(year_part, "-", (qtr_part - 1)*3 + 1, "-01"))
        qtr_end <- ymd(paste0(year_part, "-", qtr_part*3, "-", days_in_month(qtr_start %m+% months(2))))
        # 判断区间是否重叠
        int_overlaps(interval(open, close), interval(qtr_start, qtr_end))
      },
      .names = "{.col}"
    )
  )

This will automatically create columns for every quarter that touches your date ranges, no manual input needed.

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