PH Fuel Price Pipeline

An end-to-end ETL pipeline tracking Philippine petroleum retail prices from 1990 to present — with a focused lens on the 2026 US-Iran war and how the Strait of Hormuz crisis drove PH fuel to an all-time high of PHP 94.30/L.

On February 28, 2026, the US and Israel launched Operation Epic Fury against Iran. Iran threatened the Strait of Hormuz. The Philippines declared a four-day work week. This pipeline makes that story explorable with data.

Tech Stack

Layer	Tool
Language	Python 3.13
Data Processing	pandas, pyarrow
Cloud Storage	AWS S3
Processing	Databricks, PySpark (in progress)
Visualization	PowerBI Desktop
Version Control	Git, GitHub
Libraries	boto3

Project Structure

ph-fuel-pipeline/
│
├── extract/
│   ├── __init__.py
│   └── extract_doe.py        # DOE NCR prices from public Google Sheets
│
├── transform/
│   ├── __init__.py
│   ├── clean.py              # Nulls, types, outliers, aggregation
│   └── enrich.py             # WoW change, hike flags, conflict period tags
│
├── load/
│   ├── __init__.py
│   ├── s3_upload.py          # Uploads parquet + CSV to S3
│   └── schema.py             # Parquet schema definition
│
├── notebooks/
│   └── fuel_analysis.py      # Databricks PySpark notebook (7 queries)
│
├── data/                     # Local output — gitignored
│
├── pipeline.py               # Main orchestrator — run this
├── config.py                 # Constants: S3 paths, WAR_START_DATE, sheet IDs
├── requirements.txt
└── README.md

S3 Bucket Layout

s3://ph-fuel-pipeline/
├── raw/
│   └── doe_ncr.parquet              # Raw DOE NCR price data (1990–2026)
└── processed/
    ├── fuel_prices.parquet          # Clean, enriched, conflict-tagged
    └── fuel_prices.csv              # For PowerBI direct connect

Data Sources

Source	Coverage	Access
DOE NCR Monthly (RDAC)	1990–2020	Public Google Sheet — no login
DOE NCR Weekly (RDAC)	2021–2026	Public Google Sheet — no login

Both DOE sheets are published by the RDAC Data Catalog (PhilRice) sourced from the Department of Energy Philippines.

Direct link: https://ricelytics.philrice.gov.ph/data_catalog/dataset/average-retail-pump-prices-of-various-petroleum-products-ncr-data

Setup

git clone https://github.com/Dev-rja/ph-fuel-pipeline
cd ph-fuel-pipeline
pip install -r requirements.txt

Configure AWS credentials:

aws configure
# AWS Access Key ID:     <your key>
# AWS Secret Access Key: <your secret>
# Default region:        ap-southeast-1
# Default output format: json

Run

python pipeline.py

Pipeline runs automatically — no manual downloads needed. Data is pulled directly from public Google Sheets.

Without AWS configured — saves locally:

data/fuel_prices.csv

With AWS configured — uploads to S3 and prints a PowerBI pre-signed URL:

s3://ph-fuel-pipeline/raw/doe_ncr.parquet
s3://ph-fuel-pipeline/processed/fuel_prices.parquet
s3://ph-fuel-pipeline/processed/fuel_prices.csv

Key Derived Columns

Column	Description
wow_change_php	Week-on-week price change in PHP
wow_change_pct	Week-on-week % change
is_hike_week	True if price rose >= PHP 1.00
rolling_4w_avg	4-week rolling average price
is_alltime_high	True if price is a new cumulative high
conflict_period	pre_war or during_war (split at Feb 28, 2026)
pct_above_prewar_avg	% above the pre-war average price

Conflict Period Definition

Period	Date Range	Description
pre_war	Before Feb 28, 2026	Baseline — avg PHP 45.04/L
during_war	Feb 28, 2026 onwards	Post-Operation Epic Fury spike

Key Finding

Metric	Value
Pre-war average (1990–2026)	PHP 45.04/L
Latest price (March 31, 2026)	PHP 94.30/L
Increase	+PHP 49.26/L (+109%)
All-time high	PHP 95.90/L on March 24, 2026

Context

On February 28, 2026, the US and Israel launched Operation Epic Fury against Iran. Iran responded by threatening the Strait of Hormuz, through which a major share of global oil supply passes. The Philippines, which imports nearly all its petroleum, saw immediate price shocks — pump prices nearly doubled in weeks. The government declared a four-day work week in response.

This pipeline documents that impact through 35 years of official DOE price data.

Roadmap

• Databricks — Spark processing and aggregations on S3 parquet files
• Add diesel and kerosene — expand beyond RON 95 to cover all fuel types in the DOE data
• Global oil prices — pull Brent crude via EIA API and merge with PH pump prices
• Multi-source data — add USD/PHP exchange rate to show how peso depreciation amplifies oil price shocks
• Correlation analysis — fuel price vs Brent crude vs USD/PHP heatmap