Downloading data from AESO in R

Question

I had some help with this a while ago, but the site has been updated and my R scraper no longer works. I am unable to figure out how to populate and download a csv for the data here:

https://itc.aeso.ca/itc/public/atc/historic/

I had a previous version that worked well until recently:

get_intertie_capacity_report<-function(start_date,end_date){ 
if(ymd(end_date)>today())
  end_date<-today()
#max number of days is 400 days
if(as.numeric(ymd(end_date)-ymd(start_date))>=400)
  end_date<-as.character(ymd(start_date)+days(399))
#build url
url<-paste("http://itc.aeso.ca/itc/public/queryHistoricalIntertieReport.do?availableEffectiveDate=943279200000+1999-11-22+07%3A00%3A00+MST+%281999-11-22+14%3A00%3A00+GMT%29&availableExpiryDate=1582354800000+2020-02-22+00%3A00%3A00+MST+%282020-02-22+07%3A00%3A00+GMT%29&fileFormat=CSV&startDate=",start_date,"&endDate=",end_date,sep = "")  
#download data
download_indic<-download.file(url,"data/itc_temp.csv",mode="wb")
stop_for_status(download_indic)
#process data to build capability by hour and data
itc_data<-read.csv("data/itc_temp.csv",skip = 2,stringsAsFactors = F) %>% clean_names() %>%
  mutate(date=ymd(date),hour_ending=as.character(hour_ending)) %>% 
  rename("he"="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)
itc_data
}

And, I can get part way through a new version using the API link and a JSON file download, but that seems messier than what I need?

  start_date <- as.Date(start_date)
  end_date <- as.Date(start_date)
  
  res<- 
    GET(
    url = "https://itc.aeso.ca/itc/public/api/v2/interchange",
    query = list(
      beginDate = format(start_date, "%Y%m%d"),
      endDate = format(end_date, "%Y%m%d"),
      #Accept = "application/json",
      Accept = "application/xml"#, NULL
    )
  )
  
  
  
  stop_for_status(res)
  
  json <- httr::content(res, as = "text")
  dat <- jsonlite::fromJSON(json, simplifyVector = FALSE, simplifyDataFrame = FALSE, flatten=FALSE)

Ideally, I want something I can append to the data I already have, and I'm agnostic to whether this runs through the csv download or the api->json.

Suggestions most welcome!

Answer 1

Here is a reproducible example retrieving the JSON.

start_date <- end_date <- Sys.Date()

res <- httr::GET(
       url = "https://itc.aeso.ca/itc/public/api/v2/interchange",
       query = list(
           beginDate = format(start_date, "%Y%m%d"),
           endDate = format(end_date, "%Y%m%d"),
           Accept = "application/json"
       )
   )
json <- httr::content(res, as = "text", encoding = "UTF-8")

I explored the JSON using the listviewer package

listviewer::jsonedit(json)

I then used rjsoncons to write a query that extracted, directly from the JSON, some of the fields that seem to correspond to what you are interested in. The queries are written using JMESPath. A simple example would extract the 'date' from the field return.BcIntertie.Allocations[].date

library(rjsoncons)
path <- 'return.BcIntertie.Allocations[].date'
j_query(json, path) |>
    noquote()
## [1] ["2024-07-10","2024-07-10","2024-07-10","2024-07-10",...]

I expanded on this by querying several different fields, and then re-formating the query into a new JSON object. I developed the code by querying / viewing until things looked about right

path <- paste0(
    'return.{',
        'date: BcIntertie.Allocations[].date,',
        'he: BcIntertie.Allocations[].he,',
        'bc_import: BcIntertie.Allocations[].import.atc,',
        'bc_export:  BcIntertie.Allocations[].export.atc,',
        'matl_import: MatlIntertie.Allocations[].import.atc,',
        'matl_export:  MatlIntertie.Allocations[].export.atc',
    '}'
)

j_query(json, path) |>
    listviewer::jsonedit()

And finally I ran the j_pivot() command to transform the JSON to a tibble.

j_pivot(json, path, as = "tibble")

The result is

# A tibble: 48 × 6
   date       he    bc_import bc_export matl_import matl_export
   <chr>      <chr>     <int>     <int>       <int>       <int>
 1 2024-07-10 2           750       950         295         300
 2 2024-07-10 3           750       950         295         300
 3 2024-07-10 4           750       950         295         300
 4 2024-07-10 5           750       950         295         300
 5 2024-07-10 6           750       950         295         300
 6 2024-07-10 7           750       950         295         300
 7 2024-07-10 8           750       950         295         300
 8 2024-07-10 9           750       950         295         300
 9 2024-07-10 10          750       950         295         300
10 2024-07-10 11          750       950         295         300
# ℹ 38 more rows
# ℹ Use `print(n = ...)` to see more rows

Answer 2

I managed it (not as elegantly as Martin Morgan) here:

get_itc_report <- function(start_date,end_date) {
  if(as.numeric(as_date(end_date)-as_date(start_date))>=100)
    end_date<-as_date(start_date)+days(99)
  
  res<- 
    GET(
    url = "https://itc.aeso.ca/itc/public/api/v2/interchange",
    query = list(
      startDate = format(as_date(start_date), "%Y%m%d"),
      endDate = format(as_date(end_date), "%Y%m%d"),
      startHE = 1,
      endHE = 24,
      Accept = "application/json",
      version= "false",
      dataType="ATC"
    )
  )
  stop_for_status(res)
  #listviewer::jsonedit(raw_json, height = "800px", mode = "view")
  itc_add <- res%>% httr::content()%>% pluck("return")%>%
    enframe()%>%
    unnest_wider(value) %>% 
    select(-links)%>%
    unnest(Allocations) %>%
    unnest_wider(Allocations) %>%
    unnest_wider(import,names_sep = "_") %>% 
    unnest_wider(export,names_sep = "_") %>% 
    as_tibble()%>%
    select(-import_transferType,-export_transferType)%>%
    select(date,he,flowgate=name,import_atc,export_atc)%>%
    mutate(date=ymd(date),
           flowgate=as_factor(flowgate),
           flowgate=fct_recode(flowgate,"bc"="BcIntertie",
                                "sk"="SkIntertie",
                                "matl"="MatlIntertie",
                                "system"="SystemlFlowgate", 
                                "bc_matl"="BcMatlFlowgate"))%>%
    pivot_wider(names_from = flowgate,values_from = c(import_atc,export_atc),
                names_glue = "{flowgate}_{.value}")
  
  names(itc_add)<-gsub("atc","capability",names(itc_add))
  
  itc_add
}