Argus Media
Viviane Rocha
3/21/2022
Technical test to Data Engineer - Argus Media
This is the technical test for the data engineer position at Argus Media. The database is from Argus and is fictitious.
The tasks to be completed are:
1) Create a new table containing the index price for each deal date that is included in the raw data.
The rules for creating an index price are:
a) Only include a deal if the delivery period is within 180 days of the Deal Date.
b) Index prices are calculated as a Volume-Weighted Average Price (VWAP) of all the relevant deals.
c) There are two indices to calculate:
i.) COAL2: Coal deals that are delivered to Northwest Europe (delivery location in ARA, AMS, ROT, ANT).
ii.) COAL4: Coal deals that are delivered from South Africa.
2) Add a macro that will export the contents of the “Data” sheet to a CSV file at a path specified by the user. The data should be sorted by COMMODITY SOURCE LOCATION in the following order:
i.) South Africa <br>
ii.) Australia <br>
iii.) Columbia <br>Dataset completo: Dataset Argus Media
For this, we’ll use the packages below:
install.packages("tidyverse")## package 'tidyverse' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
## C:\Users\Viviane\AppData\Local\Temp\RtmpqKsPUS\downloaded_packagesinstall.packages("tidyr")## package 'tidyr' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
## C:\Users\Viviane\AppData\Local\Temp\RtmpqKsPUS\downloaded_packagesinstall.packages("stringr")## package 'stringr' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
## C:\Users\Viviane\AppData\Local\Temp\RtmpqKsPUS\downloaded_packagesinstall.packages("lubridate")## package 'lubridate' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
## C:\Users\Viviane\AppData\Local\Temp\RtmpqKsPUS\downloaded_packagesinstall.packages("ggplot")
install.packages("plotly")## package 'plotly' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
## C:\Users\Viviane\AppData\Local\Temp\RtmpqKsPUS\downloaded_packagesinstall.packages("rmarkdown")## package 'rmarkdown' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
## C:\Users\Viviane\AppData\Local\Temp\RtmpqKsPUS\downloaded_packagestinytex::install_tinytex()library(ggplot2)## Warning: package 'ggplot2' was built under R version 4.1.3library(tidyverse)## Warning: package 'tidyverse' was built under R version 4.1.3## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v tibble 3.1.6 v dplyr 1.0.8
## v tidyr 1.2.0 v stringr 1.4.0
## v readr 2.1.2 v forcats 0.5.1
## v purrr 0.3.4## Warning: package 'tibble' was built under R version 4.1.3## Warning: package 'tidyr' was built under R version 4.1.3## Warning: package 'readr' was built under R version 4.1.3## Warning: package 'purrr' was built under R version 4.1.3## Warning: package 'dplyr' was built under R version 4.1.3## Warning: package 'stringr' was built under R version 4.1.3## Warning: package 'forcats' was built under R version 4.1.3## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()library(dplyr)
library(tidyr)
library(stringr)
library(lubridate)## Warning: package 'lubridate' was built under R version 4.1.3##
## Attaching package: 'lubridate'## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, unionlibrary(plotly)## Warning: package 'plotly' was built under R version 4.1.3##
## Attaching package: 'plotly'## The following object is masked from 'package:ggplot2':
##
## last_plot## The following object is masked from 'package:stats':
##
## filter## The following object is masked from 'package:graphics':
##
## layoutlibrary(knitr)## Warning: package 'knitr' was built under R version 4.1.3library(rmarkdown)## Warning: package 'rmarkdown' was built under R version 4.1.3install.packages('plyr', repos = "http://cran.us.r-project.org")## Installing package into 'C:/Users/Viviane/Documents/R/win-library/4.1'
## (as 'lib' is unspecified)## package 'plyr' successfully unpacked and MD5 sums checked
##
## The downloaded binary packages are in
## C:\Users\Viviane\AppData\Local\Temp\RtmpqKsPUS\downloaded_packagesWe start load the dataset
data = read.csv("dataArgus.csv", header = TRUE)Reading the first data from the dataset
head(data)## ID DEAL.DATE COMMODITY COMMODITY.SOURCE.LOCATION DELIVERY.LOCATION
## 1 1 10-May-2021 Coal Australia AMS
## 2 2 10-May-2021 Coal Australia SOT
## 3 3 10-May-2021 Coal Australia ANT
## 4 4 10-May-2021 Coal South Africa ANT
## 5 5 10-May-2021 Coal Australia ANT
## 6 6 10-May-2021 Coal Australia ANT
## DELIVERY.MONTH DELIVERY.YEAR VOLUME PRICE
## 1 JUL 2021 200 91.75
## 2 JUN 2021 100 93.49
## 3 JUL 2021 25 93.04
## 4 JUN 2021 10 96.32
## 5 MAY 2021 100 93.49
## 6 SEP 2021 50 93.78Showing the size of the dataset
dim(data)## [1] 159 9Showing the data type of the dataset
str(data)## 'data.frame': 159 obs. of 9 variables:
## $ ID : int 1 2 3 4 5 6 7 8 9 10 ...
## $ DEAL.DATE : chr "10-May-2021" "10-May-2021" "10-May-2021" "10-May-2021" ...
## $ COMMODITY : chr "Coal" "Coal" "Coal" "Coal" ...
## $ COMMODITY.SOURCE.LOCATION: chr "Australia" "Australia" "Australia" "South Africa" ...
## $ DELIVERY.LOCATION : chr "AMS" "SOT" "ANT" "ANT" ...
## $ DELIVERY.MONTH : chr "JUL" "JUN" "JUL" "JUN" ...
## $ DELIVERY.YEAR : int 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 ...
## $ VOLUME : int 200 100 25 10 100 50 10 200 250 100 ...
## $ PRICE : num 91.8 93.5 93 96.3 93.5 ...Checking for Null or Missing Data
is.na(data)## ID DEAL.DATE COMMODITY COMMODITY.SOURCE.LOCATION DELIVERY.LOCATION
## [1,] FALSE FALSE FALSE FALSE FALSE
## [2,] FALSE FALSE FALSE FALSE FALSE
## [3,] FALSE FALSE FALSE FALSE FALSE
## [4,] FALSE FALSE FALSE FALSE FALSE
## [5,] FALSE FALSE FALSE FALSE FALSE
## [6,] FALSE FALSE FALSE FALSE FALSE
## [7,] FALSE FALSE FALSE FALSE FALSE
## [8,] FALSE FALSE FALSE FALSE FALSE
## [9,] FALSE FALSE FALSE FALSE FALSE
## [10,] FALSE FALSE FALSE FALSE FALSE
## [11,] FALSE FALSE FALSE FALSE FALSE
## [12,] FALSE FALSE FALSE FALSE FALSE
## [13,] FALSE FALSE FALSE FALSE FALSE
## [14,] FALSE FALSE FALSE FALSE FALSE
## [15,] FALSE FALSE FALSE FALSE FALSE
## [16,] FALSE FALSE FALSE FALSE FALSE
## [17,] FALSE FALSE FALSE FALSE FALSE
## [18,] FALSE FALSE FALSE FALSE FALSE
## [19,] FALSE FALSE FALSE FALSE FALSE
## [20,] FALSE FALSE FALSE FALSE FALSE
## [21,] FALSE FALSE FALSE FALSE FALSE
## [22,] FALSE FALSE FALSE FALSE FALSE
## [23,] FALSE FALSE FALSE FALSE FALSE
## [24,] FALSE FALSE FALSE FALSE FALSE
## [25,] FALSE FALSE FALSE FALSE FALSE
## [26,] FALSE FALSE FALSE FALSE FALSE
## [27,] FALSE FALSE FALSE FALSE FALSE
## [28,] FALSE FALSE FALSE FALSE FALSE
## [29,] FALSE FALSE FALSE FALSE FALSE
## [30,] FALSE FALSE FALSE FALSE FALSE
## [31,] FALSE FALSE FALSE FALSE FALSE
## [32,] FALSE FALSE FALSE FALSE FALSE
## [33,] FALSE FALSE FALSE FALSE FALSE
## [34,] FALSE FALSE FALSE FALSE FALSE
## [35,] FALSE FALSE FALSE FALSE FALSE
## [36,] FALSE FALSE FALSE FALSE FALSE
## [37,] FALSE FALSE FALSE FALSE FALSE
## [38,] FALSE FALSE FALSE FALSE FALSE
## [39,] FALSE FALSE FALSE FALSE FALSE
## [40,] FALSE FALSE FALSE FALSE FALSE
## [41,] FALSE FALSE FALSE FALSE FALSE
## [42,] FALSE FALSE FALSE FALSE FALSE
## [43,] FALSE FALSE FALSE FALSE FALSE
## [44,] FALSE FALSE FALSE FALSE FALSE
## [45,] FALSE FALSE FALSE FALSE FALSE
## [46,] FALSE FALSE FALSE FALSE FALSE
## [47,] FALSE FALSE FALSE FALSE FALSE
## [48,] FALSE FALSE FALSE FALSE FALSE
## [49,] FALSE FALSE FALSE FALSE FALSE
## [50,] FALSE FALSE FALSE FALSE FALSE
## [51,] FALSE FALSE FALSE FALSE FALSE
## [52,] FALSE FALSE FALSE FALSE FALSE
## [53,] FALSE FALSE FALSE FALSE FALSE
## [54,] FALSE FALSE FALSE FALSE FALSE
## [55,] FALSE FALSE FALSE FALSE FALSE
## [56,] FALSE FALSE FALSE FALSE FALSE
## [57,] FALSE FALSE FALSE FALSE FALSE
## [58,] FALSE FALSE FALSE FALSE FALSE
## [59,] FALSE FALSE FALSE FALSE FALSE
## [60,] FALSE FALSE FALSE FALSE FALSE
## [61,] FALSE FALSE FALSE FALSE FALSE
## [62,] FALSE FALSE FALSE FALSE FALSE
## [63,] FALSE FALSE FALSE FALSE FALSE
## [64,] FALSE FALSE FALSE FALSE FALSE
## [65,] FALSE FALSE FALSE FALSE FALSE
## [66,] FALSE FALSE FALSE FALSE FALSE
## [67,] FALSE FALSE FALSE FALSE FALSE
## [68,] FALSE FALSE FALSE FALSE FALSE
## [69,] FALSE FALSE FALSE FALSE FALSE
## [70,] FALSE FALSE FALSE FALSE FALSE
## [71,] FALSE FALSE FALSE FALSE FALSE
## [72,] FALSE FALSE FALSE FALSE FALSE
## [73,] FALSE FALSE FALSE FALSE FALSE
## [74,] FALSE FALSE FALSE FALSE FALSE
## [75,] FALSE FALSE FALSE FALSE FALSE
## [76,] FALSE FALSE FALSE FALSE FALSE
## [77,] FALSE FALSE FALSE FALSE FALSE
## [78,] FALSE FALSE FALSE FALSE FALSE
## [79,] FALSE FALSE FALSE FALSE FALSE
## [80,] FALSE FALSE FALSE FALSE FALSE
## [81,] FALSE FALSE FALSE FALSE FALSE
## [82,] FALSE FALSE FALSE FALSE FALSE
## [83,] FALSE FALSE FALSE FALSE FALSE
## [84,] FALSE FALSE FALSE FALSE FALSE
## [85,] FALSE FALSE FALSE FALSE FALSE
## [86,] FALSE FALSE FALSE FALSE FALSE
## [87,] FALSE FALSE FALSE FALSE FALSE
## [88,] FALSE FALSE FALSE FALSE FALSE
## [89,] FALSE FALSE FALSE FALSE FALSE
## [90,] FALSE FALSE FALSE FALSE FALSE
## [91,] FALSE FALSE FALSE FALSE FALSE
## [92,] FALSE FALSE FALSE FALSE FALSE
## [93,] FALSE FALSE FALSE FALSE FALSE
## [94,] FALSE FALSE FALSE FALSE FALSE
## [95,] FALSE FALSE FALSE FALSE FALSE
## [96,] FALSE FALSE FALSE FALSE FALSE
## [97,] FALSE FALSE FALSE FALSE FALSE
## [98,] FALSE FALSE FALSE FALSE FALSE
## [99,] FALSE FALSE FALSE FALSE FALSE
## [100,] FALSE FALSE FALSE FALSE FALSE
## [101,] FALSE FALSE FALSE FALSE FALSE
## [102,] FALSE FALSE FALSE FALSE FALSE
## [103,] FALSE FALSE FALSE FALSE FALSE
## [104,] FALSE FALSE FALSE FALSE FALSE
## [105,] FALSE FALSE FALSE FALSE FALSE
## [106,] FALSE FALSE FALSE FALSE FALSE
## [107,] FALSE FALSE FALSE FALSE FALSE
## [108,] FALSE FALSE FALSE FALSE FALSE
## [109,] FALSE FALSE FALSE FALSE FALSE
## [110,] FALSE FALSE FALSE FALSE FALSE
## [111,] FALSE FALSE FALSE FALSE FALSE
## [112,] FALSE FALSE FALSE FALSE FALSE
## [113,] FALSE FALSE FALSE FALSE FALSE
## [114,] FALSE FALSE FALSE FALSE FALSE
## [115,] FALSE FALSE FALSE FALSE FALSE
## [116,] FALSE FALSE FALSE FALSE FALSE
## [117,] FALSE FALSE FALSE FALSE FALSE
## [118,] FALSE FALSE FALSE FALSE FALSE
## [119,] FALSE FALSE FALSE FALSE FALSE
## [120,] FALSE FALSE FALSE FALSE FALSE
## [121,] FALSE FALSE FALSE FALSE FALSE
## [122,] FALSE FALSE FALSE FALSE FALSE
## [123,] FALSE FALSE FALSE FALSE FALSE
## [124,] FALSE FALSE FALSE FALSE FALSE
## [125,] FALSE FALSE FALSE FALSE FALSE
## [126,] FALSE FALSE FALSE FALSE FALSE
## [127,] FALSE FALSE FALSE FALSE FALSE
## [128,] FALSE FALSE FALSE FALSE FALSE
## [129,] FALSE FALSE FALSE FALSE FALSE
## [130,] FALSE FALSE FALSE FALSE FALSE
## [131,] FALSE FALSE FALSE FALSE FALSE
## [132,] FALSE FALSE FALSE FALSE FALSE
## [133,] FALSE FALSE FALSE FALSE FALSE
## [134,] FALSE FALSE FALSE FALSE FALSE
## [135,] FALSE FALSE FALSE FALSE FALSE
## [136,] FALSE FALSE FALSE FALSE FALSE
## [137,] FALSE FALSE FALSE FALSE FALSE
## [138,] FALSE FALSE FALSE FALSE FALSE
## [139,] FALSE FALSE FALSE FALSE FALSE
## [140,] FALSE FALSE FALSE FALSE FALSE
## [141,] FALSE FALSE FALSE FALSE FALSE
## [142,] FALSE FALSE FALSE FALSE FALSE
## [143,] FALSE FALSE FALSE FALSE FALSE
## [144,] FALSE FALSE FALSE FALSE FALSE
## [145,] FALSE FALSE FALSE FALSE FALSE
## [146,] FALSE FALSE FALSE FALSE FALSE
## [147,] FALSE FALSE FALSE FALSE FALSE
## [148,] FALSE FALSE FALSE FALSE FALSE
## [149,] FALSE FALSE FALSE FALSE FALSE
## [150,] FALSE FALSE FALSE FALSE FALSE
## [151,] FALSE FALSE FALSE FALSE FALSE
## [152,] FALSE FALSE FALSE FALSE FALSE
## [153,] FALSE FALSE FALSE FALSE FALSE
## [154,] FALSE FALSE FALSE FALSE FALSE
## [155,] FALSE FALSE FALSE FALSE FALSE
## [156,] FALSE FALSE FALSE FALSE FALSE
## [157,] FALSE FALSE FALSE FALSE FALSE
## [158,] FALSE FALSE FALSE FALSE FALSE
## [159,] FALSE FALSE FALSE FALSE FALSE
## DELIVERY.MONTH DELIVERY.YEAR VOLUME PRICE
## [1,] FALSE FALSE FALSE FALSE
## [2,] FALSE FALSE FALSE FALSE
## [3,] FALSE FALSE FALSE FALSE
## [4,] FALSE FALSE FALSE FALSE
## [5,] FALSE FALSE FALSE FALSE
## [6,] FALSE FALSE FALSE FALSE
## [7,] FALSE FALSE FALSE FALSE
## [8,] FALSE FALSE FALSE FALSE
## [9,] FALSE FALSE FALSE FALSE
## [10,] FALSE FALSE FALSE FALSE
## [11,] FALSE FALSE FALSE FALSE
## [12,] FALSE FALSE FALSE FALSE
## [13,] FALSE FALSE FALSE FALSE
## [14,] FALSE FALSE FALSE FALSE
## [15,] FALSE FALSE FALSE FALSE
## [16,] FALSE FALSE FALSE FALSE
## [17,] FALSE FALSE FALSE FALSE
## [18,] FALSE FALSE FALSE FALSE
## [19,] FALSE FALSE FALSE FALSE
## [20,] FALSE FALSE FALSE FALSE
## [21,] FALSE FALSE FALSE FALSE
## [22,] FALSE FALSE FALSE FALSE
## [23,] FALSE FALSE FALSE FALSE
## [24,] FALSE FALSE FALSE FALSE
## [25,] FALSE FALSE FALSE FALSE
## [26,] FALSE FALSE FALSE FALSE
## [27,] FALSE FALSE FALSE FALSE
## [28,] FALSE FALSE FALSE FALSE
## [29,] FALSE FALSE FALSE FALSE
## [30,] FALSE FALSE FALSE FALSE
## [31,] FALSE FALSE FALSE FALSE
## [32,] FALSE FALSE FALSE FALSE
## [33,] FALSE FALSE FALSE FALSE
## [34,] FALSE FALSE FALSE FALSE
## [35,] FALSE FALSE FALSE FALSE
## [36,] FALSE FALSE FALSE FALSE
## [37,] FALSE FALSE FALSE FALSE
## [38,] FALSE FALSE FALSE FALSE
## [39,] FALSE FALSE FALSE FALSE
## [40,] FALSE FALSE FALSE FALSE
## [41,] FALSE FALSE FALSE FALSE
## [42,] FALSE FALSE FALSE FALSE
## [43,] FALSE FALSE FALSE FALSE
## [44,] FALSE FALSE FALSE FALSE
## [45,] FALSE FALSE FALSE FALSE
## [46,] FALSE FALSE FALSE FALSE
## [47,] FALSE FALSE FALSE FALSE
## [48,] FALSE FALSE FALSE FALSE
## [49,] FALSE FALSE FALSE FALSE
## [50,] FALSE FALSE FALSE FALSE
## [51,] FALSE FALSE FALSE FALSE
## [52,] FALSE FALSE FALSE FALSE
## [53,] FALSE FALSE FALSE FALSE
## [54,] FALSE FALSE FALSE FALSE
## [55,] FALSE FALSE FALSE FALSE
## [56,] FALSE FALSE FALSE FALSE
## [57,] FALSE FALSE FALSE FALSE
## [58,] FALSE FALSE FALSE FALSE
## [59,] FALSE FALSE FALSE FALSE
## [60,] FALSE FALSE FALSE FALSE
## [61,] FALSE FALSE FALSE FALSE
## [62,] FALSE FALSE FALSE FALSE
## [63,] FALSE FALSE FALSE FALSE
## [64,] FALSE FALSE FALSE FALSE
## [65,] FALSE FALSE FALSE FALSE
## [66,] FALSE FALSE FALSE FALSE
## [67,] FALSE FALSE FALSE FALSE
## [68,] FALSE FALSE FALSE FALSE
## [69,] FALSE FALSE FALSE FALSE
## [70,] FALSE FALSE FALSE FALSE
## [71,] FALSE FALSE FALSE FALSE
## [72,] FALSE FALSE FALSE FALSE
## [73,] FALSE FALSE FALSE FALSE
## [74,] FALSE FALSE FALSE FALSE
## [75,] FALSE FALSE FALSE FALSE
## [76,] FALSE FALSE FALSE FALSE
## [77,] FALSE FALSE FALSE FALSE
## [78,] FALSE FALSE FALSE FALSE
## [79,] FALSE FALSE FALSE FALSE
## [80,] FALSE FALSE FALSE FALSE
## [81,] FALSE FALSE FALSE FALSE
## [82,] FALSE FALSE FALSE FALSE
## [83,] FALSE FALSE FALSE FALSE
## [84,] FALSE FALSE FALSE FALSE
## [85,] FALSE FALSE FALSE FALSE
## [86,] FALSE FALSE FALSE FALSE
## [87,] FALSE FALSE FALSE FALSE
## [88,] FALSE FALSE FALSE FALSE
## [89,] FALSE FALSE FALSE FALSE
## [90,] FALSE FALSE FALSE FALSE
## [91,] FALSE FALSE FALSE FALSE
## [92,] FALSE FALSE FALSE FALSE
## [93,] FALSE FALSE FALSE FALSE
## [94,] FALSE FALSE FALSE FALSE
## [95,] FALSE FALSE FALSE FALSE
## [96,] FALSE FALSE FALSE FALSE
## [97,] FALSE FALSE FALSE FALSE
## [98,] FALSE FALSE FALSE FALSE
## [99,] FALSE FALSE FALSE FALSE
## [100,] FALSE FALSE FALSE FALSE
## [101,] FALSE FALSE FALSE FALSE
## [102,] FALSE FALSE FALSE FALSE
## [103,] FALSE FALSE FALSE FALSE
## [104,] FALSE FALSE FALSE FALSE
## [105,] FALSE FALSE FALSE FALSE
## [106,] FALSE FALSE FALSE FALSE
## [107,] FALSE FALSE FALSE FALSE
## [108,] FALSE FALSE FALSE FALSE
## [109,] FALSE FALSE FALSE FALSE
## [110,] FALSE FALSE FALSE FALSE
## [111,] FALSE FALSE FALSE FALSE
## [112,] FALSE FALSE FALSE FALSE
## [113,] FALSE FALSE FALSE FALSE
## [114,] FALSE FALSE FALSE FALSE
## [115,] FALSE FALSE FALSE FALSE
## [116,] FALSE FALSE FALSE FALSE
## [117,] FALSE FALSE FALSE FALSE
## [118,] FALSE FALSE FALSE FALSE
## [119,] FALSE FALSE FALSE FALSE
## [120,] FALSE FALSE FALSE FALSE
## [121,] FALSE FALSE FALSE FALSE
## [122,] FALSE FALSE FALSE FALSE
## [123,] FALSE FALSE FALSE FALSE
## [124,] FALSE FALSE FALSE FALSE
## [125,] FALSE FALSE FALSE FALSE
## [126,] FALSE FALSE FALSE FALSE
## [127,] FALSE FALSE FALSE FALSE
## [128,] FALSE FALSE FALSE FALSE
## [129,] FALSE FALSE FALSE FALSE
## [130,] FALSE FALSE FALSE FALSE
## [131,] FALSE FALSE FALSE FALSE
## [132,] FALSE FALSE FALSE FALSE
## [133,] FALSE FALSE FALSE FALSE
## [134,] FALSE FALSE FALSE FALSE
## [135,] FALSE FALSE FALSE FALSE
## [136,] FALSE FALSE FALSE FALSE
## [137,] FALSE FALSE FALSE FALSE
## [138,] FALSE FALSE FALSE FALSE
## [139,] FALSE FALSE FALSE FALSE
## [140,] FALSE FALSE FALSE FALSE
## [141,] FALSE FALSE FALSE FALSE
## [142,] FALSE FALSE FALSE FALSE
## [143,] FALSE FALSE FALSE FALSE
## [144,] FALSE FALSE FALSE FALSE
## [145,] FALSE FALSE FALSE FALSE
## [146,] FALSE FALSE FALSE FALSE
## [147,] FALSE FALSE FALSE FALSE
## [148,] FALSE FALSE FALSE FALSE
## [149,] FALSE FALSE FALSE FALSE
## [150,] FALSE FALSE FALSE FALSE
## [151,] FALSE FALSE FALSE FALSE
## [152,] FALSE FALSE FALSE FALSE
## [153,] FALSE FALSE FALSE FALSE
## [154,] FALSE FALSE FALSE FALSE
## [155,] FALSE FALSE FALSE FALSE
## [156,] FALSE FALSE FALSE FALSE
## [157,] FALSE FALSE FALSE FALSE
## [158,] FALSE FALSE FALSE FALSE
## [159,] FALSE FALSE FALSE FALSEis.null(data)## [1] FALSEShowing the DEAL.DATE column class and transforming it into data.
class(data$DEAL.DATE)## [1] "character"data$DEAL.DATE = dmy(data$DEAL.DATE)
class(data$DEAL.DATE)## [1] "Date"Create the DELIVERYDATE column
data$DELIVERYDATE[data$DELIVERY.MONTH == "JAN"] <- "01-31"
data$DELIVERYDATE[data$DELIVERY.MONTH == "FEV"] <- "02-28"
data$DELIVERYDATE[data$DELIVERY.MONTH == "MAR"] <- "03-31"
data$DELIVERYDATE[data$DELIVERY.MONTH == "APR"] <- "04-30"
data$DELIVERYDATE[data$DELIVERY.MONTH == "MAY"] <- "05-31"
data$DELIVERYDATE[data$DELIVERY.MONTH == "JUN"] <- "06-30"
data$DELIVERYDATE[data$DELIVERY.MONTH == "JUL"] <- "07-31"
data$DELIVERYDATE[data$DELIVERY.MONTH == "AUG"] <- "08-31"
data$DELIVERYDATE[data$DELIVERY.MONTH == "SEP"] <- "09-30"
data$DELIVERYDATE[data$DELIVERY.MONTH == "OCT"] <- "10-31"
data$DELIVERYDATE[data$DELIVERY.MONTH == "NOV"] <- "11-30"
data$DELIVERYDATE[data$DELIVERY.MONTH == "DEC"] <- "12-31"
DELIVERY_DATE <- paste(data$DELIVERY.YEAR, data$DELIVERYDATE,
sep = "-")
DELIVERY_DATE <- data.frame(DELIVERY_DATE)Showing the DEAL.DATE column class and transforming it into data.
class(DELIVERY_DATE$DELIVERY_DATE)## [1] "character"Create the ID column and merge
DELIVERY_DATE$ID <- 1:159
df <- merge(data, DELIVERY_DATE, by = "ID")
class(df$DEAL.DATE)## [1] "Date"class(df$DELIVERYDATE)## [1] "character"df$DELIVERY_DATE = ymd(df$DELIVERY_DATE)
class(df$DELIVERY_DATE)## [1] "Date"Create the column with the difference between the delivery and the offer date
df$PERIOD <- df$DELIVERY_DATE - df$DEAL.DATEApplying a filter for a period greater than 180 days.
ACIMA <- filter(df, PERIOD >= 180)Showing the DEAL.DATE column class and transforming it into data.
class(df$DELIVERY_DATE)## [1] "Date"df$DELIVERY_DATE = ydm(df$DELIVERY_DATE)## Warning: All formats failed to parse. No formats found.class(df$DELIVERY_DATE)## [1] "Date"Calculating weighted average grouped by commodity type and delivery location
mediaponderada <- df %>%
group_by(COMMODITY.SOURCE.LOCATION, COMMODITY, DELIVERY.LOCATION) %>%
summarise(PRICE = weighted.mean(PRICE))## `summarise()` has grouped output by 'COMMODITY.SOURCE.LOCATION', 'COMMODITY'.
## You can override using the `.groups` argument.mediaponderada## # A tibble: 18 x 4
## # Groups: COMMODITY.SOURCE.LOCATION, COMMODITY [5]
## COMMODITY.SOURCE.LOCATION COMMODITY DELIVERY.LOCATION PRICE
## <chr> <chr> <chr> <dbl>
## 1 Australia Coal AMS 84.2
## 2 Australia Coal ANT 81.4
## 3 Australia Coal ARA 90.5
## 4 Australia Coal ROT 93.8
## 5 Australia Coal SOT 95.4
## 6 Australia Coal UK 96.0
## 7 Australia Gas ARA 51.4
## 8 Columbia Coal AMS 80.3
## 9 Columbia Coal ANT 88.6
## 10 Columbia Coal ARA 96.3
## 11 Columbia Coal UK 96.5
## 12 Columbia Gas ARA 98.4
## 13 South Africa Coal AMS 80.2
## 14 South Africa Coal ANT 84.0
## 15 South Africa Coal ARA 96.0
## 16 South Africa Coal ROT 96.1
## 17 South Africa Coal SOT 97.8
## 18 South Africa Coal UK 83.3Filtering the values by DELIVERY.LOCATION
coal2 <- mediaponderada %>%
filter(DELIVERY.LOCATION %in% c("ARA", "AMS", "ROT", "ANT"))coal2## # A tibble: 13 x 4
## # Groups: COMMODITY.SOURCE.LOCATION, COMMODITY [5]
## COMMODITY.SOURCE.LOCATION COMMODITY DELIVERY.LOCATION PRICE
## <chr> <chr> <chr> <dbl>
## 1 Australia Coal AMS 84.2
## 2 Australia Coal ANT 81.4
## 3 Australia Coal ARA 90.5
## 4 Australia Coal ROT 93.8
## 5 Australia Gas ARA 51.4
## 6 Columbia Coal AMS 80.3
## 7 Columbia Coal ANT 88.6
## 8 Columbia Coal ARA 96.3
## 9 Columbia Gas ARA 98.4
## 10 South Africa Coal AMS 80.2
## 11 South Africa Coal ANT 84.0
## 12 South Africa Coal ARA 96.0
## 13 South Africa Coal ROT 96.1Filtering the values by COMMODITY.SOURCE.LOCATION
coal4 <- mediaponderada %>%
filter(COMMODITY.SOURCE.LOCATION == "South Africa")coal4## # A tibble: 6 x 4
## # Groups: COMMODITY.SOURCE.LOCATION, COMMODITY [1]
## COMMODITY.SOURCE.LOCATION COMMODITY DELIVERY.LOCATION PRICE
## <chr> <chr> <chr> <dbl>
## 1 South Africa Coal AMS 80.2
## 2 South Africa Coal ANT 84.0
## 3 South Africa Coal ARA 96.0
## 4 South Africa Coal ROT 96.1
## 5 South Africa Coal SOT 97.8
## 6 South Africa Coal UK 83.3Price Index
ind2 <- summary(coal2$PRICE)
ind4 <- summary(coal4$PRICE)
vetorcoal2 <- c(ind2)
vetorcoal4 <- c(ind4)
ind2e4 <- cbind(vetorcoal2, vetorcoal4)
ind2e4 <- data.frame(ind2e4)ind2e4## vetorcoal2 vetorcoal4
## Min. 51.45000 80.17273
## 1st Qu. 81.43000 83.47907
## Median 88.56455 90.00327
## Mean 86.24840 89.55234
## 3rd Qu. 95.96455 96.04614
## Max. 98.42000 97.77000Coal2 Price
graphcoal2 <- coal2 %>%
filter(DELIVERY.LOCATION %in% c("ARA", "AMS", "ROT", "ANT")) %>%
group_by(COMMODITY.SOURCE.LOCATION,DELIVERY.LOCATION) %>%
ggplot(aes(x = COMMODITY.SOURCE.LOCATION, y = PRICE, fill = DELIVERY.LOCATION)) +
geom_bar(stat = "identity", position = "dodge")
interativo2 = ggplotly(graphcoal2)
interativo2Coal4 Price
graphcoal4 <- coal4 %>%
filter(DELIVERY.LOCATION %in% c("ARA", "AMS", "ROT", "ANT")) %>%
group_by(COMMODITY.SOURCE.LOCATION,DELIVERY.LOCATION) %>%
ggplot(aes(x = COMMODITY.SOURCE.LOCATION, y = PRICE, fill = DELIVERY.LOCATION)) +
geom_bar(stat = "identity", position = "dodge")
interativo4 = ggplotly(graphcoal4)
interativo4Create csv - South Africa
sa1 <- filter(coal2, COMMODITY.SOURCE.LOCATION == "South Africa")
sa2 <- filter(coal4, COMMODITY.SOURCE.LOCATION == "South Africa")
southafrica <- rbind(sa1, sa2)
southafrica## # A tibble: 10 x 4
## # Groups: COMMODITY.SOURCE.LOCATION, COMMODITY [1]
## COMMODITY.SOURCE.LOCATION COMMODITY DELIVERY.LOCATION PRICE
## <chr> <chr> <chr> <dbl>
## 1 South Africa Coal AMS 80.2
## 2 South Africa Coal ANT 84.0
## 3 South Africa Coal ARA 96.0
## 4 South Africa Coal ROT 96.1
## 5 South Africa Coal AMS 80.2
## 6 South Africa Coal ANT 84.0
## 7 South Africa Coal ARA 96.0
## 8 South Africa Coal ROT 96.1
## 9 South Africa Coal SOT 97.8
## 10 South Africa Coal UK 83.3Create csv - Australia
a1 <- filter(coal2, COMMODITY.SOURCE.LOCATION == "Australia")
a2 <- filter(coal4, COMMODITY.SOURCE.LOCATION == "Australia")
australia <- rbind(a1, a2)
australia## # A tibble: 5 x 4
## # Groups: COMMODITY.SOURCE.LOCATION, COMMODITY [2]
## COMMODITY.SOURCE.LOCATION COMMODITY DELIVERY.LOCATION PRICE
## <chr> <chr> <chr> <dbl>
## 1 Australia Coal AMS 84.2
## 2 Australia Coal ANT 81.4
## 3 Australia Coal ARA 90.5
## 4 Australia Coal ROT 93.8
## 5 Australia Gas ARA 51.4Create csv - Columbia
c1 <- filter(coal2, COMMODITY.SOURCE.LOCATION == "Columbia")
c2 <- filter(coal4, COMMODITY.SOURCE.LOCATION == "Columbia")
columbia <- rbind(c1, c2)
columbia## # A tibble: 4 x 4
## # Groups: COMMODITY.SOURCE.LOCATION, COMMODITY [2]
## COMMODITY.SOURCE.LOCATION COMMODITY DELIVERY.LOCATION PRICE
## <chr> <chr> <chr> <dbl>
## 1 Columbia Coal AMS 80.3
## 2 Columbia Coal ANT 88.6
## 3 Columbia Coal ARA 96.3
## 4 Columbia Gas ARA 98.4Export
#Export csv - South Africa
write_csv(southafrica, file = "C:/Users/Viviane/Documents/ArgusMedia/southafrica.csv")
#Export csv - Australia
write_csv(australia, file = "C:/Users/Viviane/Documents/ArgusMedia/australia.csv")
#Export csv - Columbia
write_csv(columbia, file = "C:/Users/Viviane/Documents/ArgusMedia/columbia.csv")
