基于起止日期按季度扩展行：生成ID状态的季度观测记录

核心步骤 #

1. 替换缺失值：将End_date的NA值替换为2023-10-31，Quarter_End的NA值替换为2023.3
2. 基于Quarter_Start和Quarter_End生成完整季度序列，将每一行展开为对应季度的多行记录

R实现（tidyverse） #

library(tidyverse)

# 构造示例数据
df <- tibble(
  ID = c(1,2,3,3),
  Start_date = c("2019-03-28", "2011-02-28", "2019-03-28", "2005-02-28"),
  End_date = c("2020-03-26", "2011-04-12", NA, "2007-06-20"),
  Status = c("A", "C", "F", "A"),
  Quarter_Start = c(2019.1, 2011.1, 2019.1, 2005.1),
  Quarter_End = c(2020.1, 2011.2, NA, 2020.2)
)

# 替换缺失值
df_clean <- df %>%
  mutate(
    End_date = replace_na(End_date, "2023-10-31"),
    Quarter_End = replace_na(Quarter_End, 2023.3)
  )

# 生成季度序列并展开行
df_expanded <- df_clean %>%
  rowwise() %>%
  mutate(
    # 拆分年度和季度
    start_year = floor(Quarter_Start),
    start_qtr = as.integer((Quarter_Start - start_year)*10),
    end_year = floor(Quarter_End),
    end_qtr = as.integer((Quarter_End - end_year)*10),
    # 生成所有涉及的季度
    Quarter = list(
      if(start_year == end_year){
        paste0(start_year, ".", start_qtr:end_qtr) %>% as.numeric()
      } else {
        # 第一年剩余季度
        first_part <- paste0(start_year, ".", start_qtr:4) %>% as.numeric()
        # 中间完整年度的季度
        middle_years <- (start_year+1):(end_year-1)
        middle_part <- if(length(middle_years) > 0){
          cross_df(list(year=middle_years, qtr=1:4)) %>%
            mutate(quarter = paste0(year, ".", qtr)) %>%
            pull(quarter) %>% as.numeric()
        } else numeric(0)
        # 最后一年的前几个季度
        last_part <- paste0(end_year, ".", 1:end_qtr) %>% as.numeric()
        # 合并所有季度
        c(first_part, middle_part, last_part)
      }
    )
  ) %>%
  ungroup() %>%
  unnest(Quarter) %>%
  # 移除临时计算列和原季度起止列
  select(-start_year, -start_qtr, -end_year, -end_qtr, -Quarter_Start, -Quarter_End)

# 输出结果
print(df_expanded, n=20)

Python实现（pandas） #

import pandas as pd
import numpy as np

# 构造示例数据
data = {
    "ID": [1,2,3,3],
    "Start_date": ["2019-03-28", "2011-02-28", "2019-03-28", "2005-02-28"],
    "End_date": ["2020-03-26", "2011-04-12", np.nan, "2007-06-20"],
    "Status": ["A", "C", "F", "A"],
    "Quarter_Start": [2019.1, 2011.1, 2019.1, 2005.1],
    "Quarter_End": [2020.1, 2011.2, np.nan, 2020.2]
}

df = pd.DataFrame(data)

# 替换缺失值
df["End_date"] = df["End_date"].fillna("2023-10-31")
df["Quarter_End"] = df["Quarter_End"].fillna(2023.3)

# 定义生成季度序列的函数
def generate_quarters(start_q, end_q):
    start_year = int(start_q // 1)
    start_qtr = int((start_q - start_year) * 10)
    end_year = int(end_q // 1)
    end_qtr = int((end_q - end_year) * 10)
    
    quarters = []
    # 同一年的情况
    if start_year == end_year:
        for q in range(start_qtr, end_qtr + 1):
            quarters.append(f"{start_year}.{q}")
    else:
        # 第一年剩余季度
        for q in range(start_qtr, 5):
            quarters.append(f"{start_year}.{q}")
        # 中间完整年度的所有季度
        for year in range(start_year + 1, end_year):
            for q in range(1, 5):
                quarters.append(f"{year}.{q}")
        # 最后一年的前几个季度
        for q in range(1, end_qtr + 1):
            quarters.append(f"{end_year}.{q}")
    # 转换为数值类型
    return [float(q) for q in quarters]

# 生成季度列表
df["Quarter"] = df.apply(lambda row: generate_quarters(row["Quarter_Start"], row["Quarter_End"]), axis=1)

# 展开列表为多行
df_expanded = df.explode("Quarter", ignore_index=True)

# 移除不需要的列
df_expanded = df_expanded.drop(columns=["Quarter_Start", "Quarter_End"])

# 输出结果
print(df_expanded)

为什么之前的日期范围展开方案无效？ #

之前的方案是基于日期区间生成连续的日/月行，但你的需求是基于已有的Quarter_Start和Quarter_End字段生成季度序列，直接使用这两个字段可以避免日期转季度的误差（比如End_date=2020-03-26对应2020.1季度，和原数据的Quarter_End完全匹配），同时更贴合你需要的季度维度展开逻辑。

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