---
title: "Fuel Price Analysis: India's Retail Prices & International Crude Dynamics"
description: "A comprehensive analysis of crude oil price trends, international petrol/diesel prices, and retail fuel prices in India's metro cities."
author: "Pawan"
date: "2025/10/30"
date-modified: last-modified
categories:
- "Fuel"
- "Prices"
- "Energy"
format:
html:
code-fold: true
toc: true
toc-depth: 3
theme: cosmo
editor_options:
chunk_output_type: console
execute:
error: false
message: false
warning: false
eval: true
---
## Overview
India imports ~85% of its crude oil needs, making domestic fuel prices highly sensitive to global energy markets. This analysis tracks the Indian Basket crude price, international petrol/diesel FOB prices, retail prices in four metro cities, and aggregate petroleum import volumes. Key events — COVID-19 demand collapse (2020), the 2022 Russia–Ukraine war price spike, and periodic government tax adjustments — are visible in the data.
---
## Setup
```{r setup}
library(data.table)
library(ggplot2)
library(ggiraph)
library(scales)
library(plotly)
library(readxl)
library(lubridate)
library(patchwork)
library(gt)
my_theme <- theme_minimal(base_size = 13) +
theme(
plot.title = element_text(size = 16, face = "bold", hjust = 0.5, margin = margin(b = 8)),
plot.subtitle = element_text(size = 11, hjust = 0.5, color = "#7F8C8D"),
plot.caption = element_text(size = 9, color = "#95A5A6", hjust = 0),
axis.title = element_text(size = 12, face = "bold"),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1),
legend.position = "bottom",
legend.title = element_blank(),
panel.grid.major= element_line(color = "#ECF0F1", linewidth = 0.4),
panel.grid.minor= element_blank(),
strip.background= element_rect(fill = "#F8F9FA", color = NA),
strip.text = element_text(face = "bold", size = 11)
)
```
---
## Section 1 · International Crude Oil Price (Indian Basket)
The Indian Basket is a weighted average of Oman, Dubai (sour crude) and Brent (sweet crude). It is the reference price used for setting domestic fuel prices.
```{r crude-price}
crude_path <- "/home/pawan/fuelprices/international_price_indian_basket.xlsx"
crude <- as.data.table(read_excel(crude_path, sheet="crude-oil"))
crude_long <- melt(crude, id=c("Year","Average","Ratio"))
crude_long[, Month_num := as.integer(match(substr(variable,1,3), month.abb))]
crude_long[, FY_Year := as.integer(ifelse(Month_num > 3,
substr(Year,1,4), paste0("20",substr(Year,6,7))))]
crude_long[, Date := as.Date(paste0(FY_Year,"-",Month_num,"-20"))]
crude_long <- crude_long[!is.na(value) & !is.na(Date)]
avg_crude <- crude_long[, .(Price = mean(value, na.rm=TRUE)), by=FY_Year]
## Interactive monthly chart
p_crude_m <- ggplot(crude_long, aes(x=Date, y=value, group=1,
text=paste0(format(Date,"%b %Y"),"\n$",round(value,1),"/bbl"))) +
geom_line(color="#2C3E50", linewidth=0.8) +
geom_smooth(method="loess", se=TRUE, color="#E74C3C", fill="#FADBD8", linewidth=1, alpha=0.3) +
geom_vline(xintercept=as.Date("2020-03-01"), linetype="dashed", color="#3498DB", alpha=0.7) +
geom_vline(xintercept=as.Date("2022-02-24"), linetype="dashed", color="#E74C3C", alpha=0.7) +
annotate("text", x=as.Date("2020-05-01"), y=max(crude_long$value,na.rm=TRUE)*0.9,
label="COVID-19", color="#3498DB", size=3, hjust=0) +
annotate("text", x=as.Date("2022-05-01"), y=max(crude_long$value,na.rm=TRUE)*0.95,
label="Ukraine War", color="#E74C3C", size=3, hjust=0) +
my_theme +
scale_x_date(date_labels="%Y", date_breaks="2 years") +
labs(title="Indian Basket Crude Oil Price (Monthly)",
subtitle="Red shading = LOESS confidence interval | Key events annotated",
y="USD per Barrel", x=NULL,
caption="Source: PPAC, Ministry of Petroleum & Natural Gas")
ggplotly(p_crude_m, tooltip="text") |> layout(hovermode="x unified")
```
```{r crude-annual}
p_crude_a <- ggplot(avg_crude, aes(x=FY_Year, y=Price,
text=paste0("FY",FY_Year,"\nAvg: $",round(Price,1),"/bbl"))) +
geom_col_interactive(aes(tooltip=paste0("FY",FY_Year,"\n$",round(Price,1),"/bbl"),
data_id=FY_Year),
fill="#2C3E50", alpha=0.8, width=0.7) +
geom_text(aes(label=paste0("$",round(Price))), vjust=-0.4, size=3.5, fontface="bold") +
my_theme +
labs(title="Annual Average Indian Basket Crude Price",
y="USD per Barrel", x="Financial Year",
caption="Source: PPAC | Analysis: Pawan")
girafe(ggobj=p_crude_a, width_svg=11, height_svg=5,
options=list(opts_tooltip(css="background:#fff;color:#333;padding:8px;border-radius:5px;border:1px solid #ddd;"),
opts_hover(css="opacity:0.85;"),
opts_sizing(rescale=TRUE,width=1)))
```
---
## Section 2 · International Petrol & Diesel Prices
```{r intl-fuel}
diesel <- as.data.table(read_excel(crude_path, sheet="diesel"))
petrol <- as.data.table(read_excel(crude_path, sheet="petrol"))
prep_fuel <- function(dt, fuel_name) {
dt_long <- melt(dt, id=c("Year","Average"))
dt_long[, Month_num := as.integer(match(substr(variable,1,3), month.abb))]
dt_long[, FY_Year := as.integer(ifelse(Month_num > 3,
substr(Year,1,4), paste0("20",substr(Year,6,7))))]
dt_long[, Date := as.Date(paste0(FY_Year,"-",Month_num,"-20"))]
dt_long[, Fuel := fuel_name]
dt_long[!is.na(value) & !is.na(Date)]
}
diesel_l <- prep_fuel(diesel, "Diesel")
petrol_l <- prep_fuel(petrol, "Petrol")
fuel_intl <- rbind(diesel_l, petrol_l)
p_fuel <- ggplot(fuel_intl, aes(x=Date, y=value, color=Fuel,
text=paste0(Fuel,"\n",format(Date,"%b %Y"),
"\n$",round(value,1),"/bbl"))) +
geom_line(linewidth=1) +
facet_wrap(~Fuel) +
scale_color_manual(values=c("Diesel"="#3498DB","Petrol"="#E74C3C")) +
scale_x_date(date_labels="%Y", date_breaks="2 years") +
my_theme + theme(legend.position="none") +
labs(title="International Diesel & Petrol FOB Prices",
subtitle="Prices track crude with a small refining-margin premium",
y="USD per Barrel", x=NULL,
caption="Source: PPAC, Ministry of Petroleum & Natural Gas")
ggplotly(p_fuel, tooltip="text") |> layout(hovermode="x unified")
```
---
## Section 3 · Petroleum Imports (Volume)
```{r petroleum-imports}
imp_raw <- as.data.table(read_excel("/home/pawan/fuelprices/IMPORT_EXPORT.xlsx",
sheet="PT_IMPORT_H"))
imp_long <- melt(imp_raw, id="IMPORT_EXPORT")
imp_sub <- imp_long[IMPORT_EXPORT %in% c("CRUDE OIL","TOTAL PRODUCT IMPORT")]
imp_sub[, tt := paste0(IMPORT_EXPORT,"\nYear: ",variable,
"\nQty: ",format(round(value/1000,0), big.mark=",")," MMT")]
p_imp <- ggplot(imp_sub, aes(x=variable, y=value/1000, fill=IMPORT_EXPORT)) +
geom_bar_interactive(aes(tooltip=tt, data_id=IMPORT_EXPORT),
stat="identity", position="stack", width=0.75, alpha=0.9) +
scale_fill_manual(values=c("CRUDE OIL"="#2C3E50","TOTAL PRODUCT IMPORT"="#E67E22")) +
scale_y_continuous(labels=comma_format()) +
my_theme +
labs(title="India's Petroleum Import Volumes",
subtitle="Crude oil + refined product imports (MMT = million metric tonnes)",
y="Million Metric Tonnes", x="Year",
caption="Source: PPAC | Analysis: Pawan")
girafe(ggobj=p_imp, width_svg=12, height_svg=6,
options=list(opts_tooltip(css="background:#fff;color:#333;padding:8px;border-radius:5px;border:1px solid #ddd;"),
opts_hover(css="opacity:0.85;stroke:#333;stroke-width:1.5;"),
opts_sizing(rescale=TRUE,width=1)))
```
---
## Section 4 · Retail Fuel Prices in Metro Cities
```{r retail-prices}
t <- fread("/home/pawan/fuelprices/india_retail_fuelprices.csv")
t[, Date := as.Date(Date, format="%d/%m/%y")]
t_long <- melt(t, id=c("Date","Fuel"))
setnames(t_long, "variable", "City")
## Faceted interactive chart — all cities, both fuels
p_retail <- ggplot(t_long, aes(x=Date, y=value, color=Fuel,
text=paste0(City,"\n",Fuel,"\n",format(Date,"%d %b %Y"),
"\n₹",round(value,2),"/L"))) +
geom_line(linewidth=0.7, alpha=0.85) +
facet_wrap(~City) +
scale_color_manual(values=c("Petrol"="#E74C3C","Diesel"="#3498DB")) +
scale_x_date(date_labels="%Y", date_breaks="2 years") +
scale_y_continuous(labels=function(x) paste0("₹",x)) +
my_theme +
labs(title="Retail Petrol & Diesel Prices in Indian Metro Cities",
subtitle="Long periods of flat prices reflect administered pricing; cuts/hikes are sudden",
y="Price (₹/Litre)", x=NULL,
caption="Source: PPAC / IOC | Analysis: Pawan")
ggplotly(p_retail, tooltip="text") |> layout(hovermode="x unified")
```
---
## Section 5 · Latest Prices Snapshot
```{r latest-prices}
petrol_dt <- t_long[Fuel == "Petrol"]
diesel_dt <- t_long[Fuel == "Diesel"]
latest_petrol <- petrol_dt[, .(Date=max(Date)), by=City]
latest_petrol <- merge(latest_petrol, petrol_dt, by=c("City","Date"))
latest_diesel <- diesel_dt[, .(Date=max(Date)), by=City]
latest_diesel <- merge(latest_diesel, diesel_dt, by=c("City","Date"))
snapshot <- merge(
latest_petrol[, .(City, Date, Petrol=round(value,2))],
latest_diesel[, .(City, Diesel=round(value,2))],
by="City"
)
snapshot |>
gt() |>
tab_header(title=paste("Latest Retail Fuel Prices —", format(max(snapshot$Date),"%d %b %Y"))) |>
cols_label(City="Metro City", Date="Date", Petrol="Petrol (₹/L)", Diesel="Diesel (₹/L)") |>
data_color(Petrol, colors=scales::col_numeric(c("#FFEEBA","#DC3545"), domain=NULL)) |>
data_color(Diesel, colors=scales::col_numeric(c("#CCE5FF","#0056B3"), domain=NULL)) |>
opt_stylize(style=3) |>
tab_source_note("Source: PPAC / Indian Oil Corporation")
```
---
## Section 6 · Crude–Retail Price Correlation
```{r correlation}
## Merge monthly crude with Delhi retail prices (as proxy)
delhi_petrol <- t_long[City=="Delhi" & Fuel=="Petrol",
.(Date, Delhi_Petrol=value)]
delhi_petrol[, Month := floor_date(Date, "month")]
delhi_monthly <- delhi_petrol[, .(Delhi_Petrol=mean(Delhi_Petrol,na.rm=TRUE)), by=Month]
crude_monthly <- crude_long[, .(Crude=mean(value,na.rm=TRUE)),
by=.(Month=floor_date(Date,"month"))]
corr_dt <- merge(crude_monthly, delhi_monthly, by="Month")
p_corr <- ggplot(corr_dt, aes(x=Crude, y=Delhi_Petrol,
text=paste0(format(Month,"%b %Y"),
"\nCrude: $",round(Crude,1),"/bbl",
"\nDelhi Petrol: ₹",round(Delhi_Petrol,2),"/L"))) +
geom_point(alpha=0.5, color="#3498DB", size=2.5) +
geom_smooth(method="lm", se=TRUE, color="#E74C3C", fill="#FADBD8") +
my_theme +
labs(title="Crude Oil Price vs Delhi Petrol Retail Price",
subtitle="Positive correlation but government taxes/subsidies create discontinuities",
x="Indian Basket Crude (USD/bbl)", y="Delhi Petrol Price (₹/L)",
caption="Source: PPAC | Analysis: Pawan")
ggplotly(p_corr, tooltip="text")
```