notebooks/Pandas_walkthrough.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas\n",
    "\n",
    "Copyright 2023 Marco A. Lopez-Sanchez.  \n",
    "Content under [Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0) License](http://creativecommons.org/licenses/by-nc-sa/4.0/).\n",
    "\n",
    "> **Goal**:  \n",
    "> Learn the basics of Pandas and give an overview of the library.\n",
    ">\n",
    "> **What it is?**  \n",
    ">A library for dealing with tabular data structures in Python. \n",
    ">\n",
    "> **What it is used for?**  \n",
    ">This is Python's default library for manipulating tabular-like data. Pandas adds two data structures to Python called Series and DataFrames, as well as all the expected functionality for dealing with tabular data structures. Here we will focus on DataFrames, a 2D tabular data structure with labelled axes (rows and columns) in which columns are of potentially different types. If you normally process your data using Excel-like applications, chances are that this will be your daily buddy in Python.\n",
    ">\n",
    "> **Resources**  \n",
    "> https://pandas.pydata.org/ (official website)  \n",
    "> https://pandas.pydata.org/docs/ (official documentation)  \n",
    "> [Python for Data Analysis](https://wesmckinney.com/book/preliminaries.html#navigating_book): A nice book focus on Pandas from the main author of Pandas Wes McKinney   \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing pandas following the standard\n",
    "import pandas as pd"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas Dataframe: what is it and how to create it?\n",
    "\n",
    "A Pandas DataFrame is a two-dimensonal data structure with rows (data observations) and labelled columns (variables). It is a mutable (i.e. modifiable) and potentially heterogeneous object so that each column can have similar or different data types (integers, floats, strings, etc) being the number of rows per column equal."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>state</th>\n",
       "      <th>year</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2000</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2001</td>\n",
       "      <td>1.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2002</td>\n",
       "      <td>3.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>2001</td>\n",
       "      <td>2.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>2002</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>2003</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    state  year  pop\n",
       "0    Ohio  2000  1.5\n",
       "1    Ohio  2001  1.7\n",
       "2    Ohio  2002  3.6\n",
       "3  Nevada  2001  2.4\n",
       "4  Nevada  2002  2.9\n",
       "5  Nevada  2003  3.2"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a dataframe from a Python dict\n",
    "# (example taken from https://wesmckinney.com/book/pandas-basics.html)\n",
    "data = {\n",
    "    \"state\": [\"Ohio\", \"Ohio\", \"Ohio\", \"Nevada\", \"Nevada\", \"Nevada\"],\n",
    "    \"year\": [2000, 2001, 2002, 2001, 2002, 2003],\n",
    "    \"pop\": [1.5, 1.7, 3.6, 2.4, 2.9, 3.2],\n",
    "}\n",
    "\n",
    "# create the DataFrame\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# show the data in the notebook\n",
    "df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the example above, the dictionary keys become the column labels and the lists associated with each key, the rows of the DataFrame. The numbers on the left (unnamed) column indicate the index of each row starting at zero.\n",
    "\n",
    "## Importing tabular data from files\n",
    "\n",
    "Although the way of creating DataFrames from Python dictionaries is useful for learning or showing examples, DataFrames are rarely created in this way in real situations. Most of the time you will have to work with data in tabular format that will be hosted on your hard disk or a server. Pandas provides many methods to handle almost any imaginable tabular file format into a DataFrame as well as methods to save this type of data in the desired format. The two most used functions are ``read_csv()`` and ``read_excel()``. The ``read_csv()`` method is used to read CSV or other text-based tabular files, while ``read_excel()`` is used to read Excel files. Both functions return a DataFrame object that can be used to manipulate and analyze data.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>primitive mantle</th>\n",
       "      <th>mean lower crust</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiO2</td>\n",
       "      <td>44.91</td>\n",
       "      <td>53.40</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TiO2</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Al2O3</td>\n",
       "      <td>4.44</td>\n",
       "      <td>16.90</td>\n",
       "      <td>15.54</td>\n",
       "      <td>16.95</td>\n",
       "      <td>3.24</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fe2O3t</td>\n",
       "      <td>8.05</td>\n",
       "      <td>8.57</td>\n",
       "      <td>11.06</td>\n",
       "      <td>13.93</td>\n",
       "      <td>6.12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MnO</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.14</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MgO</td>\n",
       "      <td>37.81</td>\n",
       "      <td>7.24</td>\n",
       "      <td>8.84</td>\n",
       "      <td>5.44</td>\n",
       "      <td>21.84</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CaO</td>\n",
       "      <td>3.54</td>\n",
       "      <td>9.59</td>\n",
       "      <td>12.31</td>\n",
       "      <td>10.43</td>\n",
       "      <td>14.87</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Na2O</td>\n",
       "      <td>0.36</td>\n",
       "      <td>2.65</td>\n",
       "      <td>1.89</td>\n",
       "      <td>1.66</td>\n",
       "      <td>0.33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>K2O</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.61</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.05</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>P2O5</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>LOI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.62</td>\n",
       "      <td>1.59</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  primitive mantle  mean lower crust  CO_eclogite  CO_granulite  \\\n",
       "0        SiO2             44.91             53.40        48.44         48.41   \n",
       "1        TiO2              0.16              0.82         1.06          1.19   \n",
       "2       Al2O3              4.44             16.90        15.54         16.95   \n",
       "3      Fe2O3t              8.05              8.57        11.06         13.93   \n",
       "4         MnO              0.13              0.10         0.18          0.25   \n",
       "5         MgO             37.81              7.24         8.84          5.44   \n",
       "6         CaO              3.54              9.59        12.31         10.43   \n",
       "7        Na2O              0.36              2.65         1.89          1.66   \n",
       "8         K2O              0.03              0.61         0.12          0.62   \n",
       "9        P2O5              0.02              0.10         0.12          0.44   \n",
       "10        LOI               NaN               NaN         0.66          0.62   \n",
       "\n",
       "    CO_pyroxenite  Unnamed: 6  \n",
       "0           50.94         NaN  \n",
       "1            0.11         NaN  \n",
       "2            3.24         NaN  \n",
       "3            6.12         NaN  \n",
       "4            0.14         NaN  \n",
       "5           21.84         NaN  \n",
       "6           14.87         NaN  \n",
       "7            0.33         NaN  \n",
       "8            0.05         NaN  \n",
       "9            0.01         NaN  \n",
       "10           1.59         NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url = \"https://raw.githubusercontent.com/marcoalopez/Python_course/main/data/bulk_composition.csv\"\n",
    "dataset = pd.read_csv(url, delimiter=';')\n",
    "dataset"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ``read_csv()`` function is highly customisable and allows you to modify the behaviour of the function to suit your needs. At a minimum, you have to specify the file path as a Python string, which can point to a local file or a URL address. Another typical parameter is to set the delimiter type of the file to import. By default, the ``read_csv()`` function assumes a comma, a typical separator for CSV files (meaning comma-separated values). In the example above the separator used in the file \"bulk_composition.csv\" is not a comma but a semicolon and the delimiter is specified inside the function as ``delimiter`` (you can also use the alias ``sep``). If you import a .txt or a .tsv (tab-separated) file you will need to specify ``'\\t'`` as the delimiter and so on. Another example would be the ``skiprows`` parameter which allows you to define the number of lines to skip as often text/CSV files contain metadata in the first few lines that are not the actual tabular data, etc. The ``read_csv()`` function has 50 possible parameters at the time this is written, which gives an idea of the control one can exercise when reading tabular data files. You can read the details of each of them in the following link https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html\n",
    "\n",
    "> 👉 Pandas also allows you to read data copied into your clipboard using the method ``read_clipboard()``.\n",
    "\n",
    "Pandas can read other tabular file formats including:\n",
    "\n",
    "- Excel (xls, xlsx, xlsm, xlsb, odf, ods, and odt)\n",
    "- JSON\n",
    "- SQL \n",
    "- HTML\n",
    "- HDF5\n",
    "- XML\n",
    "- Parquet\n",
    "- Msgpack\n",
    "- Stata\n",
    "- SAS\n",
    "- SPSS\n",
    "- Pickle\n",
    "\n",
    "All Pandas reading methods start with the word ``read`` followed by an underscore and the file type, e.g. ``read_excel()`` or ``read_hdf()`` and so on. On the other hand, if you need to save a DataFrame in any of these formats we follow a similar logic using the word ``to``. For example, ``dataset.to_excel(\"filename.xlsx\")`` or ``dataset.to_csv(\"filename.csv\")``. You can read more on this at https://pandas.pydata.org/docs/user_guide/io.html\n",
    "\n",
    "> 👉 By default, the ``.to_csv()`` method creates an unlabelled column with the indexes in the first column. To avoid this use ``index=False``."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inspecting your DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Unnamed: 0</th>\n",
       "      <th>primitive mantle</th>\n",
       "      <th>mean lower crust</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiO2</td>\n",
       "      <td>44.91</td>\n",
       "      <td>53.40</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TiO2</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Al2O3</td>\n",
       "      <td>4.44</td>\n",
       "      <td>16.90</td>\n",
       "      <td>15.54</td>\n",
       "      <td>16.95</td>\n",
       "      <td>3.24</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fe2O3t</td>\n",
       "      <td>8.05</td>\n",
       "      <td>8.57</td>\n",
       "      <td>11.06</td>\n",
       "      <td>13.93</td>\n",
       "      <td>6.12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MnO</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.14</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0  primitive mantle  mean lower crust  CO_eclogite  CO_granulite  \\\n",
       "0       SiO2             44.91             53.40        48.44         48.41   \n",
       "1       TiO2              0.16              0.82         1.06          1.19   \n",
       "2      Al2O3              4.44             16.90        15.54         16.95   \n",
       "3     Fe2O3t              8.05              8.57        11.06         13.93   \n",
       "4        MnO              0.13              0.10         0.18          0.25   \n",
       "\n",
       "   CO_pyroxenite  Unnamed: 6  \n",
       "0          50.94         NaN  \n",
       "1           0.11         NaN  \n",
       "2           3.24         NaN  \n",
       "3           6.12         NaN  \n",
       "4           0.14         NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# head() and tail() methods show the first and last rows respectively (by default five)\n",
    "dataset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 11 entries, 0 to 10\n",
      "Data columns (total 7 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   Unnamed: 0        11 non-null     object \n",
      " 1   primitive mantle  10 non-null     float64\n",
      " 2   mean lower crust  10 non-null     float64\n",
      " 3   CO_eclogite       11 non-null     float64\n",
      " 4   CO_granulite      11 non-null     float64\n",
      " 5   CO_pyroxenite     11 non-null     float64\n",
      " 6   Unnamed: 6        0 non-null      float64\n",
      "dtypes: float64(6), object(1)\n",
      "memory usage: 748.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "# check all the data types in a dataframe (alternative: dataset.dtypes)\n",
    "dataset.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "You can access single columns by indexing using square brackets or using dot notation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     48.44\n",
       "1      1.06\n",
       "2     15.54\n",
       "3     11.06\n",
       "4      0.18\n",
       "5      8.84\n",
       "6     12.31\n",
       "7      1.89\n",
       "8      0.12\n",
       "9      0.12\n",
       "10     0.66\n",
       "Name: CO_eclogite, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using squared brackets\n",
    "dataset['CO_eclogite']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     48.44\n",
       "1      1.06\n",
       "2     15.54\n",
       "3     11.06\n",
       "4      0.18\n",
       "5      8.84\n",
       "6     12.31\n",
       "7      1.89\n",
       "8      0.12\n",
       "9      0.12\n",
       "10     0.66\n",
       "Name: CO_eclogite, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using dot notation\n",
    "dataset.CO_eclogite"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> 👉 Do **not** use column labels with spaces or dots. If column labels have spaces or dots, you will be unable to use dot notation. If you want to use more than one word in column labels, use underscores between words."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15.54</td>\n",
       "      <td>16.95</td>\n",
       "      <td>3.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11.06</td>\n",
       "      <td>13.93</td>\n",
       "      <td>6.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.18</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8.84</td>\n",
       "      <td>5.44</td>\n",
       "      <td>21.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12.31</td>\n",
       "      <td>10.43</td>\n",
       "      <td>14.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1.89</td>\n",
       "      <td>1.66</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.12</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.12</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.66</td>\n",
       "      <td>0.62</td>\n",
       "      <td>1.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CO_eclogite  CO_granulite  CO_pyroxenite\n",
       "0         48.44         48.41          50.94\n",
       "1          1.06          1.19           0.11\n",
       "2         15.54         16.95           3.24\n",
       "3         11.06         13.93           6.12\n",
       "4          0.18          0.25           0.14\n",
       "5          8.84          5.44          21.84\n",
       "6         12.31         10.43          14.87\n",
       "7          1.89          1.66           0.33\n",
       "8          0.12          0.62           0.05\n",
       "9          0.12          0.44           0.01\n",
       "10         0.66          0.62           1.59"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# accesing multiple columns (note the double square brackets as we are passing a list of labels)\n",
    "dataset[['CO_eclogite', 'CO_granulite', 'CO_pyroxenite']]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rename column labels\n",
    "\n",
    "The preferred way is to use the rename method that uses a Python dictionary as follows: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Oxides</th>\n",
       "      <th>primitive_mantle</th>\n",
       "      <th>mean_lower_crust</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiO2</td>\n",
       "      <td>44.91</td>\n",
       "      <td>53.4</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Oxides  primitive_mantle  mean_lower_crust  CO_eclogite  CO_granulite  \\\n",
       "0   SiO2             44.91              53.4        48.44         48.41   \n",
       "\n",
       "   CO_pyroxenite  Unnamed: 6  \n",
       "0          50.94         NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset = dataset.rename(columns={'Unnamed: 0':'Oxides', \n",
    "                                  'primitive mantle':'primitive_mantle',\n",
    "                                  'mean lower crust':'mean_lower_crust'})\n",
    "\n",
    "dataset.head(1)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Selecting rows and columns\n",
    "\n",
    "Above we have already seen a way of accessing one or more columns in a DataFrame using column names. Here we will look at slightly more sophisticated examples of how to access different columns or rows (i.e. subsetting your DataFrame), and how to modify or combine two or more columns.\n",
    "\n",
    "## By attribute\n",
    "\n",
    "Pandas allows to select columns by type as follows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>primitive_mantle</th>\n",
       "      <th>mean_lower_crust</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>44.91</td>\n",
       "      <td>53.40</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.16</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.44</td>\n",
       "      <td>16.90</td>\n",
       "      <td>15.54</td>\n",
       "      <td>16.95</td>\n",
       "      <td>3.24</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8.05</td>\n",
       "      <td>8.57</td>\n",
       "      <td>11.06</td>\n",
       "      <td>13.93</td>\n",
       "      <td>6.12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.13</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.14</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   primitive_mantle  mean_lower_crust  CO_eclogite  CO_granulite  \\\n",
       "0             44.91             53.40        48.44         48.41   \n",
       "1              0.16              0.82         1.06          1.19   \n",
       "2              4.44             16.90        15.54         16.95   \n",
       "3              8.05              8.57        11.06         13.93   \n",
       "4              0.13              0.10         0.18          0.25   \n",
       "\n",
       "   CO_pyroxenite  Unnamed: 6  \n",
       "0          50.94         NaN  \n",
       "1           0.11         NaN  \n",
       "2           3.24         NaN  \n",
       "3           6.12         NaN  \n",
       "4           0.14         NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset.select_dtypes(\"float64\").head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing columns and rows using ``.drop()``\n",
    "\n",
    "TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using ``.iloc()`` and ``.loc()`` methods\n",
    "\n",
    "So far we have only used methods to select specific columns, not specific rows or combinations of rows and columns, this is what the ``.iloc`` and ``.loc`` methods allow us to do. They differ in how they refer to the rows and columns:\n",
    "\n",
    "- ``.loc`` is label-based and uses row and column **labels** to slice the data.\n",
    "- ``.iloc`` is integer-based (_i_ comes from integer) and uses integer **indexes** to slice the data\n",
    "\n",
    "<img src=\"https://raw.githubusercontent.com/marcoalopez/Python_course/main/notebooks/imgs/iloc_vs_loc.jpg\" style=\"zoom:30%;\" />\n",
    "\n",
    "TODO"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "> 👉 When subsetting a DataFrame it is good practice to use the ``.copy()`` method because what you get by default is a view of the original DataFrame and all modifications you make to this new DataFrame will affect the original one. This is:  \n",
    ">```python\n",
    ">new_df = df.loc[['CO_eclogite', 'CO_granulite', 'CO_pyroxenite']] ❌  \n",
    ">new_df = df.loc[['CO_eclogite', 'CO_granulite', 'CO_pyroxenite']].copy() ✅  \n",
    ">```\n",
    "> ⚠️ Beware! Contrary to regular Python or Numpy, the Pandas ``.loc`` method **includes** the  index stop point so that ``df.loc[0:2]`` is equivalent to ``array[0:3]``"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling missing values\n",
    "\n",
    "When loading a dataframe from a CSV file or similar, an empty value in a numeric column will become NaN or ``np.nan``, which means \"not a number\" (some synonyms are ``null``, ``None``, ``nan`` or ``<NA>``). Pandas has several methods for dealing with missing values. Here, we will show examples of the following methods (the names are quite explicit):\n",
    "\n",
    "``.info()``: provide a concise summary of a DataFrame including missing values   \n",
    "``.isna()``: detect missing values returning a boolean mask, ie. empty values get mapped as True.  \n",
    "``.notna()``: detect not missing values returning a boolean mask, ie. not empty values get mapped as True.  \n",
    "``.fillna()``: fill NA/NaN values using different method (e.g. using interpolation or a especific value)  \n",
    "``.dropna()``: remove missing values based on certain conditions.  \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     False\n",
       "1     False\n",
       "2     False\n",
       "3     False\n",
       "4     False\n",
       "5     False\n",
       "6     False\n",
       "7     False\n",
       "8     False\n",
       "9     False\n",
       "10     True\n",
       "Name: primitive_mantle, dtype: bool"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check if there are values with missing values in your dataset\n",
    "dataset['primitive_mantle'].isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Oxides</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiO2</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TiO2</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Al2O3</td>\n",
       "      <td>15.54</td>\n",
       "      <td>16.95</td>\n",
       "      <td>3.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fe2O3t</td>\n",
       "      <td>11.06</td>\n",
       "      <td>13.93</td>\n",
       "      <td>6.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MnO</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MgO</td>\n",
       "      <td>8.84</td>\n",
       "      <td>5.44</td>\n",
       "      <td>21.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CaO</td>\n",
       "      <td>12.31</td>\n",
       "      <td>10.43</td>\n",
       "      <td>14.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Na2O</td>\n",
       "      <td>1.89</td>\n",
       "      <td>1.66</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>K2O</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>P2O5</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>LOI</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.62</td>\n",
       "      <td>1.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Oxides  CO_eclogite  CO_granulite  CO_pyroxenite\n",
       "0     SiO2        48.44         48.41          50.94\n",
       "1     TiO2         1.06          1.19           0.11\n",
       "2    Al2O3        15.54         16.95           3.24\n",
       "3   Fe2O3t        11.06         13.93           6.12\n",
       "4      MnO         0.18          0.25           0.14\n",
       "5      MgO         8.84          5.44          21.84\n",
       "6      CaO        12.31         10.43          14.87\n",
       "7     Na2O         1.89          1.66           0.33\n",
       "8      K2O         0.12          0.62           0.05\n",
       "9     P2O5         0.12          0.44           0.01\n",
       "10     LOI         0.66          0.62           1.59"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop all columns with nan values\n",
    "dataset.dropna(axis='columns')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> 👉 **Axis in DataFrames**.  \n",
    "> Contrary to NumPy arrays, that can have as many axes as you need, DataFrames only has two axes: axis 0 to refer to rows, and axis 1 to refer to columns. Pandas allows the user to use regular names instead of integers to refer to them. Axis 0 has two aliases ``'index'`` and ``'rows'`` and axis 1 ``'columns'``. For example if we want to remove all columns with NaN values using the ``.drop`` method we will do ``df.dropna(axis='columns')``, while if we want to do the same but for rows ``df.dropna(axis='rows')``. Series only have one axis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Oxides</th>\n",
       "      <th>primitive_mantle</th>\n",
       "      <th>mean_lower_crust</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiO2</td>\n",
       "      <td>44.91</td>\n",
       "      <td>53.40</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TiO2</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Al2O3</td>\n",
       "      <td>4.44</td>\n",
       "      <td>16.90</td>\n",
       "      <td>15.54</td>\n",
       "      <td>16.95</td>\n",
       "      <td>3.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fe2O3t</td>\n",
       "      <td>8.05</td>\n",
       "      <td>8.57</td>\n",
       "      <td>11.06</td>\n",
       "      <td>13.93</td>\n",
       "      <td>6.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MnO</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MgO</td>\n",
       "      <td>37.81</td>\n",
       "      <td>7.24</td>\n",
       "      <td>8.84</td>\n",
       "      <td>5.44</td>\n",
       "      <td>21.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CaO</td>\n",
       "      <td>3.54</td>\n",
       "      <td>9.59</td>\n",
       "      <td>12.31</td>\n",
       "      <td>10.43</td>\n",
       "      <td>14.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Na2O</td>\n",
       "      <td>0.36</td>\n",
       "      <td>2.65</td>\n",
       "      <td>1.89</td>\n",
       "      <td>1.66</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>K2O</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.61</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>P2O5</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>LOI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.62</td>\n",
       "      <td>1.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Oxides  primitive_mantle  mean_lower_crust  CO_eclogite  CO_granulite  \\\n",
       "0     SiO2             44.91             53.40        48.44         48.41   \n",
       "1     TiO2              0.16              0.82         1.06          1.19   \n",
       "2    Al2O3              4.44             16.90        15.54         16.95   \n",
       "3   Fe2O3t              8.05              8.57        11.06         13.93   \n",
       "4      MnO              0.13              0.10         0.18          0.25   \n",
       "5      MgO             37.81              7.24         8.84          5.44   \n",
       "6      CaO              3.54              9.59        12.31         10.43   \n",
       "7     Na2O              0.36              2.65         1.89          1.66   \n",
       "8      K2O              0.03              0.61         0.12          0.62   \n",
       "9     P2O5              0.02              0.10         0.12          0.44   \n",
       "10     LOI               NaN               NaN         0.66          0.62   \n",
       "\n",
       "    CO_pyroxenite  \n",
       "0           50.94  \n",
       "1            0.11  \n",
       "2            3.24  \n",
       "3            6.12  \n",
       "4            0.14  \n",
       "5           21.84  \n",
       "6           14.87  \n",
       "7            0.33  \n",
       "8            0.05  \n",
       "9            0.01  \n",
       "10           1.59  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop only the columns that are all nans, for rows will be: .dropna(axis='rows', how='all')\n",
    "dataset.dropna(axis='columns', how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Oxides</th>\n",
       "      <th>primitive_mantle</th>\n",
       "      <th>mean_lower_crust</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiO2</td>\n",
       "      <td>44.91</td>\n",
       "      <td>53.40</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TiO2</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Al2O3</td>\n",
       "      <td>4.44</td>\n",
       "      <td>16.90</td>\n",
       "      <td>15.54</td>\n",
       "      <td>16.95</td>\n",
       "      <td>3.24</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fe2O3t</td>\n",
       "      <td>8.05</td>\n",
       "      <td>8.57</td>\n",
       "      <td>11.06</td>\n",
       "      <td>13.93</td>\n",
       "      <td>6.12</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MnO</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.18</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.14</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MgO</td>\n",
       "      <td>37.81</td>\n",
       "      <td>7.24</td>\n",
       "      <td>8.84</td>\n",
       "      <td>5.44</td>\n",
       "      <td>21.84</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CaO</td>\n",
       "      <td>3.54</td>\n",
       "      <td>9.59</td>\n",
       "      <td>12.31</td>\n",
       "      <td>10.43</td>\n",
       "      <td>14.87</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Na2O</td>\n",
       "      <td>0.36</td>\n",
       "      <td>2.65</td>\n",
       "      <td>1.89</td>\n",
       "      <td>1.66</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>K2O</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.61</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>P2O5</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>LOI</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.62</td>\n",
       "      <td>1.59</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Oxides  primitive_mantle  mean_lower_crust  CO_eclogite  CO_granulite  \\\n",
       "0     SiO2             44.91             53.40        48.44         48.41   \n",
       "1     TiO2              0.16              0.82         1.06          1.19   \n",
       "2    Al2O3              4.44             16.90        15.54         16.95   \n",
       "3   Fe2O3t              8.05              8.57        11.06         13.93   \n",
       "4      MnO              0.13              0.10         0.18          0.25   \n",
       "5      MgO             37.81              7.24         8.84          5.44   \n",
       "6      CaO              3.54              9.59        12.31         10.43   \n",
       "7     Na2O              0.36              2.65         1.89          1.66   \n",
       "8      K2O              0.03              0.61         0.12          0.62   \n",
       "9     P2O5              0.02              0.10         0.12          0.44   \n",
       "10     LOI              0.00              0.00         0.66          0.62   \n",
       "\n",
       "    CO_pyroxenite  Unnamed: 6  \n",
       "0           50.94         0.0  \n",
       "1            0.11         0.0  \n",
       "2            3.24         0.0  \n",
       "3            6.12         0.0  \n",
       "4            0.14         0.0  \n",
       "5           21.84         0.0  \n",
       "6           14.87         0.0  \n",
       "7            0.33         0.0  \n",
       "8            0.05         0.0  \n",
       "9            0.01         0.0  \n",
       "10           1.59         0.0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# fill null values with an specific value\n",
    "dataset.fillna(0.0)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> ⚠️ If we apply any Pandas method to a DataFrame, as we do in these examples, what we are getting are the output of the method without modifying the original DataFrame. If we really want to modify the DataFrame we must do it explicitly as follows:\n",
    ">\n",
    "> ```python\n",
    "> dataset = dataset.fillna(0.0)  ✅ Recommended way\n",
    "> ```\n",
    "> an alternative way, which you will see in many older (and not so old) tutorials, is to pass the ``inplace`` parameter as True inside the method (see below). This is currently **not recommended**, the ``inplace`` parameter remains for legacy reasons, but it is possible that it will disappear in future versions of Pandas.\n",
    ">\n",
    "> ```python\n",
    "> dataset.fillna(0.0, inplace=True)  ❌ Not recommended\n",
    "> ```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Oxides</th>\n",
       "      <th>primitive_mantle</th>\n",
       "      <th>mean_lower_crust</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiO2</td>\n",
       "      <td>44.91</td>\n",
       "      <td>53.40</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TiO2</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Oxides  primitive_mantle  mean_lower_crust  CO_eclogite  CO_granulite  \\\n",
       "0   SiO2             44.91             53.40        48.44         48.41   \n",
       "1   TiO2              0.16              0.82         1.06          1.19   \n",
       "\n",
       "   CO_pyroxenite  Unnamed: 6  \n",
       "0          50.94         NaN  \n",
       "1           0.11         NaN  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# just a view\n",
    "dataset.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Oxides</th>\n",
       "      <th>primitive_mantle</th>\n",
       "      <th>mean_lower_crust</th>\n",
       "      <th>CO_eclogite</th>\n",
       "      <th>CO_granulite</th>\n",
       "      <th>CO_pyroxenite</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SiO2</td>\n",
       "      <td>44.91</td>\n",
       "      <td>53.40</td>\n",
       "      <td>48.44</td>\n",
       "      <td>48.41</td>\n",
       "      <td>50.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TiO2</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.82</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Oxides  primitive_mantle  mean_lower_crust  CO_eclogite  CO_granulite  \\\n",
       "0   SiO2             44.91             53.40        48.44         48.41   \n",
       "1   TiO2              0.16              0.82         1.06          1.19   \n",
       "\n",
       "   CO_pyroxenite  \n",
       "0          50.94  \n",
       "1           0.11  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# you actually modified the DataFrame\n",
    "dataset = dataset.dropna(axis='columns', how='all')\n",
    "dataset.head(2)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extending a DataFrame\n",
    "\n",
    "## Adding columns\n",
    "\n",
    "TODO\n",
    "\n",
    "> 👉 Avoid iterating over rows. Use vectorized operations when possible or the ``.apply()`` method\n",
    "\n",
    "## Adding rows\n",
    "\n",
    "TODO\n",
    "\n",
    "## Operations\n",
    "\n",
    "TODO\n",
    "\n",
    "| Operators | Pandas method   |                 |                |\n",
    "| --------- | --------------- | --------------- | -------------- |\n",
    "| \\+        | ``.add()``      |                 |                |\n",
    "| \\-        | ``.sub()``      | ``.subtract()`` |                |\n",
    "| \\*        | ``.mul()``      | ``.multiply()`` |                |\n",
    "| /         | ``.div()``      | ``.divide()``   | ``.truediv()`` |\n",
    "| //        | ``.floordiv()`` |                 |                |\n",
    "| %         | ``.mod()``      |                 |                |\n",
    "| \\**       | ``.pow()``      |                 |                |\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Filtering\n",
    "\n",
    "Filtering refers to the process of selecting specific rows or columns from a DataFrame based on certain conditions or criteria. It allows you to extract the data that meets a specific set of requirements, such as values that satisfy a particular condition or fall within a certain range, to focus on relevant information for analysis or further processing.\n",
    "\n",
    "## Boolean indexing\n",
    "\n",
    "TODO\n",
    "\n",
    "E.g. ``df[df['column'] > 10]``\n",
    "\n",
    "``.isin()``  \n",
    "``.between()``\n",
    "\n",
    "### Using .iloc and .loc\n",
    "\n",
    "TODO\n",
    "\n",
    "E.g. ``df.loc[df['column'] > 10, 'column']``\n",
    "\n",
    "## Using ``.query()`` and ``.eval()`` methods (preferred!)\n",
    "\n",
    "TODO\n",
    "\n",
    "E.g. ``df.query('column > 10')``  \n",
    "E.g. ``df.query('SiO2 > 50 and MgO > 5')``\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reshape DataFrames: the ``pd.pivot_table()`` method\n",
    "\n",
    "Let's imagine the case where we are really interested in comparing the major element composition of different types of rock. In this case the observations (rows) would be the rock type and the variables (columns) the percentage of oxides of the different elements. The ``.pivot_table()`` method allows us to reorganise a table according to our needs. In this case, we are going to tell it to take the different oxides as columns so that the old column names will become the indexes of the rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Oxides</th>\n",
       "      <th>SiO2</th>\n",
       "      <th>TiO2</th>\n",
       "      <th>Al2O3</th>\n",
       "      <th>Fe2O3t</th>\n",
       "      <th>MnO</th>\n",
       "      <th>MgO</th>\n",
       "      <th>CaO</th>\n",
       "      <th>Na2O</th>\n",
       "      <th>K2O</th>\n",
       "      <th>P2O5</th>\n",
       "      <th>LOI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>primitive_mantle</th>\n",
       "      <td>44.91</td>\n",
       "      <td>0.16</td>\n",
       "      <td>4.44</td>\n",
       "      <td>8.05</td>\n",
       "      <td>0.13</td>\n",
       "      <td>37.81</td>\n",
       "      <td>3.54</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.02</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_lower_crust</th>\n",
       "      <td>53.40</td>\n",
       "      <td>0.82</td>\n",
       "      <td>16.90</td>\n",
       "      <td>8.57</td>\n",
       "      <td>0.10</td>\n",
       "      <td>7.24</td>\n",
       "      <td>9.59</td>\n",
       "      <td>2.65</td>\n",
       "      <td>0.61</td>\n",
       "      <td>0.10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO_eclogite</th>\n",
       "      <td>48.44</td>\n",
       "      <td>1.06</td>\n",
       "      <td>15.54</td>\n",
       "      <td>11.06</td>\n",
       "      <td>0.18</td>\n",
       "      <td>8.84</td>\n",
       "      <td>12.31</td>\n",
       "      <td>1.89</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO_granulite</th>\n",
       "      <td>48.41</td>\n",
       "      <td>1.19</td>\n",
       "      <td>16.95</td>\n",
       "      <td>13.93</td>\n",
       "      <td>0.25</td>\n",
       "      <td>5.44</td>\n",
       "      <td>10.43</td>\n",
       "      <td>1.66</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO_pyroxenite</th>\n",
       "      <td>50.94</td>\n",
       "      <td>0.11</td>\n",
       "      <td>3.24</td>\n",
       "      <td>6.12</td>\n",
       "      <td>0.14</td>\n",
       "      <td>21.84</td>\n",
       "      <td>14.87</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.01</td>\n",
       "      <td>1.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Oxides             SiO2  TiO2  Al2O3  Fe2O3t   MnO    MgO    CaO  Na2O   K2O  \\\n",
       "primitive_mantle  44.91  0.16   4.44    8.05  0.13  37.81   3.54  0.36  0.03   \n",
       "mean_lower_crust  53.40  0.82  16.90    8.57  0.10   7.24   9.59  2.65  0.61   \n",
       "CO_eclogite       48.44  1.06  15.54   11.06  0.18   8.84  12.31  1.89  0.12   \n",
       "CO_granulite      48.41  1.19  16.95   13.93  0.25   5.44  10.43  1.66  0.62   \n",
       "CO_pyroxenite     50.94  0.11   3.24    6.12  0.14  21.84  14.87  0.33  0.05   \n",
       "\n",
       "Oxides            P2O5   LOI  \n",
       "primitive_mantle  0.02   NaN  \n",
       "mean_lower_crust  0.10   NaN  \n",
       "CO_eclogite       0.12  0.66  \n",
       "CO_granulite      0.44  0.62  \n",
       "CO_pyroxenite     0.01  1.59  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_table = pd.pivot_table(dataset, columns=['Oxides'], sort=False)\n",
    "new_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Oxides\n",
       "SiO2      44.91\n",
       "TiO2       0.16\n",
       "Al2O3      4.44\n",
       "Fe2O3t     8.05\n",
       "MnO        0.13\n",
       "MgO       37.81\n",
       "CaO        3.54\n",
       "Na2O       0.36\n",
       "K2O        0.03\n",
       "P2O5       0.02\n",
       "LOI         NaN\n",
       "Name: primitive_mantle, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show the first row of the new DataFrame (should correspond to a column of the original DataFrame)\n",
    "new_table.iloc[0, :]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a simple example of ``.pivot_table()``, you can find more information with much more sophisticated examples (outside the scope of this course) in the following links:  \n",
    "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot_table.html#pandas.DataFrame.pivot_table  \n",
    "https://pandas.pydata.org/docs/user_guide/reshaping.html  "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Using the ``.groupby()`` method\n",
    "\n",
    "TODO\n",
    "\n",
    "# Concatenating operations\n",
    "\n",
    "TODO\n",
    "\n",
    "\n",
    "# Using of Pandas' plotting capabilities\n",
    "\n",
    "TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Notebook tested in 2024-04-10 using:\n",
      "Python 3.11.8 | packaged by Anaconda, Inc. | (main, Feb 26 2024, 21:34:05) [MSC v.1916 64 bit (AMD64)]\n",
      "Pandas 2.1.4\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "from datetime import date    \n",
    "today = date.today().isoformat()\n",
    "\n",
    "print(f'Notebook tested in {today} using:')\n",
    "print('Python', sys.version)\n",
    "print('Pandas', pd.__version__)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  },
  "vscode": {
   "interpreter": {
    "hash": "073408933f31e8ac7f6b4bf29a6f48e1a2f613d7d5b6e5c6c58fd8fdc8389e42"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}