This will fetch and clean the current fuel surcharge table.
library(tidyverse)
library(lubridate)
library(rvest)
fedex_html <-
read_html('https://www.fedex.com/en-us/shipping/fuel-surcharge.html')
fedex_current_tables <- fedex_html %>%
html_elements("table") %>%
html_table()
fedex_current_cleaned <- fedex_current_tables[[1]][c(-1,-2), ] %>%
mutate(X1 = str_replace_all(X1, 'Sept', 'Sep')) %>%
select(-X6, -X8) %>%
rename(
date_range = X1,
`fedex_express_fuel_surcharge_rate` = X2,
`fedex_freight_fuel_surcharge_rate` = X3,
`fedex_export_fuel_surcharge_rate` = X4,
`fedex_import_fuel_surcharge_rate` = X5,
`fedex_ground_fuel_surcharge_rate` = X7
) %>%
mutate(start_date = mdy(str_split(date_range, '–', 2, simplify = T)[, 1]),
end_date = mdy(str_split(date_range, '–', 2, simplify = T)[, 2])) %>%
mutate(
fedex_ground_fuel_surcharge_rate = as.numeric(str_remove(fedex_ground_fuel_surcharge_rate, '%')) /
100,
fedex_express_fuel_surcharge_rate = as.numeric(str_remove(fedex_express_fuel_surcharge_rate, '%')) /
100,
fedex_import_fuel_surcharge_rate = as.numeric(str_remove(fedex_import_fuel_surcharge_rate, '%')) /
100,
fedex_export_fuel_surcharge_rate = as.numeric(str_remove(fedex_export_fuel_surcharge_rate, '%')) /
100,
fedex_freight_fuel_surcharge_per_lbs = as.numeric(str_remove(
str_remove(fedex_freight_fuel_surcharge_rate, '\\$'),
' per lb.'
))
)
fedex_current_cleaned## # A tibble: 2 x 9
## date_range fedex_express_f~ fedex_freight_f~ fedex_export_fu~ fedex_import_fu~
## <chr> <dbl> <chr> <dbl> <dbl>
## 1 Sep. 12, ~ 0.2 $0.516 per lb. 0.222 0.26
## 2 Sep. 5, 2~ 0.21 $0.532 per lb. 0.235 0.272
## # ... with 4 more variables: fedex_ground_fuel_surcharge_rate <dbl>,
## # start_date <date>, end_date <date>,
## # fedex_freight_fuel_surcharge_per_lbs <dbl>This will fetch and clean the previous fuel surcharge table.
fedex_history_html <-
read_html("https://www.fedex.com/en-us/shipping/historical-fuel-surcharge.html")
fedex_history_tables <- fedex_history_html %>%
html_elements("table") %>%
html_table()
fedex_history_cleaned <-
fedex_history_tables[[1]][c(-1, -2),] %>%
mutate(X1 = str_replace_all(X1, 'Sept', 'Sep')) %>%
rename(
date_range = X1,
`fedex_express_fuel_surcharge_rate` = X2,
`fedex_freight_fuel_surcharge_rate` = X3,
`fedex_export_fuel_surcharge_rate` = X4,
`fedex_import_fuel_surcharge_rate` = X5,
`fedex_ground_fuel_surcharge_rate` = X6
) %>%
mutate(start_date = mdy(str_split(date_range, '–', 2, simplify = T)[, 1]),
end_date = mdy(str_split(date_range, '–', 2, simplify = T)[, 2])) %>%
mutate(
fedex_ground_fuel_surcharge_rate = as.numeric(str_remove(fedex_ground_fuel_surcharge_rate, '%')) /
100,
fedex_express_fuel_surcharge_rate = as.numeric(str_remove(fedex_express_fuel_surcharge_rate, '%')) /
100,
fedex_import_fuel_surcharge_rate = as.numeric(str_remove(fedex_import_fuel_surcharge_rate, '%')) /
100,
fedex_export_fuel_surcharge_rate = as.numeric(str_remove(fedex_export_fuel_surcharge_rate, '%')) /
100,
fedex_freight_fuel_surcharge_per_lbs = as.numeric(str_remove(
str_remove(fedex_freight_fuel_surcharge_rate, '\\$'),
' per lb.'
))
)
fedex_history_cleaned## # A tibble: 13 x 9
## date_range fedex_express_f~ fedex_freight_f~ fedex_export_fu~
## <chr> <dbl> <chr> <dbl>
## 1 Aug. 29, 2022–Sep. 4, 2022 0.192 $0.504 per lb. 0.212
## 2 Aug. 22, 2022–Aug. 28, 2022 0.185 $0.492 per lb. 0.205
## 3 Aug. 15, 2022–Aug. 21, 2022 0.185 $0.492 per lb. 0.202
## 4 Aug. 8, 2022–Aug. 14, 2022 0.2 $0.516 per lb. 0.222
## 5 Aug. 1, 2022–Aug. 7, 2022 0.198 $0.512 per lb. 0.22
## 6 July 25, 2022–July 31, 2022 0.205 $0.524 per lb. 0.23
## 7 July 18, 2022–July 24, 2022 0.2 $0.516 per lb. 0.22
## 8 July 11, 2022–July 17, 2022 0.225 $0.556 per lb. 0.252
## 9 July 4, 2022–July 10, 2022 0.238 $0.576 per lb. 0.268
## 10 June 27, 2022–July 3, 2022 0.235 $0.572 per lb. 0.265
## 11 June 20, 2022–June 26, 2022 0.235 $0.572 per lb. 0.265
## 12 June 13, 2022–June 19, 2022 0.228 $0.560 per lb. 0.255
## 13 June 6, 2022–June 12, 2022 0.215 $0.540 per lb. 0.24
## # ... with 5 more variables: fedex_import_fuel_surcharge_rate <dbl>,
## # fedex_ground_fuel_surcharge_rate <dbl>, start_date <date>, end_date <date>,
## # fedex_freight_fuel_surcharge_per_lbs <dbl>fedex_rates <-
bind_rows(fedex_current_cleaned, fedex_history_cleaned)
fedex_full_table <-
tibble(
date = date(),
fedex_ground_fuel_surcharge_rate = numeric(),
fedex_express_fuel_surcharge_rate = numeric(),
fedex_import_fuel_surcharge_rate = numeric(),
fedex_export_fuel_surcharge_rate = numeric(),
fedex_freight_fuel_surcharge_rate = numeric()
,
)
for (i in 1:dim(fedex_rates)[1]) {
fedex2 <-
tibble(
date = seq(fedex_rates$start_date[i], fedex_rates$end_date[i], by = 'days'),
fedex_ground_fuel_surcharge_rate = fedex_rates$fedex_ground_fuel_surcharge_rate[i],
fedex_express_fuel_surcharge_rate = fedex_rates$fedex_express_fuel_surcharge_rate[i],
fedex_import_fuel_surcharge_rate = fedex_rates$fedex_import_fuel_surcharge_rate[i],
fedex_export_fuel_surcharge_rate = fedex_rates$fedex_export_fuel_surcharge_rate[i],
fedex_freight_fuel_surcharge_rate = fedex_rates$fedex_freight_fuel_surcharge_rate[i]
)
fedex_full_table <- rbind(fedex_full_table, fedex2)
}
fedex_full_table %>% arrange(desc(date))## # A tibble: 105 x 6
## date fedex_ground_fuel_surcharge_rate fedex_express_f~ fedex_import_fu~
## <date> <dbl> <dbl> <dbl>
## 1 2022-09-18 0.178 0.2 0.26
## 2 2022-09-17 0.178 0.2 0.26
## 3 2022-09-16 0.178 0.2 0.26
## 4 2022-09-15 0.178 0.2 0.26
## 5 2022-09-14 0.178 0.2 0.26
## 6 2022-09-13 0.178 0.2 0.26
## 7 2022-09-12 0.178 0.2 0.26
## 8 2022-09-11 0.178 0.21 0.272
## 9 2022-09-10 0.178 0.21 0.272
## 10 2022-09-09 0.178 0.21 0.272
## # ... with 95 more rows, and 2 more variables:
## # fedex_export_fuel_surcharge_rate <dbl>,
## # fedex_freight_fuel_surcharge_rate <chr>write_csv(fedex_full_table, "FedEx Fuel Surcharges.csv")