Notebooks/Data_Analysis___IO_with_Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Analysis - Input-Output with Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Author**: [Gabriele Pompa](https://www.linkedin.com/in/gabrielepompa/): gabriele.pompa@unisi.com"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of contents\n",
    "\n",
    "[Executive Summary](#summary)\n",
    "\n",
    "1. [Introduction](#intro)\n",
    "2. [Serialization Protocols in Python](#io_python)\\\n",
    "    2.1. [JSON format: `json` module](#json)\\\n",
    "    2.2. [`pickle` module](#pickle)\n",
    "3.  [IO with Pandas](#io_pandas)\\\n",
    "    3.1. [SQL](#sql)\\\n",
    "&nbsp; &nbsp; &nbsp; &nbsp; 3.1.1. [SQL queries from Python: `sqlite3` module](#sqlite3)\\\n",
    "&nbsp; &nbsp; &nbsp; &nbsp; 3.1.2. [Pandas and SQL: `.to_sql()` and `pd.read_sql()`](#pd_sql)\\\n",
    "&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3.1.2.1. [Parsing Dates: `pd.read_sql(..., parse_dates)`](#sql_dates)\\\n",
    "    3.2. [CSV: `.to_csv()` and `pd.read_csv()`](#pd_csv)\\\n",
    "&nbsp; &nbsp; &nbsp; &nbsp; 3.2.1. [Parsing Dates: `pd.read_csv(..., parse_dates)`](#csv_dates)\\\n",
    "    3.3. [Excel: `.to_excel()` and `df.read_excel()`](#excel)\\\n",
    "&nbsp; &nbsp; &nbsp; &nbsp; 3.3.1. [Parsing Dates: it's automatic](#excel_dates)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Resources**: \n",
    "\n",
    "- [_Python for Finance (2nd ed.)_](http://shop.oreilly.com/product/0636920117728.do): Sec. 9.Basic I/O with Python, 9.I/O with Pandas.\n",
    "\n",
    "\n",
    "- [_The Python Tutorial_](https://docs.python.org/3.7/tutorial/): Sec. [7.1.2](https://docs.python.org/3.7/tutorial/inputoutput.html#the-string-format-method) (the String `.format()` method) and [7.2](https://docs.python.org/3.7/tutorial/inputoutput.html#reading-and-writing-files) (reading and writing files)\n",
    "\n",
    "\n",
    "- From _[Pandas - Getting started tutorials](https://pandas.pydata.org/docs/getting_started/intro_tutorials/index.html):_ [How do I read and write tabular data?](https://pandas.pydata.org/docs/getting_started/intro_tutorials/02_read_write.html)\n",
    "\n",
    "\n",
    "- **Software**: [DB Browser for SQLite](https://sqlitebrowser.org/). [Download](https://sqlitebrowser.org/dl/)  for [Windows 64-bit](https://download.sqlitebrowser.org/DB.Browser.for.SQLite-3.11.2-win64.msi) | [Mac OS](https://download.sqlitebrowser.org/DB.Browser.for.SQLite-3.11.2.dmg) | Linux (check distribution on [download page]((https://sqlitebrowser.org/dl/)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Executive Summary <a name=\"summary\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this Notebook we will cover topics about serialization of Python objects, as well standard input output operations between Pandas and standard output formats."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following sections are organized as follows:\n",
    "\n",
    "- in Sec. [1](#intro) we introduce the `os` Python module which provides a useful interface between Python code and the underlying Operating System;\n",
    "- in Sec. [2](#io_python) we cover two standard serializations protocols available in Python: JSON and Pickle;\n",
    "- in Sec. [3](#io_pandas) we describe how to perform Input/Output operations between Pandas and SQL, CSV and Excel formats."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These are the basic imports that we need to work with NumPy, Pandas and to plot data using Matplotlib functionalities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for NumPy arrays\n",
    "import numpy as np\n",
    "\n",
    "# for Pandas Series and DataFrame\n",
    "import pandas as pd\n",
    "\n",
    "# for Matplotlib plotting\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# to do inline plots in the Notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Introduction <a name=\"intro\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before talking about specific Input/Output (IO) protocols, it is important to mention that typical operating system functionalities (like creating and deleting files, folders, etc) are accessible from Python code using [os module](https://docs.python.org/3/library/os.html). This is a module we will include in our basic imports sectsion hereafter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we use `os.makedirs()` function to create the `Data` folder, under our `IT_For_Business_And_Finance_2019_20` class folder, where we will put all our data files. Function `os.path.exists()` returns `True` if the folder (or file) path it receives in input already exists, otherwise `False`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataFolderPath = \"../Data\"\n",
    "\n",
    "if not os.path.exists(dataFolderPath):\n",
    "    os.makedirs(dataFolderPath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the use of `..` syntax. The double dots `..` in file path Strings refers to _one directory above_ in the directory tree. Therefore, since the notebook you are reading is located in the `IT_For_Business_And_Finance_2019_20/Notebooks` folder, `../Data` points (and is thus equivalent) to `IT_For_Business_And_Finance_2019_20/Data`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Serialization Protocols in Python <a name=\"io_python\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When it comes to IO operations, Python is very flexible and offers several options. We'll review here two typical ways to transfer Python objects across machines:\n",
    "- [JSON](https://docs.python.org/3/library/json.html#module-json) module, which implements human-readable encoding and decoding of basic Python object hierarchies. It is mostly suitable for Python Lists and Dicts.\n",
    "- [Pickle](https://docs.python.org/3/library/pickle.html) module, which implements binary protocols for serializing and de-serializing a Python object structure. It convers a broad spectrum of Python data-structures.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1. JSON format: `json` module <a name=\"json\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[JSON](https://docs.python.org/3.7/tutorial/inputoutput.html#saving-structured-data-with-json) is the acronym for JavaScript Object Notation. It is a popular data interchange format. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `json` Python module can take Python hierarchies (like nested Lists with Dicts inside etc.), _serialize_ them as `.json` files (that is, convert to String representations) and then _deserialize_ them (that is, reconstruct back the original Python object)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pros:\n",
    "- JSON format is the standard to send data over a network connection.\n",
    "- `.json` files are, in general, human-readable.\n",
    "\n",
    "Cons: \n",
    "- not all Python objects are serializable using `json` (e.g. NumPy arrays cannot be serialized in this way)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make an example. We want to save the `refData` Dict of Python Lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'S&P Rating': ['A', 'BB', 'AA', 'CCC'],\n",
       " 'Spread': [100, 300, 70, 700],\n",
       " 'Country': ['USA', 'ITA', 'UK', 'ITA']}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refData = {\n",
    "    'S&P Rating': ['A', 'BB', 'AA', 'CCC'],\n",
    "    'Spread': [100, 300, 70, 700],\n",
    "    'Country': ['USA', 'ITA', 'UK', 'ITA']\n",
    "}\n",
    "\n",
    "refData"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First-of all we import the `json` module"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We define the complete file path using the `os.path.join()` function, which concatenates the `dataFolderPath` to `Data` folder, together with `\"refData.json\"`, which is going to be the name of the `.json` file containing the serialized `refData` object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"refData.json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create and open a new file `filePath`, we use [`open(filename, mode)` function](https://docs.python.org/3/tutorial/inputoutput.html#reading-and-writing-files), giving it the complete path `filePath` to the file to open and mode `'w'` to open it in write-mode. Function `open()` returns a [file-object](https://docs.python.org/3/glossary.html#term-file-object) (which mediates the between IO operations and the underlying resource). We capture it in the `file` variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 0 ns\n"
     ]
    }
   ],
   "source": [
    "with open(filePath, 'w') as file:\n",
    "    %time json.dump(refData, file, indent=\"\\t\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here and alsewhere we use the syntax\n",
    "\n",
    "```python\n",
    "%time statement\n",
    "```\n",
    "to execute a statement and measure its execution time (Wall time) with the [`%time` magic function](https://ipython.readthedocs.io/en/stable/interactive/magics.html#magic-time)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Function \n",
    "\n",
    "````python\n",
    "json.dump(obj, file_object[, indent])\n",
    "```\n",
    "\n",
    "takes the `refData` object and serializes it as a text file, using the `file_object` file object. The optional argument `indent` is used to pretty-print nested levels of the `refData` object. Here we have used the `\"\\t\"` character so that nested levels are distantiated of one tab. Take a look at `refData.json` file in `Data` folder... you can actually read it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the use of the [`with` statement](https://www.geeksforgeeks.org/with-statement-in-python/) which:\n",
    "- manages the opening of the file `filePath`, calling `open()` function, \n",
    "- assign the file-object to the `file` variable, through the `as` keyword,\n",
    "- manages the closing of the file after the end of the indented block"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have serialized the `refData` object as the `refData.json` file, we can assess whether the file-object is effectively now closed using the `.closed` attribute of the `file` file-object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file.closed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now reload the serialized object and retrieve the original `refData` object. Same opening through `open()`, but now in reading-mode, using mode `'r'`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 0 ns\n"
     ]
    }
   ],
   "source": [
    "with open(filePath, 'r') as file:\n",
    "    %time refData_reloaded = json.load(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The deserialization (from text file to Python object) is managed by function\n",
    "\n",
    "```python\n",
    "json.load(file_object)\n",
    "```\n",
    "\n",
    "which loads the contents of the file referred by `file_object` and convert them into a Python object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'S&P Rating': ['A', 'BB', 'AA', 'CCC'],\n",
       " 'Spread': [100, 300, 70, 700],\n",
       " 'Country': ['USA', 'ITA', 'UK', 'ITA']}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refData_reloaded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have finished our IO operation, we can delete our `refData.json` file. We define a utility function to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def removeFile(fileName):\n",
    "    \"\"\"\n",
    "    removeFile(fileName) function remove file 'fileName', if it exists. It also prints on screen a success/failure message.\n",
    "    \n",
    "    Parameters:\n",
    "        fileName (str): name of the file ('Data' folder is assumed)\n",
    "        \n",
    "    Returns:\n",
    "        None\n",
    "    \"\"\"\n",
    "\n",
    "    if os.path.isfile(os.path.join(dataFolderPath, fileName)):\n",
    "        os.remove(os.path.join(dataFolderPath, fileName))\n",
    "\n",
    "        # double-check if file still exists\n",
    "        fileStillExists = os.path.isfile(os.path.join(dataFolderPath, fileName))\n",
    "\n",
    "        if fileStillExists:\n",
    "            print(\"Failure: file {} still exists...\".format(fileName))\n",
    "        else:\n",
    "            print(\"Success: file {} successfully removed!\".format(fileName))\n",
    "            \n",
    "    else:\n",
    "        print(\"File {} already removed.\".format(fileName))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the use of `os`'s functions:\n",
    "- `os.path.isfile()` which returns `True` if the file in input exists and `False`, otherwise;\n",
    "- `os.remove()` which removes the file in input."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\refData.json successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take a look in `Data` folder to see that effectively `refData.json` file is not there anymore..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unfortunately, not all object that you work with in Python are serializable (and thus, transferrable) using the JSON format. A counter-example? NumPy arrays..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# unserializableFilePath = os.path.join(dataFolderPath, \"dummyArray.json\")\n",
    "#\n",
    "# with open(unserializableFilePath, 'w') as file:\n",
    "#    \n",
    "#    # raises a TypeError: Object of type ndarray is not JSON serializable\n",
    "#    %time json.dump(np.array([1,2,3]), file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2. `pickle` module <a name=\"pickle\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Contrary to JSON, pickle is a protocol which allows the serialization of arbitrarily complex Python objects. In Python, it is implemented in the `pickle` module."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pros:\n",
    "- Pickle works with arbitrary Python obkects (NumPy array and Pandas Series/DataFrames too).\n",
    "\n",
    "Cons: \n",
    "- Pickle format is not cross-platform. That is, a file serialized on a Mac OS might be impossible to de-serialize on a Windows machine (and viceversa).\n",
    "- `.pkl` files are not human-readable.\n",
    "\n",
    "In real life, especially if you have to pass data across different machines, don't use Pickle."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make an example. We want to save the `mat` NumPy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = int(1e6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "mat = np.array([[i*k for i in range(1,rows+1)] for k in range(1,6)]).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[      1,       2,       3,       4,       5],\n",
       "       [      2,       4,       6,       8,      10],\n",
       "       [      3,       6,       9,      12,      15],\n",
       "       ...,\n",
       "       [ 999998, 1999996, 2999994, 3999992, 4999990],\n",
       "       [ 999999, 1999998, 2999997, 3999996, 4999995],\n",
       "       [1000000, 2000000, 3000000, 4000000, 5000000]])"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000000, 5)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int32')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First-of all we import the `pickle` module"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 35.9 ms\n"
     ]
    }
   ],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"mat.pkl\")\n",
    "\n",
    "with open(filePath, 'wb') as file:\n",
    "    %time pickle.dump(mat, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the use of `'wb'` mode when opening the file to store `mat` array. It's going to be a binary file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Function \n",
    "\n",
    "````python\n",
    "pickle.dump(obj, file_object)\n",
    "```\n",
    "\n",
    "takes the `mat` object and serializes it as a binary file, using the `file_object` file object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file.closed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now reload it, using the `'rb'` mode to read the binary file `\"mat.pkl\"`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 26.9 ms\n"
     ]
    }
   ],
   "source": [
    "with open(filePath, 'rb') as file:\n",
    "    %time mat_reloaded = pickle.load(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file.closed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[      1,       2,       3,       4,       5],\n",
       "       [      2,       4,       6,       8,      10],\n",
       "       [      3,       6,       9,      12,      15],\n",
       "       ...,\n",
       "       [ 999998, 1999996, 2999994, 3999992, 4999990],\n",
       "       [ 999999, 1999998, 2999997, 3999996, 4999995],\n",
       "       [1000000, 2000000, 3000000, 4000000, 5000000]])"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat_reloaded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's clean-up Data folder..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\mat.pkl successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In case you have several object that you want to keep together in a unique file, wrap them in a Python Dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "mat_dict = {'mat': mat,\n",
    "            'mat_squared': mat**2}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[      1,       2,       3,       4,       5],\n",
       "       [      2,       4,       6,       8,      10],\n",
       "       [      3,       6,       9,      12,      15],\n",
       "       ...,\n",
       "       [ 999998, 1999996, 2999994, 3999992, 4999990],\n",
       "       [ 999999, 1999998, 2999997, 3999996, 4999995],\n",
       "       [1000000, 2000000, 3000000, 4000000, 5000000]])"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat_dict['mat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[          1,           4,           9,          16,          25],\n",
       "       [          4,          16,          36,          64,         100],\n",
       "       [          9,          36,          81,         144,         225],\n",
       "       ...,\n",
       "       [ -731379964,  1369447440,  2007514916,  1182822464, -1104629916],\n",
       "       [ -729379967,  1377447428,  2025514889,  1214822416, -1054629991],\n",
       "       [ -727379968,  1385447424,  2043514880,  1246822400, -1004630016]],\n",
       "      dtype=int32)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat_dict['mat_squared']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"mat_dict.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 64.8 ms\n"
     ]
    }
   ],
   "source": [
    "with open(filePath, 'wb') as file:\n",
    "    %time pickle.dump(mat_dict, file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 54.9 ms\n"
     ]
    }
   ],
   "source": [
    "with open(filePath, 'rb') as file:\n",
    "    %time mat_dict_reloaded = pickle.load(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[      1,       2,       3,       4,       5],\n",
       "       [      2,       4,       6,       8,      10],\n",
       "       [      3,       6,       9,      12,      15],\n",
       "       ...,\n",
       "       [ 999998, 1999996, 2999994, 3999992, 4999990],\n",
       "       [ 999999, 1999998, 2999997, 3999996, 4999995],\n",
       "       [1000000, 2000000, 3000000, 4000000, 5000000]])"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat_dict_reloaded['mat']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[          1,           4,           9,          16,          25],\n",
       "       [          4,          16,          36,          64,         100],\n",
       "       [          9,          36,          81,         144,         225],\n",
       "       ...,\n",
       "       [ -731379964,  1369447440,  2007514916,  1182822464, -1104629916],\n",
       "       [ -729379967,  1377447428,  2025514889,  1214822416, -1054629991],\n",
       "       [ -727379968,  1385447424,  2043514880,  1246822400, -1004630016]])"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mat_dict_reloaded['mat_squared']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\mat_dict.pkl successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. IO with Pandas <a name=\"io_pandas\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas supports IO operations from/to many file formats. As a rule of thumb:\n",
    "\n",
    "- to import data into Pandas, you can use `read_*` functions (like `pd.read_sql()`, `pd.read_csv()`, `pd.read_excel()`, etc.);\n",
    "- to export data from Pandas, you can use `to_*` methods of Pandas DataFrames (like for a `df` DataFrame, `df.to_sql()`, `df.to_csv()`, `df.to_excel()`, etc.);\n",
    "\n",
    "Here we'll review some of the most common file formats:\n",
    "\n",
    "- [SQL](https://en.wikipedia.org/wiki/Structured_Query_Language), using the `sqlite3` module;\n",
    "- [CSV](https://en.wikipedia.org/wiki/Comma-separated_values)\n",
    "- [Excel](https://en.wikipedia.org/wiki/Microsoft_Excel)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.1. SQL <a name=\"sql\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In a nutshell, SQL stands for Structured Query Language and it is a domain-specific language to manage data held in Relational Databases. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll first follow a step-by-step approach, typing real SQL queries and executing them using the `sqlite3` module. Then we'll explain the real-life approach using `df.to_sql()` method and `pd.read_sql()` function."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1.1. SQL queries from Python: `sqlite3` module <a name=\"sqlite3\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[SQLite](https://sqlite.org/index.html) is a library (written in [C programming language](https://en.wikipedia.org/wiki/C_(programming_language))) that implements a SQL database engine. The built-in [module `sqlite3`](https://wiki.python.org/moin/SQLite) implements the interface between Python and SQLite, such that you can define SQL tables using Python code and store Pandas DataFrames into them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's suppose we want to store in a SQL table our reference data DataFrame `df_refData`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_2</td>\n",
       "      <td>BB</td>\n",
       "      <td>300</td>\n",
       "      <td>ITA</td>\n",
       "      <td>45.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_4</td>\n",
       "      <td>CCC</td>\n",
       "      <td>700</td>\n",
       "      <td>ITA</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_2         BB     300     ITA       45.00\n",
       "Firm_3         AA      70      UK      161.25\n",
       "Firm_4        CCC     700     ITA        5.00"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_refData = pd.DataFrame(data={\n",
    "                             'S&P Rating': ['A', 'BB', 'AA', 'CCC'],\n",
    "                             'Spread': [100, 300, 70, 700],\n",
    "                             'Country': ['USA', 'ITA', 'UK', 'ITA'],\n",
    "                             'Market Cap': [430.0, 45.0, 161.25, 5.00]\n",
    "                              },\n",
    "                          index=['Firm_1', 'Firm_2', 'Firm_3', 'Firm_4'])\n",
    "\n",
    "df_refData"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First of all we import `sqlite3`, giving it the alias name `sq3` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 as sq3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we have to open a _connection_ to the SQL engine, which creates an empty `\"refData.db\"` file in our `Data` folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"refData.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sq3.connect(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `sq3.connect()` returns a connector `con` that manages the interaction between Python code and SQLite engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sqlite3.Connection"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now write the SQL query to create the table `refData`, as a Python String named `query`. For details on SQL syntax, there is a good [SQLite tutorial](https://www.sqlitetutorial.net/). Knowledge of SQL is not required to pass this class, but understanding at least basic syntax might be very usefull in your daily working life. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE refData (\n",
      "                Firms TEXT NOT NULL,\n",
      "                SnP_Rating TEXT,\n",
      "                Spread INT,\n",
      "                Country TEXT,\n",
      "                Market_Cap REAL\n",
      ")\n"
     ]
    }
   ],
   "source": [
    "query = \"\"\"CREATE TABLE refData (\n",
    "                Firms TEXT NOT NULL,\n",
    "                SnP_Rating TEXT,\n",
    "                Spread INT,\n",
    "                Country TEXT,\n",
    "                Market_Cap REAL\n",
    ")\"\"\"\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, the meaning of the SQL query which uses the [`CREATE TABLE` statement](https://www.sqlitetutorial.net/sqlite-create-table/) and typed as the Python String `query` is that of just creating an empty table `refData` of five columns:\n",
    "- `Firms`: a column of text strings (`TEXT`) constrained to store non-missing values (`NOT NULL`). We will store here the index of `df_refData`;\n",
    "- `SnP_Rating`: column of text strings (`TEXT`) that will store the `'S&P Rating'` column;\n",
    "- `Spread`: column of integer numbers (`INT`) that will store the `'Spread'` column;\n",
    "- `Country`: column of text strings (`TEXT`) that will store the `'Country'` column;\n",
    "- `Market_Cap`: column of float numbers (`REAL`) that will store the `'Market Cap'` column;\n",
    "\n",
    "For those who are not familiar with SQL data-types, notice that `TEXT`, `INT` and `REAL` are the SQLite analogous of Python `str`, `int` and `float` data-types, respectively. For details, see section [SQLite Data Types](https://www.sqlitetutorial.net/sqlite-data-types/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now execute the query using the `.execute()` method of the connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x18eb1671960>"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.execute(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using the `.commit()` method, we actually implement the changes due to the run of the `query` to the `\"refData.db\"` file.\n",
    "\n",
    "**TAKE-HOME MESSAGE**: always `.commit()` after `.execute()`, otherwise no changes will be made to the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can actually open the `\"refData.db\"` using [DB Browser for SQLite](https://sqlitebrowser.org/) and there, under 'Browse Data' tab, you'll see an empty table `refData` of five columns and SQLite data-types as above."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can store `df_refData` into `refData` table row-by-row, using the [`.iterrows()` method](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.iterrows.html) of a Pandas DataFrame, which in a for loop returns the index and a Pandas Series of the given row. Check it out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index: \n",
      "Firm_1 \n",
      "\n",
      "Row: \n",
      "S&P Rating      A\n",
      "Spread        100\n",
      "Country       USA\n",
      "Market Cap    430\n",
      "Name: Firm_1, dtype: object \n",
      "\n",
      "\n",
      "Index: \n",
      "Firm_2 \n",
      "\n",
      "Row: \n",
      "S&P Rating     BB\n",
      "Spread        300\n",
      "Country       ITA\n",
      "Market Cap     45\n",
      "Name: Firm_2, dtype: object \n",
      "\n",
      "\n",
      "Index: \n",
      "Firm_3 \n",
      "\n",
      "Row: \n",
      "S&P Rating        AA\n",
      "Spread            70\n",
      "Country           UK\n",
      "Market Cap    161.25\n",
      "Name: Firm_3, dtype: object \n",
      "\n",
      "\n",
      "Index: \n",
      "Firm_4 \n",
      "\n",
      "Row: \n",
      "S&P Rating    CCC\n",
      "Spread        700\n",
      "Country       ITA\n",
      "Market Cap      5\n",
      "Name: Firm_4, dtype: object \n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for index, row in df_refData.iterrows():\n",
    "    print(\"Index: \\n{} \\n\\nRow: \\n{} \\n\\n\".format(index, row))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looping over `df_refData`'s rows, let's now store each row as a row in `refData` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INSERT INTO refData VALUES ('Firm_1', 'A', 100, 'USA', 430.0)\n",
      "INSERT INTO refData VALUES ('Firm_2', 'BB', 300, 'ITA', 45.0)\n",
      "INSERT INTO refData VALUES ('Firm_3', 'AA', 70, 'UK', 161.25)\n",
      "INSERT INTO refData VALUES ('Firm_4', 'CCC', 700, 'ITA', 5.0)\n"
     ]
    }
   ],
   "source": [
    "for index, row in df_refData.iterrows():\n",
    "    query = \"INSERT INTO refData VALUES ('{}', '{}', {}, '{}', {})\".\\\n",
    "    format(index, row['S&P Rating'], row['Spread'], row[\"Country\"], row[\"Market Cap\"])\n",
    "    \n",
    "    print(query)\n",
    "    con.execute(query)\n",
    "\n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `INSERT INTO refData VALUES...` query, using the [`INSERT INTO` statement](https://www.sqlitetutorial.net/sqlite-insert/), is responsible to store the values of each row. We `.execute()` one query per row, replacing the `{}` brackets in the string declaration with the appropriate values of each column of the given `row`, thanks to the `.format()` String method. Notice the use of the `''` to write in SQL TEXT values, like `'Firm_1'`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When all the `df_refData.iterrows()` loop is over, we then `.commit()` the changes all together. Check it out with DB Browser. Of course we could have `.commit()` after the `.execute()` of every row addition, but this would have been inefficient (and I discourage you to do that), since the `.commit()` is, in general, an time-consuming operation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have stored values into the `refData` table, we can retrieve them using the standard `SELECT *` query. Rember always to `.commit()` the changes after you have `.execute()` your query, otherwise the selection of data won't be effective."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x18eb633a2d0>"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM refData\"\n",
    "cursor = con.execute(query)\n",
    "con.commit()\n",
    "cursor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `SELECT * FROM refData` query is an SQL query to using the [`SELECT` statement](https://www.sqlitetutorial.net/sqlite-select/) to query all (`*`) the contents `FROM` the `refData` table. We capture the output of `.execute()` selection as a `cursor`, which we can use to then fetch the selected data, using the `.fetchall()` method of the `cursor`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Firm_1', 'A', 100, 'USA', 430.0),\n",
       " ('Firm_2', 'BB', 300, 'ITA', 45.0),\n",
       " ('Firm_3', 'AA', 70, 'UK', 161.25),\n",
       " ('Firm_4', 'CCC', 700, 'ITA', 5.0)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data=cursor.fetchall()\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As expected, the values are there, but the `data` returned are not the original `df_refData` Pandas DataFrame, but in the form of a Python List. With some List comprehension effort, we can reconstruct our original DataFrame... but, as you could imagine, there is a much more efficient method to do all this IO operation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_2</td>\n",
       "      <td>BB</td>\n",
       "      <td>300</td>\n",
       "      <td>ITA</td>\n",
       "      <td>45.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_4</td>\n",
       "      <td>CCC</td>\n",
       "      <td>700</td>\n",
       "      <td>ITA</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_2         BB     300     ITA       45.00\n",
       "Firm_3         AA      70      UK      161.25\n",
       "Firm_4        CCC     700     ITA        5.00"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_refData_reloaded = pd.DataFrame(data=[t[1:] for t in data],\n",
    "                                   index=[t[0] for t in data],\n",
    "                                   columns=['S&P Rating', 'Spread', 'Country', 'Market Cap'])\n",
    "\n",
    "df_refData_reloaded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course you can make conditional selections using the [`WHERE` SQL statement](https://www.sqlitetutorial.net/sqlite-where/). Here, we select rows corresponding to Firms having a `Market_Cap` above 100 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Firm_1', 'A', 100, 'USA', 430.0), ('Firm_3', 'AA', 70, 'UK', 161.25)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM refData WHERE Market_Cap > 100\"\n",
    "cursor = con.execute(query)\n",
    "con.commit()\n",
    "blueChips_data=cursor.fetchall()\n",
    "blueChips_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the next section we'll see that Pandas provides much more efficient methods to save and retrieve data with and SQL engine."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you stop working with a SQL engine, close the connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We clean-up our `Data` folder deleting the `\"refData.db\"` file (if still open, shut down DB Browser otherwise you won't be able to remove the file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\refData.db successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1.2. Pandas and SQL: `.to_sql()` and `pd.read_sql()` <a name=\"pd_sql\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thanks to the `.to_sql()` DataFrame's method and the function `.read_sql()`, Pandas allows us to replace all the\n",
    "\n",
    "- `refData` table creation (`.execute()` of `CREATE TABLE refData...` query);\n",
    "- the (highly infficient) row-by-row data insertion (`.execute()` of `INSERT INTO refData VALUES...` query);\n",
    "- all the `.commit()` to make the changes to `refData` table effective\n",
    "\n",
    "with a call to `df_refData.to_sql()` method, and\n",
    "\n",
    "- the selection of the data (`.execute()` of `SELECT * FROM refData` and `.fetchall()` selected data);\n",
    "- as well conditional selection of the data (`.execute()` of `SELECT * FROM refData WHERE Market_Cap > 100` and `.fetchall()` selected data)\n",
    "- all the `.commit()` to make the selection query effective\n",
    "\n",
    "with a call to `pd.read_sql()` function."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All we have to do, at the very beginning, is opening a database connection. Let's quicly do it as we have already seen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sqlite3.Connection"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3 as sq3\n",
    "\n",
    "filePath = os.path.join(dataFolderPath, \"refData.db\")\n",
    "con = sq3.connect(filePath)\n",
    "type(con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's store the `df_refData` DataFrame into the newly created SQL table `refData` (physically saved into the `\"refData.db\"` file. You can inspected the newly created table opening the `\"refData.db\"` with DB Browser."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To do this we use the [`.to_sql()` method](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html) whose essential syntax is\n",
    "\n",
    "```python\n",
    "DataFrame.to_sql(name, con[, index_label])\n",
    "```\n",
    "\n",
    "This method saves the content of the `DataFrame` into a table using parameter:\n",
    "- `name` is going to be the name of the table;\n",
    "- `con` has to be an already opened database connection;\n",
    "\n",
    "Moreover, `DataFrame`'s index is going to be stored as an additional column into the db (the left-most column) and the parameter\n",
    "\n",
    "- `index_label` is an optional parameter which specifies the label of the index column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\gabri\\Anaconda3\\envs\\ITForBusAndFin2020_env\\lib\\site-packages\\pandas\\core\\generic.py:2712: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  method=method,\n"
     ]
    }
   ],
   "source": [
    "df_refData.to_sql(name=\"refData\", con=con, index_label=\"Firms\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now retrieve all the data stored in the `refData` table as the `df_refData_reloaded` DataFrame, with the idea of reconstructing a reloaded version of the original `df_refData` DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To do this we use the [`pd.read_sql()` function](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html#pandas.read_sql) whose essential syntax is\n",
    "\n",
    "```python\n",
    "pd.read_sql(sql, con[, index_col])\n",
    "```\n",
    "\n",
    "which saves the content of the `DataFrame` into a table using parameter:\n",
    "- `sql` is a SQL query to be executed;\n",
    "- `con` has to be an already opened database connection;\n",
    "\n",
    "Moreover, we can specify the column of the db to be interpreted as index in the reconstructed DataFrame and\n",
    "\n",
    "- `index_col` is a parameter which specifies the name of the column to be used as index of the reconstructed DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Firms</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_2</td>\n",
       "      <td>BB</td>\n",
       "      <td>300</td>\n",
       "      <td>ITA</td>\n",
       "      <td>45.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_4</td>\n",
       "      <td>CCC</td>\n",
       "      <td>700</td>\n",
       "      <td>ITA</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firms                                        \n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_2         BB     300     ITA       45.00\n",
       "Firm_3         AA      70      UK      161.25\n",
       "Firm_4        CCC     700     ITA        5.00"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM refData\"\n",
    "\n",
    "df_refData_reloaded = pd.read_sql(sql=query, con=con, index_col=\"Firms\")\n",
    "\n",
    "df_refData_reloaded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To make conditional selections we can either specify a SQL query using the `WHERE` statement (notice the use of backticks ` `` ` to select a column with and empty space in the column name) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 4.99 ms\n"
     ]
    },
    {
     "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Firms</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firms                                        \n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_3         AA      70      UK      161.25"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM refData WHERE `Market Cap` > 100\"\n",
    "\n",
    "%time df_refData_blueChips = pd.read_sql(sql=query, con=con, index_col=\"Firms\")\n",
    "\n",
    "df_refData_blueChips"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or you can read in-memory the whole table as a reconstructed DataFrame and then using Pandas conditional row selection to retrieve your data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 5.99 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "query = \"SELECT * FROM refData\"\n",
    "\n",
    "df_refData_reloaded = pd.read_sql(sql=query, con=con, index_col=\"Firms\")\n",
    "\n",
    "df_refData_blueChips = df_refData_reloaded[df_refData_reloaded[\"Market Cap\"] > 100] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here and alsewhere we use the syntax\n",
    "\n",
    "```python\n",
    "%%time \n",
    "\n",
    "statement(s)_in_a_cell\n",
    "```\n",
    "to execute a all the statements in a cell and measure its execution time (Wall time) with the [`%%time` magic function](https://ipython.readthedocs.io/en/stable/interactive/magics.html#magic-time) in cell mode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Firms</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firms                                        \n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_3         AA      70      UK      161.25"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_refData_blueChips"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**TAKE-HOME MESSAGE**: in general, for small to medium-sized dbs, querying in-memory the whole DataFrame (using `SELECT *...` kind of query) and then manipulating the reconstructed DataFrame to make conditional selections etc. is in general more efficient than running conditional SQL query on the db. The opposite is true when dimensions of the db are huge and/or the conditional selection involves multiple tables or operations which would be hardly replicated in Pandas: in this case is better to run a conditional SQL query (using for example the `SELECT ... WHERE...` kind of query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now close the connection and clean-up `Data` folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\refData.db successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.1.2.1. Parsing Dates: `pd.read_sql(..., parse_dates)` <a name=\"sql_dates\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make now an example of a DataFrame with Dates as index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>x</th>\n",
       "      <th>x^2</th>\n",
       "      <th>x^3</th>\n",
       "      <th>x^4</th>\n",
       "      <th>x^5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>81</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "      <td>256</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>125</td>\n",
       "      <td>625</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>216</td>\n",
       "      <td>1296</td>\n",
       "      <td>7776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>343</td>\n",
       "      <td>2401</td>\n",
       "      <td>16807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>512</td>\n",
       "      <td>4096</td>\n",
       "      <td>32768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>9</td>\n",
       "      <td>81</td>\n",
       "      <td>729</td>\n",
       "      <td>6561</td>\n",
       "      <td>59049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>10</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>10000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             x  x^2   x^3    x^4     x^5\n",
       "2020-01-01   1    1     1      1       1\n",
       "2020-01-02   2    4     8     16      32\n",
       "2020-01-03   3    9    27     81     243\n",
       "2020-01-06   4   16    64    256    1024\n",
       "2020-01-07   5   25   125    625    3125\n",
       "2020-01-08   6   36   216   1296    7776\n",
       "2020-01-09   7   49   343   2401   16807\n",
       "2020-01-10   8   64   512   4096   32768\n",
       "2020-01-13   9   81   729   6561   59049\n",
       "2020-01-14  10  100  1000  10000  100000"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(data=np.array([[i**k for i in range(1,11)] for k in range(1,6)]).T, \n",
    "                  index=pd.date_range('2020-01-01', periods=10, freq='B'), \n",
    "                  columns=['x', 'x^2', 'x^3', 'x^4', 'x^5'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-06',\n",
       "               '2020-01-07', '2020-01-08', '2020-01-09', '2020-01-10',\n",
       "               '2020-01-13', '2020-01-14'],\n",
       "              dtype='datetime64[ns]', freq='B')"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-01-01 00:00:00', freq='B')"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's open the db connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"df.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = sq3.connect(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and let's save `df` DataFrame as a `df` table in a `\"df.db\"` file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(name=\"df\", con=con, index_label=\"Dates\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see with DB Browser, the `\"Dates\"` column, is stored in the `df` table as a [`TIMESTAMP` column](https://www.sqlitetutorial.net/sqlite-date/). Let's what happen if we try to reconstruct the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>x</th>\n",
       "      <th>x^2</th>\n",
       "      <th>x^3</th>\n",
       "      <th>x^4</th>\n",
       "      <th>x^5</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dates</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02 00:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03 00:00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>81</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06 00:00:00</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "      <td>256</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07 00:00:00</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>125</td>\n",
       "      <td>625</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08 00:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>216</td>\n",
       "      <td>1296</td>\n",
       "      <td>7776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09 00:00:00</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>343</td>\n",
       "      <td>2401</td>\n",
       "      <td>16807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10 00:00:00</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>512</td>\n",
       "      <td>4096</td>\n",
       "      <td>32768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-13 00:00:00</td>\n",
       "      <td>9</td>\n",
       "      <td>81</td>\n",
       "      <td>729</td>\n",
       "      <td>6561</td>\n",
       "      <td>59049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-14 00:00:00</td>\n",
       "      <td>10</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>10000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      x  x^2   x^3    x^4     x^5\n",
       "Dates                                            \n",
       "2020-01-01 00:00:00   1    1     1      1       1\n",
       "2020-01-02 00:00:00   2    4     8     16      32\n",
       "2020-01-03 00:00:00   3    9    27     81     243\n",
       "2020-01-06 00:00:00   4   16    64    256    1024\n",
       "2020-01-07 00:00:00   5   25   125    625    3125\n",
       "2020-01-08 00:00:00   6   36   216   1296    7776\n",
       "2020-01-09 00:00:00   7   49   343   2401   16807\n",
       "2020-01-10 00:00:00   8   64   512   4096   32768\n",
       "2020-01-13 00:00:00   9   81   729   6561   59049\n",
       "2020-01-14 00:00:00  10  100  1000  10000  100000"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM df\"\n",
    "df_reloaded = pd.read_sql(sql=query, con=con, index_col=\"Dates\")\n",
    "df_reloaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['2020-01-01 00:00:00', '2020-01-02 00:00:00', '2020-01-03 00:00:00',\n",
       "       '2020-01-06 00:00:00', '2020-01-07 00:00:00', '2020-01-08 00:00:00',\n",
       "       '2020-01-09 00:00:00', '2020-01-10 00:00:00', '2020-01-13 00:00:00',\n",
       "       '2020-01-14 00:00:00'],\n",
       "      dtype='object', name='Dates')"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-01-01 00:00:00'"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df_reloaded.index[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, the contents of the `\"Dates\"` column in `df` column are interpreted as indexes in the `df_reloaded` DataFrame. Nevertheless, these index is not a DatetimeIndex, it is made of Strings...  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To parse SQL `TIMESTAMP` columns as dates you have to use the additional parameter of [`pd.read_sql()` function](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html#pandas.read_sql) \n",
    "```python\n",
    "pd.read_sql(..., parse_dates)\n",
    "```\n",
    "\n",
    "where the parameter `parse_dates` can be the name (or a List of names) of columns in the db to be parsed as dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>x</th>\n",
       "      <th>x^2</th>\n",
       "      <th>x^3</th>\n",
       "      <th>x^4</th>\n",
       "      <th>x^5</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dates</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>81</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "      <td>256</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>125</td>\n",
       "      <td>625</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>216</td>\n",
       "      <td>1296</td>\n",
       "      <td>7776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>343</td>\n",
       "      <td>2401</td>\n",
       "      <td>16807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>512</td>\n",
       "      <td>4096</td>\n",
       "      <td>32768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>9</td>\n",
       "      <td>81</td>\n",
       "      <td>729</td>\n",
       "      <td>6561</td>\n",
       "      <td>59049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>10</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>10000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             x  x^2   x^3    x^4     x^5\n",
       "Dates                                   \n",
       "2020-01-01   1    1     1      1       1\n",
       "2020-01-02   2    4     8     16      32\n",
       "2020-01-03   3    9    27     81     243\n",
       "2020-01-06   4   16    64    256    1024\n",
       "2020-01-07   5   25   125    625    3125\n",
       "2020-01-08   6   36   216   1296    7776\n",
       "2020-01-09   7   49   343   2401   16807\n",
       "2020-01-10   8   64   512   4096   32768\n",
       "2020-01-13   9   81   729   6561   59049\n",
       "2020-01-14  10  100  1000  10000  100000"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT * FROM df\"\n",
    "df_reloaded = pd.read_sql(sql=query, con=con, index_col=\"Dates\", parse_dates=\"Dates\")\n",
    "df_reloaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-06',\n",
       "               '2020-01-07', '2020-01-08', '2020-01-09', '2020-01-10',\n",
       "               '2020-01-13', '2020-01-14'],\n",
       "              dtype='datetime64[ns]', name='Dates', freq=None)"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-01-01 00:00:00')"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "cool. We have correctly reconstructed our original DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now close the connection and clean-up `Data` folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\df.db successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2. CSV: `.to_csv()` and `pd.read_csv()` <a name=\"pd_csv\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The [CSV format](https://en.wikipedia.org/wiki/Comma-separated_values) (for Comma Separated Values) is a delimited text file that uses a comma to separate values. Doing IO operations with this format is very simple, since Pandas has the built-in method `DataFrame.to_csv()` and the function `pd.read_csv()`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We work first with our reference data DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_2</td>\n",
       "      <td>BB</td>\n",
       "      <td>300</td>\n",
       "      <td>ITA</td>\n",
       "      <td>45.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_4</td>\n",
       "      <td>CCC</td>\n",
       "      <td>700</td>\n",
       "      <td>ITA</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_2         BB     300     ITA       45.00\n",
       "Firm_3         AA      70      UK      161.25\n",
       "Firm_4        CCC     700     ITA        5.00"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_refData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"df_refData.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To save `df_refData` as a `.csv` file we use the [`.to_csv()` method](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html) whose essential syntax is\n",
    "\n",
    "```python\n",
    "DataFrame.to_csv(path_or_buf)\n",
    "```\n",
    "\n",
    "where the `path_or_buf` parameter is as String representing the complete path of the `.csv` file we want to save."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 5.98 ms\n"
     ]
    }
   ],
   "source": [
    "%time df_refData.to_csv(path_or_buf = filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To reload in memory the `.csv` file and reconstruct the original DataFrame, we use the [`pd.read_csv()` function](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html#pandas.read_csv) whose essential syntax is\n",
    "\n",
    "```python\n",
    "pd.read_csv(filepath_or_buffer, index_col)\n",
    "```\n",
    "\n",
    "where\n",
    "\n",
    "- `filepath_or_buffer` parameter is as String representing the complete path of the `.csv` file we want to load.\n",
    "- `index_col` is an Integer parameter which specifies the column position (e.g. the `0` for the first) to be used as index of the reconstructed DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 11 ms\n"
     ]
    },
    {
     "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_2</td>\n",
       "      <td>BB</td>\n",
       "      <td>300</td>\n",
       "      <td>ITA</td>\n",
       "      <td>45.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_4</td>\n",
       "      <td>CCC</td>\n",
       "      <td>700</td>\n",
       "      <td>ITA</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_2         BB     300     ITA       45.00\n",
       "Firm_3         AA      70      UK      161.25\n",
       "Firm_4        CCC     700     ITA        5.00"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df_refData_reloaded = pd.read_csv(filepath_or_buffer = filePath, index_col = 0)\n",
    "df_refData_reloaded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now clean-up `Data` folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\df_refData.csv successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2.1. Parsing Dates: `pd.read_csv(..., parse_dates)` <a name=\"csv_dates\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As was the case for `pd.read_sql()`, in the case of a DataFrame having Dates as indexes, as `df` DataFrame. The parsing of the index column as an appropriate DatetimeIndex is not automatic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>x</th>\n",
       "      <th>x^2</th>\n",
       "      <th>x^3</th>\n",
       "      <th>x^4</th>\n",
       "      <th>x^5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>81</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "      <td>256</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>125</td>\n",
       "      <td>625</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>216</td>\n",
       "      <td>1296</td>\n",
       "      <td>7776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>343</td>\n",
       "      <td>2401</td>\n",
       "      <td>16807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>512</td>\n",
       "      <td>4096</td>\n",
       "      <td>32768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>9</td>\n",
       "      <td>81</td>\n",
       "      <td>729</td>\n",
       "      <td>6561</td>\n",
       "      <td>59049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>10</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>10000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             x  x^2   x^3    x^4     x^5\n",
       "2020-01-01   1    1     1      1       1\n",
       "2020-01-02   2    4     8     16      32\n",
       "2020-01-03   3    9    27     81     243\n",
       "2020-01-06   4   16    64    256    1024\n",
       "2020-01-07   5   25   125    625    3125\n",
       "2020-01-08   6   36   216   1296    7776\n",
       "2020-01-09   7   49   343   2401   16807\n",
       "2020-01-10   8   64   512   4096   32768\n",
       "2020-01-13   9   81   729   6561   59049\n",
       "2020-01-14  10  100  1000  10000  100000"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's save the `df` DataFrame in a `\"df.csv\"` file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 3.99 ms\n"
     ]
    }
   ],
   "source": [
    "%time df.to_csv(path_or_buf = filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and let's reload it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 11 ms\n"
     ]
    },
    {
     "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>x</th>\n",
       "      <th>x^2</th>\n",
       "      <th>x^3</th>\n",
       "      <th>x^4</th>\n",
       "      <th>x^5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>81</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "      <td>256</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>125</td>\n",
       "      <td>625</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>216</td>\n",
       "      <td>1296</td>\n",
       "      <td>7776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>343</td>\n",
       "      <td>2401</td>\n",
       "      <td>16807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>512</td>\n",
       "      <td>4096</td>\n",
       "      <td>32768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>9</td>\n",
       "      <td>81</td>\n",
       "      <td>729</td>\n",
       "      <td>6561</td>\n",
       "      <td>59049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>10</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>10000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             x  x^2   x^3    x^4     x^5\n",
       "2020-01-01   1    1     1      1       1\n",
       "2020-01-02   2    4     8     16      32\n",
       "2020-01-03   3    9    27     81     243\n",
       "2020-01-06   4   16    64    256    1024\n",
       "2020-01-07   5   25   125    625    3125\n",
       "2020-01-08   6   36   216   1296    7776\n",
       "2020-01-09   7   49   343   2401   16807\n",
       "2020-01-10   8   64   512   4096   32768\n",
       "2020-01-13   9   81   729   6561   59049\n",
       "2020-01-14  10  100  1000  10000  100000"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df_reloaded = pd.read_csv(filepath_or_buffer = filePath, index_col = 0)\n",
    "df_reloaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-06', '2020-01-07',\n",
       "       '2020-01-08', '2020-01-09', '2020-01-10', '2020-01-13', '2020-01-14'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-01-01'"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df_reloaded.index[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "again, the index is made of Strings, not Dates... In order to have an appropriate DatetimeIndex object as index of the reconstructed `df_reloaded` DataFrame, we have to use the optional parameter\n",
    "\n",
    "```python\n",
    "pd.read_csv(...[, parse_dates])\n",
    "```\n",
    "\n",
    "where the parameter `parse_dates` is a Bool value which, if `True`, make the values of the index of the reconstructed DataFrame parsed as Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 4.99 ms\n"
     ]
    }
   ],
   "source": [
    "%time df_reloaded = pd.read_csv(filepath_or_buffer = filePath, index_col = 0, parse_dates = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>x</th>\n",
       "      <th>x^2</th>\n",
       "      <th>x^3</th>\n",
       "      <th>x^4</th>\n",
       "      <th>x^5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>81</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "      <td>256</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>125</td>\n",
       "      <td>625</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>216</td>\n",
       "      <td>1296</td>\n",
       "      <td>7776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>343</td>\n",
       "      <td>2401</td>\n",
       "      <td>16807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>512</td>\n",
       "      <td>4096</td>\n",
       "      <td>32768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>9</td>\n",
       "      <td>81</td>\n",
       "      <td>729</td>\n",
       "      <td>6561</td>\n",
       "      <td>59049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>10</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>10000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             x  x^2   x^3    x^4     x^5\n",
       "2020-01-01   1    1     1      1       1\n",
       "2020-01-02   2    4     8     16      32\n",
       "2020-01-03   3    9    27     81     243\n",
       "2020-01-06   4   16    64    256    1024\n",
       "2020-01-07   5   25   125    625    3125\n",
       "2020-01-08   6   36   216   1296    7776\n",
       "2020-01-09   7   49   343   2401   16807\n",
       "2020-01-10   8   64   512   4096   32768\n",
       "2020-01-13   9   81   729   6561   59049\n",
       "2020-01-14  10  100  1000  10000  100000"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-06',\n",
       "               '2020-01-07', '2020-01-08', '2020-01-09', '2020-01-10',\n",
       "               '2020-01-13', '2020-01-14'],\n",
       "              dtype='datetime64[ns]', freq=None)"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-01-01 00:00:00')"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "cool. That's what we wanted! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now clean-up `Data` folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\df.csv successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.3. Excel: `.to_excel()` and `df.read_excel()` <a name=\"excel\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using Pandas, IO operations with Excel spreadsheet are mediated by `.to_excel()` DataFrame method and `pd.read_excel()` function. Let's redo the same steps we have done in the CSV section, but saving our `df_refData` DataFrame as a `.xlsx` spreadsheet file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_2</td>\n",
       "      <td>BB</td>\n",
       "      <td>300</td>\n",
       "      <td>ITA</td>\n",
       "      <td>45.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_4</td>\n",
       "      <td>CCC</td>\n",
       "      <td>700</td>\n",
       "      <td>ITA</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_2         BB     300     ITA       45.00\n",
       "Firm_3         AA      70      UK      161.25\n",
       "Firm_4        CCC     700     ITA        5.00"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_refData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"df_refData.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To save `df_refData` as a `.xlsx` file we use the [`.to_excel()` method](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html) whose essential syntax is\n",
    "\n",
    "```python\n",
    "DataFrame.to_excel(excel_writer[, sheet_name])\n",
    "```\n",
    "\n",
    "where the \n",
    "\n",
    "- `excel_writer` parameter is as String representing the complete path of the `.xlsx` file we want to save;\n",
    "- `sheet_name` is an optional String parameter (default `\"Sheet1\"`) representing the sheet name where our DataFrame will be stored."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 129 ms\n"
     ]
    }
   ],
   "source": [
    "%time df_refData.to_excel(excel_writer = filePath, sheet_name = \"Reference_Data_Table\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To reload in memory the `.xlsx` file and reconstruct the original DataFrame, we use the [`pd.read_excel()` function](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html#pandas.read_excel) whose essential syntax is\n",
    "\n",
    "```python\n",
    "pd.read_excel(io, index_col[, sheet_name])\n",
    "```\n",
    "\n",
    "where\n",
    "\n",
    "- `io` parameter is as String representing the complete path of the `.csv` file we want to load.\n",
    "- `index_col` is an Integer parameter which specifies the column position (e.g. the `0` for the first) to be used as index of the reconstructed DataFrame;\n",
    "- `sheet_name` is an optional Integer or String parameter (default 0 to refer to the first sheet) representing the sheet position (if Integer) or the sheet name (if String) from where our DataFrame will be loaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 68.8 ms\n"
     ]
    },
    {
     "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>S&amp;P Rating</th>\n",
       "      <th>Spread</th>\n",
       "      <th>Country</th>\n",
       "      <th>Market Cap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Firm_1</td>\n",
       "      <td>A</td>\n",
       "      <td>100</td>\n",
       "      <td>USA</td>\n",
       "      <td>430.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_2</td>\n",
       "      <td>BB</td>\n",
       "      <td>300</td>\n",
       "      <td>ITA</td>\n",
       "      <td>45.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_3</td>\n",
       "      <td>AA</td>\n",
       "      <td>70</td>\n",
       "      <td>UK</td>\n",
       "      <td>161.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Firm_4</td>\n",
       "      <td>CCC</td>\n",
       "      <td>700</td>\n",
       "      <td>ITA</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       S&P Rating  Spread Country  Market Cap\n",
       "Firm_1          A     100     USA      430.00\n",
       "Firm_2         BB     300     ITA       45.00\n",
       "Firm_3         AA      70      UK      161.25\n",
       "Firm_4        CCC     700     ITA        5.00"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df_refData_reloaded = pd.read_excel(io = filePath, index_col = 0, sheet_name = \"Reference_Data_Table\")\n",
    "df_refData_reloaded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now clean-up `Data` folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\df_refData.xlsx successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3.1. Parsing Dates: it's automatic <a name=\"excel_dates\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Well, all the extra work you have to do when you do IO operations in case of Dates indexes is... nothing! Let's consider as usual example our `df` DataFrame "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "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>x</th>\n",
       "      <th>x^2</th>\n",
       "      <th>x^3</th>\n",
       "      <th>x^4</th>\n",
       "      <th>x^5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>81</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "      <td>256</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>125</td>\n",
       "      <td>625</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>216</td>\n",
       "      <td>1296</td>\n",
       "      <td>7776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>343</td>\n",
       "      <td>2401</td>\n",
       "      <td>16807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>512</td>\n",
       "      <td>4096</td>\n",
       "      <td>32768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>9</td>\n",
       "      <td>81</td>\n",
       "      <td>729</td>\n",
       "      <td>6561</td>\n",
       "      <td>59049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>10</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>10000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             x  x^2   x^3    x^4     x^5\n",
       "2020-01-01   1    1     1      1       1\n",
       "2020-01-02   2    4     8     16      32\n",
       "2020-01-03   3    9    27     81     243\n",
       "2020-01-06   4   16    64    256    1024\n",
       "2020-01-07   5   25   125    625    3125\n",
       "2020-01-08   6   36   216   1296    7776\n",
       "2020-01-09   7   49   343   2401   16807\n",
       "2020-01-10   8   64   512   4096   32768\n",
       "2020-01-13   9   81   729   6561   59049\n",
       "2020-01-14  10  100  1000  10000  100000"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "filePath = os.path.join(dataFolderPath, \"df.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 65.8 ms\n"
     ]
    }
   ],
   "source": [
    "%time df.to_excel(excel_writer = filePath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 15 ms\n"
     ]
    },
    {
     "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>x</th>\n",
       "      <th>x^2</th>\n",
       "      <th>x^3</th>\n",
       "      <th>x^4</th>\n",
       "      <th>x^5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>81</td>\n",
       "      <td>243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>64</td>\n",
       "      <td>256</td>\n",
       "      <td>1024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>125</td>\n",
       "      <td>625</td>\n",
       "      <td>3125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>6</td>\n",
       "      <td>36</td>\n",
       "      <td>216</td>\n",
       "      <td>1296</td>\n",
       "      <td>7776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>7</td>\n",
       "      <td>49</td>\n",
       "      <td>343</td>\n",
       "      <td>2401</td>\n",
       "      <td>16807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>8</td>\n",
       "      <td>64</td>\n",
       "      <td>512</td>\n",
       "      <td>4096</td>\n",
       "      <td>32768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-13</td>\n",
       "      <td>9</td>\n",
       "      <td>81</td>\n",
       "      <td>729</td>\n",
       "      <td>6561</td>\n",
       "      <td>59049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-14</td>\n",
       "      <td>10</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>10000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             x  x^2   x^3    x^4     x^5\n",
       "2020-01-01   1    1     1      1       1\n",
       "2020-01-02   2    4     8     16      32\n",
       "2020-01-03   3    9    27     81     243\n",
       "2020-01-06   4   16    64    256    1024\n",
       "2020-01-07   5   25   125    625    3125\n",
       "2020-01-08   6   36   216   1296    7776\n",
       "2020-01-09   7   49   343   2401   16807\n",
       "2020-01-10   8   64   512   4096   32768\n",
       "2020-01-13   9   81   729   6561   59049\n",
       "2020-01-14  10  100  1000  10000  100000"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time df_reloaded = pd.read_excel(io = filePath, index_col = 0)\n",
    "df_reloaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-06',\n",
       "               '2020-01-07', '2020-01-08', '2020-01-09', '2020-01-10',\n",
       "               '2020-01-13', '2020-01-14'],\n",
       "              dtype='datetime64[ns]', freq=None)"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-01-01 00:00:00')"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_reloaded.index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "cool. The index values are automatically parsed as Dates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now clean-up `Data` folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: file ../Data\\df.xlsx successfully removed!\n"
     ]
    }
   ],
   "source": [
    "removeFile(filePath)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}