AESO网站更新后R爬虫失效，寻求历史互联数据获取方案

原R脚本依赖的AESO历史互联容量报告下载接口已失效，无法通过原URL获取CSV，尝试调用v2 API获取JSON但流程繁琐，以下是两种可与现有数据无缝追加整合的方案：

方案一：优化API调用并转换为兼容原有格式 #

通过调用AESO公开的v2 API，自动处理日期范围并将JSON数据转换为与原脚本完全一致的输出格式，直接支持与现有数据追加合并：

library(tidyverse)
library(httr)
library(jsonlite)
library(lubridate)

get_intertie_capacity_report <- function(start_date, end_date) {
  # 处理日期边界（与原脚本逻辑对齐）
  start_date <- ymd(start_date)
  end_date <- ymd(end_date)
  
  if (end_date > today()) end_date <- today()
  max_days <- 400
  if (as.numeric(end_date - start_date) >= max_days) {
    end_date <- start_date + days(max_days - 1)
  }
  
  # 按日拆分请求，避免单次数据量过大
  date_seq <- seq(start_date, end_date, by = "day")
  all_data <- list()
  
  for (single_date in date_seq) {
    # 调用API获取当日数据
    res <- GET(
      url = "https://itc.aeso.ca/itc/public/api/v2/interchange",
      query = list(
        beginDate = format(single_date, "%Y%m%d"),
        endDate = format(single_date, "%Y%m%d")
      ),
      accept_json()
    )
    
    stop_for_status(res)
    json_content <- content(res, as = "text")
    dat <- fromJSON(json_content, simplifyDataFrame = TRUE)
    
    # 转换格式并重命名列，匹配原脚本输出
    if (!is.null(dat$interchangeData)) {
      daily_data <- dat$interchangeData %>%
        mutate(
          date = ymd(substr(timeStamp, 1, 10)),
          he = str_sub(timeStamp, 12, 13) %>% paste0(":00")
        ) %>%
        rename(
          sk_import_capability = skImportCapability,
          sk_export_capability = skExportCapability,
          bc_export_capability = bcExportCapability,
          bc_import_capability = bcImportCapability,
          matl_export_capability = matlExportCapability,
          matl_import_capability = matlImportCapability,
          bc_matl_export_capability = bcMatlExportCapability,
          bc_matl_import_capability = bcMatlImportCapability
        ) %>%
        select(date, he, sk_import_capability, sk_export_capability,
               bc_export_capability, bc_import_capability,
               matl_export_capability, matl_import_capability,
               bc_matl_export_capability, bc_matl_import_capability)
      
      all_data[[length(all_data) + 1]] <- daily_data
    }
  }
  
  # 合并所有日期数据
  bind_rows(all_data)
}

方案优势 #

• 完全继承原脚本的日期限制逻辑，无需额外调整
• 输出列名、格式与原脚本完全一致，可直接用rbind()追加到现有数据集
• 按日拆分请求降低API调用风险，避免触发频率限制

方案二：模拟网页操作获取官方CSV #

如果偏好直接使用官方CSV文件，可通过rvest模拟网页表单提交，复刻用户手动下载CSV的流程：

library(rvest)
library(httr)
library(lubridate)
library(janitor)

get_intertie_csv <- function(start_date, end_date) {
  start_date <- ymd(start_date)
  end_date <- ymd(end_date)
  
  # 日期边界处理
  if (end_date > today()) end_date <- today()
  if (as.numeric(end_date - start_date) >= 400) {
    end_date <- start_date + days(399)
  }
  
  # 建立网页会话并填充表单
  session <- session("https://itc.aeso.ca/itc/public/atc/historic/")
  form <- html_form(session)[[1]]
  filled_form <- set_values(form,
                            startDate = format(start_date, "%m/%d/%Y"),
                            endDate = format(end_date, "%m/%d/%Y"),
                            fileFormat = "CSV")
  
  # 提交表单获取下载链接
  response <- session_submit(session, filled_form)
  download_link <- html_node(response, "a.download-link") %>% html_attr("href")
  download_url <- paste0("https://itc.aeso.ca", download_link)
  
  # 下载并处理CSV（与原脚本逻辑一致）
  temp_file <- tempfile(fileext = ".csv")
  download.file(download_url, temp_file, mode = "wb")
  
  itc_data <- read.csv(temp_file, skip = 2, stringsAsFactors = FALSE) %>%
    clean_names() %>%
    mutate(date = ymd(date), he = as.character(hour_ending)) %>%
    select(date, he, sk_import_capability, sk_export_capability,
           bc_export_capability, bc_import_capability,
           matl_export_capability, matl_import_capability,
           bc_matl_export_capability, bc_matl_import_capability)
  
  unlink(temp_file)
  itc_data
}

方案优势 #

• 直接获取官方生成的CSV文件，数据格式与原下载文件完全一致
• 无需手动解析JSON结构，降低维护成本

注意事项 #

• 两种方案均需提前安装依赖包：tidyverse、httr、lubridate；方案二额外需要rvest、janitor
• 若AESO调整API接口或网页结构，需对应修改代码中的参数或选择器
• API调用需注意请求频率，避免短时间内大量请求被服务器拦截

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