Data Assignment #2 (due November 3, 2023 by 11:59pm)

Canada is an energy exporting country.

The Canadian Energy Regulator produces pipeline profiles for the largest oil and gas pipelines in Canada, and also tracks other export-related data. The pipeline throughput and capacity data are available here. Crude oil export data sets are available here, while gas trade data are available here.

The US EIA also tracks crude oil imports from Canada and elsewhere, and often provides much more detail than the Canadian export data. Unfortunately, their new Application Programming Interface (API) is very cumbersome and so I’ve skipped teaching you that step and downloaded some data from their API for you to use for this assignment.

There are five deliverables for this assignment, and they build on skills we have already learned:

To execute my versions of the deliverables that you see below, I’ve used the following packages. Use this as a guide to set up your document:

library(kableExtra)
library(readxl)
library(janitor)
library(tidyverse)
library(lubridate)
library(scales)
library(viridis)

You may also find it useful to have a look at the Functions Demo before you start this assignment.

Deliverable 1: the Enbridge Mainline

The Enbridge Mainline moves more crude oil than all other systems combined. For this graph, I expect you to download data from the pipeline profiles and produce a graph in R that shows exports ex-Gretna by grade as well as capacity at that point. You’ll need to filter the data to make it useful for your graph, and you’ll need to combine an area plot and a line plot.

You should produce something like (but not necessarily identical to) this:

Hints:

To add the second y axis, use scale_y_continuous(expand = c(0, 0),sec.axis = sec_axis( trans=~.*1/.16, name="Shipments (Monthly, Thousands of Barrels per Day)")) which translates the units using the multiplier in trans=~;

You’ll probably want to download a csv and look at it. You’ll also likely want to do some fixing of cases. You can do this in R or in your CSV (but it’s easier in R);

There are a couple of ways to make a variable out of two existing varibles for graphing. We’ve previously used the interaction() function, e.g. group=interaction(product,trade_type), or you can also use paste, as I did in another part of the functions demo. For example, you could create a new variable using mutate(pair=paste(product," (",trade_type,")",sep="")) which will create, for example, a variable like Light / NGL (Export);

If you’re getting a weird looking graph, you’re probably graphing by year. Check your data to see what you really should be using.

To add a capacity line, there are a couple of ways to do it, but I’ll show you the way I did it here. First, make a separate data set for capacity, and use the mean in each month:

enb_capacity<-enb_data%>%group_by(date)%>%summarize(capacity=mean(available_capacity_1000_m3_d,na.rm=T))

And then, you can add a line into your graph using those data. This let’s me show you a neat trick too - if you name the colour in a ggplot aesthetic, this will carry through to your legends.

geom_line(data=enb_capacity,aes(date,capacity,color="Available Capacity"),linewidth=.85, lty="21") + scale_color_manual("",values=c("black"))

You should also note that, for a time before the opening of the Dakota Access Pipeline, there was some US-produced crude moving east on the Mainline.

Deliverable 2: the Keystone Pipeline

The Keystone system was supposed to grow to rival the Enbridge Mainline, but it stalled after the construction of a single cross-border pipeline. There was, at one point, talk of two pairs of lines (a twinned base Keystone, and a twinned line on the Keystone XL right of way). Today, though, the single line still moves a lot of heavy crude oil to refining locations in US PADDs 2 and 3.

You should produce something like (but not necessarily identical to) this for Keystone:

Deliverable 3: the Trans-Mountain Pipeline

The Trans-Mountain pipeline has been around since the 1950s and is the only major crude oil pipeline to cross the continental divide. Trans-Mountain is also unique in that it ships both crude oil and refined products in batches (other pipelines ship in batches too, just not with refined products in the mix). The following description is taken from the Trans-Mountain website”

Transmountain also has three delivery points: exports to the US via Sumas, deliveries to domestic refining in Burnaby, and a port delivery point at Westridge.

To see what gets delivered where on TransMountain, you should produce something like (but not necessarily identical to) this for TransMountain:

Hint: use facet_wrap(~key_point,ncol = 1, scales = "free_y") to allow different y-axes on each of the plots, and to stack the plots vertically if you like. You are not required to present them this way, but I do want to see a three-facet plot

Deliverable 4: Canadian exports of crude by rail

This one should be a bit easier - it’s a single-variable graph with no filters or extensive modifications.

The CER crude-by-rail data are available in an Excel workbook here.

There is one bit of code I’d like you to see:

#read data in from the excel using read_excel
oil_by_rail<-read_excel("crude_by_rail.xlsx", sheet = NULL, range = NULL, col_names = TRUE,
                        col_types = NULL, na = "", trim_ws = TRUE, skip = 7)
#fix the data format
oil_by_rail <- oil_by_rail %>% clean_names () %>% select(-1)%>%slice(1:(n()-5))%>% #drop first column and the last 5 rows
  fill(year) %>% #fill in the missing years
  mutate(mth_num=match(month,month.name),#create numeric months, and then make dates
    date=ymd(paste(year,"-",mth_num,"-1",sep = "")))

Again, I’m not fussy about you replicating all aspects of this plot - just make sure you get the data on the page nicely:

Deliverable 5: US imports of Canadian crude

We haven’t talked a lot about the US market, but it’s worth having a sense of how the US relies on imports. Using data that I’ve provided for you here, you should be able to reproduce a graph that looks like this one for use imports by grade and refining region. Note that refining regions give you a better sense of where demand lies, rather than a focus on the import port or pipeline border crossing

RMD File and HTML/PDF Preparation

As before, use the basic RMD file to complete this (and future) assignments, just rename it assignment 2. You only need to submit the HTML.