1_naturalGas_Wrangling.ipynb

{
 "metadata": {
  "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.8.2-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python38264bit1bc03026842f4e58ab986af81481d738",
   "display_name": "Python 3.8.2 64-bit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import datetime\n",
    "\n",
    "from googletrans import Translator"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MyDataFrame Class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "class MyDataFrame: \n",
    "    def __init__(self, df, translate=False, translate_first_level=False, white_space=False, drop_level=True):\n",
    "        \n",
    "        self.df = df\n",
    "        self.translator = Translator()\n",
    "        self.title = ''\n",
    "        self.unit = ''\n",
    "        self.footer = ''\n",
    "        self.translate = translate\n",
    "        self.translate_first_level = translate_first_level\n",
    "        self.white_space= white_space\n",
    "        self.drop_level = drop_level\n",
    "        #self.ratio = (((self.df.iloc[:,-2]-self.df.iloc[:,-3])/self.df.iloc[:,-3])*100).round()\n",
    "        #self.last_column = self.df.iloc[:,-1].replace('..', 0).round()\n",
    "        \n",
    "        \n",
    "\n",
    "        if self.df.index.nlevels > 1:\n",
    "            self.title_unit_multiindex();\n",
    "        \n",
    "        if self.drop_level:\n",
    "            self.drop_levels()\n",
    "            \n",
    "        #if (self.ratio == self.last_column).all():\n",
    "        #   self.drop_last_column()\n",
    "        \n",
    "        \n",
    "        if self.translate:\n",
    "            if self.df.index.nlevels == 1:\n",
    "                self.translate_index()\n",
    "                \n",
    "            \n",
    "        if self.translate:\n",
    "            if self.df.index.nlevels > 1:\n",
    "                self.translate_multi_index()\n",
    "        \n",
    "        \n",
    "        if self.white_space:\n",
    "            self.replace_white_space()\n",
    "\n",
    "        self.drop_na()\n",
    "            \n",
    "            \n",
    "    def title_unit_multiindex(self):\n",
    "        self.title = self.df.columns[0][0]\n",
    "        self.unit = self.df.columns[1][1]\n",
    "            \n",
    "    def drop_levels(self):\n",
    "        \"\"\"\n",
    "        This method drops two column levels that contained the infos previously captured (table's title and unit)\n",
    "        \"\"\"     \n",
    "        while self.df.columns.nlevels>1:\n",
    "            self.df.columns = self.df.columns.droplevel(0)\n",
    "            \n",
    "            \n",
    "    def drop_na(self):\n",
    "        \"\"\"\n",
    "        This method drops all rows and columns that have all values equals to NaN.\n",
    "        \"\"\"  \n",
    "        self.df.dropna(how = 'all', inplace = True)\n",
    "        self.df.dropna(axis = 'columns', how = 'all', inplace = True)\n",
    "\n",
    "    \n",
    "    def translate_index(self):\n",
    "        \"\"\"\n",
    "        This method translates the index of a DataFrame to English.\n",
    "        \"\"\"\n",
    "        self.new_index = []\n",
    "        for index in self.df.index:\n",
    "            if index == 'Reinjeção':\n",
    "                self.new_index.append('Reinjection')\n",
    "            elif (index == 'Espírito Santo') or (index == 'Espirito_Santo'):\n",
    "                self.new_index.append('Espirito_Santo')\n",
    "            elif index == 'Amazonas':\n",
    "                self.new_index.append('Amazonas')\n",
    "            elif index == 'Alagoas':\n",
    "                self.new_index.append('Alagoas')\n",
    "            elif (index == 'Ceará') or (index == 'Ceara'):\n",
    "                self.new_index.append('Ceara')\n",
    "            elif (index == 'Rio Grande do Norte') or (index == 'Rio_Grande_do_Norte'):\n",
    "                self.new_index.append('Rio_Grande_do_Norte')\n",
    "            else:\n",
    "                self.new_index.append(self.translator.translate(index).text)\n",
    "        self.df.index = self.new_index\n",
    "        \n",
    "                        \n",
    "    def translate_multi_index(self):\n",
    "        \"\"\"\n",
    "        This method translates a MultiIndex DataFrame to English.\n",
    "        \"\"\"\n",
    "        \n",
    "        if self.translate_first_level == True:\n",
    "            for i, num in enumerate(self.df.index):\n",
    "                    for j in range(self.df.index.nlevels):       \n",
    "                        if j==0:\n",
    "                            if (self.df.index[i][j] == 'Espírito_Santo') or (self.df.index[i][j] == 'Espirito_Santo'):\n",
    "                                self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace('Espírito_Santo','Espirito_Santo'), level = j)\n",
    "                            elif self.df.index[i][j] == 'Amazonas':\n",
    "                                self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace('Amazonas','Amazonas'), level = j)\n",
    "                            elif self.df.index[i][j] == 'Alagoas':\n",
    "                                self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace('Alagoas','Alagoas'), level = j)\n",
    "                            elif (self.df.index[i][j] == 'Ceará') or (self.df.index[i][j] == 'Ceara'):\n",
    "                                self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace('Ceará','Ceara'), level = j)\n",
    "                            elif (self.df.index[i][j] == 'Rio Grande do Norte') or (self.df.index[i][j] == 'Rio_Grande_do_Norte'):\n",
    "                                self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace(' ','_'), level = j)\n",
    "                            else:\n",
    "                                self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace(self.df.index[i][j], self.translator.translate(self.df.index[i][j]).text), level = j)\n",
    "        \n",
    "        for i, num in enumerate(self.df.index):\n",
    "            for j in range(self.df.index.nlevels):\n",
    "                if j==0:\n",
    "                    pass\n",
    "                if j==1:\n",
    "                    if self.df.index[i][j] == 'Mar': # checks if one of the words that the translate package can not translate\n",
    "                        self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace('Mar','Offshore'), level = j)\n",
    "                    elif self.df.index[i][j] == 'Terra': # checks if one of the words that the translate package can not translate\n",
    "                        self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace('Terra','Onshore'), level = j)\n",
    "                    elif not isinstance(self.df.index[i][j], str):\n",
    "                        pass\n",
    "                    else:\n",
    "                        self.df.index = self.df.index.set_levels(self.df.index.levels[j].str.replace(self.df.index[i][j], self.translator.translate(self.df.index[i][j]).text), level = j)\n",
    "        \n",
    "        \n",
    "    def replace_underscore(self):\n",
    "        \"\"\"\n",
    "        This method replaces all underscore for white space.\n",
    "        \"\"\"\n",
    "        if self.df.index.nlevels > 1: # tells how many level are\n",
    "            for i, level in enumerate(range(self.df.index.nlevels)): # runs through levels\n",
    "                #for j, value in enumerate(self.df.index.levels[i]): # runs through the level's value and replace white space for underline\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[i].str.replace(\"_\", \" \"), level = i)\n",
    "        \n",
    "        elif self.df.index.nlevels == 1:\n",
    "            self.new_index = []\n",
    "            for index in self.df.index:\n",
    "                self.new_index.append(index.replace('_', ' '))\n",
    "            self.df.index = self.new_index\n",
    "            \n",
    "            \n",
    "    def drop_unnamed_column(self):\n",
    "        \"\"\"\n",
    "        This method drops the last column if its name starts with 'Unnamed'.\n",
    "        \"\"\"            \n",
    "        for i,name in enumerate(self.df.columns):\n",
    "            if type(name) == str and name.startswith('Unnamed'):\n",
    "                self.df = self.df.drop(self.df.columns[-1], axis=1)\n",
    "\n",
    "    def index_sups(self):\n",
    "        if self.df.index.nlevels > 1:\n",
    "            for name in self.df.index.levels[0]:\n",
    "\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('1','¹'), level = 0)\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('2','²'), level = 0)\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('3','³'), level = 0)\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('4','⁴'), level = 0)\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('5','⁵'), level = 0)\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('6','⁶'), level = 0)\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('7','⁷'), level = 0)\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('8','⁸'), level = 0)\n",
    "                self.df.index = self.df.index.set_levels(self.df.index.levels[0].str.replace('9','⁹'), level = 0)\n",
    "\n",
    "        if self.df.index.nlevels == 1:\n",
    "            self.df.index = self.df.index.str.replace('1','¹')\n",
    "            self.df.index = self.df.index.str.replace('2','²')\n",
    "            self.df.index = self.df.index.str.replace('3','³')\n",
    "            self.df.index = self.df.index.str.replace('4','⁴')\n",
    "            self.df.index = self.df.index.str.replace('5','⁵')\n",
    "            self.df.index = self.df.index.str.replace('6','⁶')\n",
    "            self.df.index = self.df.index.str.replace('7','⁷')\n",
    "            self.df.index = self.df.index.str.replace('8','⁸')\n",
    "            self.df.index = self.df.index.str.replace('9','⁹')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load and Wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Total¹ Reserves of Natural Gas, by Location (Shore and Offshore), according to Federation² Units - 2010-2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_reserves = MyDataFrame(pd.read_excel(r'data_set/anuario-2020-tabela-2_5.xls',  header = [0,2,3], index_col = [0,1]), translate=True,)\n",
    "\n",
    "#dropping Unnamed column\n",
    "total_reserves.drop_unnamed_column()\n",
    "\n",
    "# Correcting Index Supscrit\n",
    "total_reserves.index_sups()\n",
    "\n",
    "# Transposing DataFrame\n",
    "total_reserves.df = total_reserves.df.T\n",
    "\n",
    "# Setting unit, title and footer\n",
    "total_reserves.unit = '10⁶ m³'\n",
    "total_reserves.title = 'Total¹ Reserves of Natural Gas, by Location (Shore and Offshore), according to Federation² Units'\n",
    "total_reserves.footer ='Source:\\nANP/SDP, as per Resolution ANP No. 47/2014.\\n\\nNotes:\\n1. Reserves on 12/31 of the reference years.\\n2. See the General Notes item on \"Brazilian Oil and Natural Gas Reserves\".\\n\\n¹ Including reserves whose fields Development Plans are under analysis.\\n² The reserves are fully appropriated to the state in which each field has its area mostly located.\\n³ The Roncador and Frade field reserves are fully appropriated in the State of Rio de Janeiro, for simplification.\\n⁴ The Sapinhoá field reserves are fully appropriated in the State of São Paulo for simplification.\\n⁵ The reserves in the Caravela field are fully appropriated in the State of Paraná, for simplification.\\n⁶ The Tubarão field reserves are fully appropriated in the State of Santa Catarina, for simplification.\\n'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Evolution of Natural Gas Processing Capacity, According to Production Centers - 2010-2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "processing = MyDataFrame(pd.read_excel(r'data_set/anuario-2020-tabela-2_30.xls',  header = [0,2,3], index_col = [0]))\n",
    "\n",
    "# Transposing DataFrame\n",
    "processing.df = processing.df.T\n",
    "\n",
    "# Setting unit, title and footer\n",
    "processing.unit = '10³ m³/dia'\n",
    "processing.title = 'Evolution of Natural Gas Processing Capacity, According to Production Centers'\n",
    "processing.footer = '¹ Volume in the gaseous state.\\n² Includes the UPGNs (Natural Gas Production Units) in Catu and Bahia until 2013. From 2014, only includes Catu.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Proved Natural Gas Reserves¹, per Location (Onshore and Offshore), by State² – 2010-2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "proved_reserves = MyDataFrame(pd.read_excel(r'data_set/table-1_2.xls', header = [0,2,3], index_col = [0,1]))\n",
    "\n",
    "# Dropping Unnamed Column\n",
    "proved_reserves.drop_unnamed_column()\n",
    "\n",
    "# Correcting Index Supscrit\n",
    "proved_reserves.index_sups()\n",
    "\n",
    "# Transposing DataFrame\n",
    "proved_reserves.df = proved_reserves.df.T\n",
    "\n",
    "# Setting unit, title and footer\n",
    "proved_reserves.unit = '10⁶ m³'\n",
    "proved_reserves.title = 'Proved natural gas reserves¹, per location (onshore and offshore), by State²'\n",
    "proved_reserves.footer = 'Source: \\nANP/SDP, as per Ordinance ANP No. 47/2014.\\n\\nNotes: \\n1. Reserves on December 31 of reference years.1Reserves related to fields whose development plans are still under analysis by ANP included.\\n2. Condensates included.\\n3. See item in General Notes on \"Brazilian Oil and Natural Gas Reserves\".\\n\\n¹ Reserves related to fields whose development plans are still under analysis by ANP included.\\n² The reserves are fully appropriated to the State in which each field has its area.\\n³ Reserves related to Roncador and Frade fields were totally accounted to the State of Rio de Janeiro by means of simplification.\\n⁴ Sapinhoá field reserves are fully appropriated in the State of São Paulo for simplification.\\n⁵ Reserves related to Caravela field were totally accounted to the State of Paraná by means of simplification.\\n⁶ Reserves related to Tubarão field are totally accounted to the State of Santa Catarina by means of simplification.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Natural Gas Domestic Sales by Brazilian Region and State – 2010-2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "domestic_sale = MyDataFrame(pd.read_excel(r'data_set/table-1_25.xls', header = [0,2,3], index_col = [0]))\n",
    "\n",
    "# Dropping Unnamed Column\n",
    "domestic_sale.drop_unnamed_column()\n",
    "\n",
    "# Correcting Index Supscrit\n",
    "domestic_sale.index_sups()\n",
    "\n",
    "# Transposing DataFrame\n",
    "domestic_sale.df = domestic_sale.df.T\n",
    "\n",
    "# Setting unit, title and footer\n",
    "domestic_sale.unit = '10⁶ m³'\n",
    "domestic_sale.title = 'Natural Gas Domestic Sales by Brazilian Region and State'\n",
    "domestic_sale.footer = 'Sources: \\nPetrobras and ANP.\\n\\nNote:\\nRelated only the States where there were sales of natural gas in the specified period.\\n\\n¹Include sales to thermal generation ²Sales for Nitrogen Fertilizer plants (Fafen) and sales for thermal generation.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Brazilian Natural Gas Balance – 2010-2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "balance = MyDataFrame(pd.read_excel(r'data_set/table-1_26.xls', header = [0,2,3], index_col = [0], skipfooter=1))\n",
    "\n",
    "# Dropping Unnamed Column\n",
    "balance.drop_unnamed_column()\n",
    "\n",
    "# Correcting Index Supscrit\n",
    "balance.index_sups()\n",
    "\n",
    "# Transposing DataFrame and Skiping Duplicate Row\n",
    "balance.df = balance.df.iloc[[0,1,2,3,4,5,6,7,8]].T\n",
    "\n",
    "# Setting unit, title and footer\n",
    "balance.unit = '10⁶ m³'\n",
    "balance.title = 'Brazilian Natural Gas Balance'\n",
    "balance.footer = 'Sources: \\nANP/SIM, as per Ordinance ANP No. 43/98, for imports data; ANP/SDP, as per Decree No. 2.705/98, for\\nproduction, reinjection, gas flaring and losses data; Petrobras, for own consumption, NGL and sales data.\\n\\n¹ Refers to Petrobras own consumption in production areas, refineries, NGPP (Natural Gas Power Plant),\\n transportation and storage. \\n² Volume of gas absorbed in NGPPs. \\n³ Sales to distributors, nitrofertilizers plants (Fafen) and electricity generation.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Brazilian Natural Gas Demand – 2010-2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Demand DataFrame\n",
    "demand = MyDataFrame(balance.df.loc[:,['Import','Reinjection', 'Gas flaring', 'Own consumption¹', 'NGL²', 'Sales³', 'Adjustments and losses']])\n",
    "\n",
    "# Setting unit, title and footer\n",
    "demand.title = 'Brazilian Natural Gas Demand'\n",
    "demand.unit = '10⁶ m³'\n",
    "demand.footer = 'Sources: \\nANP/SIM, as per Ordinance ANP No. 43/98, for imports data; ANP/SDP, as per Decree No. 2.705/98, for\\nproduction, reinjection, gas flaring and losses data; Petrobras, for own consumption, NGL and sales data.\\n\\n¹ Refers to Petrobras own consumption in production areas, refineries, NGPP (Natural Gas Power Plant),\\n transportation and storage. \\n² Volume of gas absorbed in NGPPs. \\n³ Sales to distributors, nitrofertilizers plants (Fafen) and electricity generation.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Demand Total Series\n",
    "demand_total = demand.df.sum(axis=1)\n",
    "\n",
    "# Setting unit, title and footer\n",
    "demand_total_title = 'Brazilian Natural Gas Total Demand – 2010-2019'\n",
    "demand_total_unit = '10⁶ m³'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Natural Gas Production, per Location (Onshore and Offshore, Pre-salt and Post-salt), by State – 2010-2019 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "production = MyDataFrame(pd.read_excel(r'data_set/table-1_5.xls', header = [0,2,3], index_col = [0,1]))\n",
    "\n",
    "# Dropping Unnamed Column\n",
    "production.drop_unnamed_column()\n",
    "\n",
    "# Correcting Index Supscrit\n",
    "production.index_sups()\n",
    "\n",
    "# Transposing DataFrame\n",
    "production.df = production.df.T\n",
    "\n",
    "# Setting unit, title and footer\n",
    "production.unit='10⁶ m³'\n",
    "production.title = 'Natural gas production, per location (onshore and offshore, pre-salt and post-salt), by State'\n",
    "production.footer = 'Source: \\nANP/SDP, as per Decree No. 2.705/1998.\\n\\nNote: \\nTotal production includes reinjection, gas flaring, losses and own consumption.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Production of Associated and Non-Associated Natural Gas, by State – 2010-2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "gas_type = MyDataFrame(pd.read_excel(r'data_set/table-1_6.xls', header = [0,2,3], index_col = [0,1]))\n",
    "\n",
    "# Dropping Unnamed Column\n",
    "gas_type.drop_unnamed_column()\n",
    "\n",
    "# Transposing DataFrame\n",
    "gas_type.df = gas_type.df.T\n",
    "\n",
    "# Setting unit, title and footer\n",
    "gas_type.unit='10⁶ m³'\n",
    "gas_type.title = ('Production of associated and non-associated natural gas, by State')\n",
    "gas_type.footer = 'Source: \\nANP/SDP, as per Decree No. 2.705/1998.\\n\\nNote: \\nTotal production volume includes reinjection, flared gas, losses, own consumption.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Average Reference Prices for Natural Gas, According to Federation Units - 2010-2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_price = MyDataFrame(pd.read_excel(r'data_set/anuario-2020-tabela-2_24.xls',  header = [0,2,3,4], index_col = [0]))\n",
    "\n",
    "# Slicing 'US$/mil m³' units only\n",
    "avg_price.df = avg_price.df.iloc[:,10:20]\n",
    "\n",
    "# Transposing DataFrame\n",
    "avg_price.df = avg_price.df.T\n",
    "\n",
    "# Setting unit, title and footer\n",
    "avg_price.unit='US$/mil m³'\n",
    "avg_price.title = 'Average Reference Prices for Natural Gas, According to Federation Units'\n",
    "avg_price.footer = 'Source: \\nANP / SPG, according to Law No. 9,478 / 1997, Decree No. 2,705 / 1998 and \\nANP Ordinance No. 206/2000. \\n\\nNotes: \\n1. Prices in current values. \\n2. Only the Federation units that presented natural gas production in the \\nindicated period are listed. \\n3. The above prices do not serve as a basis for calculating government\\nstakes,since they are weighted averages only for production volumes per field \\nand do not consider royalty and special participation rates per \\nproducing field.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Electric Energy by Source - Monthly 2000-2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "energy_source = pd.read_csv(r'data_set/GeracaoFonte.csv')\n",
    "\n",
    "# Translating Manually\n",
    "energy_source.columns = ['ID', 'Energy Type', 'GWh', 'Month', 'Year', 'dthProx']\n",
    "\n",
    "# Converting into MyDataFrame\n",
    "energy_source = MyDataFrame(pd.pivot_table(energy_source, values='GWh', index=['Energy Type'], columns=['Year', 'Month']), drop_level=False)\n",
    "\n",
    "#Translating Index Manually\n",
    "energy_source.df.index = ['Biomass','Coal','Energy Produced Out of SIN','Eolic','Natural Gas','Hidrelectric','Itaipu','Nuclear','Diesel/Oil','Waste Industrial Processes','Solar']\n",
    "\n",
    "# Transposing DataFrame\n",
    "energy_source.df = energy_source.df.T\n",
    "\n",
    "# Adding all Hydro Sources into one column\n",
    "energy_source.df['Hydroelectric'] = (energy_source.df.loc[:,'Itaipu'] + energy_source.df.loc[:,'Hidrelectric'])\n",
    "energy_source.df.drop(['Hidrelectric','Itaipu'], axis=1, inplace=True)\n",
    "                "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "energy_source = pd.read_csv(r'data_set/GeracaoFonte.csv')\n",
    "\n",
    "# Translating Manually\n",
    "energy_source.columns = ['ID', 'Energy Type', 'GWh', 'Month', 'Year', 'dthProx']\n",
    "\n",
    "# Converting into MyDataFrame\n",
    "energy_source = MyDataFrame(pd.pivot_table(energy_source, values='GWh', index=['Energy Type'], columns=['Year', 'Month']), drop_level=False)\n",
    "\n",
    "#Translating Index Manually\n",
    "energy_source.df.index = ['Biomass','Coal','Energy Produced Out of SIN','Eolic','Natural Gas','Hidrelectric','Itaipu','Nuclear','Diesel/Oil','Waste Industrial Processes','Solar']\n",
    "\n",
    "# Transposing DataFrame\n",
    "energy_source.df = energy_source.df.T\n",
    "\n",
    "# Adding all Hydro Sources into one column\n",
    "energy_source.df['Hydroelectric'] = (energy_source.df.loc[:,'Itaipu'] + energy_source.df.loc[:,'Hidrelectric'])\n",
    "energy_source.df.drop(['Hidrelectric','Itaipu'], axis=1, inplace=True)\n",
    "\n",
    "#Dropping 2019 \n",
    "energy_source.df.drop(2019,inplace=True)\n",
    "\n",
    "# Setting unit, title and footer\n",
    "energy_source.unit = 'GWh'\n",
    "energy_source.title = 'Electric Energy by Source'\n",
    "energy_source.footer = 'Source: ANEEL\\nNotes: History of the electric energy volume produced in the country in GWh, expressed by the values of energy load \\ndispatched in the National Interconnected System - SIN, classified by renewable sources or not and the volume \\nproduced by the generators not yet interconnected.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Brazilian Indicators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "bra_ind = pd.read_csv(r'data_set/API_BRA_DS2_en_csv_v2_1221399.csv', skiprows=[0,1,2,3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Total Own Consumption of Natural Gas, According to Regions and Federation Units - 2009-2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "consumption = MyDataFrame(pd.read_csv(r'data_set\\anuario-2019-abertos-tabela3_31.csv'))\n",
    "\n",
    "# Translating Manually Columns Name\n",
    "consumption.df.columns = ['Region', 'Federation Unit', 'Consume 10³ m³','Year']\n",
    "\n",
    "# Rearanging MyDataFrame\n",
    "consumption.df = pd.pivot_table(consumption.df, values='Consume 10³ m³', index='Year', columns=['Region','Federation Unit'])\n",
    "\n",
    "# Translating Manually Columns Name\n",
    "consumption.df.columns = consumption.df.columns.set_levels(consumption.df.columns.levels[0].str.replace('Nordeste','Northwest'), level = 0)\n",
    "consumption.df.columns = consumption.df.columns.set_levels(consumption.df.columns.levels[0].str.replace('Norte','North'), level = 0)\n",
    "consumption.df.columns = consumption.df.columns.set_levels(consumption.df.columns.levels[0].str.replace('Sudeste','Southeast'), level = 0)\n",
    "consumption.df.columns = consumption.df.columns.set_levels(consumption.df.columns.levels[0].str.replace('Sul','South'), level = 0)\n",
    "\n",
    "\n",
    "# Setting unit, title and footer\n",
    "\n",
    "consumption.df.unit = '10³ m³'\n",
    "consumption.df.title = 'Natural Gas Consumption by Region and Federation Unit'\n",
    "consumption.df.footer = 'Source: \\nANP Statistical Yearbook 2020'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. Sales by Segment Scrapped From Scratch\n",
    "\n",
    "Source: http://www.mme.gov.br/documents/36216/1119340/06+-+Boletim+Mensal+de+Acompanhamento+da+Ind%C3%BAstria+de+G%C3%A1s+Natural+Junho+2020/4ecd27ca-bd64-bfa7-3510-03799045f87f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Dict with values scrapp from MME Relatory\n",
    "sales_segment_ = {'Industrial¹' : [43.61, 40.82, 40.77, 39.75, 36.97, 36.34, 37.17, 35.70, 28.16, 31.22, 34.61, 33.87],\n",
    "                  'Automotive' : [4.82, 4.96, 5.40, 6.06, 6.26, 5.87, 6.29, 4.83, 3.36, 3.63, 4.34, 4.72],\n",
    "                  'Residencial' : [0.97, 1.11, 1.18, 1.26, 1.27, 1.00, 1.14, 1.30, 1.38, 1.49, 1.64, 1.33],\n",
    "                  'Comercial' : [0.79, 0.83, 0.78, 0.84, 0.91, 0.86, 0.87, 0.84, 0.51, 0.32, 0.46, 0.64],\n",
    "                  'Electric Generation' : [45.90, 29.59, 34.25, 27.69, 29.03, 40.46, 25.63, 19.52, 17.26, 15.70, 18.12, 22.78],\n",
    "                  'Cogenaration' : [2.50, 2.37, 2.65, 2.84, 2.65, 2.30, 2.12, 2.26, 2.22, 1.65, 2.07, 2.10],\n",
    "                  'Others (including GNC)' : [0.04, 0.58, 0.53, 0.40, 0.83, 0.42, 0.35, 0.36, 1.22, 0.76, 0.65, 0.63]}\n",
    "\n",
    "# Creating DataFrame\n",
    "sales_segment_ = pd.DataFrame(data=sales_segment_)\n",
    "\n",
    "# Setting Index\n",
    "sales_segment_.index = [2015,2016,2017,2018,2019,1,2,3,4,5,6,2020]\n",
    "\n",
    "# Creating DataFrame for Covid Period\n",
    "sales_seg_covid_ = sales_segment_.loc[[1,2,3,4,5,6]].copy()\n",
    "\n",
    "# Setting Index to string\n",
    "sales_seg_covid_.index = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']\n",
    "\n",
    "# Dropping Covid Period from Segment DataFrame\n",
    "sales_segment_.drop([1,2,3,4,5,6,2020], inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Turning Sales Period DataFrame into MyDataFrame\n",
    "sales_segment = MyDataFrame(sales_segment_)\n",
    "\n",
    "# Setting unit, title and footer\n",
    "sales_segment.unit = '10⁶ m³/day'\n",
    "sales_segment.title = 'Brazilian Sales of Natural Gas by Segment'\n",
    "sales_segment.footer = 'Source:\\nMME, Monthly Industry Follow-up Bulletin of Natural Gas - June 2020\\n\\n¹ Includes consumption by refineries, fertilizer factories and use of gas as raw material.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Turning Sales Covid Period DataFrame into MyDataFrame\n",
    "sales_seg_covid = MyDataFrame(sales_seg_covid_)\n",
    "\n",
    "# Setting unit, title and footer\n",
    "sales_seg_covid.unit = '10⁶ m³/day'\n",
    "sales_seg_covid.title = 'Brazilian Sales of Natural Gas 1º Semester 2020'\n",
    "sales_seg_covid.footer = 'Source:\\nMME, Monthly Industry Follow-up Bulletin of Natural Gas - June 2020\\n\\n¹ Includes consumption by refineries, fertilizer factories and use of gas as raw material.'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sales by Segment %"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_per = sales_segment.df.copy()\n",
    "\n",
    "# Calculation proportion\n",
    "years=np.arange(2015,2020,1)\n",
    "for i, year in enumerate(years):\n",
    "    sales_per.loc[year] = sales_segment.df.iloc[i,:].div(sales_segment.df.iloc[i,:].sum())\n",
    "\n",
    "# Converting into MyDataFrame\n",
    "sales_per = MyDataFrame(sales_per)\n",
    "\n",
    "# Setting unit, title and footer\n",
    "sales_per.unit = '%'\n",
    "sales_per.title = 'Brazilian Sales of Natural Gas by Segment'\n",
    "sales_per.footer = 'Source:\\nMME, Monthly Industry Follow-up Bulletin of Natural Gas - June 2020\\n\\n¹ Includes consumption by refineries, fertilizer factories and use of gas as raw material.'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14. Precipitation DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": [
    "skip = np.arange(0,21,1)\n",
    "precip = MyDataFrame(pd.read_csv(r'data_set\\precip.csv', skiprows=skip))\n",
    "\n",
    "# Setting Columns Name\n",
    "precip.df.columns = ['Year','Jan','Feb','Mar','Apr','May','Jun','Jul','Ago','Sep','Oct','Nov','Dec']\n",
    "\n",
    "# Setting DataFrama Index\n",
    "precip.df.index = precip.df.loc[:,'Year']\n",
    "\n",
    "# Dropping Year Column\n",
    "precip.df.drop('Year', axis=1, inplace=True)\n",
    "\n",
    "# Setting unit, title and footer\n",
    "precip.unit='mm/month'\n",
    "precip.title='Average Preciptation Rate Monthly'\n",
    "precip.footer='Source: \\nThe data used in this visualization were produced with the Giovanni online data system, \\ndeveloped and maintained by the NASA GES DISC.'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15. Creating Energy, Hydro Enegy and Precipitation Time Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_energy = energy_source.df.sum(axis=1).copy().to_frame('GWh')\n",
    "\n",
    "# Creating datetime index\n",
    "dt = pd.date_range('1/1/2000', periods=228, freq='M')\n",
    "\n",
    "# Inserting and Changing ts_energy index\n",
    "ts_energy.insert(0,'Date',dt)\n",
    "ts_energy.reset_index(inplace=True)\n",
    "ts_energy.index = ts_energy['Date']\n",
    "ts_energy.drop(['Year','Month','Date'], axis=1, inplace=True)\n",
    "\n",
    "# Changing index name\n",
    "ts_energy.index.name = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_hydro = energy_source.df.loc[:,'Hydroelectric'].copy().to_frame('GWh')\n",
    "\n",
    "# Creating datetime index\n",
    "dt = pd.date_range('1/1/2000', periods=228, freq='M')\n",
    "\n",
    "# Inserting and Changing ts_hydro index\n",
    "ts_hydro.insert(0,'Date',dt)\n",
    "ts_hydro.reset_index(inplace=True)\n",
    "ts_hydro.index = ts_hydro['Date']\n",
    "ts_hydro.drop(['Year','Month','Date'], axis=1, inplace=True)\n",
    "\n",
    "# Changing index name\n",
    "ts_hydro.index.name = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_precip = precip.df.copy()\n",
    "\n",
    "# Creating datetime index\n",
    "dt = pd.date_range('1/1/2000', periods=228, freq='M')\n",
    "\n",
    "# Changing ts_precip index\n",
    "ts_precip.reset_index(inplace=True)\n",
    "\n",
    "# Dropping Year\n",
    "ts_precip.drop('Year', axis=1, inplace=True)\n",
    "\n",
    "# Turning DataFrame into a Series\n",
    "ts_precip = ts_precip.stack()\n",
    "\n",
    "# Changing ts_precip index\n",
    "ts_precip.index = dt\n",
    "\n",
    "# Changing index name\n",
    "ts_precip.index.name = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 16. Carbon Dioxide Emissions Coefficients"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating CO2 MyDataFrame\n",
    "co2_em = MyDataFrame(pd.read_excel(r'data_set\\co2_vol_mass_updated.xls', header = [0,1,2], index_col=[0]))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "co2_emission = MyDataFrame(co2_em.df.iloc[:,-1].to_frame().copy())\n",
    "\n",
    "# Setting unit, title and footer\n",
    "co2_emission.unit = 'Kg/10⁶ BTU'\n",
    "co2_emission.title = 'Carbon Dioxide Emissions Coefficients by Fuel'\n",
    "co2_emission.footer = 'Source: U.S. Energy Information Administration Estimates.\\nNote: To convert to carbon equivalents multiply by 12/44. Coefficients may vary slightly with estimation method and across time.'\n",
    "\n",
    "# Changing index name\n",
    "co2_emission.df.index.name = None\n",
    "\n",
    "# Changing index name\n",
    "co2_emission.df.columns.name= None\n",
    "\n",
    "# Changing column\n",
    "co2_emission.df.columns = [co2_emission.unit]\n",
    "\n",
    "# Sorting values\n",
    "co2_emission.df.sort_values(co2_emission.unit, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Less Poluent DataFrame %"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def cmp_co2(names, fuel):\n",
    "    df = co2_emission.df.loc[names].T.copy()\n",
    "    for value in names:\n",
    "        df[value]=(co2_emission.df.loc[value].values-co2_emission.df.loc[fuel])/co2_emission.df.loc[value]*100\n",
    "    df = df.T\n",
    "    df.columns = ['Less Poluent %']\n",
    "    df.drop(fuel, inplace=True)\n",
    "    return df\n",
    "\n",
    "#less_pol.index = 'Less Poluent'\n",
    "less_pol = cmp_co2(['Propane', 'Butane', 'Butane/Propane Mix',\n",
    "       'Home Heating and Diesel Fuel (Distillate)', 'Kerosene',\n",
    "       'Coal (All types)', 'Natural Gas', 'Gasoline',\n",
    "       'Residual Heating Fuel (Businesses only)', 'Petroleum coke',\n",
    "       'Other petroleum & miscellaneous', 'Coke','Waste oil'],'Natural Gas')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}