Exercise Script

################ Exercise Script
################ Version: 1.0
################ Author: almeidam

#Cleaning Environment
rm(list = ls()) 

#Setting interest date
today <- Sys.Date()

#Loading pacman and packages through pacman (installing package if necessary)
if (!requireNamespace("pacman", quietly = TRUE)) {
  install.packages("pacman")
}
library(pacman)
pacman::p_load(
  "tidyverse",
  "readxl",
  "lubridate",
  "stringr",
  "janitor",
  "arsenal", 
  "diffdf",
  "knitr",
  "kableExtra",
  "RQuantLib",
  "rvest",
  "here",
  "zoo",
  "forecast"
  )

#Please notice: 
#working directory(WD) is set using "here" package. WD is set to be the in the place this R script is located (by the time the package "here" is loaded).
#The database "field_production_monthly.csv" was read in the same this .R script is located. 

here <- here::here()

# 1) Data gathering 

#Declaring the .csv link as object 
csv_link <- "https://factpages.npd.no/ReportServer_npdpublic?/FactPages/tableview/field_production_monthly&rs:Command=Render&rc:Toolbar=false&rc:Parameters=f&IpAddress=not_used&CultureCode=en&rs:Format=CSV&Top100=false"

#Declaring the name of the file to be saved
file_name <- "field_production_monthly.csv"

#Downloading the CSV file
download.file(csv_link, file_name, method = "auto")

#Defining local file path. The location will be the same as the WD.
local_file_path <- "field_production_monthly.csv"

# 2) Data Processing 
# a)
if (
  file.exists(
    local_file_path
           )) {
  df <- read.csv(local_file_path) #Checking if  file exists and reading database "field_production_monthly.csv" if exists in WD (downloaded in the previous step) in R. 

  cat("CSV file read successfully.\n")
} else {
  cat("CSV file not read successfully.\n")
}

## CSV file read successfully.

# b)

#Checking names of collumns
colnames <- colnames(df)
print(colnames)

##  [1] "prfInformationCarrier"             "prfYear"                          
##  [3] "prfMonth"                          "prfPrdOilNetMillSm3"              
##  [5] "prfPrdGasNetBillSm3"               "prfPrdNGLNetMillSm3"              
##  [7] "prfPrdCondensateNetMillSm3"        "prfPrdOeNetMillSm3"               
##  [9] "prfPrdProducedWaterInFieldMillSm3" "prfNpdidInformationCarrier"

#Declaring as object a subset of the first four collumns
subset_1 <- df %>% select(
  prfInformationCarrier, 
  prfYear,
  prfMonth,
  prfPrdOilNetMillSm3
)

# c) 

#Declaring observations that we're interested in 
vector <- c("EKOFISK", "ELDFISK",  "EMBLA")
#Subsetting "subset_df_field_production_monthly" by the vector above and by the condition of time (>=2013)
subset_2 <- subset(subset_1,df$prfInformationCarrier  %in% vector ) 
subset_2 <- subset_2 %>% filter(prfYear>=2013)

# d) 

#Creating time series with aggregated production collapsed for the three fields (by year and month)
`Greater Ekofisk Area` <- subset_2 %>% group_by(prfYear, prfMonth) %>%  
  summarise(prfPrdOilNetMillSm3 = sum(prfPrdOilNetMillSm3),
            .groups = 'drop')


# e) 

`Greater Ekofisk Area` <- `Greater Ekofisk Area` %>%  mutate(
  Date = dmy(paste0("01/",prfMonth,"/",prfYear))
)

class(`Greater Ekofisk Area`$Date)

## [1] "Date"

# f) If     1 barrel per day = 58.00 Sm3 per year, 
#           58.00 Sm3 per year = 58.00 Sm3 per 12 months =~ 4.83 Sm3 per month 
#             So, 1 barrel per day =~ 4.83 Sm3 per month and 
#                 1 Sm3 per month =~ 1 barrel per day/4.83. 
#             So, 1 Sm3 per month =~ 0.2068 barrel per day.
#             So, 1 Sm3 * 10^6 per month = 0.206896552 * 10^6 barrel per day = 206.896552 thousand barrel per day.


`Greater Ekofisk Area` <- `Greater Ekofisk Area` %>%  mutate(
  prfPrdOilNetThousandBarrelsperDay = prfPrdOilNetMillSm3*206.89
)

# g) 

#Creating an object with summarised information  (yearly average)
Yearly_Avg <- `Greater Ekofisk Area` %>%
  group_by(prfYear) %>%
  summarize(Avg_prfPrdOilNetMillSm3 = mean(prfPrdOilNetMillSm3))
#Merging the yearly average production with the information created above. 
subset_4 <- left_join(`Greater Ekofisk Area`, Yearly_Avg, by = "prfYear" )
#Including the deviation from the Yearly Avg
subset_4 <- subset_4 %>% mutate(
  Deviation_prfPrdOilNetMillSm3 = prfPrdOilNetMillSm3 - Avg_prfPrdOilNetMillSm3
)

# h)
# Calculating a 6-month moving average
subset_4$production_MA <- zoo::rollapply(subset_4$prfPrdOilNetMillSm3, width = 6,
                                         FUN = mean, align = "right", fill = NA)

# i) 

subset_5 <- subset_4 %>% select(prfPrdOilNetMillSm3, Date, prfPrdOilNetThousandBarrelsperDay, Avg_prfPrdOilNetMillSm3, Deviation_prfPrdOilNetMillSm3,production_MA)

# Creating and exporting as .png the table
table <- subset_5 %>%
  kbl() %>%
  kable_styling() %>% 
  add_header_above(c(" " = 1, "Output Table " = ncol(subset_5) - 1)) %>%  
  row_spec(0, color = "white", background = "black") 

kable(subset_5)

prfPrdOilNetMillSm3	Date	prfPrdOilNetThousandBarrelsperDay	Avg_prfPrdOilNetMillSm3	Deviation_prfPrdOilNetMillSm3	production_MA
0.81037	2013-01-01	167.65745	0.7039625	0.1064075	NA
0.72091	2013-02-01	149.14907	0.7039625	0.0169475	NA
0.78273	2013-03-01	161.93901	0.7039625	0.0787675	NA
0.72379	2013-04-01	149.74491	0.7039625	0.0198275	NA
0.71815	2013-05-01	148.57805	0.7039625	0.0141875	NA
0.09636	2013-06-01	19.93592	0.7039625	-0.6076025	0.6420517
0.75077	2013-07-01	155.32681	0.7039625	0.0468075	0.6321183
0.85387	2013-08-01	176.65716	0.7039625	0.1499075	0.6542783
0.69370	2013-09-01	143.51959	0.7039625	-0.0102625	0.6394400
0.80308	2013-10-01	166.14922	0.7039625	0.0991175	0.6526550
0.74803	2013-11-01	154.75993	0.7039625	0.0440675	0.6576350
0.74579	2013-12-01	154.29649	0.7039625	0.0418275	0.7658733
0.77271	2014-01-01	159.86597	0.7444292	0.0282808	0.7695300
0.73687	2014-02-01	152.45103	0.7444292	-0.0075592	0.7500300
0.77493	2014-03-01	160.32527	0.7444292	0.0305008	0.7635683
0.77648	2014-04-01	160.64595	0.7444292	0.0320508	0.7591350
0.77506	2014-05-01	160.35216	0.7444292	0.0306308	0.7636400
0.75329	2014-06-01	155.84817	0.7444292	0.0088608	0.7648900
0.74414	2014-07-01	153.95512	0.7444292	-0.0002892	0.7601283
0.74012	2014-08-01	153.12343	0.7444292	-0.0043092	0.7606700
0.67828	2014-09-01	140.32935	0.7444292	-0.0661492	0.7445617
0.73622	2014-10-01	152.31656	0.7444292	-0.0082092	0.7378517
0.70713	2014-11-01	146.29813	0.7444292	-0.0372992	0.7265300
0.73792	2014-12-01	152.66827	0.7444292	-0.0065092	0.7239683
0.73901	2015-01-01	152.89378	0.7491367	-0.0101267	0.7231133
0.61044	2015-02-01	126.29393	0.7491367	-0.1386967	0.7015000
0.73576	2015-03-01	152.22139	0.7491367	-0.0133767	0.7110800
0.72402	2015-04-01	149.79250	0.7491367	-0.0251167	0.7090467
0.78961	2015-05-01	163.36241	0.7491367	0.0404733	0.7227933
0.71099	2015-06-01	147.09672	0.7491367	-0.0381467	0.7183050
0.77654	2015-07-01	160.65836	0.7491367	0.0274033	0.7245600
0.78247	2015-08-01	161.88522	0.7491367	0.0333333	0.7532317
0.74666	2015-09-01	154.47649	0.7491367	-0.0024767	0.7550483
0.78872	2015-10-01	163.17828	0.7491367	0.0395833	0.7658317
0.79175	2015-11-01	163.80516	0.7491367	0.0426133	0.7661883
0.79367	2015-12-01	164.20239	0.7491367	0.0445333	0.7799683
0.80012	2016-01-01	165.53683	0.7281300	0.0719900	0.7838983
0.72431	2016-02-01	149.85250	0.7281300	-0.0038200	0.7742050
0.79488	2016-03-01	164.45272	0.7281300	0.0667500	0.7822417
0.74690	2016-04-01	154.52614	0.7281300	0.0187700	0.7752717
0.73944	2016-05-01	152.98274	0.7281300	0.0113100	0.7665533
0.12461	2016-06-01	25.78056	0.7281300	-0.6035200	0.6550433
0.83346	2016-07-01	172.43454	0.7281300	0.1053300	0.6606000
0.83065	2016-08-01	171.85318	0.7281300	0.1025200	0.6783233
0.79105	2016-09-01	163.66033	0.7281300	0.0629200	0.6776850
0.79349	2016-10-01	164.16515	0.7281300	0.0653600	0.6854500
0.77113	2016-11-01	159.53909	0.7281300	0.0430000	0.6907317
0.78752	2016-12-01	162.93001	0.7281300	0.0593900	0.8012167
0.79484	2017-01-01	164.44445	0.7512467	0.0435933	0.7947800
0.72725	2017-02-01	150.46075	0.7512467	-0.0239967	0.7775467
0.80377	2017-03-01	166.29198	0.7512467	0.0525233	0.7796667
0.76425	2017-04-01	158.11568	0.7512467	0.0130033	0.7747933
0.76637	2017-05-01	158.55429	0.7512467	0.0151233	0.7740000
0.76544	2017-06-01	158.36188	0.7512467	0.0141933	0.7703200
0.77599	2017-07-01	160.54457	0.7512467	0.0247433	0.7671783
0.77404	2017-08-01	160.14114	0.7512467	0.0227933	0.7749767
0.71783	2017-09-01	148.51185	0.7512467	-0.0334167	0.7606533
0.73358	2017-10-01	151.77037	0.7512467	-0.0176667	0.7555417
0.69786	2017-11-01	144.38026	0.7512467	-0.0533867	0.7441233
0.69374	2017-12-01	143.52787	0.7512467	-0.0575067	0.7321733
0.72387	2018-01-01	149.76146	0.6996442	0.0242258	0.7234867
0.63296	2018-02-01	130.95309	0.6996442	-0.0666842	0.6999733
0.73152	2018-03-01	151.34417	0.6996442	0.0318758	0.7022550
0.70240	2018-04-01	145.31954	0.6996442	0.0027558	0.6970583
0.69809	2018-05-01	144.42784	0.6996442	-0.0015542	0.6970967
0.66827	2018-06-01	138.25838	0.6996442	-0.0313742	0.6928517
0.72605	2018-07-01	150.21248	0.6996442	0.0264058	0.6932150
0.73708	2018-08-01	152.49448	0.6996442	0.0374358	0.7105683
0.70763	2018-09-01	146.40157	0.6996442	0.0079858	0.7065867
0.70408	2018-10-01	145.66711	0.6996442	0.0044358	0.7068667
0.63548	2018-11-01	131.47446	0.6996442	-0.0641642	0.6964317
0.72830	2018-12-01	150.67799	0.6996442	0.0286558	0.7064367
0.72134	2019-01-01	149.23803	0.6675308	0.0538092	0.7056517
0.64056	2019-02-01	132.52546	0.6675308	-0.0269708	0.6895650
0.70318	2019-03-01	145.48091	0.6675308	0.0356492	0.6888233
0.71085	2019-04-01	147.06776	0.6675308	0.0433192	0.6899517
0.73281	2019-05-01	151.61106	0.6675308	0.0652792	0.7061733
0.09530	2019-06-01	19.71662	0.6675308	-0.5722308	0.6006733
0.82424	2019-07-01	170.52701	0.6675308	0.1567092	0.6178233
0.78850	2019-08-01	163.13277	0.6675308	0.1209692	0.6424800
0.72476	2019-09-01	149.94560	0.6675308	0.0572292	0.6460767
0.74892	2019-10-01	154.94406	0.6675308	0.0813892	0.6524217
0.69223	2019-11-01	143.21546	0.6675308	0.0246992	0.6456583
0.62768	2019-12-01	129.86072	0.6675308	-0.0398508	0.7343883
0.71335	2020-01-01	147.58498	0.6366642	0.0766858	0.7159067
0.65210	2020-02-01	134.91297	0.6366642	0.0154358	0.6931733
0.67565	2020-03-01	139.78523	0.6366642	0.0389858	0.6849883
0.62563	2020-04-01	129.43659	0.6366642	-0.0110342	0.6644400
0.63938	2020-05-01	132.28133	0.6366642	0.0027158	0.6556317
0.54558	2020-06-01	112.87505	0.6366642	-0.0910842	0.6419483
0.66849	2020-07-01	138.30390	0.6366642	0.0318258	0.6344717
0.63238	2020-08-01	130.83310	0.6366642	-0.0042842	0.6311850
0.61876	2020-09-01	128.01526	0.6366642	-0.0179042	0.6217033
0.62948	2020-10-01	130.23312	0.6366642	-0.0071842	0.6223450
0.60313	2020-11-01	124.78157	0.6366642	-0.0335342	0.6163033
0.63604	2020-12-01	131.59032	0.6366642	-0.0006242	0.6313800
0.63053	2021-01-01	130.45035	0.6169467	0.0135833	0.6250533
0.56820	2021-02-01	117.55490	0.6169467	-0.0487467	0.6143567
0.63517	2021-03-01	131.41032	0.6169467	0.0182233	0.6170917
0.56940	2021-04-01	117.80317	0.6169467	-0.0475467	0.6070783
0.62390	2021-05-01	129.07867	0.6169467	0.0069533	0.6105400
0.63407	2021-06-01	131.18274	0.6169467	0.0171233	0.6102117
0.63644	2021-07-01	131.67307	0.6169467	0.0194933	0.6111967
0.63250	2021-08-01	130.85792	0.6169467	0.0155533	0.6219133
0.61978	2021-09-01	128.22628	0.6169467	0.0028333	0.6193483
0.64444	2021-10-01	133.32819	0.6169467	0.0274933	0.6318550
0.59878	2021-11-01	123.88159	0.6169467	-0.0181667	0.6276683
0.61015	2021-12-01	126.23393	0.6169467	-0.0067967	0.6236817
0.58064	2022-01-01	120.12861	0.5422725	0.0383675	0.6143817
0.51852	2022-02-01	107.27660	0.5422725	-0.0237525	0.5953850
0.56266	2022-03-01	116.40873	0.5422725	0.0203875	0.5858650
0.54591	2022-04-01	112.94332	0.5422725	0.0036375	0.5694433
0.47374	2022-05-01	98.01207	0.5422725	-0.0685325	0.5486033
0.15758	2022-06-01	32.60173	0.5422725	-0.3846925	0.4731750
0.64375	2022-07-01	133.18544	0.5422725	0.1014775	0.4836933
0.62479	2022-08-01	129.26280	0.5422725	0.0825175	0.5014050
0.60307	2022-09-01	124.76915	0.5422725	0.0607975	0.5081400
0.62009	2022-10-01	128.29042	0.5422725	0.0778175	0.5205033
0.58303	2022-11-01	120.62308	0.5422725	0.0407575	0.5387183
0.59349	2022-12-01	122.78715	0.5422725	0.0512175	0.6113700
0.59134	2023-01-01	122.34233	0.5446667	0.0466733	0.6026350
0.52786	2023-02-01	109.20896	0.5446667	-0.0168067	0.5864800
0.57338	2023-03-01	118.62659	0.5446667	0.0287133	0.5815317
0.54020	2023-04-01	111.76198	0.5446667	-0.0044667	0.5682167
0.52438	2023-05-01	108.48898	0.5446667	-0.0202867	0.5584417
0.51084	2023-06-01	105.68769	0.5446667	-0.0338267	0.5446667

table <- table %>%
  save_kable(file = "output_table.png")

## save_kable will have the best result with magick installed.

#Exporting as .xlsx
writexl::write_xlsx(subset_5, path = "output_table.xlsx" )


# j) 


subset_5$Date <- as.POSIXct(subset_5$Date)

plot1 <- subset_5 %>% 
  ggplot(aes(x=Date)) +
  geom_line(aes(y=prfPrdOilNetMillSm3, color="Actual Production (Mill Sm3/month)")) +
  geom_line(aes(y=Avg_prfPrdOilNetMillSm3,  color="Yearly Average Production (Mill Sm3/month)"))+
  geom_line(aes(y=Deviation_prfPrdOilNetMillSm3, color="Deviation (Mill Sm3/month)"))+ 
  geom_line(aes(y=production_MA, color="Moving Average - 6 Months (Mill Sm3/month)"))+ 
  labs(title = "Evolution of Aggregated Oil Production, Average Production, Deviation and Moving Average",
       subtitle = "EKOFISK, ELDFISK and EMBLA (Mill Sm3/month)",
       x = "Months",
       y = "Oil (Mill Sm3)")+
  ggthemes::theme_clean() +
  theme(plot.title = element_text(face = "bold"))+
  scale_x_datetime(date_labels = '%b %Y', date_breaks = '6 months')+
  theme(axis.text.x=element_text(angle=60,hjust=1)) +
  scale_color_manual(values = c("#cb181d", "#181CA4", "#23D747", "#FFFF33"),
                     labels = c("Actual Production (Mill Sm3/month)", 
                                "Yearly Average Production (Mill Sm3/month)", 
                                "Deviation (Mill Sm3/month)", 
                                "Moving Average - 6 Months (Mill Sm3/month)")) +
  theme(legend.position = "top")

print(plot1)

## Warning: Removed 5 rows containing missing values (`geom_line()`).

ggsave(paste0("plot1.png"), 
       plot = last_plot(),
       width = 15, height = 7, 
       units = "in", 
       dpi = 300)  

## Warning: Removed 5 rows containing missing values (`geom_line()`).