One of the recommendations of the Alberta Royalty Review in 2015-16 was more transparency with respect to oil sands costs, profits, production and royalty payments. The Alberta Government has followed that recommendation and makes project-level data available on their open data site. This document compiles those data and provides some basic graphs that you might find useful. Where appropriate, I’ve provided a link to the R code used to process the data.

Production

ggplot(os_data %>% filter(project,mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod)
                ),
       aes(factor(reporting_year),last_prod/1000,fill=project_type))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  scale_fill_brewer()+
  guides(fill="none",color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(axis.text.x = element_text(angle=-90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Bitumen Production (thousands of barrels per day)",
       title="Annual Bitumen Production, Oil Sands Mining Projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

ggplot(os_data %>% filter(big_project,!mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod)
                ),
       aes(factor(reporting_year),last_prod/1000,fill=project_type))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  scale_fill_brewer()+
  guides(fill="none",color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 8, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Bitumen Production (thousands of barrels per day)",
       title="Annual Bitumen Production, Larger Oil Sands In Situ Projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

Revenue per Barrel

ggplot(os_data %>% filter(mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod)
                ),
       aes(factor(reporting_year),gross_revenue_bbl,fill=project_type))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  scale_fill_brewer()+
  guides(fill="none",color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Revenue per barrel bitumen (CA$/bbl)",
       title="Gross Revenue per Barrel Bitumen, Oil Sands Mining Projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

ggplot(os_data %>% filter(big_project,!mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod)
                ),
       aes(factor(reporting_year),gross_revenue_bbl,fill=project_type))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  scale_fill_brewer()+
  guides(fill="none",color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Revenue per barrel bitumen (CA$/bbl)",
       title="Gross Revenue per Barrel Bitumen, Larger In Situ Oil Sands Projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

Operating Costs

ggplot(os_data %>% filter(mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod)
                ),
       aes(factor(reporting_year),op_costs_bbl,fill=project_type))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  scale_fill_brewer()+
  guides(fill="none",color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Operating cost per barrel bitumen (CA$/bbl)",
       title="Operating Costs, Oil Sands Mining Projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

ggplot(os_data %>% filter(big_project,!mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod)
                ),
       aes(factor(reporting_year),op_costs_bbl,fill=project_type))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  scale_fill_brewer()+
  guides(fill="none",color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Operating cost per barrel bitumen (CA$/bbl)",
       title="Operating Costs, Larger In Situ Oil Sands Projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

Project-level operating costs ($/bbl bitumen)

#test<-
  os_data %>% 
  #filter(!mine)%>%
  group_by(project_name)%>%
  mutate(
      last_prod=last(cleaned_crude_bitumen_at_rcp_barrels)/365,
      label=paste(project_name,"\n(",formatC(round(last_prod,digits=-3),format="f", big.mark=",",digits = 0)," bbl/d)",sep=""))%>%
  ungroup()%>%
  filter(last_prod>10000)%>%
  mutate(label=factor(label),
      project_type=ifelse(mine,"Mine","In Situ"),
      label=fct_reorder(label,last_prod),
      op_costs_bbl=format(op_costs_bbl,nsmall=2),
      op_costs_bbl=ifelse(grepl("NaN",op_costs_bbl),"",op_costs_bbl))%>%
    select(label,project_type,reporting_year,op_costs_bbl,last_prod)%>%
  pivot_wider(names_from = reporting_year,values_from = op_costs_bbl)%>%
  rename("Project"=label,"Project Type"=project_type)%>%
  arrange(-last_prod)%>%select(-last_prod)%>%
  #rename("2021 Production"=last_prod)%>%
  rename_all( ~ gsub("x","",.))%>%
  kable(table.attr = "style='width:80%;'",align = c("l","c", rep("r", 7)), linesep = "", escape = FALSE) %>% 
  kable_styling(fixed_thead = T,bootstrap_options = c("hover", "condensed","responsive"),full_width = T)%>%
  scroll_box(width = "1000px", height = "500px")%>%
  I()
Project Project Type 2016 2017 2018 2019 2020 2021 2022
Syncrude (370,000 bbl/d) Mine 24.38 21.28 31.69 24.66 23.09 27.54 31.35
Horizon (257,000 bbl/d) Mine 18.66 17.63 15.86 17.55 14.20 17.51 22.14
Suncor (257,000 bbl/d) Mine 28.60 24.23 31.47 32.24 30.46 32.21 41.98
Christina Lake (CVE) (245,000 bbl/d) In Situ 6.86 6.49 5.98 6.97 7.19 8.99 10.83
Kearl (239,000 bbl/d) Mine 31.02 30.64 32.47 37.30 26.98 27.42 36.83
Firebag (199,000 bbl/d) In Situ 10.42 10.35 9.86 11.39 11.42 13.14 16.79
Foster Creek (190,000 bbl/d) In Situ 9.82 10.38 8.49 8.79 9.57 11.62 14.79
Muskeg River (164,000 bbl/d) Mine 22.50 24.32 21.84 23.26 20.97 18.54 23.15
Fort Hills (157,000 bbl/d) Mine Inf 3.80 38.50 30.67 34.97 49.01 38.72
Surmont (142,000 bbl/d) In Situ 22.16 16.38 10.59 11.70 9.22 9.97 13.54
Cold Lake (140,000 bbl/d) In Situ 11.75 12.37 15.21 16.58 16.32 20.63 24.38
Jackpine (123,000 bbl/d) Mine 22.92 21.74 20.53 18.64 19.66 17.06 22.19
Christina Lake (MEG) (95,000 bbl/d) In Situ 9.23 8.95 8.80 8.30 8.92 11.14 15.44
Primrose (67,000 bbl/d) In Situ 13.20 13.49 17.66 15.35 12.59 19.02 28.29
Long Lake (48,000 bbl/d) In Situ 24.61 15.72 12.52 11.07 13.09 15.20 16.74
Sunrise (47,000 bbl/d) In Situ 26.75 15.22 14.95 15.34 13.88 11.47 17.68
Jackfish 3 (47,000 bbl/d) In Situ 7.80 8.13 9.47 8.04 7.78 9.68 11.07
Jackfish (38,000 bbl/d) In Situ 10.83 9.62 12.52 8.82 9.12 11.35 13.29
Kirby North (35,000 bbl/d) In Situ 22.72 8.09 9.91 14.94
Jackfish 2 (33,000 bbl/d) In Situ 8.87 8.48 9.34 10.52 10.79 10.10 14.79
MacKay River (Suncor) (32,000 bbl/d) In Situ 16.23 15.26 13.14 16.12 31.26 16.45 20.21
South Brintnell (28,000 bbl/d) In Situ 6.14 6.34 6.16 6.34 6.05 6.87 8.51
Hangingstone Expansion (22,000 bbl/d) In Situ 19.09 11.83 8.71 11.10 11.17 15.48
Leismer (20,000 bbl/d) In Situ 13.13 10.90 11.29 14.00 12.75 15.05 17.43
Kirby South (20,000 bbl/d) In Situ 10.13 9.69 9.93 11.42 12.58 15.98 23.78
Tucker (19,000 bbl/d) In Situ 8.98 9.89 10.90 9.75 13.87 15.80 21.94
North Pelican Lake (16,000 bbl/d) In Situ 12.05 11.52 7.02 7.54 7.68 7.97 10.52
Orion (15,000 bbl/d) In Situ 18.76 18.54 14.27 11.73 10.05 16.38 22.21
Lindbergh (15,000 bbl/d) In Situ 9.83 12.41 9.97 9.60 10.80 12.65 19.18
Bonnyville (14,000 bbl/d) In Situ 13.55 15.46 15.93 14.10 15.63 15.27 18.10
MacKay River (PetroChina) (11,000 bbl/d) In Situ 58.46 42.47 39.23 30.50 29.25 41.50

Operating Cost Density Plot

ggplot(os_data%>%filter(big_project) %>% group_by(reporting_year) %>% 
  mutate(year_total=sum(cleaned_crude_bitumen_at_rcp_barrels), weight=cleaned_crude_bitumen_at_rcp_barrels/year_total) %>%
  ungroup()%>%
    mutate(mine=as.factor(mine),
           mine=fct_recode(mine,Mine="TRUE","In Situ"="FALSE"))
           
           , aes(op_costs_bbl,group=factor(reporting_year),weights=weight))+
  stat_density(aes(color=factor(reporting_year)),geom="line",position = "identity",trim=T,size=1.6)+
  scale_colour_manual(NULL,values=colors_tableau10())+
  facet_wrap(~mine,scales="free_x")+
  expand_limits(x=0)+
  guides(color=guide_legend(nrow=1))+
  #geom_density(aes(color=factor(reporting_year)), alpha=0.8) + 
  labs(title="Density plot of oil sands operating costs per barrel bitumen", 
       subtitle="Production-weighted, for projects with more than 10,000 barrels per day of bitumen production",
       caption="Source: Government of Alberta 2016 and 2017 Royalty Data, graph by Andrew Leach",
       x="Operating Costs ($Cdn/bbl bitumen)",
       fill="Reporting Year")+
  work_theme()

Royalties

ggplot(os_data %>% filter(mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod),
                payout=as_factor(payout_status),
                payout=fct_relevel(payout,"PRE")
                ),
       aes(factor(reporting_year),royalty_bbl,fill=payout))+
  scale_fill_manual("",values=c("PRE"="lightgreen","POST"="darkgreen"),labels=c("Pre-Payout","Post-Payout"))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  guides(fill=guide_legend(nrow = 1),color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(legend.position="bottom",
        axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Royalties payable per barrel bitumen (CA$/bbl)",
       title="Royalties Payable per Barrel Bitumen, Oil Sands Mining Projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

ggplot(os_data %>% filter(big_project,!mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod),
                payout=as_factor(payout_status),
                payout=fct_relevel(payout,"PRE")
                ),
       aes(factor(reporting_year),royalty_bbl,fill=payout))+
  scale_fill_manual("",values=c("PRE"="lightgreen","POST"="darkgreen"),labels=c("Pre-Payout","Post-Payout"))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  guides(fill=guide_legend(nrow = 1),color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(legend.position="bottom",
        axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Royalties payable per barrel bitumen (CA$/bbl)",
       title="Royalties Payable per Barrel Bitumen, Larger In Situ Oil Sands Projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

Project-level royalties payable ($/bbl bitumen)

#test<-

  os_data %>% #filter(!mine)%>%
  
  group_by(project_name)%>%
  mutate(
      last_prod=last(cleaned_crude_bitumen_at_rcp_barrels)/365,
      label=paste(project_name,"\n(",formatC(round(last_prod,digits=-3),format="f", big.mark=",",digits = 0)," bbl/d)",sep=""))%>%
  ungroup()%>%
  filter(last_prod>10000)%>%
  mutate(label=factor(label),
       project_type=ifelse(mine,"Mine","In Situ"),
      label=fct_reorder(label,last_prod),
      #op_profit_net_bbl=op_profit_net_bbl/gross_revenue_bbl*100,
      royalty_bbl=format(round(royalty_bbl,2),nsmall=2),
      royalty_bbl=ifelse(grepl("NaN",royalty_bbl),"",royalty_bbl),
      royalty_bbl=ifelse(grepl("Inf",royalty_bbl),"",royalty_bbl)
      )%>%
    select(label,project_type,reporting_year,royalty_type,royalty_bbl,last_prod)%>%
  pivot_wider(names_from = reporting_year,values_from = royalty_bbl)%>%
  rename("Project"=label,
         "Project Type"=project_type)%>%
  arrange(-last_prod)%>%select(-last_prod)%>%
  #rename("2021 Production"=last_prod)%>%
  rename_all( ~ gsub("x","",.))%>%
  kable(table.attr = "style='width:80%;'",align = c("l","c", rep("r", 7)), linesep = "", escape = FALSE) %>% 
  kable_styling(fixed_thead = T,bootstrap_options = c("hover", "condensed","responsive"),full_width = T)%>%
  scroll_box(width = "1000px", height = "500px")%>%
  I()
Project Project Type royalty_type 2016 2017 2018 2019 2020 2021 2022
Syncrude (370,000 bbl/d) Mine GROSS 0.32 NA 1.75 NA NA NA NA
Syncrude (370,000 bbl/d) Mine NET NA 4.95 NA 7.47 1.05 8.51 19.28
Horizon (257,000 bbl/d) Mine GROSS 0.44 0.95 1.89 1.69 0.36 2.68 NA
Horizon (257,000 bbl/d) Mine NET NA NA NA NA NA NA 16.17
Suncor (257,000 bbl/d) Mine GROSS 0.28 0.83 1.76 1.62 0.22 2.36 7.16
Christina Lake (CVE) (245,000 bbl/d) In Situ GROSS 0.34 0.78 0.26 NA NA NA NA
Christina Lake (CVE) (245,000 bbl/d) In Situ NET NA NA NA 9.46 2.84 12.68 26.62
Kearl (239,000 bbl/d) Mine GROSS 0.38 0.86 1.98 1.74 0.38 2.62 6.90
Firebag (199,000 bbl/d) In Situ GROSS 0.32 0.69 1.36 1.43 0.29 NA NA
Firebag (199,000 bbl/d) In Situ NET NA NA NA NA NA 5.43 19.47
Foster Creek (190,000 bbl/d) In Situ GROSS 0.23 NA NA NA NA NA NA
Foster Creek (190,000 bbl/d) In Situ NET NA 3.41 6.13 9.02 1.46 12.39 25.71
Muskeg River (164,000 bbl/d) Mine GROSS 0.33 NA NA NA NA NA NA
Muskeg River (164,000 bbl/d) Mine NET NA 4.48 5.46 7.75 0.73 12.51 23.09
Fort Hills (157,000 bbl/d) Mine GROSS 0.94 1.45 1.51 0.28 2.15 6.75
Surmont (142,000 bbl/d) In Situ GROSS 0.27 0.60 1.31 1.42 0.28 2.46 6.60
Cold Lake (140,000 bbl/d) In Situ NET 4.27 7.60 6.93 9.56 2.06 11.24 21.61
Jackpine (123,000 bbl/d) Mine GROSS 0.49 0.94 1.98 1.76 0.39 NA NA
Jackpine (123,000 bbl/d) Mine NET NA NA NA NA NA 8.68 23.70
Christina Lake (MEG) (95,000 bbl/d) In Situ GROSS 0.31 0.77 1.35 1.48 0.27 2.34 6.48
Primrose (67,000 bbl/d) In Situ NET 4.58 6.73 2.28 7.07 3.92 11.60 20.51
Long Lake (48,000 bbl/d) In Situ GROSS 0.19 0.61 1.18 1.47 0.27 2.44 6.32
Sunrise (47,000 bbl/d) In Situ GROSS 0.34 0.76 1.38 1.46 0.30 2.27 6.16
Jackfish 3 (47,000 bbl/d) In Situ GROSS 0.35 0.77 1.28 1.47 0.32 NA NA
Jackfish 3 (47,000 bbl/d) In Situ NET NA NA NA NA NA 3.79 26.35
Jackfish (38,000 bbl/d) In Situ GROSS 0.24 NA 0.74 NA NA NA NA
Jackfish (38,000 bbl/d) In Situ NET NA 3.77 NA 6.49 2.73 12.06 24.36
Kirby North (35,000 bbl/d) In Situ GROSS 1.01 0.26 2.34 6.30
Jackfish 2 (33,000 bbl/d) In Situ GROSS 0.36 0.80 1.30 NA NA NA NA
Jackfish 2 (33,000 bbl/d) In Situ NET NA NA NA 2.92 1.81 13.08 23.18
MacKay River (Suncor) (32,000 bbl/d) In Situ GROSS 0.15 0.70 1.27 NA 0.29 NA NA
MacKay River (Suncor) (32,000 bbl/d) In Situ NET NA NA NA 5.18 NA 5.50 19.95
South Brintnell (28,000 bbl/d) In Situ NET 5.11 8.30 8.60 12.26 4.83 15.95 30.75
Hangingstone Expansion (22,000 bbl/d) In Situ GROSS 0.59 1.21 1.39 0.24 2.21 6.55
Leismer (20,000 bbl/d) In Situ GROSS 0.28 0.63 1.21 1.37 0.27 2.37 6.20
Kirby South (20,000 bbl/d) In Situ GROSS 0.40 0.81 1.32 1.42 0.29 2.35 6.33
Tucker (19,000 bbl/d) In Situ GROSS 0.48 0.95 1.84 1.71 NA NA NA
Tucker (19,000 bbl/d) In Situ NET NA NA NA NA 1.76 12.18 21.30
North Pelican Lake (16,000 bbl/d) In Situ NET 3.80 7.44 6.97 10.28 3.97 14.55 29.22
Orion (15,000 bbl/d) In Situ GROSS 0.47 0.92 1.44 1.71 0.40 NA NA
Orion (15,000 bbl/d) In Situ NET NA NA NA NA NA 6.56 18.61
Lindbergh (15,000 bbl/d) In Situ GROSS 0.43 0.79 2.00 1.41 0.37 2.53 NA
Lindbergh (15,000 bbl/d) In Situ NET NA NA NA NA NA NA 9.61
Bonnyville (14,000 bbl/d) In Situ NET 3.50 5.54 2.03 8.10 2.99 12.16 25.09
MacKay River (PetroChina) (11,000 bbl/d) In Situ GROSS 0.34 0.75 1.15 0.19 1.90 5.41

Operating Profits (Post-Royalty)

ggplot(os_data %>% filter(mine,project_name!="Fort Hills")%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod),
                payout=as_factor(payout_status),
                payout=fct_relevel(payout,"PRE")
                ),
       aes(factor(reporting_year),op_profit_net_bbl,fill=payout))+
  scale_fill_manual("",values=c("PRE"="lightgreen","POST"="darkgreen"),labels=c("Pre-Payout","Post-Payout"))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  guides(fill=guide_legend(nrow = 1),color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(legend.position="bottom",
        axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Operating profit per barrel bitumen (CA$/bbl)",
       title="Operating Profit per Barrel Bitumen, Oil Sands Mining Projects",
       subtitle="Gross revenue net operating and capital costs and royalties, excluding Fort Hills",
       caption="Data via Government of Alberta, graph by @andrew_leach")

ggplot(os_data %>% filter(big_project,!mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod),
                payout=as_factor(payout_status),
                payout=fct_relevel(payout,"PRE")
                ),
       aes(factor(reporting_year),op_profit_net_bbl,fill=payout))+
  scale_fill_manual("",values=c("PRE"="lightgreen","POST"="darkgreen"),labels=c("Pre-Payout","Post-Payout"))+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  #scale_fill_viridis(discrete=FALSE,"Royalties Paid ($/bbl)")+
  #coord_flip()+
  guides(fill=guide_legend(nrow = 1),color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 7, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(legend.position="bottom",
        axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Operating profit per barrel bitumen (CA$/bbl)",
       title="Operating Profit per Barrel Bitumen, Oil Sands In-Situ Projects",
       subtitle="Gross revenue net operating and capital costs and royalties",
       caption="Data via Government of Alberta, graph by @andrew_leach")

Project-level operating profits ($/bbl bitumen)

#test<-

  os_data %>% #filter(!mine)%>%
  
  group_by(project_name)%>%
  mutate(
      last_prod=last(cleaned_crude_bitumen_at_rcp_barrels)/365,
      label=paste(project_name,"\n(",formatC(round(last_prod,digits=-3),format="f", big.mark=",",digits = 0)," bbl/d)",sep=""))%>%
  ungroup()%>%
  filter(last_prod>10000)%>%
  mutate(label=factor(label),
       project_type=ifelse(mine,"Mine","In Situ"),
      label=fct_reorder(label,last_prod),
      #op_profit_net_bbl=op_profit_net_bbl/gross_revenue_bbl*100,
      op_profit_net_bbl=format(round(op_profit_net_bbl,2),nsmall=2),
      op_profit_net_bbl=ifelse(grepl("NaN",op_profit_net_bbl),"",op_profit_net_bbl),
      op_profit_net_bbl=ifelse(grepl("Inf",op_profit_net_bbl),"",op_profit_net_bbl)
      )%>%
    select(label,project_type,reporting_year,op_profit_net_bbl,last_prod)%>%
  pivot_wider(names_from = reporting_year,values_from = op_profit_net_bbl)%>%
  rename("Project"=label,
         "Project Type"=project_type)%>%
  arrange(-last_prod)%>%select(-last_prod)%>%
  #rename("2021 Production"=last_prod)%>%
  rename_all( ~ gsub("x","",.))%>%
  kable(table.attr = "style='width:80%;'",align = c("l","c", rep("r", 7)), linesep = "", escape = FALSE) %>% 
  kable_styling(fixed_thead = T,bootstrap_options = c("hover", "condensed","responsive"),full_width = T)%>%
  scroll_box(width = "1000px", height = "500px")%>%
  I()
Project Project Type 2016 2017 2018 2019 2020 2021 2022
Syncrude (370,000 bbl/d) Mine 3.14 14.82 4.15 19.04 4.39 22.38 34.04
Horizon (257,000 bbl/d) Mine 8.74 20.36 19.64 28.42 10.64 34.06 40.64
Suncor (257,000 bbl/d) Mine -7.34 -2.09 -7.24 -5.25 -30.42 -21.34 -3.09
Christina Lake (CVE) (245,000 bbl/d) In Situ 12.70 25.72 18.77 26.34 9.96 30.60 43.66
Kearl (239,000 bbl/d) Mine -7.86 4.69 4.27 8.99 -2.04 23.79 21.85
Firebag (199,000 bbl/d) In Situ 7.34 18.67 15.51 26.62 8.05 26.55 36.85
Foster Creek (190,000 bbl/d) In Situ 6.52 17.91 19.19 24.86 6.73 30.60 43.52
Muskeg River (164,000 bbl/d) Mine 5.17 15.59 15.69 22.78 7.58 31.34 41.54
Fort Hills (157,000 bbl/d) Mine 34.38 -12.44 9.18 -18.81 -6.88 27.93
Surmont (142,000 bbl/d) In Situ -6.38 9.43 13.10 27.37 4.06 38.44 51.55
Cold Lake (140,000 bbl/d) In Situ 13.35 20.42 16.54 26.25 8.99 26.49 38.10
Jackpine (123,000 bbl/d) Mine 7.45 17.73 16.57 29.14 6.20 31.53 38.43
Christina Lake (MEG) (95,000 bbl/d) In Situ 9.24 20.01 16.83 31.21 7.23 34.46 48.75
Primrose (67,000 bbl/d) In Situ 17.22 22.38 14.96 27.55 14.50 30.62 40.62
Long Lake (48,000 bbl/d) In Situ -13.86 10.98 12.40 28.14 3.46 33.90 45.15
Sunrise (47,000 bbl/d) In Situ -8.19 13.55 10.63 23.60 6.44 33.55 42.52
Jackfish 3 (47,000 bbl/d) In Situ 13.55 24.03 14.39 30.93 16.88 35.78 40.46
Jackfish (38,000 bbl/d) In Situ 1.95 17.43 3.66 20.38 9.13 28.93 39.65
Kirby North (35,000 bbl/d) In Situ -9.47 12.91 36.11 47.37
Jackfish 2 (33,000 bbl/d) In Situ 12.99 24.20 15.01 25.25 6.66 29.09 39.40
MacKay River (Suncor) (32,000 bbl/d) In Situ -9.66 2.17 4.10 20.36 -2.65 14.23 33.88
South Brintnell (28,000 bbl/d) In Situ 23.04 30.83 25.87 34.38 18.05 39.74 52.89
Hangingstone Expansion (22,000 bbl/d) In Situ 3.22 9.49 27.46 6.28 31.17 49.05
Leismer (20,000 bbl/d) In Situ -1.49 15.16 10.65 23.34 6.41 31.07 43.25
Kirby South (20,000 bbl/d) In Situ 14.23 24.38 14.69 25.80 8.29 30.49 38.64
Tucker (19,000 bbl/d) In Situ 20.17 29.51 23.67 36.88 9.22 27.53 39.95
North Pelican Lake (16,000 bbl/d) In Situ 15.44 24.08 27.23 35.72 18.50 40.98 53.72
Orion (15,000 bbl/d) In Situ 9.87 19.57 -3.84 33.75 17.06 33.99 44.72
Lindbergh (15,000 bbl/d) In Situ 11.97 19.74 27.42 28.47 12.31 38.11 49.80
Bonnyville (14,000 bbl/d) In Situ 13.88 21.88 17.21 29.28 12.85 36.04 50.11
MacKay River (PetroChina) (11,000 bbl/d) In Situ -40.29 -29.16 -7.81 -13.78 8.65 11.65

Unrecovered capital costs

ggplot(os_data %>% filter(project,!project_payout,mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod),
                payout=as_factor(payout_status),
                payout=fct_relevel(payout,"PRE")
                ),
       aes(factor(reporting_year),unrecovered_balance_net_loss_at_eop/10^6,fill=mine))+
  #scale_fill_manual("",values=c("FALSE"="lightgreen","TRUE"="darkgreen"),labels=c("In Situ","Mine"))+
  #scale_fill_brewer("Project type")+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  scale_fill_brewer()+
  #coord_flip()+
  guides(fill="none",color="none")+
  facet_wrap(~project_name_factor,nrow = 1,labeller = label_wrap_gen(width = 10, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(legend.position="bottom",
        axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Unrecovered capital costs (CA$ million)",
       title="Unrecovered capital costs per royalty formula, oil sands mining projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")

ggplot(os_data %>% filter(project,!project_payout,!mine)%>%
         mutate(project_type=ifelse(mine,"Mine","In Situ"),
                project_name_factor=as.factor(project_name),
                project_name_factor=fct_reorder(project_name_factor,last_prod),
                payout=as_factor(payout_status),
                payout=fct_relevel(payout,"PRE")
                ),
       aes(factor(reporting_year),unrecovered_balance_net_loss_at_eop/10^6,fill=mine))+
  #scale_fill_manual("",values=c("FALSE"="lightgreen","TRUE"="darkgreen"),labels=c("In Situ","Mine"))+
  #scale_fill_brewer("Project type")+
  geom_col(size=0.25,position = position_dodge(width = .25),color="black")+
  #scale_color_viridis("Royalties Paid\nPer Barrel\nCleaned Bitumen")+
  scale_fill_brewer()+
  #coord_flip()+
  guides(fill="none",color="none")+
  facet_wrap(~project_name_factor,nrow = 2,labeller = label_wrap_gen(width = 8, multi_line = TRUE))+
  #scale_x_reverse()+
  work_theme()+
  theme(legend.position="bottom",
        axis.text.x = element_text(angle=90,hjust = 0.5,vjust=0.5)
        )+
  labs(x=NULL,y="Unrecovered capital costs (CA$ million)",
       title="Unrecovered capital costs per royalty formula, larger in situ oil sands projects",
       #subtitle="Excluding Electricity,by NAICS 4-Digit Code",
       caption="Data via Government of Alberta, graph by @andrew_leach")