如何在R或Python中扩展低频表并与高频周表关联？

问题核心 #

你遇到两个关键问题：

• KeyError：合并时指定的列名Week不存在，你的扩展表和高频表的周数列名实际是Week Number
• 跨年周重复：直接提取周数会导致跨年场景下周值重复（如2023年最后一周与2024年第一周可能共用周52/53），需用ISO标准年周组合唯一标识每周

Python 实现方案 #

关键修正点 #

1. 列名对齐：合并时使用实际列名Year和Week Number
2. ISO年周标准：用isocalendar()获取唯一的年周组合，避免跨年重复

完整代码 #

import pandas as pd

# 读取并预处理低频表
low_freq_table = pd.read_csv(r"/content/low freq.csv")
low_freq_table['Tariff Start Date'] = pd.to_datetime(low_freq_table['Tariff Start Date'], format='%d-%b-%y')
low_freq_table['Tariff End Date'] = pd.to_datetime(low_freq_table['Tariff End Date'], format='%d-%b-%y')

# 扩展低频表为周度数据（基于ISO年周）
expanded_rows = []
for _, row in low_freq_table.iterrows():
    # 生成日期范围内的所有天，再映射到对应周
    date_range = pd.date_range(start=row['Tariff Start Date'], end=row['Tariff End Date'], freq='D')
    # 获取ISO标准的年和周数
    iso_weeks = date_range.isocalendar()
    # 去重，保留唯一的年周组合
    unique_weeks = iso_weeks[['year', 'week']].drop_duplicates()
    # 绑定对应的费率值
    unique_weeks['Interest'] = row['Excise Rate']
    expanded_rows.append(unique_weeks)

# 合并扩展数据并调整列名匹配高频表
expanded_table = pd.concat(expanded_rows, ignore_index=True).rename(columns={
    'year': 'Year',
    'week': 'Week Number'
})

# 读取并格式化高频表
high_freq_table = pd.read_csv(r"/content/high freq.csv")
high_freq_table['Year'] = high_freq_table['Year'].astype(int)

# 完成合并
merged_table = pd.merge(high_freq_table, expanded_table, on=['Year', 'Week Number'], how='left')
print(merged_table)

R 实现方案 #

使用lubridate处理日期周数，tidyr扩展数据，dplyr完成合并：

完整代码 #

library(dplyr)
library(lubridate)
library(tidyr)

# 读取并预处理低频表
low_freq_table <- read.csv("/content/low freq.csv") %>%
  mutate(
    Tariff_Start_Date = dmy(`Tariff Start Date`),
    Tariff_End_Date = dmy(`Tariff End Date`)
  )

# 扩展低频表为周度数据
expanded_table <- low_freq_table %>%
  rowwise() %>%
  mutate(week_dates = list(seq(Tariff_Start_Date, Tariff_End_Date, by = "week"))) %>%
  unnest(week_dates) %>%
  # 获取ISO年周
  mutate(
    Year = isoyear(week_dates),
    `Week Number` = isoweek(week_dates)
  ) %>%
  distinct(Year, `Week Number`, `Excise Rate`) %>%
  rename(Interest = `Excise Rate`)

# 读取高频表并合并
high_freq_table <- read.csv("/content/high freq.csv")
merged_table <- high_freq_table %>% left_join(expanded_table, by = c("Year", "Week Number"))

print(merged_table)

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