notebooks/oil_res_prod.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "08ae9407",
   "metadata": {},
   "source": [
    "# Oil reserves  vs Oil production \n",
    "\n",
    "Data from the [statistical review of world energy](https://www.energyinst.org/statistical-review) and from the [World Bank population](https://data.worldbank.org/indicator/SP.POP.TOTL).\n",
    "\n",
    "**Note:** I added to the original Excel file, a sheet with the codes and regions numbers of the countries in the dataset. This allows coloring the countries by region, and labeling them by code. See the file `Statistical Review of World Energy Data.xlsx` in the data directory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c36bea8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "984ab912",
   "metadata": {},
   "outputs": [],
   "source": [
    "# primary energy consumption\n",
    "# file path\n",
    "path = \"../data/Statistical Review of World Energy Data.xlsx\"\n",
    "# read data from third row and drop last 13 rows\n",
    "pec = pd.read_excel(path, sheet_name = \"Primary Energy Consumption\", header=2, skipfooter=13) \n",
    "# remove empty rows\n",
    "pec.dropna(inplace=True)\n",
    "# remove rows containing \"Total\"\n",
    "pec.drop(pec[pec[\"Exajoules\"].str.contains(\"Total\")].index, inplace=True)\n",
    "# remove last three columns\n",
    "pec.drop(columns=pec.columns[-3:], axis=1,  inplace=True)\n",
    "# make first column the index of the DataFrame\n",
    "pec.set_index(\"Exajoules\", inplace=True)\n",
    "# make all columns numeric\n",
    "pec = pec.apply(pd.to_numeric, errors=\"coerce\")\n",
    "# Number of rows should be 92\n",
    "print(\"Number of rows =\", len(pec.index))\n",
    "\n",
    "pec.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a0ef7cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# primary energy consumption per capita\n",
    "# read data from third row and drop last 13 rows\n",
    "pec_cap = pd.read_excel(path, sheet_name = \"Primary Energy - Cons capita\", header=2, skipfooter=13) \n",
    "# remove empty rows\n",
    "pec_cap.dropna(inplace=True)\n",
    "# remove rows containing \"Total\"\n",
    "pec_cap.drop(pec_cap[pec_cap[\"Gigajoule per capita\"].str.contains(\"Total\")].index, inplace=True)\n",
    "# remove last two columns\n",
    "pec_cap.drop(columns=pec_cap.columns[-2:], axis=1,  inplace=True)\n",
    "# make first column the index of the DataFrame\n",
    "pec_cap.set_index(\"Gigajoule per capita\", inplace=True)\n",
    "# make all columns numeric\n",
    "pec_cap = pec_cap.apply(pd.to_numeric, errors=\"coerce\")\n",
    "# Number of rows should be 92\n",
    "print(\"Number of rows =\", len(pec_cap.index))\n",
    "\n",
    "pec_cap.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f414a4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# oil reserves\n",
    "# read data from third row and drop last 29 rows\n",
    "res = pd.read_excel(path, sheet_name = \"Oil - Proved reserves history\", header=2, skipfooter=29) \n",
    "# remove empty rows\n",
    "res.dropna(how=\"all\",inplace=True)\n",
    "# fill nans with zeros\n",
    "res.fillna(0, inplace=True)\n",
    "# remove rows containing \"Total\"\n",
    "res.drop(res[res[\"Thousand million barrels\"].str.contains(\"Total\")].index, inplace=True)\n",
    "# remove last three columns\n",
    "res.drop(columns=res.columns[-3:], axis=1,  inplace=True)\n",
    "# make first column the index of the DataFrame\n",
    "res.set_index(\"Thousand million barrels\", inplace=True)\n",
    "# make all columns numeric\n",
    "res = res.apply(pd.to_numeric, errors=\"coerce\")\n",
    "# Number of rows should be 56\n",
    "print(\"Number of rows =\", len(res.index))\n",
    "\n",
    "res.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad03963c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# oil production\n",
    "# read data from third row and drop last 16 rows\n",
    "prod = pd.read_excel(path, sheet_name = \"Oil Production - Barrels\", header=2, skipfooter=16) \n",
    "# remove empty rows\n",
    "prod.dropna(inplace=True)\n",
    "# remove rows containing \"Total\"\n",
    "prod.drop(prod[prod[\"Thousand barrels daily\"].str.contains(\"Total\")].index, inplace=True)\n",
    "# remove last three columns\n",
    "prod.drop(columns=prod.columns[-3:], axis=1,  inplace=True)\n",
    "# make first column the index of the DataFrame\n",
    "prod.set_index(\"Thousand barrels daily\", inplace=True)\n",
    "# make all columns numeric\n",
    "prod = prod.apply(pd.to_numeric, errors=\"coerce\")\n",
    "# To be on the safe side with indexes\n",
    "prod.index = res.index\n",
    "# set the name of the axis for the index to Thousand barrels daily\n",
    "prod.rename_axis(\"Thousand barrels daily\", inplace=True)\n",
    "# Number of rows should be 56\n",
    "print(\"Number of rows =\", len(prod.index))\n",
    "\n",
    "prod.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e266748",
   "metadata": {},
   "outputs": [],
   "source": [
    "# codes and regions\n",
    "cod_reg = pd.read_excel(path, sheet_name = \"Codes and regions\") \n",
    "# make first column the index of the DataFrame\n",
    "cod_reg.set_index(\"Country\", inplace=True)\n",
    "# use only the indexes/countries in the oil reserves DataFrame\n",
    "cod_reg = cod_reg.loc[res.index]\n",
    "# set the name of the axis for the index to Codes and regions\n",
    "cod_reg.rename_axis(\"Codes and regions\", inplace=True)\n",
    "# set the name of the axis for the index to \"\"\n",
    "cod_reg.rename_axis(\"\", inplace=True)\n",
    "# Number of rows should be 56\n",
    "print(\"Number of rows =\", len(cod_reg.index))\n",
    "\n",
    "cod_reg.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bfa8070",
   "metadata": {},
   "outputs": [],
   "source": [
    "# compute population by dividing primary energy consumption pec,\n",
    "# by primary energy_consumption per capita pec_cap\n",
    "# Notice that pec is in Exajoules, while pec_cap is in Gigajoules\n",
    "# Therefore population in millions is\n",
    "population = (pec*1000)/pec_cap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df7afbae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add missing countries to population\n",
    "\n",
    "# Read World Bank population database\n",
    "path = \"../data/WorldBankPopulation.xls\"\n",
    "# read data from third row and drop last 14 rows\n",
    "wbp = pd.read_excel(path, sheet_name = \"Data\", header=3) \n",
    "# make first column the index of the DataFrame\n",
    "wbp.set_index(\"Country Name\", inplace=True)\n",
    "# remove first eight columns\n",
    "wbp.drop(columns=wbp.columns[0:8], axis=1,  inplace=True)\n",
    "# Since the most recent year is 2021, add a column for 2022 equal to 2021\n",
    "wbp[\"2022\"] = wbp[\"2021\"]\n",
    "# make wbp columns equal to population columns\n",
    "wbp.columns = population.columns\n",
    "# rename some countries\n",
    "wbp.rename(index={\"Syrian Arab Republic\":\"Syria\", \"Yemen, Rep.\":\"Yemen\", \n",
    "                  \"Congo, Dem. Rep.\":\"Republic of Congo\", \"Brunei Darussalam\":\"Brunei\"}, inplace=True)\n",
    "# select missing countries \n",
    "wbp = wbp.loc[[\"Syria\", \"Yemen\", \"Angola\", \"Chad\", \"Republic of Congo\", \n",
    "               \"Equatorial Guinea\", \"Gabon\", \"Libya\", \"Nigeria\", \"South Sudan\", \"Sudan\", \"Tunisia\", \"Brunei\"]]\n",
    "# convert population to millions\n",
    "wbp = wbp / 1e6\n",
    "# add missing countries\n",
    "population = pd.concat([population, wbp])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "043b5274",
   "metadata": {},
   "outputs": [],
   "source": [
    "# There can be errors in the computed populations here\n",
    "# but I believe this is the best way to compute the \"Other\" areas populations\n",
    "\n",
    "# Add population of Other areas\n",
    "population.loc[\"Other S. & Cent. America\"] = population.loc[[\"Central America\", \"Other Caribbean\", \n",
    "                                                             \"Other South America\"]].sum()\n",
    "population.loc[\"Other Africa\"] = population.loc[[\"Eastern Africa\", \"Middle Africa\", \"Western Africa\", \n",
    "                                                 \"Other Northern Africa\", \"Other Southern Africa\"]].sum()\n",
    "# subtract to Other areas\n",
    "population.loc[\"Other Middle East\"] = population.loc[\"Other Middle East\"] \\\n",
    "    - population.loc[\"Syria\"] - population.loc[\"Yemen\"]\n",
    "\n",
    "population.loc[\"Other Africa\"] = population.loc[\"Other Africa\"] - population.loc[\"Angola\"] \\\n",
    "    - population.loc[\"Chad\"] - population.loc[\"Republic of Congo\"] - population.loc[\"Equatorial Guinea\"] \\\n",
    "    - population.loc[\"Gabon\"] - population.loc[\"Libya\"] - population.loc[\"Nigeria\"] \\\n",
    "    - population.loc[\"South Sudan\"] - population.loc[\"Sudan\"] - population.loc[\"Tunisia\"]\n",
    "\n",
    "population.loc[\"Other Asia Pacific\"] = population.loc[\"Other Asia Pacific\"] - population.loc[\"Brunei\"]\n",
    "\n",
    "# use only the indexes/countries in the oil reserves DataFrame\n",
    "population = population.loc[res.index]\n",
    "# set the name of the axis for the index to Millions\n",
    "population.rename_axis(\"Millions\", inplace=True)\n",
    "# Number of rows should be 56\n",
    "print(\"Number of rows =\", len(population.index))\n",
    "\n",
    "population.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87fdb956",
   "metadata": {},
   "outputs": [],
   "source": [
    "# check the indexes of the DataFrames are equal\n",
    "print(res.index.equals(prod.index))\n",
    "print(res.index.equals(population.index))\n",
    "print(res.index.equals(cod_reg.index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d3e1a58",
   "metadata": {},
   "outputs": [],
   "source": [
    "# graph as scatter the oil reserves versus oil production for the year 2020\n",
    "# color the points by region and make their size proportional to population\n",
    "\n",
    "# regions:\n",
    "# 1 = North America\n",
    "# 2 = South and Central America\n",
    "# 3 = Europe\n",
    "# 4 = CIS\n",
    "# 5 = Middle East\n",
    "# 6 = Africa\n",
    "# 7 = Asia Pacific\n",
    "regions = [1, 2, 3, 4, 5, 6, 7]\n",
    "regions = regions[::-1] # reverse list of regions\n",
    "\n",
    "# colors for regions\n",
    "colors = [\"palegreen\", \"darkgreen\", \"blue\", \"magenta\", \"orange\", \"red\", \"yellow\"]\n",
    "colors = colors[::-1] # reverse list of colors\n",
    "\n",
    "# year\n",
    "year = 2020\n",
    "\n",
    "# make figure\n",
    "fig, ax = plt.subplots(figsize=(15,7.5))\n",
    "\n",
    "# for each region\n",
    "for (region, color) in zip(regions, colors):\n",
    "    # extract region data\n",
    "    my_res = res[cod_reg[\"region\"] == region]\n",
    "    my_prod = prod[cod_reg[\"region\"] == region]\n",
    "    my_population = population[cod_reg[\"region\"] == region]\n",
    "    # plot data\n",
    "    ax.scatter(my_res[year], my_prod[year], s=my_population[year]*2, \n",
    "               c=color, edgecolor=\"0\", alpha=0.75, zorder=2)\n",
    "    # plot labels\n",
    "    for index in my_res.index:\n",
    "        if my_res.loc[index,year] >= 0.1 and my_prod.loc[index,year] >= 1:\n",
    "            ax.text(x=my_res.loc[index,year], y=my_prod.loc[index,year], \n",
    "                    s=cod_reg.loc[index,\"code\"], size=8, zorder=3)\n",
    "\n",
    "# plot year\n",
    "ax.text(x = 0.4, y = 15, s=str(year), \n",
    "        fontdict=dict(fontfamily=\"Courier New\", color=\"lightgray\", size=250), zorder=1)    \n",
    "\n",
    "# set axes\n",
    "ax.set_xlim([0.1, 500])\n",
    "ax.set_ylim([1, 30_000])\n",
    "ax.set_xscale(\"log\") # x axis is log\n",
    "ax.set_yscale(\"log\") # y axis is log\n",
    "ax.set_xlabel(\"Oil reserves [Thousand million barrels]\")\n",
    "ax.set_ylabel(\"Oil production [Thousand barrels daily]\")\n",
    "ax.grid(True, which=\"both\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72aca6bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# run this cell to install celluloid\n",
    "import sys\n",
    "!{sys.executable} -m pip install celluloid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65140c8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create animation of oil reserves versus oil production  over time\n",
    "\n",
    "# import celluloid Camera\n",
    "from celluloid import Camera\n",
    "\n",
    "# create figure\n",
    "fig, ax = plt.subplots(figsize=(15,7.5))\n",
    "# set axes\n",
    "ax.set_xlim([0.1, 500])\n",
    "ax.set_ylim([1, 30_000])\n",
    "ax.set_xscale(\"log\") # x axis is log\n",
    "ax.set_yscale(\"log\") # y axis is log\n",
    "ax.set_xlabel(\"Oil reserves [Thousand million barrels]\")\n",
    "ax.set_ylabel(\"Oil production [Thousand barrels daily]\")\n",
    "ax.grid(True, which=\"both\")\n",
    "# create camera\n",
    "camera = Camera(fig)\n",
    "\n",
    "# for each year\n",
    "for year in res.columns:\n",
    "    # for each region\n",
    "    for (region, color) in zip(regions, colors):\n",
    "        # extract region data\n",
    "        my_res = res[cod_reg[\"region\"] == region]\n",
    "        my_prod = prod[cod_reg[\"region\"] == region]\n",
    "        my_population = population[cod_reg[\"region\"] == region]\n",
    "        # plot data\n",
    "        ax.scatter(my_res[year], my_prod[year], s=my_population[year]*2, \n",
    "                   c=color, edgecolor=\"0\", alpha=0.75, zorder=2)\n",
    "        # plot labels\n",
    "        for index in my_res.index:\n",
    "            if my_res.loc[index,year] >= 0.1 and my_prod.loc[index,year] >= 1:\n",
    "                ax.text(x=my_res.loc[index,year], y=my_prod.loc[index,year], \n",
    "                        s=cod_reg.loc[index,\"code\"], size=8, zorder=3)\n",
    "    # plot year\n",
    "    ax.text(x = 0.4, y = 15, s=str(year), \n",
    "            fontdict=dict(fontfamily=\"Courier New\", color=\"lightgray\", size=250), zorder=1)\n",
    "    # snap current plot\n",
    "    camera.snap()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccbffc33",
   "metadata": {},
   "source": [
    "To play the animation in the notebook, you may need to install ffmpeg. For macOS, follow [this link](https://phoenixnap.com/kb/ffmpeg-mac)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ce4a246",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import HTML to display video in notebook\n",
    "from IPython.display import HTML\n",
    "# create animation\n",
    "animation = camera.animate(interval = 500, repeat = True, repeat_delay = 500)\n",
    "# play animation\n",
    "HTML(animation.to_html5_video())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c71dff3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save animation\n",
    "animation.save(\"../movies/OilResVsProd.mp4\", dpi=300)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}