data_scraping/IBGE_inflation_rates/IBGE_inflation_rate_IPCA.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8536624f",
   "metadata": {},
   "source": [
    "# Getting historical inflations indices values from IBGE website\n",
    "\n",
    "**What ?** The IBGE (Instituto Brasileiro de Geografia e Estatística) is the institution that provides various inflation indices for the Brazilian economy. These indices serve as key references for monetary policy and investment decisions. The most important inflation indices provided by the IBGE are:\n",
    "\n",
    ">- **IPCA**: The primary inflation index in Brazil, reflecting price changes for goods and services consumed by a broad range of households.\n",
    ">- **IPCA-15**: A preliminary version of the IPCA, providing early insights into inflation trends.\n",
    ">- **IPCA-E**: The cumulative IPCA-15 over a specific period, used for short-term economic analysis.\n",
    ">- **INPC**: Measures inflation for households with lower incomes, earning between 1 and 5 minimum wages, often reflecting the inflationary impact on lower-income households more accurately.\n",
    "\n",
    "\n",
    "**Why ?** Inflation indices such as IPCA, IPCA-15, IPCA-E, and INPC are crucial for data analysis because they provide valuable insights into the economic environment like economic trends, monetary policy, cost of living, investment decisions and more.\n",
    "\n",
    "**How ?** Historical inflation rate data will be downloaded directly from the IBGE website using the 'requests' library\n",
    "[(see this link)](https://www.ibge.gov.br/estatisticas/economicas/precos-e-custos/9256-indice-nacional-de-precos-ao-consumidor-amplo?=&t=downloads). Once obtained, the data will be cleaned by removing empty rows, and the data columns will be transformed to create a consolidated dataframe with consistent columns and rules. Finally, the dataframe will be uploaded into a local SQLite database for further analysis.\n",
    "\n",
    "<img src=\"https://lh3.googleusercontent.com/d/1WRUYhcCLQ7PNGn6yNmc9m5k8w9LccJq-\" alt=\"texto_alternativo\" width=\"400\" align=\"center\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "385c243b",
   "metadata": {},
   "source": [
    "## Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ac5a5add",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import io\n",
    "\n",
    "import sqlite3\n",
    "import requests\n",
    "import zipfile"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65a918ad",
   "metadata": {},
   "source": [
    "### IPCA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4805220c",
   "metadata": {},
   "source": [
    "#### Downloading IPCA data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3e0a5386",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame loaded successfully.\n",
      "  yeardt monthdt num_indice var_mes var_3mes var_6mes var_ano var_12mes\n",
      "0   1994     JAN     141.31   41.31   162.13   533.33   41.31   2693.84\n",
      "1    NaN     FEV     198.22   40.27   171.24   568.17   98.22   3035.71\n"
     ]
    }
   ],
   "source": [
    "url_ipca = 'https://ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/IPCA/Serie_Historica/ipca_SerieHist.zip'\n",
    "\n",
    "response = requests.get(url_ipca)\n",
    "\n",
    "columns_name = ['yeardt','monthdt','num_indice','var_mes','var_3mes','var_6mes','var_ano','var_12mes'] # previous mapped columns name \n",
    "\n",
    "if response.status_code == 200:\n",
    "    # Create a ZipFile object from the response content\n",
    "    with zipfile.ZipFile(io.BytesIO(response.content)) as zip_ref:\n",
    "        for file_name in zip_ref.namelist():\n",
    "            if file_name.endswith('.xlsx') or file_name.endswith('.xls'):\n",
    "                # Read the Excel file into a DataFrame\n",
    "                with zip_ref.open(file_name) as excel_file:\n",
    "                    df_ipca = pd.read_excel(excel_file,skiprows = 7, names = columns_name,usecols=range(8))\n",
    "                break\n",
    "    print(\"DataFrame loaded successfully.\")\n",
    "    # Display the DataFrame\n",
    "    print(df_ipca.head(2))    \n",
    "else:\n",
    "    print('Connection erro: {}'.format(response.status_code))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a1853ec",
   "metadata": {},
   "source": [
    "#### Clean and prepare the dataframe IPCA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e4b82363",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yeardt</th>\n",
       "      <th>monthdt</th>\n",
       "      <th>num_indice</th>\n",
       "      <th>var_mes</th>\n",
       "      <th>var_3mes</th>\n",
       "      <th>var_6mes</th>\n",
       "      <th>var_ano</th>\n",
       "      <th>var_12mes</th>\n",
       "      <th>year_monthdt</th>\n",
       "      <th>index_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>JAN</td>\n",
       "      <td>141.31</td>\n",
       "      <td>41.31</td>\n",
       "      <td>162.13</td>\n",
       "      <td>533.33</td>\n",
       "      <td>41.31</td>\n",
       "      <td>2693.84</td>\n",
       "      <td>1994-1</td>\n",
       "      <td>IPCA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1994</td>\n",
       "      <td>FEV</td>\n",
       "      <td>198.22</td>\n",
       "      <td>40.27</td>\n",
       "      <td>171.24</td>\n",
       "      <td>568.17</td>\n",
       "      <td>98.22</td>\n",
       "      <td>3035.71</td>\n",
       "      <td>1994-2</td>\n",
       "      <td>IPCA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  yeardt monthdt num_indice var_mes var_3mes var_6mes var_ano var_12mes  \\\n",
       "0   1994     JAN     141.31   41.31   162.13   533.33   41.31   2693.84   \n",
       "1   1994     FEV     198.22   40.27   171.24   568.17   98.22   3035.71   \n",
       "\n",
       "  year_monthdt index_name  \n",
       "0       1994-1       IPCA  \n",
       "1       1994-2       IPCA  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data cleaning for IPCA\n",
    "df_ipca['yeardt'] = df_ipca['yeardt'].replace(r'^\\s*$', np.nan, regex=True) # replace white space by NaN\n",
    "df_ipca.dropna(how = 'all', inplace = True) # drop na values\n",
    "df_ipca['yeardt'].fillna(method = 'ffill', inplace = True) # writiing year over all rows \n",
    "df_ipca = df_ipca[df_ipca['yeardt'].apply(lambda x: str(x).isnumeric())] # drop rows without information\n",
    "map_month = {'JAN':1, 'FEV':2,'MAR':3, 'ABR':4,'MAI':5,'JUN':6,'JUL':7,'AGO':8, 'SET':9,'OUT':10,'NOV':11,'DEZ':12}\n",
    "df_ipca['year_monthdt'] = df_ipca['yeardt'].astype(str)+'-'+df_ipca['monthdt'].map(map_month).astype(str)  # remap data to a new format\n",
    "df_ipca['index_name'] = 'IPCA'\n",
    "df_ipca.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23eb205d",
   "metadata": {},
   "source": [
    "### IPCA 15"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d435ca6b",
   "metadata": {},
   "source": [
    "#### Downloading IPCA-15 data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "af7b0b51",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame loaded successfully.\n",
      "  yeardt monthdt num_indice var_mes var_3mes var_6mes var_ano var_12mes\n",
      "0   1994     JAN     139.17   39.17   154.72   510.69   39.17   2561.94\n",
      "1    NaN     FEV     194.42    39.7   165.75   546.36   94.42   2834.59\n"
     ]
    }
   ],
   "source": [
    "url_ipca15 = 'https://ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/IPCA_15/Series_Historicas/ipca-15_SerieHist.zip'\n",
    "\n",
    "response = requests.get(url_ipca15)\n",
    "\n",
    "columns_name = ['yeardt','monthdt','num_indice','var_mes','var_3mes','var_6mes','var_ano','var_12mes'] # previous mapped columns name \n",
    "\n",
    "if response.status_code == 200:\n",
    "    # Create a ZipFile object from the response content\n",
    "    with zipfile.ZipFile(io.BytesIO(response.content)) as zip_ref:\n",
    "        for file_name in zip_ref.namelist():\n",
    "            if file_name.endswith('.xlsx') or file_name.endswith('.xls'):\n",
    "                # Read the Excel file into a DataFrame\n",
    "                with zip_ref.open(file_name) as excel_file:\n",
    "                    df_ipca_15 = pd.read_excel(excel_file,skiprows = 8, names = columns_name,usecols=range(8))\n",
    "                break\n",
    "    print(\"DataFrame loaded successfully.\")\n",
    "    # Display the DataFrame\n",
    "    print(df_ipca_15.head(2))    \n",
    "else:\n",
    "    print('Connection erro: {}'.format(response.status_code))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d63dc54b",
   "metadata": {},
   "source": [
    "#### Clean and prepare the dataframe IPCA-15"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6404ad87",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yeardt</th>\n",
       "      <th>monthdt</th>\n",
       "      <th>num_indice</th>\n",
       "      <th>var_mes</th>\n",
       "      <th>var_3mes</th>\n",
       "      <th>var_6mes</th>\n",
       "      <th>var_ano</th>\n",
       "      <th>var_12mes</th>\n",
       "      <th>year_monthdt</th>\n",
       "      <th>index_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>JAN</td>\n",
       "      <td>139.17</td>\n",
       "      <td>39.17</td>\n",
       "      <td>154.72</td>\n",
       "      <td>510.69</td>\n",
       "      <td>39.17</td>\n",
       "      <td>2561.94</td>\n",
       "      <td>1994-1</td>\n",
       "      <td>IPCA-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1994</td>\n",
       "      <td>FEV</td>\n",
       "      <td>194.42</td>\n",
       "      <td>39.7</td>\n",
       "      <td>165.75</td>\n",
       "      <td>546.36</td>\n",
       "      <td>94.42</td>\n",
       "      <td>2834.59</td>\n",
       "      <td>1994-2</td>\n",
       "      <td>IPCA-15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  yeardt monthdt num_indice var_mes var_3mes var_6mes var_ano var_12mes  \\\n",
       "0   1994     JAN     139.17   39.17   154.72   510.69   39.17   2561.94   \n",
       "1   1994     FEV     194.42    39.7   165.75   546.36   94.42   2834.59   \n",
       "\n",
       "  year_monthdt index_name  \n",
       "0       1994-1    IPCA-15  \n",
       "1       1994-2    IPCA-15  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data cleaning for IPCA 15\n",
    "df_ipca_15['yeardt'] = df_ipca_15['yeardt'].replace(r'^\\s*$', np.nan, regex=True) # replace white space by NaN\n",
    "df_ipca_15.dropna(how = 'all', inplace = True) # drop na values\n",
    "df_ipca_15['yeardt'].fillna(method = 'ffill', inplace = True) # writiing year over all rows \n",
    "df_ipca_15 = df_ipca_15[df_ipca_15['yeardt'].apply(lambda x: str(x).isnumeric())] # drop rows without information\n",
    "map_month = {'JAN':1, 'FEV':2,'MAR':3, 'ABR':4,'MAI':5,'JUN':6,'JUL':7,'AGO':8, 'SET':9,'OUT':10,'NOV':11,'DEZ':12}\n",
    "df_ipca_15['year_monthdt'] = df_ipca_15['yeardt'].astype(str)+'-'+df_ipca_15['monthdt'].map(map_month).astype(str)  # remap data to a new format\n",
    "df_ipca_15['index_name'] = 'IPCA-15'\n",
    "df_ipca_15.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d289849d",
   "metadata": {},
   "source": [
    "### IPCA E"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97daff93",
   "metadata": {},
   "source": [
    "#### Downloading IPCA-E data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0dda5cb2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame loaded successfully.\n",
      "  yeardt monthdt num_indice var_mes var_3mes var_6mes var_ano var_12mes\n",
      "0   1994     JAN     139.17   39.17   154.72   510.69   39.17   2561.94\n",
      "1    NaN     FEV     194.42    39.7   165.75   546.36   94.42   2834.59\n"
     ]
    }
   ],
   "source": [
    "url_ipca_e = 'https://ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/IPCA_E/Series_Historicas/ipca-e_SerieHist.zip'\n",
    "\n",
    "response = requests.get(url_ipca_e)\n",
    "columns_name = ['yeardt','monthdt','num_indice','var_mes','var_3mes','var_6mes','var_ano','var_12mes'] # previous mapped columns name \n",
    "\n",
    "if response.status_code == 200:\n",
    "    # Create a ZipFile object from the response content\n",
    "    with zipfile.ZipFile(io.BytesIO(response.content)) as zip_ref:\n",
    "        for file_name in zip_ref.namelist():\n",
    "            if file_name.endswith('.xlsx') or file_name.endswith('.xls'):\n",
    "                # Read the Excel file into a DataFrame\n",
    "                with zip_ref.open(file_name) as excel_file:\n",
    "                    df_ipca_e = pd.read_excel(excel_file,skiprows = 5, names = columns_name, usecols=range(8))\n",
    "                break\n",
    "    print(\"DataFrame loaded successfully.\")\n",
    "    # Display the DataFrame\n",
    "    print(df_ipca_e.head(2))    \n",
    "else:\n",
    "    print('Connection erro: {}'.format(response.status_code))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdb61a6d",
   "metadata": {},
   "source": [
    "#### Clean and prepare the dataframe IPCA-E"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "41485090",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yeardt</th>\n",
       "      <th>monthdt</th>\n",
       "      <th>num_indice</th>\n",
       "      <th>var_mes</th>\n",
       "      <th>var_3mes</th>\n",
       "      <th>var_6mes</th>\n",
       "      <th>var_ano</th>\n",
       "      <th>var_12mes</th>\n",
       "      <th>year_monthdt</th>\n",
       "      <th>index_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>JAN</td>\n",
       "      <td>139.17</td>\n",
       "      <td>39.17</td>\n",
       "      <td>154.72</td>\n",
       "      <td>510.69</td>\n",
       "      <td>39.17</td>\n",
       "      <td>2561.94</td>\n",
       "      <td>1994-1</td>\n",
       "      <td>IPCA-E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1994</td>\n",
       "      <td>FEV</td>\n",
       "      <td>194.42</td>\n",
       "      <td>39.7</td>\n",
       "      <td>165.75</td>\n",
       "      <td>546.36</td>\n",
       "      <td>94.42</td>\n",
       "      <td>2834.59</td>\n",
       "      <td>1994-2</td>\n",
       "      <td>IPCA-E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  yeardt monthdt num_indice var_mes var_3mes var_6mes var_ano var_12mes  \\\n",
       "0   1994     JAN     139.17   39.17   154.72   510.69   39.17   2561.94   \n",
       "1   1994     FEV     194.42    39.7   165.75   546.36   94.42   2834.59   \n",
       "\n",
       "  year_monthdt index_name  \n",
       "0       1994-1     IPCA-E  \n",
       "1       1994-2     IPCA-E  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data cleaning for IPCA E\n",
    "df_ipca_e['yeardt'] = df_ipca_e['yeardt'].replace(r'^\\s*$', np.nan, regex=True) # replace white space by NaN\n",
    "df_ipca_e.dropna(how = 'all', inplace = True) # drop na values\n",
    "df_ipca_e['yeardt'].fillna(method = 'ffill', inplace = True) # writiing year over all rows \n",
    "df_ipca_e = df_ipca_e[df_ipca_e['yeardt'].apply(lambda x: str(x).isnumeric())] # drop rows without information\n",
    "map_month = {'JAN':1, 'FEV':2,'MAR':3, 'ABR':4,'MAI':5,'JUN':6,'JUL':7,'AGO':8, 'SET':9,'OUT':10,'NOV':11,'DEZ':12}\n",
    "df_ipca_e['year_monthdt'] = df_ipca_e['yeardt'].astype(str)+'-'+df_ipca_e['monthdt'].map(map_month).astype(str)  # remap data to a new format\n",
    "df_ipca_e['index_name'] = 'IPCA-E'\n",
    "df_ipca_e.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d505917",
   "metadata": {},
   "source": [
    "### INPC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8515688b",
   "metadata": {},
   "source": [
    "#### Downloading INPC data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2b129a6c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame loaded successfully.\n",
      "  yeardt monthdt num_indice var_mes var_3mes var_6mes var_ano var_12mes\n",
      "0   1994     JAN     141.31   41.31   162.13   533.33   41.31   2693.84\n",
      "1    NaN     FEV     198.22   40.27   171.24   568.17   98.22   3035.71\n"
     ]
    }
   ],
   "source": [
    "url_inpc= 'https://ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/INPC/Serie_Historica/inpc_SerieHist.zip'\n",
    "\n",
    "response = requests.get(url_ipca)\n",
    "\n",
    "columns_name = ['yeardt','monthdt','num_indice','var_mes','var_3mes','var_6mes','var_ano','var_12mes'] # previous mapped columns name \n",
    "\n",
    "if response.status_code == 200:\n",
    "    # Create a ZipFile object from the response content\n",
    "    with zipfile.ZipFile(io.BytesIO(response.content)) as zip_ref:\n",
    "        for file_name in zip_ref.namelist():\n",
    "            if file_name.endswith('.xlsx') or file_name.endswith('.xls'):\n",
    "                # Read the Excel file into a DataFrame\n",
    "                with zip_ref.open(file_name) as excel_file:\n",
    "                    df_inpc = pd.read_excel(excel_file,skiprows = 7, names = columns_name, usecols=range(8))\n",
    "                break\n",
    "    print(\"DataFrame loaded successfully.\")\n",
    "    # Display the DataFrame\n",
    "    print(df_inpc.head(2))    \n",
    "else:\n",
    "    print('Connection erro: {}'.format(response.status_code))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1346cbf4",
   "metadata": {},
   "source": [
    "#### Clean and prepare the dataframe INPC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e146583d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yeardt</th>\n",
       "      <th>monthdt</th>\n",
       "      <th>num_indice</th>\n",
       "      <th>var_mes</th>\n",
       "      <th>var_3mes</th>\n",
       "      <th>var_6mes</th>\n",
       "      <th>var_ano</th>\n",
       "      <th>var_12mes</th>\n",
       "      <th>year_monthdt</th>\n",
       "      <th>index_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1994</td>\n",
       "      <td>JAN</td>\n",
       "      <td>141.31</td>\n",
       "      <td>41.31</td>\n",
       "      <td>162.13</td>\n",
       "      <td>533.33</td>\n",
       "      <td>41.31</td>\n",
       "      <td>2693.84</td>\n",
       "      <td>1994-1</td>\n",
       "      <td>INPC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1994</td>\n",
       "      <td>FEV</td>\n",
       "      <td>198.22</td>\n",
       "      <td>40.27</td>\n",
       "      <td>171.24</td>\n",
       "      <td>568.17</td>\n",
       "      <td>98.22</td>\n",
       "      <td>3035.71</td>\n",
       "      <td>1994-2</td>\n",
       "      <td>INPC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  yeardt monthdt num_indice var_mes var_3mes var_6mes var_ano var_12mes  \\\n",
       "0   1994     JAN     141.31   41.31   162.13   533.33   41.31   2693.84   \n",
       "1   1994     FEV     198.22   40.27   171.24   568.17   98.22   3035.71   \n",
       "\n",
       "  year_monthdt index_name  \n",
       "0       1994-1       INPC  \n",
       "1       1994-2       INPC  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data cleaning for INPC\n",
    "df_inpc['yeardt'] = df_inpc['yeardt'].replace(r'^\\s*$', np.nan, regex=True) # replace white space by NaN\n",
    "df_inpc.dropna(how = 'all', inplace = True) # drop na values\n",
    "df_inpc['yeardt'].fillna(method = 'ffill', inplace = True) # writiing year over all rows \n",
    "df_inpc = df_inpc[df_inpc['yeardt'].apply(lambda x: str(x).isnumeric())] # drop rows without information\n",
    "map_month = {'JAN':1, 'FEV':2,'MAR':3, 'ABR':4,'MAI':5,'JUN':6,'JUL':7,'AGO':8, 'SET':9,'OUT':10,'NOV':11,'DEZ':12}\n",
    "df_inpc['year_monthdt'] = df_inpc['yeardt'].astype(str)+'-'+df_inpc['monthdt'].map(map_month).astype(str)  # remap data to a new format\n",
    "df_inpc['index_name'] = 'INPC'\n",
    "df_inpc.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02e5378d",
   "metadata": {},
   "source": [
    "### Concat all inflations rates in a unique dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5d375b66",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df_inflation_rates = pd.concat([df_ipca,df_ipca_15,df_ipca_e,df_inpc])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15874ef4",
   "metadata": {},
   "source": [
    "### Saving the final dataframe into the SQLite database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5c5cdb1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(os.getenv('MY_FINANCE_DB_PATH')+'/finance_database.db')\n",
    "df_inflation_rates.to_sql('IBGE_inflation_rates',conn,if_exists='replace',index=False)\n",
    "\n",
    "conn.close()    "
   ]
  }
 ],
 "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}