---
title: "Fuel Prices"
description: "Fuel Prices in Metro city in India and Crude Oil International Price"
author: "Pawan"
date: "2025/10/30"
date-modified: last-modified
categories:
- "Fuel"
- "Prices"
# image: "thumbnails/health_map_hy.png"
format:
html:
code-fold: true
editor_options:
chunk_output_type: console
execute:
error: false
message: false
warning: false
eval: true
---
Load the necessary packages
```{r}
library(data.table) ## Raed data table
library(ggplot2) ## Graphical represantation
library(readxl)
library(gridExtra)
library(stringr) ## Work with string
library(lubridate) ## date
library(scales) ## Nice Scales for ggplot2
library(fontawesome) ## Icons display in ggplot2
library(ggtext) ## Markdown text support for ggplot2
library(showtext) ## Display fonts in ggplot2
library(colorspace) ## Lighten and Darken colours
library(seecolor) ## To print and view colours
library(patchwork) ## Combining plots
```
Costum the plot theme
```{r}
my_theme <- theme_bw(base_size = 16) + ## Start with theme_bw and increase base size
theme(text = element_text(family = "serif", color = "#333333"), ## Set font family and color
plot.title = element_text(size = 20, face = "bold",
hjust = 0.5, margin = margin(10, 0,
10, 0)), ## Center and style title
axis.title = element_text(size = 18),
axis.text = element_text(size = 14),
legend.position = "bottom",
legend.title = element_blank(), ## Remove legend title
panel.grid.major = element_line(color = "#EEEEEE"), ## Lighter grid lines
panel.border = element_blank(), ## Remove panel border
axis.line = element_line(color = "#333333"), ## Add axis lines
axis.text.x = element_text(angle = 90, vjust = 0.5, hjust=1),
strip.background = element_rect(fill = "#F2F2F2",
color = NA) ## Style facet strips (if used)
)
```
## International Fuel Price
Read the data
```{r}
as.data.table(read_excel("/home/pawan/fuelprices/international_price_indian_basket.xlsx",
sheet = "crude-oil"))->crude
melt(crude, id = c("Year", "Average", "Ratio"))->crude
as.numeric(match(substr(crude$variable, 1, 3), month.abb))->crude$variable
as.numeric(ifelse(crude$variable>3, substr(crude$Year, 1, 4),
paste0(20,substr(crude$Year, 6, 7))))->crude$Year
as.Date(paste0(crude$Year, "-", crude$variable, "-", 20))->crude$Date
crude[,.(Price = mean(value)),.(Year)]->avgcrude
## Diesel Internationl Price
as.data.table(read_excel("/home/pawan/fuelprices/international_price_indian_basket.xlsx",
sheet = "diesel"))->diesel
melt(diesel, id = c("Year", "Average"))->diesel
as.numeric(match(substr(diesel$variable, 1, 3), month.abb))->diesel$variable
as.numeric(ifelse(diesel$variable>3, substr(diesel$Year, 1, 4),
paste0(20, substr(diesel$Year, 6, 7))))->diesel$Year
as.Date(paste0(diesel$Year, "-", diesel$variable, "-", 20))->diesel$Date
paste("Diesel")->diesel$Fuel
## Pertol International Price
as.data.table(read_excel("/home/pawan/fuelprices/international_price_indian_basket.xlsx",
sheet = "petrol"))->petrol
melt(petrol, id = c("Year", "Average"))->petrol
as.numeric(match(substr(petrol$variable, 1, 3), month.abb))->petrol$variable
as.numeric(ifelse(petrol$variable>3, substr(petrol$Year, 1, 4),
paste0(20,substr(petrol$Year, 6, 7))))->petrol$Year
as.Date(paste0(petrol$Year, "-", petrol$variable, "-", 20))->petrol$Date
paste("Petrol")->petrol$Fuel
## Merge Diesel and Petrol
rbind(diesel, petrol)->fuel
```
Visualizations
Average Crude Oil FOB Price (Indian Basket)
```{r}
ggplot(avgcrude, aes(x = Year, y = Price, group = 1)) +
geom_line() +
my_theme +
labs(y = "Price (Dollars per Barrel)") +
ggtitle("Average Crude Oil FOB Price (Indian Basket)")
```
Crude Oil FOB Price (Indian Basket)
```{r}
ggplot(crude, aes(x = Date, y = value, group = 1)) +
geom_line() +
my_theme +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Dollars per Barrel)") +
ggtitle("Crude Oil FOB Price (Indian Basket)")
```
Diesel FOB International Price
```{r}
ggplot(diesel, aes(x = Date, y = value, group = 1)) +
geom_line() +
my_theme +
scale_x_date(date_labels = "%Y-%b", date_breaks = "3 months") +
labs(y = "Price (Dollars per Barrel)") +
ggtitle("Diesel FOB International Price")->p1
p1
```
Petrol FOB International Price
```{r}
ggplot(petrol, aes(x = Date, y = value, group = 1)) +
geom_line() +
my_theme +
scale_x_date(date_labels = "%Y-%b", date_breaks = "3 months") +
labs(y = "Price (Dollars per Barrel)") +
ggtitle("Petrol FOB International Price")->p2
p2
```
```{r}
grid.arrange(p1, p2)
```
Disesl and Petrol FOB International Price
```{r}
ggplot(fuel, aes(x = Date, y = value, group = 1)) +
geom_line() +
my_theme + facet_wrap(~Fuel) +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Dollars per Barrel)") +
ggtitle("Disesl and Petrol FOB International Price")->p
p
```
Import of Crude oil and Petroleum Products
```{r}
as.data.table(read_excel("/home/pawan/fuelprices/IMPORT_EXPORT.xlsx",
sheet = "PT_IMPORT_H"))->t
melt(t, id = "IMPORT_EXPORT")->t
t[IMPORT_EXPORT %in% c("CRUDE OIL", "TOTAL PRODUCT IMPORT")]->imp
ggplot(imp, aes(x = variable, y = value, fill = IMPORT_EXPORT)) +
geom_bar(stat = "identity", position = "stack") +
my_theme +
scale_fill_brewer(palette = "Set2") +
scale_y_continuous(labels = scales::comma) +
labs(x = "Year",
y = "Qty (000 Metric Tonnes)") +
ggtitle("Import of Crude oil and Petroleum Products")
##ggsave("import-crude-and-petroleum.png", width = 15, height = 8)
```
Crude Oil Price
```{r}
fread("/home/pawan/fuelprices/eia-oil-prices.csv")->t
as.Date(t$Date,"%Y-%b-%d")->t$Date
ggplot(t, aes(x = Date, y = RBRTE, group = 1)) +
geom_line() +
my_theme +
labs(y = "Price (Dollars per Barrel)") +
scale_x_date(date_labels = "%Y", date_breaks = "2 years")->p
p
```
## Fuel Price in Metro Cities of India
Load Data
```{r}
fread("/home/pawan/fuelprices/india_retail_fuelprices.csv")->t
as.Date(t$Date, format = "%d/%m/%y")->t$Date
melt(t, id = c("Date", "Fuel"))->t1
t[Fuel == "Petrol"]->petrol
t[Fuel == "Diesel"]->diesel
melt(petrol, id = c("Date", "Fuel"))->petrol
petrol[order(Date)]->petrol
tail(petrol, n = 4)->petrol1
melt(diesel, id = c("Date", "Fuel"))->diesel
diesel[order(Date)]->diesel
tail(diesel, n = 4)->diesel1
```
```{r}
ggplot(t1, aes(x = Date, y = value, color = Fuel)) +
geom_line(linewidth = 0.6) +
facet_wrap(~variable) +
my_theme +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Rs./Litre)") +
ggtitle("Fuel Price in Metro City")->p
p
```
Petrol Price
```{r}
## Petrol Price
ggplot(petrol, aes(x = Date, y = value, group = 1)) +
geom_line(linewidth = 1.5) +
facet_wrap(~variable) +
geom_text(data = petrol1, aes(x = Date, y = value,
label = sprintf("%0.2f", value)),
color = "red",
vjust = -0.4,
hjust = 0.9,
size = 3*4) +
my_theme +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Rs./Litre)") +
ggtitle("Pertol Price")->p1
p1
```
Diesel Price
```{r}
## Diesel Price
ggplot(diesel, aes(x = Date, y = value, group = 1)) +
geom_line(size = 1.5) +
facet_wrap(~variable) +
geom_text(data = diesel1, aes(x = Date, y = value,
label = sprintf("%0.2f", value)),
color = "red",
vjust = -0.4,
hjust = 1,
size = 3*4) +
my_theme +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Rs./Litre)") +
ggtitle("Diesel Price")->p2
p2
```
```{r}
cowplot::plot_grid(p1,
p2 + theme(axis.text.y = element_blank(),
axis.ticks.y = element_blank(),
axis.title.y = element_blank()))
## ggsave("fuel-price.png", width = 15, height = 8)
```
Daily Fuel Price in different City
```{r}
## Delhi Petrol Diesel Daily Price
ggplot(t, aes(x = Date, y =Delhi, group = 1)) +
geom_line(size = 0.8) +
facet_wrap(~Fuel) +
my_theme +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Rs./Litre)")->p
p
## Mumbai Petrol Diesel Daily Price
ggplot(t, aes(x = Date, y = Mumbai, group = 1)) +
geom_line(size = 0.8) +
facet_wrap(~Fuel) +
my_theme +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Rs./Litre)")->p
p
## Chennai Petrol Diesel Daily Price
ggplot(t, aes(x = Date, y = Chennai, group = 1)) +
geom_line(size = 0.8) +
facet_wrap(~Fuel) +
my_theme +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Rs./Litre)")->p
p
## Kolkata Petrol Diesel Daily Price
ggplot(t, aes(x = Date, y = Kolkata, group = 1)) +
geom_line(size = 0.8) +
facet_wrap(~Fuel) +
my_theme +
scale_x_date(date_labels = "%Y", date_breaks = "1 years") +
labs(y = "Price (Rs./Litre)")->p
p
```
