pandas.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "pandas.ipynb",
      "provenance": []
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nQW_J5sj_IuF"
      },
      "source": [
        "# [Aman's AI Journal](https://aman.ai) | Primers | Pandas Tutorial\n",
        "\n",
        "## Overview\n",
        "\n",
        "- The pandas library provides high-performance, easy-to-use data structures and data analysis tools. The main data structure is the `DataFrame`, which you can think of as an in-memory 2D table (like a spreadsheet, with column names and row labels). \n",
        "- Many features available in Excel are available programmatically, such as creating pivot tables, computing columns based on other columns, plotting graphs, etc. You can also group rows by column value, or join tables much like in SQL. Pandas is also great at handling time series data.\n",
        "\n",
        "## Background\n",
        "\n",
        "- It's possible that Python wouldn't have become [the lingua franca of data science if it wasn't for pandas](https://stackoverflow.blog/2017/09/14/python-growing-quickly/). The package's exponential growth on Stack Overflow means two things:\n",
        "\n",
        "1. It's getting increasingly popular.\n",
        "2. It can be frustrating to use sometimes (hence the high number of questions).\n",
        "\n",
        "- This tutorial contains a few peculiar things about pandas that hopefully make your life easier and code faster.\n",
        "\n",
        "## Prerequisites\n",
        "\n",
        "- If you are not familiar with NumPy, we recommend that you go through the [NumPy tutorial](../numpy) now.\n",
        "\n",
        "## Setup\n",
        "\n",
        "- First, let's import pandas. People usually import it as `pd`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "enbywB6i_IuF"
      },
      "source": [
        "import pandas as pd"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4KE7bowd_IuF"
      },
      "source": [
        "## Overview: Pandas data-structures \n",
        "\n",
        "- The pandas library contains these useful data structures:\n",
        "    - `Series` objects: A `Series` object is 1D array, similar to a column in a spreadsheet (with a column name and row labels).\n",
        "    - `DataFrame` objects: A `DataFrame` is a concept inspired by R's Data Frame, which is, in turn, similar to tables in relational databases. This is a 2D table with rows and columns, similar to a spreadsheet (with names for columns and labels for rows).\n",
        "    - `Panel` objects: You can see a `Panel` as a dictionary of `DataFrame`s. These are less used, so we will not discuss them here.\n",
        "\n",
        "## pandas is column-major\n",
        "\n",
        "> An important thing to know about pandas is that it is column-major, which explains many of its quirks.\n",
        "\n",
        "- Column-major means consecutive elements in a column are stored next to each other in memory. Row-major means the same but for elements in a row. Because modern computers process sequential data more efficiently than non-sequential data, if a table is row-major, accessing its rows will be much faster than accessing its columns.\n",
        "- In NumPy, major order can be specified. When a `ndarray` is created, it's row-major by default if you don't specify the order.\n",
        "- Like R's Data Frame, pandas' `DataFrame` is column-major. People coming to pandas from NumPy tend to treat `DataFrame` the way they would `ndarray`, e.g. trying to access data by rows, and find `DataFrame` slow.\n",
        "- **Note**: A column in a `DataFrame` is a `Series`. You can think of a `DataFrame` as a bunch of `Series` being stored next to each other in memory.\n",
        "- **For our dataset, accessing a row takes about 50x longer than accessing a column in our `DataFrame`.**\n",
        "- Run the following code snippet in a Colab/Jupyter notebook:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Wl3LuLTU_IuF"
      },
      "source": [
        "# Get the column `date`, 1000 loops\n",
        "%timeit -n1000 df[\"Date\"]\n",
        "\n",
        "# Get the first row, 1000 loops\n",
        "%timeit -n1000 df.iloc[0]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e-Uzh55k_IuF"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bt_fqOK__IuF"
      },
      "source": [
        "1.78 µs ± 167 ns per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
        "145 µs ± 9.41 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WAKjNUek_IuF"
      },
      "source": [
        "### Converting DataFrame to row-major order\n",
        "\n",
        "- If you need to do a lot of row operations, you might want to convert your `DataFrame` to a NumPy's row-major `ndarray`, then iterating through the rows. Run the following code snippet in a Colab/Jupyter notebook:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "W9TanzgP_IuF"
      },
      "source": [
        "# Now, iterating through our DataFrame is 100x faster.\n",
        "%timeit -n1 df_np = df.to_numpy(); rows = [row for row in df_np]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "igk_PqQT_IuF"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e3r3-H5g_IuF"
      },
      "source": [
        "4.55 ms ± 280 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EwX92zPZ_IuF"
      },
      "source": [
        "- Accessing a row or a column of our `ndarray` takes nanoseconds instead of microseconds. Run the following code snippet in a Colab/Jupyter notebook:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yxD-lANG_IuF"
      },
      "source": [
        "df_np = df.to_numpy()\n",
        "%timeit -n1000 df_np[0]\n",
        "%timeit -n1000 df_np[:,0]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nu-pjVAL_IuF"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "300SVqaa_IuF"
      },
      "source": [
        "147 ns ± 1.54 ns per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
        "204 ns ± 0.678 ns per loop (mean ± std. dev. of 7 runs, 1000 loops each)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BnT8kTS2_IuF"
      },
      "source": [
        "## `Series` objects\n",
        "\n",
        "### Creating a `Series`\n",
        "\n",
        "- Let's start by creating our first `Series` object!"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BnznPnOd_IuF"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "pd.Series([1, 2, 3])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6po5HT6p_IuF"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aBPnGoNO_IuF"
      },
      "source": [
        "0    1\n",
        "1    2\n",
        "2    3\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vrMF7PY4_IuF"
      },
      "source": [
        "### Similar to a 1D `ndarray`\n",
        "\n",
        "- `Series` objects behave much like one-dimensional NumPy `ndarray`s, and you can often pass them as parameters to NumPy functions:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PeHpJrBd_IuF"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "s = pd.Series([1, 2, 3])\n",
        "np.exp(s)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SSEf90vp_IuF"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aJAYWsdf_IuF"
      },
      "source": [
        "0     2.718282\n",
        "1     7.389056\n",
        "2    20.085537\n",
        "dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "btVUHV8l_IuG"
      },
      "source": [
        "- Arithmetic operations on `Series` are also possible, and they apply **elementwise**, just like for `ndarray`s:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qrIWPWMu_IuG"
      },
      "source": [
        "s = pd.Series([1, 2, 3])\n",
        "np.exp(s)\n",
        "s + [1000, 2000, 3000]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SlEZKiWs_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CpPez7gv_IuG"
      },
      "source": [
        "0    1001\n",
        "1    2002\n",
        "2    3003\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ePeldZ75_IuG"
      },
      "source": [
        "- Similar to NumPy, if you add a single number to a `Series`, that number is added to all items in the `Series`. This is called **broadcasting**:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nw6tW_Qz_IuG"
      },
      "source": [
        "s = pd.Series([1, 2, 3])\n",
        "np.exp(s)\n",
        "s + 1000"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PADVW7bU_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bmqZ8d04_IuG"
      },
      "source": [
        "0    1001\n",
        "1    1002\n",
        "2    1003\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8W2sF6LH_IuG"
      },
      "source": [
        "- The same is true for all binary operations such as `*` or `/`, and even conditional operations:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zXuiGz3j_IuG"
      },
      "source": [
        "s = pd.Series([1, 2, 3])\n",
        "np.exp(s)\n",
        "s < 0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "x1jYqWQ7_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dr0YIegw_IuG"
      },
      "source": [
        "0    False\n",
        "1    False\n",
        "2    False\n",
        "dtype: bool"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oIUnu4la_IuG"
      },
      "source": [
        "### Index labels\n",
        "\n",
        "- Each item in a `Series` object has a unique identifier called the **index label**. By default, it is simply the rank of the item in the `Series` (starting at `0`) but you can also set the index labels manually:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YY8f7OWO_IuG"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "s = pd.Series([68, 83, 112, 68], index=[\"alice\", \"bob\", \"charles\", \"darwin\"])\n",
        "s"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NQyAjHzk_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SAvMdmRX_IuG"
      },
      "source": [
        "alice       68\n",
        "bob         83\n",
        "charles    112\n",
        "darwin      68\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pxtjr_CY_IuG"
      },
      "source": [
        "- You can then use the `Series` just like a `dict`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cwA6m4nM_IuG"
      },
      "source": [
        "s[\"bob\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T4VmuBkd_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D-cv6na3_IuG"
      },
      "source": [
        "83"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lkxmrq59_IuG"
      },
      "source": [
        "- You can still access the items by integer location, like in a regular array:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zw3rQoda_IuG"
      },
      "source": [
        "s[1]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qiINxgIM_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UaMJGrKz_IuG"
      },
      "source": [
        "83"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-JilJbie_IuG"
      },
      "source": [
        "- To make it clear when you are accessing by label, it is recommended to always use the `loc` attribute when accessing by label:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vMRPfSgk_IuG"
      },
      "source": [
        "s.loc[\"bob\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NRBfq0za_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NMvPRxnj_IuG"
      },
      "source": [
        "83"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7AUefdsQ_IuG"
      },
      "source": [
        "- Similarly, to make it clear when you are accessing by integer location, it is recommended to always use the `iloc` attribute when accessing by integer location:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GcNK3YRp_IuG"
      },
      "source": [
        "s2.iloc[1]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AiRe57Kt_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2yVwbZFr_IuG"
      },
      "source": [
        "83"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KoGsUnuI_IuG"
      },
      "source": [
        "- Slicing a `Series` also slices the index labels:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AUf1eUFJ_IuG"
      },
      "source": [
        "s.iloc[1:3]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "33llw_mD_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6A27frd1_IuG"
      },
      "source": [
        "bob         83\n",
        "charles    112\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8ZljnNpQ_IuG"
      },
      "source": [
        "- Pitfall: This can lead to unexpected results when using the default numeric labels, so be careful:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6E9VHw-Q_IuG"
      },
      "source": [
        "surprise = pd.Series([1000, 1001, 1002, 1003])\n",
        "surprise"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9ZzvTqyJ_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ixISifXH_IuG"
      },
      "source": [
        "0    1000\n",
        "1    1001\n",
        "2    1002\n",
        "3    1003\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sRlbMgZ1_IuG"
      },
      "source": [
        "surprise_slice = surprise[2:]\n",
        "surprise_slice"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PcW7FA6w_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0ZOPt5g6_IuG"
      },
      "source": [
        "2    1002\n",
        "3    1003\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EzjBy6FO_IuG"
      },
      "source": [
        "- Oh look! The first element has index label `2`. The element with index label `0` is absent from the slice:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yypyQQ8B_IuG"
      },
      "source": [
        "try:\n",
        "    surprise_slice[0]\n",
        "except KeyError as e:\n",
        "    print(\"Key error:\", e)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HWwW9kC7_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ApHcydi8_IuG"
      },
      "source": [
        "Key error: 0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4Wo7sDLA_IuG"
      },
      "source": [
        "- But remember that you can access elements by integer location using the `iloc` attribute. This illustrates another reason why it's always better to use `loc` and `iloc` to access `Series` objects:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cV7Tn946_IuG"
      },
      "source": [
        "surprise_slice.iloc[0]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Nj7UeTjt_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8QogpeJU_IuG"
      },
      "source": [
        "1002"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jujc9FGp_IuG"
      },
      "source": [
        "### Init from `dict`\n",
        "\n",
        "- You can create a `Series` object from a `dict`. The keys will be used as index labels:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eT0VXBDx_IuG"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "weights = {\"alice\": 68, \"bob\": 83, \"colin\": 86, \"darwin\": 68}\n",
        "pd.Series(weights)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9PAA83GQ_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6Owm9ajw_IuG"
      },
      "source": [
        "alice     68\n",
        "bob       83\n",
        "colin     86\n",
        "darwin    68\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qY0yr0Jy_IuG"
      },
      "source": [
        "- You can control which elements you want to include in the `Series` and in what order by explicitly specifying the desired `index`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6mKwf4_H_IuG"
      },
      "source": [
        "pd.Series(weights, index = [\"colin\", \"alice\"])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IYN-Wvw__IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xW3RVcQu_IuG"
      },
      "source": [
        "colin    86\n",
        "alice    68\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Knifp4I-_IuG"
      },
      "source": [
        "### Automatic alignment\n",
        "\n",
        "- When an operation involves multiple `Series` objects, pandas automatically aligns items by matching index labels."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3a48rKD-_IuG"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "s1 = pd.Series([68, 83, 112, 68], index=[\"alice\", \"bob\", \"charles\", \"darwin\"])\n",
        "\n",
        "weights = {\"alice\": 68, \"bob\": 83, \"colin\": 86, \"darwin\": 68}\n",
        "s2 = pd.Series(weights)\n",
        "\n",
        "print(s2.keys())\n",
        "print(s3.keys())\n",
        "\n",
        "s2 + s3"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xD15AmbW_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ATtDqtcM_IuG"
      },
      "source": [
        "alice     136\n",
        "bob       166\n",
        "colin     172\n",
        "darwin    136\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bvq1CLQx_IuG"
      },
      "source": [
        "- The resulting `Series` contains the union of index labels from `s2` and `s3`. Since `\"colin\"` is missing from `s2` and `\"charles\"` is missing from `s3`, these items have a `NaN` result value. (i.e., Not-a-Number means **missing**).\n",
        "- Automatic alignment is very handy when working with data that may come from various sources with varying structure and missing items. But if you forget to set the right index labels, you can have surprising results:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DxxfZbrp_IuG"
      },
      "source": [
        "s1 = pd.Series([68, 83, 112, 68], index=[\"alice\", \"bob\", \"charles\", \"darwin\"])\n",
        "s2 = pd.Series([1000, 1000, 1000, 1000])\n",
        "\n",
        "print(\"s1 = \", s1.values)\n",
        "print(\"s2 = \", s2.values)\n",
        "\n",
        "s1 + s2"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dbUoGkoQ_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dVZYRg9m_IuG"
      },
      "source": [
        "alice     NaN\n",
        "bob       NaN\n",
        "charles   NaN\n",
        "darwin    NaN\n",
        "0         NaN\n",
        "1         NaN\n",
        "2         NaN\n",
        "3         NaN\n",
        "dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zn9nUZvq_IuG"
      },
      "source": [
        "- Pandas could not align the `Series`, since their labels do not match at all, hence the full `NaN` result.\n",
        "\n",
        "#### Init with a scalar\n",
        "\n",
        "- You can also initialize a `Series` object using a scalar and a list of index labels: all items will be set to the scalar."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "32cWenSs_IuG"
      },
      "source": [
        "pd.Series(42, [\"life\", \"universe\", \"everything\"])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rkUKTWBp_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XUr3M9qS_IuG"
      },
      "source": [
        "life          42\n",
        "universe      42\n",
        "everything    42\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f_-2U9L-_IuG"
      },
      "source": [
        "#### `Series` name\n",
        "\n",
        "- A `Series` can have a `name`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DXFvR_vo_IuG"
      },
      "source": [
        "pd.Series([83, 68], index=[\"bob\", \"alice\"], name=\"weights\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AJADlH0e_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Y6mFzdOP_IuG"
      },
      "source": [
        "bob      83\n",
        "alice    68\n",
        "Name: weights, dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MSmQr6RK_IuG"
      },
      "source": [
        "#### Plotting a `Series`\n",
        "\n",
        "- Pandas makes it easy to plot `Series` data using matplotlib (for more details on matplotlib, check out the [matplotlib tutorial](tools_matplotlib.ipynb)). \n",
        "- Just import matplotlib and call the `plot()` method:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CTVBtpu__IuG"
      },
      "source": [
        "%matplotlib inline\n",
        "import matplotlib.pyplot as plt\n",
        "\n",
        "temperatures = [4.4, 5.1, 6.1, 6.2, 6.1, 6.1, 5.7, 5.2, 4.7, 4.1, 3.9, 3.5]\n",
        "s = pd.Series(temperatures, name=\"Temperature\")\n",
        "s.plot()\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZSL0MqQd_IuG"
      },
      "source": [
        "- which outputs:\n",
        "\n",
        "<img src=\"https://github.com/amanchadha/aman-ai/blob/assets/pandas/1.jpg?raw=1\" align=\"center\" style=\"background-color: #fff; margin: 10px auto\" />\n",
        "\n",
        "- There are **many** options for plotting your data. It is not necessary to list them all here: if you need a particular type of plot (histograms, pie charts, etc.), just look for it in the excellent [visualization](http://pandas.pydata.org/pandas-docs/stable/visualization.html) section of pandas' documentation, and look at the example code.\n",
        "\n",
        "## Handling time\n",
        "\n",
        "- Many datasets have timestamps, and pandas is awesome at manipulating such data:\n",
        "\n",
        "* It can represent periods (such as 2016Q3) and frequencies (such as \"monthly\"),\n",
        "* It can convert periods to actual timestamps, and **vice versa**,\n",
        "* It can resample data and aggregate values any way you like,\n",
        "* It can handle timezones.\n",
        "\n",
        "#### Time range\n",
        "\n",
        "- Let's start by creating a time series using `pd.date_range()`. This returns a `DatetimeIndex` containing one datetime per hour for 12 hours starting on October 29th 2016 at 5:30PM."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NgVkZTXW_IuG"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "dates = pd.date_range('2016/10/29 5:30pm', periods=12, freq='H')\n",
        "dates"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_G79HG3x_IuG"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Jeg2Ibmd_IuG"
      },
      "source": [
        "DatetimeIndex(['2016-10-29 17:30:00', '2016-10-29 18:30:00',\n",
        "               '2016-10-29 19:30:00', '2016-10-29 20:30:00',\n",
        "               '2016-10-29 21:30:00', '2016-10-29 22:30:00',\n",
        "               '2016-10-29 23:30:00', '2016-10-30 00:30:00',\n",
        "               '2016-10-30 01:30:00', '2016-10-30 02:30:00',\n",
        "               '2016-10-30 03:30:00', '2016-10-30 04:30:00'],\n",
        "               dtype='datetime64[ns]', freq='H')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jTSS8VMk_IuG"
      },
      "source": [
        "- This `DatetimeIndex` may be used as an index in a `Series`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wSuimxMi_IuG"
      },
      "source": [
        "temperatures = [4.4, 5.1, 6.1, 6.2, 6.1, 6.1, 5.7, 5.2, 4.7, 4.1, 3.9, 3.5]\n",
        "temp_series = pd.Series(temperatures, dates)\n",
        "temp_series"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7xw4ffve_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TV40neRA_IuH"
      },
      "source": [
        "2016-10-29 17:30:00    4.4\n",
        "2016-10-29 18:30:00    5.1\n",
        "2016-10-29 19:30:00    6.1\n",
        "2016-10-29 20:30:00    6.2\n",
        "2016-10-29 21:30:00    6.1\n",
        "2016-10-29 22:30:00    6.1\n",
        "2016-10-29 23:30:00    5.7\n",
        "2016-10-30 00:30:00    5.2\n",
        "2016-10-30 01:30:00    4.7\n",
        "2016-10-30 02:30:00    4.1\n",
        "2016-10-30 03:30:00    3.9\n",
        "2016-10-30 04:30:00    3.5\n",
        "Freq: H, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-NAfITax_IuH"
      },
      "source": [
        "- Let's plot this series:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0ihtDDTX_IuH"
      },
      "source": [
        "import matplotlib.pyplot as plt\n",
        "\n",
        "temp_series.plot(kind=\"bar\")\n",
        "\n",
        "plt.grid(True)\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3KAxw-vZ_IuH"
      },
      "source": [
        "- which outputs:\n",
        "\n",
        "<img src=\"https://github.com/amanchadha/aman-ai/blob/assets/pandas/2.png?raw=1\" align=\"center\" style=\"background-color: #fff; margin: 10px auto\" />\n",
        "\n",
        "### Resampling\n",
        "\n",
        "- Pandas lets us resample a time series very simply. Just call the `resample()` method and specify a new frequency:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2RUJWXUJ_IuH"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "temp_series = pd.Series({pd.Timestamp('2016-10-29 17:30:00', freq='H'): 4.4, pd.Timestamp('2016-10-29 18:30:00', freq='H'): 5.1, pd.Timestamp('2016-10-29 19:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 20:30:00', freq='H'): 6.2, pd.Timestamp('2016-10-29 21:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 22:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 23:30:00', freq='H'): 5.7, pd.Timestamp('2016-10-30 00:30:00', freq='H'): 5.2, pd.Timestamp('2016-10-30 01:30:00', freq='H'): 4.7, pd.Timestamp('2016-10-30 02:30:00', freq='H'): 4.1, pd.Timestamp('2016-10-30 03:30:00', freq='H'): 3.9, pd.Timestamp('2016-10-30 04:30:00', freq='H'): 3.5})\n",
        "\n",
        "temp_series_freq_2H = temp_series.resample(\"2H\")\n",
        "temp_series_freq_2H"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BOmP3EtY_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cU-60Cr2_IuH"
      },
      "source": [
        "<pandas.core.resample.DatetimeIndexResampler object at [address]>"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RpijKABO_IuH"
      },
      "source": [
        "- The resampling operation is actually a deferred operation, which is why we did not get a `Series` object, but a `DatetimeIndexResampler` object instead. To actually perform the resampling operation, we can simply call the `mean()` method: Pandas will compute the mean of every pair of consecutive hours:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7wxLkh1M_IuH"
      },
      "source": [
        "temp_series_freq_2H = temp_series_freq_2H.mean()\n",
        "temp_series_freq_2H"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qofjFGcQ_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7F0eesf4_IuH"
      },
      "source": [
        "2016-10-29 16:00:00    4.40\n",
        "2016-10-29 18:00:00    5.60\n",
        "2016-10-29 20:00:00    6.15\n",
        "2016-10-29 22:00:00    5.90\n",
        "2016-10-30 00:00:00    4.95\n",
        "2016-10-30 02:00:00    4.00\n",
        "2016-10-30 04:00:00    3.50\n",
        "Freq: 2H, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0jT24uls_IuH"
      },
      "source": [
        "- Let's plot the result:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RgzrUDPb_IuH"
      },
      "source": [
        "temp_series_freq_2H.plot(kind=\"bar\")\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gVSF3yrh_IuH"
      },
      "source": [
        "- which outputs:\n",
        "\n",
        "<img src=\"https://github.com/amanchadha/aman-ai/blob/assets/pandas/3.png?raw=1\" align=\"center\" style=\"background-color: #fff; margin: 10px auto\" />\n",
        "\n",
        "- Note how the values have automatically been aggregated into 2-hour periods. If we look at the 6-8PM period, for example, we had a value of `5.1` at 6:30PM, and `6.1` at 7:30PM. After resampling, we just have one value of `5.6`, which is the mean of `5.1` and `6.1`. Rather than computing the mean, we could have used any other aggregation function, for example we can decide to keep the minimum value of each period:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PfnNXef4_IuH"
      },
      "source": [
        "temp_series_freq_2H = temp_series.resample(\"2H\").min()\n",
        "temp_series_freq_2H"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s_IoCUbW_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NsPpVBEb_IuH"
      },
      "source": [
        "2016-10-29 16:00:00    4.4\n",
        "2016-10-29 18:00:00    5.1\n",
        "2016-10-29 20:00:00    6.1\n",
        "2016-10-29 22:00:00    5.7\n",
        "2016-10-30 00:00:00    4.7\n",
        "2016-10-30 02:00:00    3.9\n",
        "2016-10-30 04:00:00    3.5\n",
        "Freq: 2H, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "z788z8Jx_IuH"
      },
      "source": [
        "- Or, equivalently, we could use the `apply()` method instead:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L1ZnXc_h_IuH"
      },
      "source": [
        "import numpy as np\n",
        "\n",
        "temp_series_freq_2H = temp_series.resample(\"2H\").apply(np.min)\n",
        "temp_series_freq_2H"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zwxLO-3Q_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6u0DomA3_IuH"
      },
      "source": [
        "2016-10-29 16:00:00    4.4\n",
        "2016-10-29 18:00:00    5.1\n",
        "2016-10-29 20:00:00    6.1\n",
        "2016-10-29 22:00:00    5.7\n",
        "2016-10-30 00:00:00    4.7\n",
        "2016-10-30 02:00:00    3.9\n",
        "2016-10-30 04:00:00    3.5\n",
        "Freq: 2H, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DPm4jP8s_IuH"
      },
      "source": [
        "### Upsampling and interpolation\n",
        "\n",
        "- The [above](#resampling) was an example of downsampling. We can also upsample (i.e., increase the frequency), but this creates holes in our data:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rOh3_Cog_IuH"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "temp_series = pd.Series({pd.Timestamp('2016-10-29 17:30:00', freq='H'): 4.4, pd.Timestamp('2016-10-29 18:30:00', freq='H'): 5.1, pd.Timestamp('2016-10-29 19:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 20:30:00', freq='H'): 6.2, pd.Timestamp('2016-10-29 21:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 22:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 23:30:00', freq='H'): 5.7, pd.Timestamp('2016-10-30 00:30:00', freq='H'): 5.2, pd.Timestamp('2016-10-30 01:30:00', freq='H'): 4.7, pd.Timestamp('2016-10-30 02:30:00', freq='H'): 4.1, pd.Timestamp('2016-10-30 03:30:00', freq='H'): 3.9, pd.Timestamp('2016-10-30 04:30:00', freq='H'): 3.5})\n",
        "\n",
        "temp_series_freq_15min = temp_series.resample(\"15Min\").mean()\n",
        "temp_series_freq_15min.head(n=10) # `head` displays the top n values"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KtHAb8Af_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KFBPnHDj_IuH"
      },
      "source": [
        "2016-10-29 17:30:00    4.4\n",
        "2016-10-29 17:45:00    NaN\n",
        "2016-10-29 18:00:00    NaN\n",
        "2016-10-29 18:15:00    NaN\n",
        "2016-10-29 18:30:00    5.1\n",
        "2016-10-29 18:45:00    NaN\n",
        "2016-10-29 19:00:00    NaN\n",
        "2016-10-29 19:15:00    NaN\n",
        "2016-10-29 19:30:00    6.1\n",
        "2016-10-29 19:45:00    NaN\n",
        "Freq: 15T, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zgnm05VS_IuH"
      },
      "source": [
        "- One solution is to fill the gaps by interpolating. We just call the `interpolate()` method. The default is to use linear interpolation, but we can also select another method, such as cubic interpolation:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9ZKjp1kO_IuH"
      },
      "source": [
        "temp_series_freq_15min = temp_series.resample(\"15Min\").interpolate(method=\"cubic\")\n",
        "temp_series_freq_15min.head(n=10)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rjnPeOhX_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DrZZ-yDo_IuH"
      },
      "source": [
        "2016-10-29 17:30:00    4.400000\n",
        "2016-10-29 17:45:00    4.452911\n",
        "2016-10-29 18:00:00    4.605113\n",
        "2016-10-29 18:15:00    4.829758\n",
        "2016-10-29 18:30:00    5.100000\n",
        "2016-10-29 18:45:00    5.388992\n",
        "2016-10-29 19:00:00    5.669887\n",
        "2016-10-29 19:15:00    5.915839\n",
        "2016-10-29 19:30:00    6.100000\n",
        "2016-10-29 19:45:00    6.203621\n",
        "Freq: 15T, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7Fv8shMx_IuH"
      },
      "source": [
        "- Plotting the data:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4eyLTu66_IuH"
      },
      "source": [
        "temp_series.plot(label=\"Period: 1 hour\")\n",
        "temp_series_freq_15min.plot(label=\"Period: 15 minutes\")\n",
        "plt.legend()\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hKR1gXdz_IuH"
      },
      "source": [
        "- which outputs:\n",
        "\n",
        "<img src=\"https://github.com/amanchadha/aman-ai/blob/assets/pandas/4.jpg?raw=1\" align=\"center\" style=\"background-color: #fff; margin: 10px auto\" />\n",
        "\n",
        "### Timezones\n",
        "\n",
        "- By default datetimes are **naive**: they are not aware of timezones, so 2016-10-30 02:30 might mean October 30th 2016 at 2:30am in Paris or in New York. We can make datetimes timezone **aware** by calling the `tz_localize()` method:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "g2MFy38O_IuH"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "temp_series_ny = pd.Series({pd.Timestamp('2016-10-29 17:30:00', freq='H'): 4.4, pd.Timestamp('2016-10-29 18:30:00', freq='H'): 5.1, pd.Timestamp('2016-10-29 19:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 20:30:00', freq='H'): 6.2, pd.Timestamp('2016-10-29 21:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 22:30:00', freq='H'): 6.1, pd.Timestamp('2016-10-29 23:30:00', freq='H'): 5.7, pd.Timestamp('2016-10-30 00:30:00', freq='H'): 5.2, pd.Timestamp('2016-10-30 01:30:00', freq='H'): 4.7, pd.Timestamp('2016-10-30 02:30:00', freq='H'): 4.1, pd.Timestamp('2016-10-30 03:30:00', freq='H'): 3.9, pd.Timestamp('2016-10-30 04:30:00', freq='H'): 3.5})\n",
        "\n",
        "temp_series_ny = temp_series.tz_localize(\"America/New_York\")\n",
        "temp_series_ny"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zEu6Ku9Z_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SBTNZROM_IuH"
      },
      "source": [
        "2016-10-29 17:30:00-04:00    4.4\n",
        "2016-10-29 18:30:00-04:00    5.1\n",
        "2016-10-29 19:30:00-04:00    6.1\n",
        "2016-10-29 20:30:00-04:00    6.2\n",
        "2016-10-29 21:30:00-04:00    6.1\n",
        "2016-10-29 22:30:00-04:00    6.1\n",
        "2016-10-29 23:30:00-04:00    5.7\n",
        "2016-10-30 00:30:00-04:00    5.2\n",
        "2016-10-30 01:30:00-04:00    4.7\n",
        "2016-10-30 02:30:00-04:00    4.1\n",
        "2016-10-30 03:30:00-04:00    3.9\n",
        "2016-10-30 04:30:00-04:00    3.5\n",
        "Freq: H, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Pzvb-L9c_IuH"
      },
      "source": [
        "- Note that `-04:00` is now appended to all the datetimes. This means that these datetimes refer to [UTC](https://en.wikipedia.org/wiki/Coordinated_Universal_Time) - 4 hours.\n",
        "- We can convert these datetimes to Paris time like this:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fLIMW5rR_IuH"
      },
      "source": [
        "temp_series_paris = temp_series_ny.tz_convert(\"Europe/Paris\")\n",
        "temp_series_paris"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gBsBizzg_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Nc3zci67_IuH"
      },
      "source": [
        "2016-10-29 23:30:00+02:00    4.4\n",
        "2016-10-30 00:30:00+02:00    5.1\n",
        "2016-10-30 01:30:00+02:00    6.1\n",
        "2016-10-30 02:30:00+02:00    6.2\n",
        "2016-10-30 02:30:00+01:00    6.1\n",
        "2016-10-30 03:30:00+01:00    6.1\n",
        "2016-10-30 04:30:00+01:00    5.7\n",
        "2016-10-30 05:30:00+01:00    5.2\n",
        "2016-10-30 06:30:00+01:00    4.7\n",
        "2016-10-30 07:30:00+01:00    4.1\n",
        "2016-10-30 08:30:00+01:00    3.9\n",
        "2016-10-30 09:30:00+01:00    3.5\n",
        "Freq: H, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4OdOPZD2_IuH"
      },
      "source": [
        "- You may have noticed that the UTC offset changes from `+02:00` to `+01:00`: this is because France switches to winter time at 3am that particular night (time goes back to 2am). Notice that 2:30am occurs twice! Let's go back to a naive representation (if you log some data hourly using local time, without storing the timezone, you might get something like this):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B47LSmPN_IuH"
      },
      "source": [
        "temp_series_paris_naive = temp_series_paris.tz_localize(None)\n",
        "temp_series_paris_naive"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "o7n1rGNE_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MUqtNdix_IuH"
      },
      "source": [
        "2016-10-29 23:30:00    4.4\n",
        "2016-10-30 00:30:00    5.1\n",
        "2016-10-30 01:30:00    6.1\n",
        "2016-10-30 02:30:00    6.2\n",
        "2016-10-30 02:30:00    6.1\n",
        "2016-10-30 03:30:00    6.1\n",
        "2016-10-30 04:30:00    5.7\n",
        "2016-10-30 05:30:00    5.2\n",
        "2016-10-30 06:30:00    4.7\n",
        "2016-10-30 07:30:00    4.1\n",
        "2016-10-30 08:30:00    3.9\n",
        "2016-10-30 09:30:00    3.5\n",
        "Freq: H, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i8cNJgMX_IuH"
      },
      "source": [
        "- Now `02:30` is really ambiguous. If we try to localize these naive datetimes to the Paris timezone, we get an error:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dZ6qA0VE_IuH"
      },
      "source": [
        "try:\n",
        "    temp_series_paris_naive.tz_localize(\"Europe/Paris\")\n",
        "except Exception as e:\n",
        "    print(type(e))\n",
        "    print(e)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xOAo2f0I_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3W-umaTn_IuH"
      },
      "source": [
        "<class 'pytz.exceptions.AmbiguousTimeError'>\n",
        "Cannot infer dst time from %r, try using the 'ambiguous' argument"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GjXrBiCI_IuH"
      },
      "source": [
        "- Fortunately using the `ambiguous` argument we can tell pandas to infer the right DST (Daylight Saving Time) based on the order of the ambiguous timestamps:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RuhYR8jB_IuH"
      },
      "source": [
        "temp_series_paris_naive.tz_localize(\"Europe/Paris\", ambiguous=\"infer\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "My1c6xr0_IuH"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "H_5eFDBt_IuH"
      },
      "source": [
        "2016-10-29 23:30:00+02:00    4.4\n",
        "2016-10-30 00:30:00+02:00    5.1\n",
        "2016-10-30 01:30:00+02:00    6.1\n",
        "2016-10-30 02:30:00+02:00    6.2\n",
        "2016-10-30 02:30:00+01:00    6.1\n",
        "2016-10-30 03:30:00+01:00    6.1\n",
        "2016-10-30 04:30:00+01:00    5.7\n",
        "2016-10-30 05:30:00+01:00    5.2\n",
        "2016-10-30 06:30:00+01:00    4.7\n",
        "2016-10-30 07:30:00+01:00    4.1\n",
        "2016-10-30 08:30:00+01:00    3.9\n",
        "2016-10-30 09:30:00+01:00    3.5\n",
        "Freq: H, dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3AK4hXX__IuI"
      },
      "source": [
        "### Periods\n",
        "\n",
        "- The `pd.period_range()` function returns a `PeriodIndex` instead of a `DatetimeIndex`. For example, let's get all quarters in 2016 and 2017:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GcfTGvQb_IuI"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "quarters = pd.period_range('2016Q1', periods=8, freq='Q')\n",
        "quarters"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5H0RS2XZ_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z2HLgUAj_IuI"
      },
      "source": [
        "PeriodIndex(['2016Q1', '2016Q2', '2016Q3', '2016Q4', '2017Q1', '2017Q2',\n",
        "             '2017Q3', '2017Q4'],\n",
        "             dtype='period[Q-DEC]', freq='Q-DEC')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "l7-kNVcu_IuI"
      },
      "source": [
        "- Adding a number `N` to a `PeriodIndex` shifts the periods by `N` times the `PeriodIndex`'s frequency:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WMFYlS_Z_IuI"
      },
      "source": [
        "quarters + 3"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sxAMpbNL_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gcbpD44h_IuI"
      },
      "source": [
        "PeriodIndex(['2016Q4', '2017Q1', '2017Q2', '2017Q3', '2017Q4', '2018Q1',\n",
        "             '2018Q2', '2018Q3'],\n",
        "             dtype='period[Q-DEC]', freq='Q-DEC')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mtGBpxQZ_IuI"
      },
      "source": [
        "- The `asfreq()` method lets us change the frequency of the `PeriodIndex`. All periods are lengthened or shortened accordingly. For example, let's convert all the quarterly periods to monthly periods (zooming in):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PngnU7lq_IuI"
      },
      "source": [
        "quarters.asfreq(\"M\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tBGw2O30_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ju2zDC2f_IuI"
      },
      "source": [
        "PeriodIndex(['2016-03', '2016-06', '2016-09', '2016-12', '2017-03', '2017-06',\n",
        "             '2017-09', '2017-12'],\n",
        "             dtype='period[M]', freq='M')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9-QcQv1F_IuI"
      },
      "source": [
        "- By default, the `asfreq` zooms on the end of each period. We can tell it to zoom on the start of each period instead:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_R5tzqcL_IuI"
      },
      "source": [
        "quarters.asfreq(\"M\", how=\"start\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pY4F_VVa_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NZY6GwFs_IuI"
      },
      "source": [
        "PeriodIndex(['2016-01', '2016-04', '2016-07', '2016-10', '2017-01', '2017-04',\n",
        "             '2017-07', '2017-10'],\n",
        "             dtype='period[M]', freq='M')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IRDJJTuT_IuI"
      },
      "source": [
        "- And we can zoom out:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O33GnKja_IuI"
      },
      "source": [
        "quarters.asfreq(\"A\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tWLla4Fx_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9eBHffgf_IuI"
      },
      "source": [
        "PeriodIndex(['2016', '2016', '2016', '2016', '2017', '2017', '2017', '2017'], dtype='period[A-DEC]', freq='A-DEC')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2DYRCHvk_IuI"
      },
      "source": [
        "- We can create a `Series` with a `PeriodIndex`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UTnCvTxi_IuI"
      },
      "source": [
        "quarterly_revenue = pd.Series([300, 320, 290, 390, 320, 360, 310, 410], index = quarters)\n",
        "quarterly_revenue"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "O3FM_T6m_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TxlKtSiV_IuI"
      },
      "source": [
        "2016Q1    300\n",
        "2016Q2    320\n",
        "2016Q3    290\n",
        "2016Q4    390\n",
        "2017Q1    320\n",
        "2017Q2    360\n",
        "2017Q3    310\n",
        "2017Q4    410"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cRR8nlbT_IuI"
      },
      "source": [
        "- Plotting this data:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EIFDfFKR_IuI"
      },
      "source": [
        "quarterly_revenue.plot(kind=\"line\")\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-igd9v3Y_IuI"
      },
      "source": [
        "- which outputs:\n",
        "\n",
        "<img src=\"https://github.com/amanchadha/aman-ai/blob/assets/pandas/5.jpg?raw=1\" align=\"center\" style=\"background-color: #fff; margin: 10px auto\" />\n",
        "\n",
        "- We can convert periods to timestamps by calling `to_timestamp`. By default this will give us the first day of each period, but by setting `how` and `freq`, we can get the last hour of each period:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "q6XjG9cx_IuI"
      },
      "source": [
        "last_hours = quarterly_revenue.to_pd.Timestamp(how=\"end\", freq=\"H\")\n",
        "last_hours"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "N2ULzdvf_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "avN2G3Ml_IuI"
      },
      "source": [
        "2016-03-31 23:59:59.999999999    300\n",
        "2016-06-30 23:59:59.999999999    320\n",
        "2016-09-30 23:59:59.999999999    290\n",
        "2016-12-31 23:59:59.999999999    390\n",
        "2017-03-31 23:59:59.999999999    320\n",
        "2017-06-30 23:59:59.999999999    360\n",
        "2017-09-30 23:59:59.999999999    310\n",
        "2017-12-31 23:59:59.999999999    410\n",
        "Freq: Q-DEC, dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FfYT28NM_IuI"
      },
      "source": [
        "- And back to periods by calling `to_period`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FQeGeGH4_IuI"
      },
      "source": [
        "last_hours.to_period()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UZsGKFPi_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IhN-Hv_y_IuI"
      },
      "source": [
        "2016Q1    300\n",
        "2016Q2    320\n",
        "2016Q3    290\n",
        "2016Q4    390\n",
        "2017Q1    320\n",
        "2017Q2    360\n",
        "2017Q3    310\n",
        "2017Q4    410\n",
        "Freq: Q-DEC, dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6WunjeBV_IuI"
      },
      "source": [
        "- Pandas also provides many other time-related functions that we recommend you check out in the [documentation](http://pandas.pydata.org/pandas-docs/stable/timeseries.html). To whet your appetite, here is one way to get the last business day of each month in 2016, at 9am:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lj1ZLjDc_IuI"
      },
      "source": [
        "months_2016 = pd.period_range(\"2016\", periods=12, freq=\"M\")\n",
        "one_day_after_last_days = months_2016.asfreq(\"D\") + 1\n",
        "last_bdays = one_day_after_last_days.to_pd.Timestamp() - pd.tseries.offsets.BDay()\n",
        "last_bdays.to_period(\"H\") + 9"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fnHc1ZM3_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tNMwg1PX_IuI"
      },
      "source": [
        "PeriodIndex(['2016-01-29 09:00', '2016-02-29 09:00', '2016-03-31 09:00',\n",
        "             '2016-04-29 09:00', '2016-05-31 09:00', '2016-06-30 09:00',\n",
        "             '2016-07-29 09:00', '2016-08-31 09:00', '2016-09-30 09:00',\n",
        "             '2016-10-31 09:00', '2016-11-30 09:00', '2016-12-30 09:00'],\n",
        "            dtype='period[H]', freq='H')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "isdL4AQk_IuI"
      },
      "source": [
        "## `DataFrame` objects\n",
        "\n",
        "- A DataFrame object represents a spreadsheet, with cell values, column names and row index labels. You can define expressions to compute columns based on other columns, create pivot-tables, group rows, draw graphs, etc. You can see `DataFrame`s as dictionaries of `Series`.\n",
        "\n",
        "### Creating a `DataFrame`\n",
        "\n",
        "- You can create a DataFrame by passing a dictionary of `Series` objects:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bqEuxrCD_IuI"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "people_dict = {\n",
        "    \"weight\": pd.Series([68, 83, 112], index=[\"alice\", \"bob\", \"charles\"]),\n",
        "    \"birthyear\": pd.Series([1984, 1985, 1992], index=[\"bob\", \"alice\", \"charles\"], name=\"year\"),\n",
        "    \"children\": pd.Series([0, 3], index=[\"charles\", \"bob\"]),\n",
        "    \"hobby\": pd.Series([\"Biking\", \"Dancing\"], index=[\"alice\", \"bob\"]),\n",
        "}\n",
        "\n",
        "people = pd.DataFrame(people_dict)\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mxPsgy0S_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CRnewl-I_IuI"
      },
      "source": [
        "         weight  birthyear  children    hobby\n",
        "alice        68       1985       NaN   Biking\n",
        "bob          83       1984       3.0  Dancing\n",
        "charles     112       1992       0.0      NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FvaT7nDn_IuI"
      },
      "source": [
        "- A few things to note:\n",
        "\n",
        "* The `Series` were automatically aligned based on their index,\n",
        "* Missing values are represented as `NaN`,\n",
        "* `Series` names are ignored (the name `\"year\"` was dropped),\n",
        "* `DataFrame`s are displayed nicely in Jupyter notebooks, woohoo!\n",
        "\n",
        "- You can access columns pretty much as you would expect. They are returned as `Series` objects:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lZzocxye_IuI"
      },
      "source": [
        "people[\"birthyear\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p9kn74-R_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6B6cJmCz_IuI"
      },
      "source": [
        "alice      1985\n",
        "bob        1984\n",
        "charles    1992\n",
        "Name: birthyear, dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1OUGt5Al_IuI"
      },
      "source": [
        "- You can also get multiple columns at once:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "G6CU1sre_IuI"
      },
      "source": [
        "people[[\"birthyear\", \"hobby\"]]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "w40pEG5t_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Tvz8Nx5h_IuI"
      },
      "source": [
        "         birthyear    hobby\n",
        "alice         1985   Biking\n",
        "bob           1984  Dancing\n",
        "charles       1992      NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a88iN5us_IuI"
      },
      "source": [
        "- If you pass a list of columns and/or index row labels to the `DataFrame` constructor, it will guarantee that these columns and/or rows will exist, in that order, and no other column/row will exist. For example:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RPO5LA_E_IuI"
      },
      "source": [
        "d2 = pd.DataFrame(\n",
        "        people_dict,\n",
        "        columns=[\"birthyear\", \"weight\", \"height\"],\n",
        "        index=[\"bob\", \"alice\", \"eugene\"]\n",
        "     )\n",
        "d2"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5rc30Xo__IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "q7_lQ5Kx_IuI"
      },
      "source": [
        "        birthyear  weight height\n",
        "bob        1984.0    83.0    NaN\n",
        "alice      1985.0    68.0    NaN\n",
        "eugene        NaN     NaN    NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZN5TH50S_IuI"
      },
      "source": [
        "- Another convenient way to create a `DataFrame` is to pass all the values to the constructor as an `ndarray`, or a list of lists, and specify the column names and row index labels separately:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RIZDkWHJ_IuI"
      },
      "source": [
        "import numpy as np\n",
        "\n",
        "values = [\n",
        "            [1985, np.nan, \"Biking\",   68],\n",
        "            [1984, 3,      \"Dancing\",  83],\n",
        "            [1992, 0,      np.nan,    112]\n",
        "         ]\n",
        "d3 = pd.DataFrame(\n",
        "        values,\n",
        "        columns=[\"birthyear\", \"children\", \"hobby\", \"weight\"],\n",
        "        index=[\"alice\", \"bob\", \"charles\"]\n",
        "     )\n",
        "d3"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "x5DJs9Gh_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9-TfH7dn_IuI"
      },
      "source": [
        "         birthyear  children    hobby  weight\n",
        "alice         1985       NaN   Biking      68\n",
        "bob           1984       3.0  Dancing      83\n",
        "charles       1992       0.0      NaN     112"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1z0RT0bg_IuI"
      },
      "source": [
        "- To specify missing values, you can either use `np.nan` or NumPy's masked arrays:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4gV6Lanc_IuI"
      },
      "source": [
        "masked_array = np.ma.asarray(values, dtype=np.object)\n",
        "masked_array[(0, 2), (1, 2)] = np.ma.masked\n",
        "d3 = pd.DataFrame(\n",
        "        masked_array,\n",
        "        columns=[\"birthyear\", \"children\", \"hobby\", \"weight\"],\n",
        "        index=[\"alice\", \"bob\", \"charles\"]\n",
        "     )\n",
        "d3"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jqO8408t_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "z1Vsvi2n_IuI"
      },
      "source": [
        "        birthyear children    hobby weight\n",
        "alice        1985      NaN   Biking     68\n",
        "bob          1984        3  Dancing     83\n",
        "charles      1992        0      NaN    112"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y7o_HhAv_IuI"
      },
      "source": [
        "- Instead of an `ndarray`, you can also pass a `DataFrame` object:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "koTKIgep_IuI"
      },
      "source": [
        "d4 = pd.DataFrame(\n",
        "         d3,\n",
        "         columns=[\"hobby\", \"children\"],\n",
        "         index=[\"alice\", \"bob\"]\n",
        "     )\n",
        "d4"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1avbKoTR_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nQQpmNY__IuI"
      },
      "source": [
        "         hobby children\n",
        "alice   Biking      NaN\n",
        "bob    Dancing        3"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gi4swhQt_IuI"
      },
      "source": [
        "- It is also possible to create a `DataFrame` with a dictionary (or list) of dictionaries (or list):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SszfTbwS_IuI"
      },
      "source": [
        "people = pd.DataFrame({\n",
        "    \"birthyear\": {\"alice\":1985, \"bob\": 1984, \"charles\": 1992},\n",
        "    \"hobby\": {\"alice\":\"Biking\", \"bob\": \"Dancing\"},\n",
        "    \"weight\": {\"alice\":68, \"bob\": 83, \"charles\": 112},\n",
        "    \"children\": {\"bob\": 3, \"charles\": 0}\n",
        "})\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HST1FXZ2_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Z4ebxk8Y_IuI"
      },
      "source": [
        "         birthyear    hobby  weight  children\n",
        "alice         1985   Biking      68       NaN\n",
        "bob           1984  Dancing      83       3.0\n",
        "charles       1992      NaN     112       0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gMcuErrx_IuI"
      },
      "source": [
        "### Multi-indexing\n",
        "\n",
        "- If all columns are tuples of the same size, then they are understood as a multi-index. The same goes for row index labels. For example:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "boI6rRJE_IuI"
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "\n",
        "d5 = pd.DataFrame(\n",
        "  {\n",
        "    (\"public\", \"birthyear\"):\n",
        "        {(\"Paris\",\"alice\"):1985, (\"Paris\",\"bob\"): 1984, (\"London\",\"charles\"): 1992},\n",
        "    (\"public\", \"hobby\"):\n",
        "        {(\"Paris\",\"alice\"):\"Biking\", (\"Paris\",\"bob\"): \"Dancing\"},\n",
        "    (\"private\", \"weight\"):\n",
        "        {(\"Paris\",\"alice\"):68, (\"Paris\",\"bob\"): 83, (\"London\",\"charles\"): 112},\n",
        "    (\"private\", \"children\"):\n",
        "        {(\"Paris\", \"alice\"):np.nan, (\"Paris\",\"bob\"): 3, (\"London\",\"charles\"): 0}\n",
        "  }\n",
        ")\n",
        "d5"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DN95kUqo_IuI"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FvveDQhY_IuI"
      },
      "source": [
        "                  public          private\n",
        "               birthyear    hobby  weight children\n",
        "Paris  alice        1985   Biking      68      NaN\n",
        "       bob          1984  Dancing      83      3.0\n",
        "London charles      1992      NaN     112      0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gyBwUqAB_IuI"
      },
      "source": [
        "- You can now get a `DataFrame` containing all the `\"public\"` columns by simply doing:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WyMd7hpx_IuI"
      },
      "source": [
        "d5[\"public\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CjinEscq_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wKXNozIU_IuK"
      },
      "source": [
        "                birthyear    hobby\n",
        "Paris  alice         1985   Biking\n",
        "       bob           1984  Dancing\n",
        "London charles       1992      NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sWFC8aet_IuK"
      },
      "source": [
        "- You can also now get a `DataFrame` containing all the `\"hobby\"` columns within the `\"public\"` columns by simply doing:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "THkQyWZA_IuK"
      },
      "source": [
        "d5[\"public\", \"hobby\"] # Same result as d5[\"public\"][\"hobby\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1CbYQP_K_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UNvIE-5b_IuK"
      },
      "source": [
        "Paris   alice       Biking\n",
        "        bob        Dancing\n",
        "London  charles        NaN\n",
        "Name: (public, hobby), dtype: object"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qVADV9n9_IuK"
      },
      "source": [
        "### Dropping a level\n",
        "\n",
        "- Let's look at `d5` again:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wgjZis5Y_IuK"
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "\n",
        "d5 = pd.DataFrame({('public', 'birthyear'): {('Paris', 'alice'): 1985, ('Paris', 'bob'): 1984, ('London', 'charles'): 1992}, ('public', 'hobby'): {('Paris', 'alice'): 'Biking', ('Paris', 'bob'): 'Dancing', ('London', 'charles'): np.nan}, ('private', 'weight'): {('Paris', 'alice'): 68, ('Paris', 'bob'): 83, ('London', 'charles'): 112}, ('private', 'children'): {('Paris', 'alice'): np.nan, ('Paris', 'bob'): 3.0, ('London', 'charles'): 0.0}})"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n16xTvNk_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sGKYwyr7_IuK"
      },
      "source": [
        "                  public          private\n",
        "               birthyear    hobby  weight children\n",
        "Paris  alice        1985   Biking      68      NaN\n",
        "       bob          1984  Dancing      83      3.0\n",
        "London charles      1992      NaN     112      0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1TTUiZJ5_IuK"
      },
      "source": [
        "- There are two levels of columns, and two levels of indices. We can drop a column level by calling `droplevel()` (the same goes for indices):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XhFpeoL9_IuK"
      },
      "source": [
        "d5.columns = d5.columns.droplevel(level = 0)\n",
        "d5"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ax4wfD-r_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bC0lfIhH_IuK"
      },
      "source": [
        "                birthyear    hobby  weight  children\n",
        "Paris  alice         1985   Biking      68       NaN\n",
        "       bob           1984  Dancing      83       3.0\n",
        "London charles       1992      NaN     112       0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GlxDT5Wv_IuK"
      },
      "source": [
        "### Transposing\n",
        "\n",
        "- You can swap columns and indices using the `T` attribute:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zjkXaL0s_IuK"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "d5 = pd.DataFrame({'birthyear': {('Paris', 'alice'): 1985, ('Paris', 'bob'): 1984, ('London', 'charles'): 1992}, 'hobby': {('Paris', 'alice'): 'Biking', ('Paris', 'bob'): 'Dancing', ('London', 'charles'): nan}, 'weight': {('Paris', 'alice'): 68, ('Paris', 'bob'): 83, ('London', 'charles'): 112}, 'children': {('Paris', 'alice'): nan, ('Paris', 'bob'): 3.0, ('London', 'charles'): 0.0}})\n",
        "\n",
        "d6 = d5.T\n",
        "d6"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Lnl633hd_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "G7AwYXnT_IuK"
      },
      "source": [
        "            Paris           London\n",
        "            alice      bob charles\n",
        "birthyear    1985     1984    1992\n",
        "hobby      Biking  Dancing     NaN\n",
        "weight         68       83     112\n",
        "children      NaN        3       0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WBwx3eYz_IuK"
      },
      "source": [
        "### Stacking and unstacking levels\n",
        "\n",
        "- Calling the `stack()` method will push the lowest column level after the lowest index:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TET9El8H_IuK"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "d6 = pd.DataFrame({('Paris', 'alice'): {'birthyear': 1985, 'hobby': 'Biking', 'weight': 68, 'children': nan}, ('Paris', 'bob'): {'birthyear': 1984, 'hobby': 'Dancing', 'weight': 83, 'children': 3.0}, ('London', 'charles'): {'birthyear': 1992, 'hobby': nan, 'weight': 112, 'children': 0.0}})\n",
        "\n",
        "d7 = d6.stack()\n",
        "d7"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ivu1Rat5_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "imAGOeMZ_IuK"
      },
      "source": [
        "                  London    Paris\n",
        "birthyear alice      NaN     1985\n",
        "          bob        NaN     1984\n",
        "          charles   1992      NaN\n",
        "hobby     alice      NaN   Biking\n",
        "          bob        NaN  Dancing\n",
        "weight    alice      NaN       68\n",
        "          bob        NaN       83\n",
        "          charles    112      NaN\n",
        "children  bob        NaN        3\n",
        "          charles      0      NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2ULyTzd5_IuK"
      },
      "source": [
        "- Note that many `NaN` values appeared. This makes sense because many new combinations did not exist before (eg. there was no `bob` in `London`).\n",
        "- Calling `unstack()` will do the reverse, once again creating many `NaN` values."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ItP2rIyi_IuK"
      },
      "source": [
        "d8 = d7.unstack()\n",
        "d8"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uGXTSpQ4_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sIHYps8A_IuK"
      },
      "source": [
        "          London                Paris\n",
        "           alice  bob charles   alice      bob charles\n",
        "birthyear    NaN  NaN    1992    1985     1984     NaN\n",
        "hobby        NaN  NaN     NaN  Biking  Dancing     NaN\n",
        "weight       NaN  NaN     112      68       83     NaN\n",
        "children     NaN  NaN       0     NaN        3     NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dz0agyUw_IuK"
      },
      "source": [
        "- If we call `unstack` again, we end up with a `Series` object:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kfHvu8Tu_IuK"
      },
      "source": [
        "d9 = d8.unstack()\n",
        "d9"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4_Ehltfp_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1qyX6TDe_IuK"
      },
      "source": [
        "London  alice    birthyear        NaN\n",
        "                 hobby            NaN\n",
        "                 weight           NaN\n",
        "                 children         NaN\n",
        "        bob      birthyear        NaN\n",
        "                 hobby            NaN\n",
        "                 weight           NaN\n",
        "                 children         NaN\n",
        "        charles  birthyear       1992\n",
        "                 hobby            NaN\n",
        "                 weight           112\n",
        "                 children           0\n",
        "Paris   alice    birthyear       1985\n",
        "                 hobby         Biking\n",
        "                 weight            68\n",
        "                 children         NaN\n",
        "        bob      birthyear       1984\n",
        "                 hobby        Dancing\n",
        "                 weight            83\n",
        "                 children           3\n",
        "        charles  birthyear        NaN\n",
        "                 hobby            NaN\n",
        "                 weight           NaN\n",
        "                 children         NaN\n",
        "dtype: object"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8n2AVJQK_IuK"
      },
      "source": [
        "- The `stack()` and `unstack()` methods let you select the `level` to stack/unstack. You can even stack/unstack multiple levels at once:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6O5SRu3y_IuK"
      },
      "source": [
        "d10 = d9.unstack(level = (0,1))\n",
        "d10"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VLrWgSHZ_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p21h5na2_IuK"
      },
      "source": [
        "          London                Paris\n",
        "           alice  bob charles   alice      bob charles\n",
        "birthyear    NaN  NaN    1992    1985     1984     NaN\n",
        "hobby        NaN  NaN     NaN  Biking  Dancing     NaN\n",
        "weight       NaN  NaN     112      68       83     NaN\n",
        "children     NaN  NaN       0     NaN        3     NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e4ydBhAq_IuK"
      },
      "source": [
        "### Most methods return modified copies\n",
        "\n",
        "- As you may have noticed, the `stack()` and `unstack()` methods do not modify the object they apply to. Instead, they work on a copy and return that copy. This is true of most methods in pandas.\n",
        "\n",
        "### Accessing rows\n",
        "\n",
        "- Let's go back to the `people` `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wGdycCQT_IuK"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "people_dict = {\n",
        "    \"birthyear\": pd.Series([1984, 1985, 1992], index=[\"bob\", \"alice\", \"charles\"], name=\"year\"),\n",
        "    \"hobby\": pd.Series([\"Biking\", \"Dancing\"], index=[\"alice\", \"bob\"]),\n",
        "    \"weight\": pd.Series([68, 83, 112], index=[\"alice\", \"bob\", \"charles\"]),\n",
        "    \"children\": pd.Series([0, 3], index=[\"charles\", \"bob\"]),\n",
        "}\n",
        "\n",
        "people = pd.DataFrame(people_dict)\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8CPnWZ_d_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5OORZZ0l_IuK"
      },
      "source": [
        "         birthyear    hobby  weight  children\n",
        "alice         1985   Biking      68       NaN\n",
        "bob           1984  Dancing      83       3.0\n",
        "charles       1992      NaN     112       0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-QVhNhrj_IuK"
      },
      "source": [
        "- The `loc` attribute lets you access rows instead of columns. The result is a `Series` object in which the `DataFrame`'s column names are mapped to row index labels:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rmzXTmBy_IuK"
      },
      "source": [
        "people.loc[\"charles\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bM7irEgw_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CcYdXw0G_IuK"
      },
      "source": [
        "birthyear    1992\n",
        "hobby         NaN\n",
        "weight        112\n",
        "children        0\n",
        "Name: charles, dtype: object"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8BFhHWhQ_IuK"
      },
      "source": [
        "- You can also access rows by integer location using the `iloc` attribute:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mYBws2vI_IuK"
      },
      "source": [
        "people.iloc[2]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lOdsmkv8_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "flBHhV-v_IuK"
      },
      "source": [
        "birthyear    1992\n",
        "hobby         NaN\n",
        "weight        112\n",
        "children        0\n",
        "Name: charles, dtype: object"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ujEPES6u_IuK"
      },
      "source": [
        "- You can also get a slice of rows, and this returns a `DataFrame` object:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MProD5AZ_IuK"
      },
      "source": [
        "people.iloc[1:3]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lqN0xNeI_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-gxD_wk7_IuK"
      },
      "source": [
        "         birthyear    hobby  weight  children\n",
        "bob           1984  Dancing      83       3.0\n",
        "charles       1992      NaN     112       0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9Yriqcu4_IuK"
      },
      "source": [
        "- Finally, you can pass a boolean array to get the matching rows:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "F02WKWwU_IuK"
      },
      "source": [
        "people[np.array([True, False, True])]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y_AoBf9C_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OkCQ0XC6_IuK"
      },
      "source": [
        "         birthyear   hobby  weight  children\n",
        "alice         1985  Biking      68       NaN\n",
        "charles       1992     NaN     112       0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JNvVh9Fp_IuK"
      },
      "source": [
        "- This is most useful when combined with boolean expressions:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bXgKh0nK_IuK"
      },
      "source": [
        "people[people[\"birthyear\"] < 1990]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Pi17NLnc_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Wj4BHc5e_IuK"
      },
      "source": [
        "       birthyear    hobby  weight  children\n",
        "alice       1985   Biking      68       NaN\n",
        "bob         1984  Dancing      83       3.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dxrQRmkb_IuK"
      },
      "source": [
        "### Adding and removing columns\n",
        "\n",
        "- Again, let's go back to the `people` `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7jz6MsYY_IuK"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "people_dict = {\n",
        "    \"birthyear\": pd.Series([1984, 1985, 1992], index=[\"bob\", \"alice\", \"charles\"], name=\"year\"),\n",
        "    \"hobby\": pd.Series([\"Biking\", \"Dancing\"], index=[\"alice\", \"bob\"]),\n",
        "    \"weight\": pd.Series([68, 83, 112], index=[\"alice\", \"bob\", \"charles\"]),\n",
        "    \"children\": pd.Series([0, 3], index=[\"charles\", \"bob\"]),\n",
        "}\n",
        "\n",
        "people = pd.DataFrame(people_dict)\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LT-vrBUX_IuK"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MKKCyHJ8_IuK"
      },
      "source": [
        "         birthyear    hobby  weight  children\n",
        "alice         1985   Biking      68       NaN\n",
        "bob           1984  Dancing      83       3.0\n",
        "charles       1992      NaN     112       0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VwDC-kzM_IuL"
      },
      "source": [
        "- You can generally treat `DataFrame` objects like dictionaries of `Series`, so adding new columns can be accomplished using:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1bUliURb_IuL"
      },
      "source": [
        "people[\"age\"] = 2018 - people[\"birthyear\"] # adds a new column \"age\"\n",
        "people[\"over 30\"] = people[\"age\"] > 30     # adds another column \"over 30\"\n",
        "birthyears = people.pop(\"birthyear\")\n",
        "del people[\"children\"]\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I5tIMRLm_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RuRj1Lko_IuL"
      },
      "source": [
        "           hobby  weight  age  over 30\n",
        "alice     Biking      68   33     True\n",
        "bob      Dancing      83   34     True\n",
        "charles      NaN     112   26    False"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ak3_mSNN_IuL"
      },
      "source": [
        "- We can print `birthyears` using:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dzmOBQcl_IuL"
      },
      "source": [
        "birthyears"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W_TK2R4z_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zClVTJIV_IuL"
      },
      "source": [
        "alice      1985\n",
        "bob        1984\n",
        "charles    1992\n",
        "Name: birthyear, dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QyME1Fz2_IuL"
      },
      "source": [
        "- When you add a new column, it must have the same number of rows. Missing rows are filled with NaN, and extra rows are ignored:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K05KIe04_IuL"
      },
      "source": [
        "people[\"pets\"] = pd.Series({\"bob\": 0, \"charles\": 5, \"eugene\": 1}) # alice is missing, eugene is ignored\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h9EIlodB_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fmY739Ww_IuL"
      },
      "source": [
        "           hobby  weight  age  over 30  pets\n",
        "alice     Biking      68   33     True   NaN\n",
        "bob      Dancing      83   34     True   0.0\n",
        "charles      NaN     112   26    False   5.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "exHPQ7Dl_IuL"
      },
      "source": [
        "- When adding a new column, it is added at the end (on the right) by default. You can also insert a column anywhere else using the `insert()` method:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AraqP2g__IuL"
      },
      "source": [
        "people.insert(1, \"height\", [172, 181, 185])\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EoRtVq8Y_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4IvqufvL_IuL"
      },
      "source": [
        "           hobby  height  weight  age  over 30  pets\n",
        "alice     Biking     172      68   33     True   NaN\n",
        "bob      Dancing     181      83   34     True   0.0\n",
        "charles      NaN     185     112   26    False   5.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0P7xv6G__IuL"
      },
      "source": [
        "#### Assigning new columns\n",
        "\n",
        "- Again, let's go back to the `people` `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qUG0WuCe_IuL"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "people_dict = {\n",
        "    \"hobby\": pd.Series([\"Biking\", \"Dancing\"], index=[\"alice\", \"bob\"]),\n",
        "    \"height\": pd.Series([172, 181, 185], index=[\"alice\", \"bob\", \"charles\"]),\n",
        "    \"weight\": pd.Series([68, 83, 112], index=[\"alice\", \"bob\", \"charles\"]),\n",
        "    \"age\": pd.Series([33, 34, 26], index=[\"alice\", \"bob\", \"charles\"]),\n",
        "    \"over 30\": pd.Series([True, True, False], index=[\"alice\", \"bob\", \"charles\"]),\n",
        "    \"pets\": pd.Series({\"bob\": 0.0, \"charles\": 5.0}),\n",
        "}\n",
        "\n",
        "people = pd.DataFrame(people_dict)\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LADYQVXM_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zky0kuy1_IuL"
      },
      "source": [
        "           hobby  height  weight  age  over 30  pets\n",
        "alice     Biking     172      68   33     True   NaN\n",
        "bob      Dancing     181      83   34     True   0.0\n",
        "charles      NaN     185     112   26    False   5.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qaXTUZFB_IuL"
      },
      "source": [
        "- You can also create new columns by calling the `assign()` method. Note that this returns a new `DataFrame` object, the original is not modified:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4_x_unqG_IuL"
      },
      "source": [
        "people.assign(\n",
        "    body_mass_index = people[\"weight\"] / (people[\"height\"] / 100) ** 2,\n",
        "    has_pets = people[\"pets\"] > 0\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qlvIwVRR_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w3wsPnG1_IuL"
      },
      "source": [
        "         weight  height    hobby  age  over 30  pets  body_mass_index  has_pets\n",
        "alice        68     172   Biking   33     True   NaN        22.985398     False\n",
        "bob          83     181  Dancing   34     True   0.0        25.335002     False\n",
        "charles     112     185      NaN   26    False   5.0        32.724617      True"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h3nKvx8v_IuL"
      },
      "source": [
        "- Note that you cannot access columns created within the same assignment:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WeEQnVmp_IuL"
      },
      "source": [
        "try:\n",
        "    people.assign(\n",
        "        body_mass_index = people[\"weight\"] / (people[\"height\"] / 100) ** 2,\n",
        "        overweight = people[\"body_mass_index\"] > 25\n",
        "    )\n",
        "except KeyError as e:\n",
        "    print(\"Key error:\", e)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "os205_X4_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Y6hPQZJ3_IuL"
      },
      "source": [
        "Key error: 'body_mass_index'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b7iPnBSu_IuL"
      },
      "source": [
        "- The solution is to split this assignment in two consecutive assignments:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CmQP5Pts_IuL"
      },
      "source": [
        "d6 = people.assign(body_mass_index = people[\"weight\"] / (people[\"height\"] / 100) ** 2)\n",
        "d6.assign(overweight = d6[\"body_mass_index\"] > 25)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0T7IpFhe_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UuJPztr8_IuL"
      },
      "source": [
        "           hobby  height  weight  age  over 30  pets  body_mass_index  overweight\n",
        "alice     Biking     172      68   33     True   NaN        22.985398       False\n",
        "bob      Dancing     181      83   34     True   0.0        25.335002        True\n",
        "charles      NaN     185     112   26    False   5.0        32.724617        True"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "12bnJCmn_IuL"
      },
      "source": [
        "- Having to create a temporary variable `d6` is not very convenient. You may want to just chain the assigment calls, but it does not work because the `people` object is not actually modified by the first assignment:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pKswU0e4_IuL"
      },
      "source": [
        "try:\n",
        "    (people\n",
        "         .assign(body_mass_index = people[\"weight\"] / (people[\"height\"] / 100) ** 2)\n",
        "         .assign(overweight = people[\"body_mass_index\"] > 25)\n",
        "    )\n",
        "except KeyError as e:\n",
        "    print(\"Key error:\", e)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PVIn6rMX_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VAzg_cad_IuL"
      },
      "source": [
        "Key error: 'body_mass_index'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lCgdM0qi_IuL"
      },
      "source": [
        "- But fear not, there is a simple solution. You can pass a function to the `assign()` method (typically a `lambda` function), and this function will be called with the `DataFrame` as a parameter:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dyikX4E__IuL"
      },
      "source": [
        "(people\n",
        "     .assign(body_mass_index = lambda df: df[\"weight\"] / (df[\"height\"] / 100) ** 2)\n",
        "     .assign(overweight = lambda df: df[\"body_mass_index\"] > 25)\n",
        ")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "m3aHc2_w_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "H2OCBxBO_IuL"
      },
      "source": [
        "           hobby  height  weight  age  over 30  pets  body_mass_index  overweight\n",
        "alice     Biking     172      68   33     True   NaN        22.985398       False\n",
        "bob      Dancing     181      83   34     True   0.0        25.335002        True\n",
        "charles      NaN     185     112   26    False   5.0        32.724617        True"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hZSmKI2c_IuL"
      },
      "source": [
        "- Problem solved!\n",
        "\n",
        "#### Evaluating an expression\n",
        "\n",
        "- A great feature supported by pandas is expression evaluation. This relies on the `numexpr` library which must be installed."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2eKT2gCr_IuL"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "people.eval(\"weight / (height/100) ** 2 > 25\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MJ2EMpsC_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EYbzReF1_IuL"
      },
      "source": [
        "alice      False\n",
        "bob         True\n",
        "charles     True\n",
        "dtype: bool"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cqARIzro_IuL"
      },
      "source": [
        "- Assignment expressions are also supported. Let's set `inplace=True` to directly modify the `DataFrame` rather than getting a modified copy:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8Kp8Zx5V_IuL"
      },
      "source": [
        "people.eval(\"body_mass_index = weight / (height/100) ** 2\", inplace=True)\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DYuRtKTF_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VHm7hZ7Y_IuL"
      },
      "source": [
        "           hobby  height  weight  age  over 30  pets  body_mass_index\n",
        "alice     Biking     172      68   33     True   NaN        22.985398\n",
        "bob      Dancing     181      83   34     True   0.0        25.335002\n",
        "charles      NaN     185     112   26    False   5.0        32.724617"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bI6i86R6_IuL"
      },
      "source": [
        "- You can use a local or global variable in an expression by prefixing it with `'@'`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "snI0nwsL_IuL"
      },
      "source": [
        "overweight_threshold = 30\n",
        "people.eval(\"overweight = body_mass_index > @overweight_threshold\", inplace=True)\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VHtJL8DQ_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "t3U1dwDg_IuL"
      },
      "source": [
        "           hobby  height  weight  age  over 30  pets  body_mass_index  overweight\n",
        "alice     Biking     172      68   33     True   NaN        22.985398       False\n",
        "bob      Dancing     181      83   34     True   0.0        25.335002       False\n",
        "charles      NaN     185     112   26    False   5.0        32.724617        True"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NRBbDBdP_IuL"
      },
      "source": [
        "#### Querying a `DataFrame`\n",
        "\n",
        "- The `query()` method lets you filter a `DataFrame` based on a query expression:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CDc-wa7J_IuL"
      },
      "source": [
        "people.query(\"age > 30 and pets == 0\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DR7W2XoX_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zyh7dt5f_IuL"
      },
      "source": [
        "       hobby  height  weight  age  over 30  pets  body_mass_index  overweight\n",
        "bob  Dancing     181      83   34     True   0.0        25.335002       False"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t3I3hlAC_IuL"
      },
      "source": [
        "#### Sorting a `DataFrame`\n",
        "\n",
        "- You can sort a `DataFrame` by calling its `sort_index` method. By default, it sorts the rows by their index label in ascending order, but let's reverse the order:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VZSSLyXO_IuL"
      },
      "source": [
        "people.sort_index(ascending=False)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZxBmA92L_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QYYuJ02w_IuL"
      },
      "source": [
        "           hobby  height  weight  age  over 30  pets  body_mass_index  overweight\n",
        "charles      NaN     185     112   26    False   5.0        32.724617        True\n",
        "bob      Dancing     181      83   34     True   0.0        25.335002       False\n",
        "alice     Biking     172      68   33     True   NaN        22.985398       False"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zbagYoo-_IuL"
      },
      "source": [
        "- Note that `sort_index` returned a sorted **copy** of the `DataFrame`. To modify `people` directly, we can set the `inplace` argument to `True`. Also, we can sort the columns instead of the rows by setting `axis=1`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_FI9eFbS_IuL"
      },
      "source": [
        "people.sort_index(axis=1, inplace=True)\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KljSRAcn_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0enMC4JM_IuL"
      },
      "source": [
        "         age  body_mass_index  height    hobby  over 30  overweight  pets  weight\n",
        "alice     33        22.985398     172   Biking     True       False   NaN      68\n",
        "bob       34        25.335002     181  Dancing     True       False   0.0      83\n",
        "charles   26        32.724617     185      NaN    False        True   5.0     112"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KNpgBsAL_IuL"
      },
      "source": [
        "- To sort the `DataFrame` by the values instead of the labels, we can use `sort_values` and specify the column to sort by:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kT6Y-fE7_IuL"
      },
      "source": [
        "people.sort_values(by=\"age\", inplace=True)\n",
        "people"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a_vQpO3r_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a7irDpwC_IuL"
      },
      "source": [
        "         age  body_mass_index  height    hobby  over 30  overweight  pets  weight\n",
        "charles   26        32.724617     185      NaN    False        True   5.0     112\n",
        "alice     33        22.985398     172   Biking     True       False   NaN      68\n",
        "bob       34        25.335002     181  Dancing     True       False   0.0      83"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PrI0sLQN_IuL"
      },
      "source": [
        "#### Plotting a `DataFrame`\n",
        "\n",
        "- Just like for `Series`, pandas makes it easy to draw nice graphs based on a `DataFrame`.\n",
        "- For example, it is trivial to create a line plot from a `DataFrame`'s data by calling its `plot` method:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "frULgVdJ_IuL"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "people.plot(kind = \"line\", x = \"body_mass_index\", y = [\"height\", \"weight\"])\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ogUT3T0Q_IuL"
      },
      "source": [
        "- which outputs:\n",
        "\n",
        "<img src=\"https://github.com/amanchadha/aman-ai/blob/assets/pandas/6.png?raw=1\" align=\"center\" style=\"background-color: #fff; margin: 10px auto\" />\n",
        "\n",
        "- You can pass extra arguments supported by matplotlib's functions. For example, we can create scatterplot and pass it a list of sizes using the `s` argument of matplotlib's `scatter()` function:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "c7eSFjny_IuL"
      },
      "source": [
        "people.plot(kind = \"scatter\", x = \"height\", y = \"weight\", s=[40, 120, 200])\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lR3Lp2Xq_IuL"
      },
      "source": [
        "- which outputs:\n",
        "\n",
        "<img src=\"https://github.com/amanchadha/aman-ai/blob/assets/pandas/7.jpg?raw=1\" align=\"center\" style=\"background-color: #fff; margin: 10px auto\" />    \n",
        "\n",
        "- Again, there are way too many options to list here: the best option is to scroll through the [Visualization](http://pandas.pydata.org/pandas-docs/stable/visualization.html) page in pandas' documentation, find the plot you are interested in and look at the example code.\n",
        "\n",
        "#### Operations on `DataFrame`s\n",
        "\n",
        "- Although `DataFrame`s do not try to mimick NumPy arrays, there are a few similarities. Let's create a `DataFrame` to demonstrate this:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cAToFpix_IuL"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "grades_array = np.array([[8, 8, 9], [10, 9, 9], [4, 8, 2], [9, 10, 10]])\n",
        "grades = pd.DataFrame(grades_array, columns=[\"sep\", \"oct\", \"nov\"], index=[\"alice\",\"bob\",\"charles\",\"darwin\"])\n",
        "grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cIknSIbX_IuL"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xpsLLx-X_IuL"
      },
      "source": [
        "         sep  oct  nov\n",
        "alice      8    8    9\n",
        "bob       10    9    9\n",
        "charles    4    8    2\n",
        "darwin     9   10   10"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LsAfmPII_IuL"
      },
      "source": [
        "- You can apply NumPy mathematical functions on a `DataFrame`: the function is applied to all values:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Fl77mqVn_IuM"
      },
      "source": [
        "np.sqrt(grades)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qAwwL5Ug_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0R2x5gLp_IuM"
      },
      "source": [
        "              sep       oct       nov\n",
        "alice    2.828427  2.828427  3.000000\n",
        "bob      3.162278  3.000000  3.000000\n",
        "charles  2.000000  2.828427  1.414214\n",
        "darwin   3.000000  3.162278  3.162278"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lT2mt0sM_IuM"
      },
      "source": [
        "- Similarly, adding a single value to a `DataFrame` will add that value to all elements in the `DataFrame`. This is called **broadcasting**:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WRST7iyq_IuM"
      },
      "source": [
        "grades + 1"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_E6BdrIW_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pYngTgSB_IuM"
      },
      "source": [
        "         sep  oct  nov\n",
        "alice      9    9   10\n",
        "bob       11   10   10\n",
        "charles    5    9    3\n",
        "darwin    10   11   11"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0gE6zIhx_IuM"
      },
      "source": [
        "- Of course, the same is true for all other binary operations, including arithmetic (`*`,`/`,`**`...) and conditional (`>`, `==`...) operations:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IXAvqVH6_IuM"
      },
      "source": [
        "grades >= 5"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b4stDwwT_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NdB01miJ_IuM"
      },
      "source": [
        "           sep   oct    nov\n",
        "alice     True  True   True\n",
        "bob       True  True   True\n",
        "charles  False  True  False\n",
        "darwin    True  True   True"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TpTyjK8P_IuM"
      },
      "source": [
        "- Aggregation operations, such as computing the `max`, the `sum` or the `mean` of a `DataFrame`, apply to each column, and you get back a `Series` object:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HZbPl2zb_IuM"
      },
      "source": [
        "grades.mean()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fVN6dBY5_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YjPc0LnZ_IuM"
      },
      "source": [
        "sep    7.75\n",
        "oct    8.75\n",
        "nov    7.50\n",
        "dtype: float64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YQ29NXGA_IuM"
      },
      "source": [
        "- The `all` method is also an aggregation operation: it checks whether all values are `True` or not. Let's see during which months all students got a grade greater than `5`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qCWuu9Je_IuM"
      },
      "source": [
        "(grades > 5).all()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8fvva2Kc_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hMkNLrws_IuM"
      },
      "source": [
        "sep    False\n",
        "oct     True\n",
        "nov    False\n",
        "dtype: bool"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xhRXpPOV_IuM"
      },
      "source": [
        "- Most of these functions take an optional `axis` parameter which lets you specify along which axis of the `DataFrame` you want the operation executed. The default is `axis=0`, meaning that the operation is executed vertically (on each column). You can set `axis=1` to execute the operation horizontally (on each row). For example, let's find out which students had all grades greater than `5`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IYhZKmRE_IuM"
      },
      "source": [
        "(grades > 5).all(axis = 1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FqFvxCTK_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "b-yI-jvO_IuM"
      },
      "source": [
        "alice       True\n",
        "bob         True\n",
        "charles    False\n",
        "darwin      True\n",
        "dtype: bool"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xYrKIsu__IuM"
      },
      "source": [
        "- The `any` method returns `True` if any value is True. Let's see who got at least one grade 10:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "y2jAEO6C_IuM"
      },
      "source": [
        "(grades == 10).any(axis = 1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vJGFYAjt_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9fLl-9jy_IuM"
      },
      "source": [
        "alice      False\n",
        "bob         True\n",
        "charles    False\n",
        "darwin      True\n",
        "dtype: bool"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZsrYM4NN_IuM"
      },
      "source": [
        "- If you add a `Series` object to a `DataFrame` (or execute any other binary operation), pandas attempts to broadcast the operation to all **rows** in the `DataFrame`. This only works if the `Series` has the same size as the `DataFrame`s rows. For example, let's subtract the `mean` of the `DataFrame` (a `Series` object) from the `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1H-ZYBQu_IuM"
      },
      "source": [
        "grades - grades.mean() # equivalent to: grades - [7.75, 8.75, 7.50]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cXAZC8Qs_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a4JyfQWe_IuM"
      },
      "source": [
        "          sep   oct  nov\n",
        "alice    0.25 -0.75  1.5\n",
        "bob      2.25  0.25  1.5\n",
        "charles -3.75 -0.75 -5.5\n",
        "darwin   1.25  1.25  2.5"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E6EXQ0Hd_IuM"
      },
      "source": [
        "- We subtracted `7.75` from all September grades, `8.75` from October grades and `7.50` from November grades. It is equivalent to substracting this `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eSGZaY_3_IuM"
      },
      "source": [
        "pd.DataFrame([[7.75, 8.75, 7.50]]*4, index=grades.index, columns=grades.columns)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Hz1wkKGn_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aNEeYIEl_IuM"
      },
      "source": [
        "          sep   oct  nov\n",
        "alice    7.75  8.75  7.5\n",
        "bob      7.75  8.75  7.5\n",
        "charles  7.75  8.75  7.5\n",
        "darwin   7.75  8.75  7.5"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DD9FG9ql_IuM"
      },
      "source": [
        "- If you want to subtract the global mean from every grade, here is one way to do it:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fx22J6jZ_IuM"
      },
      "source": [
        "grades - grades.values.mean() # subtracts the global mean (8.00) from all grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1OOXwtKE_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L_-JZywv_IuM"
      },
      "source": [
        "         sep  oct  nov\n",
        "alice    0.0  0.0  1.0\n",
        "bob      2.0  1.0  1.0\n",
        "charles -4.0  0.0 -6.0\n",
        "darwin   1.0  2.0  2.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "r4c303Lg_IuM"
      },
      "source": [
        "#### Automatic alignment\n",
        "\n",
        "- Similar to `Series`, when operating on multiple `DataFrame`s, pandas automatically aligns them by row index label, but also by column names. Let's start with our previous grades `DataFrame` to demonstrate this:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WP15iK6f_IuM"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "grades_array = np.array([[8, 8, 9], [10, 9, 9], [4, 8, 2], [9, 10, 10]])\n",
        "grades = pd.DataFrame(grades_array, columns=[\"sep\", \"oct\", \"nov\"], index=[\"alice\", \"bob\", \"charles\", \"darwin\"])\n",
        "grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ssmR6YOq_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HgxGJ4X4_IuM"
      },
      "source": [
        "         sep  oct  nov\n",
        "alice      8    8    9\n",
        "bob       10    9    9\n",
        "charles    4    8    2\n",
        "darwin     9   10   10"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BgDkiaaP_IuM"
      },
      "source": [
        "- Now, let's create a new `DataFrame` that holds the bonus points for each person from October to December:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O2WtLKbe_IuM"
      },
      "source": [
        "import numpy as np\n",
        "\n",
        "bonus_array = np.array([[0, np.nan, 2],[np.nan, 1, 0],[0, 1, 0], [3, 3, 0]])\n",
        "bonus_points = pd.DataFrame(bonus_array, columns=[\"oct\", \"nov\", \"dec\"], index=[\"bob\", \"colin\", \"darwin\", \"charles\"])\n",
        "bonus_points"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sl6AF2Qf_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2uNTMB1D_IuM"
      },
      "source": [
        "         oct  nov  dec\n",
        "bob      0.0  NaN  2.0\n",
        "colin    NaN  1.0  0.0\n",
        "darwin   0.0  1.0  0.0\n",
        "charles  3.0  3.0  0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uL0er7sb_IuM"
      },
      "source": [
        "- Now, let's combine both `DataFrame`s:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "q7oqtfSy_IuM"
      },
      "source": [
        "grades + bonus_points"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k1S5slHl_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vbbbIHkd_IuM"
      },
      "source": [
        "         dec   nov   oct  sep\n",
        "alice    NaN   NaN   NaN  NaN\n",
        "bob      NaN   NaN   9.0  NaN\n",
        "charles  NaN   5.0  11.0  NaN\n",
        "colin    NaN   NaN   NaN  NaN\n",
        "darwin   NaN  11.0  10.0  NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "G-UkupEq_IuM"
      },
      "source": [
        "- Looks like the addition worked in some cases but way too many elements are now empty. That's because when aligning the `DataFrame`s, some columns and rows were only present on one side, and thus they were considered missing on the other side (`NaN`). Then adding `NaN` to a number results in `NaN`, hence the result.\n",
        "\n",
        "#### Handling missing data\n",
        "\n",
        "- Dealing with missing data is a frequent task when working with real life data. Pandas offers a few tools to handle missing data.\n",
        "- Let's try to fix the problem seen in the above section on [automatic alignment](#automatic-alignment). Let's start with our previous grades `DataFrame` to demonstrate this:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ieadyWSV_IuM"
      },
      "source": [
        "import pandas as pd\n",
        "\n",
        "grades = pd.DataFrame({'sep': {'alice': 8, 'bob': 10, 'charles': 4, 'darwin': 9}, 'oct': {'alice': 8, 'bob': 9, 'charles': 8, 'darwin': 10}, 'nov': {'alice': 9, 'bob': 9, 'charles': 2, 'darwin': 10}})\n",
        "grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5QmHNr7W_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "j5PJ485__IuM"
      },
      "source": [
        "         sep  oct  nov\n",
        "alice      8    8    9\n",
        "bob       10    9    9\n",
        "charles    4    8    2\n",
        "darwin     9   10   10"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KBkSdtWF_IuM"
      },
      "source": [
        "- Now, let's create a new `DataFrame` that holds the bonus points for each person from October to December:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_8Po_cXg_IuM"
      },
      "source": [
        "import numpy as np\n",
        "\n",
        "bonus_points = pd.DataFrame({'oct': {'bob': 0.0, 'colin': np.nan, 'darwin': 0.0, 'charles': 3.0}, 'nov': {'bob': np.nan, 'colin': 1.0, 'darwin': 1.0, 'charles': 3.0}, 'dec': {'bob': 2.0, 'colin': 0.0, 'darwin': 0.0, 'charles': 0.0}})\n",
        "bonus_points"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d8WjjaFF_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hGrB0cs9_IuM"
      },
      "source": [
        "         oct  nov  dec\n",
        "bob      0.0  NaN  2.0\n",
        "colin    NaN  1.0  0.0\n",
        "darwin   0.0  1.0  0.0\n",
        "charles  3.0  3.0  0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-I--iZuH_IuM"
      },
      "source": [
        "- For example, we can decide that missing data should result in a zero, instead of `NaN`. We can replace all `NaN` values by a any value using the `fillna()` method:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-mddEqwL_IuM"
      },
      "source": [
        "(grades + bonus_points).fillna(0)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fkn8yaul_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fdhEe3fm_IuM"
      },
      "source": [
        "         dec   nov   oct  sep\n",
        "alice    0.0   0.0   0.0  0.0\n",
        "bob      0.0   0.0   9.0  0.0\n",
        "charles  0.0   5.0  11.0  0.0\n",
        "colin    0.0   0.0   0.0  0.0\n",
        "darwin   0.0  11.0  10.0  0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0FahTOSG_IuM"
      },
      "source": [
        "- It's a bit unfair that we're setting grades to zero in September, though. Perhaps we should decide that missing grades are missing grades, but missing bonus points should be replaced by zeros:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7xqjXpBQ_IuM"
      },
      "source": [
        "fixed_bonus_points = bonus_points.fillna(0)\n",
        "fixed_bonus_points.insert(0, \"sep\", 0)\n",
        "fixed_bonus_points.loc[\"alice\"] = 0\n",
        "grades + fixed_bonus_points"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rEXV4AoL_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Y2NDeHG6_IuM"
      },
      "source": [
        "         dec   nov   oct   sep\n",
        "alice    NaN   9.0   8.0   8.0\n",
        "bob      NaN   9.0   9.0  10.0\n",
        "charles  NaN   5.0  11.0   4.0\n",
        "colin    NaN   NaN   NaN   NaN\n",
        "darwin   NaN  11.0  10.0   9.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CbV_ufT1_IuM"
      },
      "source": [
        "- That's much better: although we made up some data, we have not been too unfair.\n",
        "- Another way to handle missing data is to interpolate. Let's look at the `bonus_points` `DataFrame` again:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xYAgrg8B_IuM"
      },
      "source": [
        "bonus_points"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YSg02i2h_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CyimQ3SS_IuM"
      },
      "source": [
        "         oct  nov  dec\n",
        "bob      0.0  NaN  2.0\n",
        "colin    NaN  1.0  0.0\n",
        "darwin   0.0  1.0  0.0\n",
        "charles  3.0  3.0  0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WsdXDbAP_IuM"
      },
      "source": [
        "- Now let's call the `interpolate` method. By default, it interpolates vertically (`axis=0`), so let's tell it to interpolate horizontally (`axis=1`)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "E6Tmqnrx_IuM"
      },
      "source": [
        "bonus_points.interpolate(axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NZQAL1ue_IuM"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OFOX1sXs_IuN"
      },
      "source": [
        "         oct  nov  dec\n",
        "bob      0.0  1.0  2.0\n",
        "colin    NaN  1.0  0.0\n",
        "darwin   0.0  1.0  0.0\n",
        "charles  3.0  3.0  0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Dw5uGcl2_IuN"
      },
      "source": [
        "- Bob had 0 bonus points in October, and 2 in December. When we interpolate for November, we get the mean: 1 bonus point. Colin had 1 bonus point in November, but we do not know how many bonus points he had in September, so we cannot interpolate, this is why there is still a missing value in October after interpolation. To fix this, we can set the September bonus points to 0 before interpolation."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x2bxv9BC_IuN"
      },
      "source": [
        "better_bonus_points = bonus_points.copy()\n",
        "better_bonus_points.insert(0, \"sep\", 0)\n",
        "better_bonus_points.loc[\"alice\"] = 0\n",
        "better_bonus_points = better_bonus_points.interpolate(axis=1)\n",
        "better_bonus_points"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Woq-3loi_IuN"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8hhpQXOI_IuN"
      },
      "source": [
        "         sep  oct  nov  dec\n",
        "bob      0.0  0.0  1.0  2.0\n",
        "colin    0.0  0.5  1.0  0.0\n",
        "darwin   0.0  0.0  1.0  0.0\n",
        "charles  0.0  3.0  3.0  0.0\n",
        "alice    0.0  0.0  0.0  0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Kh1MKMe-_IuN"
      },
      "source": [
        "- Great, now we have reasonable bonus points everywhere. Let's find out the final grades:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fe2e-zhb_IuN"
      },
      "source": [
        "grades + better_bonus_points"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HdEQb8a2_IuN"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "92GNBq5h_IuN"
      },
      "source": [
        "         dec   nov   oct   sep\n",
        "alice    NaN   9.0   8.0   8.0\n",
        "bob      NaN  10.0   9.0  10.0\n",
        "charles  NaN   5.0  11.0   4.0\n",
        "colin    NaN   NaN   NaN   NaN\n",
        "darwin   NaN  11.0  10.0   9.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WUxVwMdn_IuN"
      },
      "source": [
        "- It is slightly annoying that the September column ends up on the right. This is because the `DataFrame`s we are adding do not have the exact same columns (the `grades` `DataFrame` is missing the `\"dec\"` column), so to make things predictable, pandas orders the final columns alphabetically. To fix this, we can simply add the missing column before adding:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iQNMaLGm_IuN"
      },
      "source": [
        "import numpy as np\n",
        "\n",
        "grades[\"dec\"] = np.nan\n",
        "final_grades = grades + better_bonus_points\n",
        "final_grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "79pX7Gfl_IuN"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EDEdU5X6_IuN"
      },
      "source": [
        "          sep   oct   nov  dec\n",
        "alice     8.0   8.0   9.0  NaN\n",
        "bob      10.0   9.0  10.0  NaN\n",
        "charles   4.0  11.0   5.0  NaN\n",
        "colin     NaN   NaN   NaN  NaN\n",
        "darwin    9.0  10.0  11.0  NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uZgT-kyw_IuN"
      },
      "source": [
        "- There's not much we can do about December and Colin: it's bad enough that we are making up bonus points, but we can't reasonably make up grades (well I guess some teachers probably do). So let's call the `dropna()` method to get rid of rows that are full of `NaN`s:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KKiO-qn1_IuN"
      },
      "source": [
        "final_grades_clean = final_grades.dropna(how=\"all\")\n",
        "final_grades_clean"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xCxGraY4_IuN"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "USCdzrLM_IuN"
      },
      "source": [
        "          sep   oct   nov  dec\n",
        "alice     8.0   8.0   9.0  NaN\n",
        "bob      10.0   9.0  10.0  NaN\n",
        "charles   4.0  11.0   5.0  NaN\n",
        "darwin    9.0  10.0  11.0  NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mW36LW1S_IuN"
      },
      "source": [
        "- Now let's remove columns that are full of `NaN`s by setting the `axis` argument to `1`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xgErdij3_IuN"
      },
      "source": [
        "final_grades_clean = final_grades_clean.dropna(axis=1, how=\"all\")\n",
        "final_grades_clean"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TnHFUHEb_IuN"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QufuKHKd_IuN"
      },
      "source": [
        "          sep   oct   nov\n",
        "alice     8.0   8.0   9.0\n",
        "bob      10.0   9.0  10.0\n",
        "charles   4.0  11.0   5.0\n",
        "darwin    9.0  10.0  11.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ogzo7hRq_IuN"
      },
      "source": [
        "#### Aggregating with `groupby`\n",
        "\n",
        "- Similar to the SQL language, pandas allows grouping your data into groups to run calculations over each group.\n",
        "\n",
        "- First, let's add some extra data about each person so we can group them, and let's go back to the `final_grades` `DataFrame` so we can see how `NaN` values are handled:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XFHGgb65_IuN"
      },
      "source": [
        "import numpy as np\n",
        "\n",
        "final_grades = pd.DataFrame({'sep': {'alice': 8.0, 'bob': 10.0, 'charles': 4.0, 'colin': np.nan, 'darwin': 9.0}, 'oct': {'alice': 8.0, 'bob': 9.0, 'charles': 11.0, 'colin': np.nan, 'darwin': 10.0}, 'nov': {'alice': 9.0, 'bob': 10.0, 'charles': 5.0, 'colin': np.nan, 'darwin': 11.0}, 'dec': {'alice': np.nan, 'bob': np.nan, 'charles': np.nan, 'colin': np.nan, 'darwin': np.nan}})\n",
        "final_grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Yo_MaFS1_IuN"
      },
      "source": [
        "- Adding a new column \"hobby\" to `final_grades`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mq9WCHbU_IuN"
      },
      "source": [
        "final_grades[\"hobby\"] = [\"Biking\", \"Dancing\", np.nan, \"Dancing\", \"Biking\"]\n",
        "final_grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-jIR_ZFU_IuN"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "d48Wst4A_IuN"
      },
      "source": [
        "          sep   oct   nov  dec    hobby\n",
        "alice     8.0   8.0   9.0  NaN   Biking\n",
        "bob      10.0   9.0  10.0  NaN  Dancing\n",
        "charles   4.0  11.0   5.0  NaN      NaN\n",
        "colin     NaN   NaN   NaN  NaN  Dancing\n",
        "darwin    9.0  10.0  11.0  NaN   Biking"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CCHJ4EMj_IuN"
      },
      "source": [
        "- Now let's group data in this `DataFrame` by hobby:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5gKIE1pL_IuN"
      },
      "source": [
        "grouped_grades = final_grades.groupby(\"hobby\")\n",
        "grouped_grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "F5geUexz_IuN"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iHpL8TTo_IuN"
      },
      "source": [
        "<pandas.core.groupby.generic.DataFrameGroupBy object at [address]>"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QmQBug9H_IuN"
      },
      "source": [
        "- We are ready to compute the average grade per hobby:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HDhBlwC8_IuN"
      },
      "source": [
        "grouped_grades.mean()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VlOJHR6S_IuN"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PHqlMmZA_IuO"
      },
      "source": [
        "          sep  oct   nov  dec\n",
        "hobby\n",
        "Biking    8.5  9.0  10.0  NaN\n",
        "Dancing  10.0  9.0  10.0  NaN"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4QKAztYB_IuO"
      },
      "source": [
        "- That was easy! Note that the `NaN` values have simply been skipped when computing the means.\n",
        "\n",
        "#### Pivot tables\n",
        "\n",
        "- Pandas supports spreadsheet-like [pivot tables](https://en.wikipedia.org/wiki/Pivot_table) that allow quick data summarization. To illustrate this, let's start with our  `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KPyZMI5c_IuO"
      },
      "source": [
        "final_grades_clean = {'sep': {'alice': 8.0, 'bob': 10.0, 'charles': 4.0, 'darwin': 9.0}, 'oct': {'alice': 8.0, 'bob': 9.0, 'charles': 11.0, 'darwin': 10.0}, 'nov': {'alice': 9.0, 'bob': 10.0, 'charles': 5.0, 'darwin': 11.0}}\n",
        "final_grades_clean"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ab9uc82s_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "a5LP8pe0_IuO"
      },
      "source": [
        "         oct  nov  dec\n",
        "bob      0.0  NaN  2.0\n",
        "colin    NaN  1.0  0.0\n",
        "darwin   0.0  1.0  0.0\n",
        "charles  3.0  3.0  0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GD5jrQcO_IuO"
      },
      "source": [
        "- Now let's restructure the data:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zAZV9Rwn_IuO"
      },
      "source": [
        "more_grades = final_grades_clean.stack().reset_index()\n",
        "more_grades.columns = [\"name\", \"month\", \"grade\"]\n",
        "more_grades[\"bonus\"] = [np.nan, np.nan, np.nan, 0, np.nan, 2, 3, 3, 0, 0, 1, 0]\n",
        "more_grades"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Se_Y8rST_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9qeR3Zcw_IuO"
      },
      "source": [
        "       name month  grade  bonus\n",
        "0     alice   sep    8.0    NaN\n",
        "1     alice   oct    8.0    NaN\n",
        "2     alice   nov    9.0    NaN\n",
        "3       bob   sep   10.0    0.0\n",
        "4       bob   oct    9.0    NaN\n",
        "5       bob   nov   10.0    2.0\n",
        "6   charles   sep    4.0    3.0\n",
        "7   charles   oct   11.0    3.0\n",
        "8   charles   nov    5.0    0.0\n",
        "9    darwin   sep    9.0    0.0\n",
        "10   darwin   oct   10.0    1.0\n",
        "11   darwin   nov   11.0    0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LyAYuSXG_IuO"
      },
      "source": [
        "- Now we can call the `pd.pivot_table()` function for this `DataFrame`, asking to group by the `name` column. By default, `pivot_table()` computes the mean of each numeric column:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7QgY0ZbU_IuO"
      },
      "source": [
        "pd.pivot_table(more_grades, index=\"name\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1Rs2_M9B_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xjx7EYYZ_IuO"
      },
      "source": [
        "            bonus      grade\n",
        "name\n",
        "alice         NaN   8.333333\n",
        "bob      1.000000   9.666667\n",
        "charles  2.000000   6.666667\n",
        "darwin   0.333333  10.000000"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xHlWvXy9_IuO"
      },
      "source": [
        "- We can change the aggregation function by setting the `aggfunc` argument, and we can also specify the list of columns whose values will be aggregated:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-Z-r9hhN_IuO"
      },
      "source": [
        "pd.pivot_table(more_grades, index=\"name\", values=[\"grade\",\"bonus\"], aggfunc=np.max)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YLJwXbjD_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cWX8cLlt_IuO"
      },
      "source": [
        "         bonus  grade\n",
        "name\n",
        "alice      NaN    9.0\n",
        "bob        2.0   10.0\n",
        "charles    3.0   11.0\n",
        "darwin     1.0   11.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7VWKCr4z_IuO"
      },
      "source": [
        "- We can also specify the `columns` to aggregate over horizontally, and request the grand totals for each row and column by setting `margins=True`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qp2aeZRy_IuO"
      },
      "source": [
        "pd.pivot_table(more_grades, index=\"name\", values=\"grade\", columns=\"month\", margins=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ckBpWrkY_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BIpIgwIG_IuO"
      },
      "source": [
        "month      nov   oct    sep        All\n",
        "name\n",
        "alice     9.00   8.0   8.00   8.333333\n",
        "bob      10.00   9.0  10.00   9.666667\n",
        "charles   5.00  11.0   4.00   6.666667\n",
        "darwin   11.00  10.0   9.00  10.000000\n",
        "All       8.75   9.5   7.75   8.666667"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "65hGOwDM_IuO"
      },
      "source": [
        "- Finally, we can specify multiple index or column names, and pandas will create multi-level indices:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AmEP2XsK_IuO"
      },
      "source": [
        "pd.pivot_table(more_grades, index=(\"name\", \"month\"), margins=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mi5_2Ui-_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rDMbu1Sj_IuO"
      },
      "source": [
        "               bonus  grade\n",
        "name    month\n",
        "alice   nov      NaN   9.00\n",
        "        oct      NaN   8.00\n",
        "        sep      NaN   8.00\n",
        "bob     nov    2.000  10.00\n",
        "        oct      NaN   9.00\n",
        "        sep    0.000  10.00\n",
        "charles nov    0.000   5.00\n",
        "        oct    3.000  11.00\n",
        "        sep    3.000   4.00\n",
        "darwin  nov    0.000  11.00\n",
        "        oct    1.000  10.00\n",
        "        sep    0.000   9.00\n",
        "All            1.125   8.75"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-5pLBzjP_IuO"
      },
      "source": [
        "#### Overview functions\n",
        "\n",
        "- When dealing with large `DataFrames`, it is useful to get a quick overview of its content. Pandas offers a few functions for this. First, let's create a large `DataFrame` with a mix of numeric values, missing values and text values. Notice how Jupyter displays only the corners of the `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "emAbW_So_IuO"
      },
      "source": [
        "much_data = np.fromfunction(lambda x,y: (x+y*y)%17*11, (10000, 26))\n",
        "large_df = pd.DataFrame(much_data, columns=list(\"ABCDEFGHIJKLMNOPQRSTUVWXYZ\"))\n",
        "large_df[large_df % 16 == 0] = np.nan\n",
        "large_df.insert(3,\"some_text\", \"Blabla\")\n",
        "large_df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UDkLNsMG_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fHBD6G8s_IuO"
      },
      "source": [
        "         A     B     C some_text      D  ...      V      W     X      Y      Z\n",
        "0      NaN  11.0  44.0    Blabla   99.0  ...    NaN   88.0  22.0  165.0  143.0\n",
        "1     11.0  22.0  55.0    Blabla  110.0  ...    NaN   99.0  33.0    NaN  154.0\n",
        "2     22.0  33.0  66.0    Blabla  121.0  ...   11.0  110.0  44.0    NaN  165.0\n",
        "3     33.0  44.0  77.0    Blabla  132.0  ...   22.0  121.0  55.0   11.0    NaN\n",
        "4     44.0  55.0  88.0    Blabla  143.0  ...   33.0  132.0  66.0   22.0    NaN\n",
        "...    ...   ...   ...       ...    ...  ...    ...    ...   ...    ...    ...\n",
        "9995   NaN   NaN  33.0    Blabla   88.0  ...  165.0   77.0  11.0  154.0  132.0\n",
        "9996   NaN  11.0  44.0    Blabla   99.0  ...    NaN   88.0  22.0  165.0  143.0\n",
        "9997  11.0  22.0  55.0    Blabla  110.0  ...    NaN   99.0  33.0    NaN  154.0\n",
        "9998  22.0  33.0  66.0    Blabla  121.0  ...   11.0  110.0  44.0    NaN  165.0\n",
        "9999  33.0  44.0  77.0    Blabla  132.0  ...   22.0  121.0  55.0   11.0    NaN\n",
        "\n",
        "[10000 rows x 27 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dxEj0uGw_IuO"
      },
      "source": [
        "- The `head()` method returns the top 5 rows:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "JHPejRni_IuO"
      },
      "source": [
        "large_df.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GO4u9SMB_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zRdIkTQj_IuO"
      },
      "source": [
        "      A     B     C some_text      D  ...     V      W     X      Y      Z\n",
        "0   NaN  11.0  44.0    Blabla   99.0  ...   NaN   88.0  22.0  165.0  143.0\n",
        "1  11.0  22.0  55.0    Blabla  110.0  ...   NaN   99.0  33.0    NaN  154.0\n",
        "2  22.0  33.0  66.0    Blabla  121.0  ...  11.0  110.0  44.0    NaN  165.0\n",
        "3  33.0  44.0  77.0    Blabla  132.0  ...  22.0  121.0  55.0   11.0    NaN\n",
        "4  44.0  55.0  88.0    Blabla  143.0  ...  33.0  132.0  66.0   22.0    NaN\n",
        "\n",
        "[5 rows x 27 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_AMpBV5P_IuO"
      },
      "source": [
        "- Similarly, there's also a `tail()` function to view the bottom 5 rows. You can pass the number of rows you want:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-hTacZDK_IuO"
      },
      "source": [
        "large_df.tail(n=2)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8sYEymqg_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RzN4047K_IuO"
      },
      "source": [
        "         A     B     C some_text      D  ...     V      W     X     Y      Z\n",
        "9998  22.0  33.0  66.0    Blabla  121.0  ...  11.0  110.0  44.0   NaN  165.0\n",
        "9999  33.0  44.0  77.0    Blabla  132.0  ...  22.0  121.0  55.0  11.0    NaN\n",
        "\n",
        "[2 rows x 27 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8Jxxc9Yf_IuO"
      },
      "source": [
        "- The `info()` method prints out a summary of each columns contents:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "X2EdJSxn_IuO"
      },
      "source": [
        "large_df.info()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ABRk8FN1_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Q0bneVwP_IuO"
      },
      "source": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "RangeIndex: 10000 entries, 0 to 9999\n",
        "Data columns (total 27 columns):\n",
        "A            8823 non-null float64\n",
        "B            8824 non-null float64\n",
        "C            8824 non-null float64\n",
        "some_text    10000 non-null object\n",
        "D            8824 non-null float64\n",
        "E            8822 non-null float64\n",
        "F            8824 non-null float64\n",
        "G            8824 non-null float64\n",
        "H            8822 non-null float64\n",
        "I            8823 non-null float64\n",
        "J            8823 non-null float64\n",
        "K            8822 non-null float64\n",
        "L            8824 non-null float64\n",
        "M            8824 non-null float64\n",
        "N            8822 non-null float64\n",
        "O            8824 non-null float64\n",
        "P            8824 non-null float64\n",
        "Q            8824 non-null float64\n",
        "R            8823 non-null float64\n",
        "S            8824 non-null float64\n",
        "T            8824 non-null float64\n",
        "U            8824 non-null float64\n",
        "V            8822 non-null float64\n",
        "W            8824 non-null float64\n",
        "X            8824 non-null float64\n",
        "Y            8822 non-null float64\n",
        "Z            8823 non-null float64\n",
        "dtypes: float64(26), object(1)\n",
        "memory usage: 2.1+ MB"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E1ARuJYT_IuO"
      },
      "source": [
        "- Finally, the `describe()` method gives a nice overview of the main aggregated values over each column:\n",
        "\n",
        "* `count`: number of non-null (not NaN) values\n",
        "* `mean`: mean of non-null values\n",
        "* `std`: [standard deviation](https://en.wikipedia.org/wiki/Standard_deviation)\n",
        "of non-null values\n",
        "* `min`: minimum of non-null values\n",
        "* `25%`, `50%`, `75%`: 25th, 50th and 75th\n",
        "[percentile](https://en.wikipedia.org/wiki/Percentile) of non-null values\n",
        "* `max`: maximum of non-null values"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OXDuxTS1_IuO"
      },
      "source": [
        "large_df.describe()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h6p6Kd7p_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LVb_A-Zi_IuO"
      },
      "source": [
        "                 A            B  ...            Y            Z\n",
        "count  8823.000000  8824.000000  ...  8822.000000  8823.000000\n",
        "mean     87.977559    87.972575  ...    88.000000    88.022441\n",
        "std      47.535911    47.535523  ...    47.536879    47.535911\n",
        "min      11.000000    11.000000  ...    11.000000    11.000000\n",
        "25%      44.000000    44.000000  ...    44.000000    44.000000\n",
        "50%      88.000000    88.000000  ...    88.000000    88.000000\n",
        "75%     132.000000   132.000000  ...   132.000000   132.000000\n",
        "max     165.000000   165.000000  ...   165.000000   165.000000\n",
        "\n",
        "[8 rows x 26 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-hxGk99I_IuO"
      },
      "source": [
        "## Saving & loading\n",
        "\n",
        "- Pandas can save `DataFrame`s to various backends, including file formats such as CSV, Excel, JSON, HTML and HDF5, or to a SQL database. Let's create a `DataFrame` to demonstrate this:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kT_gm-R6_IuO"
      },
      "source": [
        "my_df = pd.DataFrame(\n",
        "    [[\"Biking\", 68.5, 1985, np.nan], [\"Dancing\", 83.1, 1984, 3]], \n",
        "    columns=[\"hobby\",\"weight\",\"birthyear\",\"children\"],\n",
        "    index=[\"alice\", \"bob\"]\n",
        ")\n",
        "my_df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SoLDpKEV_IuO"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w3PiyCAk_IuO"
      },
      "source": [
        "         hobby  weight  birthyear  children\n",
        "alice   Biking    68.5       1985       NaN\n",
        "bob    Dancing    83.1       1984       3.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WygyygEd_IuP"
      },
      "source": [
        "#### Saving\n",
        "\n",
        "- Let's save it to CSV, HTML and JSON:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7oBEBRqo_IuP"
      },
      "source": [
        "my_df.to_csv(\"my_df.csv\")\n",
        "my_df.to_html(\"my_df.html\")\n",
        "my_df.to_json(\"my_df.json\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uZXBQjl__IuP"
      },
      "source": [
        "- Done! Let's take a peek at what was saved:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K4ZWVd-9_IuP"
      },
      "source": [
        "for filename in (\"my_df.csv\", \"my_df.html\", \"my_df.json\"):\n",
        "    print(\"##\", filename)\n",
        "    with open(filename, \"rt\") as f:\n",
        "        print(f.read())\n",
        "        print()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pXbGh_SQ_IuP"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YbSA3rAF_IuP"
      },
      "source": [
        "## my_df.csv\n",
        ",hobby,weight,birthyear,children\n",
        "alice,Biking,68.5,1985,\n",
        "bob,Dancing,83.1,1984,3.0\n",
        "\n",
        "\n",
        "## my_df.html\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>hobby</th>\n",
        "      <th>weight</th>\n",
        "      <th>birthyear</th>\n",
        "      <th>children</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>alice</th>\n",
        "      <td>Biking</td>\n",
        "      <td>68.5</td>\n",
        "      <td>1985</td>\n",
        "      <td>NaN</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>bob</th>\n",
        "      <td>Dancing</td>\n",
        "      <td>83.1</td>\n",
        "      <td>1984</td>\n",
        "      <td>3.0</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "\n",
        "## my_df.json\n",
        "{\"hobby\":{\"alice\":\"Biking\",\"bob\":\"Dancing\"},\"weight\":{\"alice\":68.5,\"bob\":83.1},\"birthyear\":{\"alice\":1985,\"bob\":1984},\"children\":{\"alice\":null,\"bob\":3.0}}"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CqQaIaxZ_IuP"
      },
      "source": [
        "- Note that the index is saved as the first column (with no name) in a CSV file, as `<th>` tags in HTML and as keys in JSON.\n",
        "\n",
        "- Saving to other formats works very similarly, but some formats require extra libraries to be installed. For example, saving to Excel requires the `openpyxl` library:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_Qb0OJRS_IuP"
      },
      "source": [
        "try:\n",
        "    my_df.to_excel(\"my_df.xlsx\", sheet_name='People')\n",
        "except ImportError as e:\n",
        "    print(e)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YDf7fCk6_IuP"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TYrc7c2n_IuP"
      },
      "source": [
        "No module named 'openpyxl'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bg5mbWFu_IuP"
      },
      "source": [
        "#### Loading\n",
        "\n",
        "- Now let's load our CSV file back into a `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hC5V5DdE_IuP"
      },
      "source": [
        "my_df_loaded = pd.read_csv(\"my_df.csv\", index_col=0)\n",
        "my_df_loaded"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tB1LLyrk_IuP"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gI8KWsOh_IuP"
      },
      "source": [
        "         hobby  weight  birthyear  children\n",
        "alice   Biking    68.5       1985       NaN\n",
        "bob    Dancing    83.1       1984       3.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "psMXH3h__IuP"
      },
      "source": [
        "- As you might guess, there are similar `read_json`, `read_html`, `read_excel` functions as well.  We can also read data straight from the Internet. For example, let's load all U.S. cities from [simplemaps.com](http://simplemaps.com/):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SV8hTttf_IuP"
      },
      "source": [
        "us_cities = None\n",
        "try:\n",
        "    csv_url = \"https://raw.githubusercontent.com/datasets/world-cities/master/data/world-cities.csv\"\n",
        "    us_cities = pd.read_csv(csv_url, index_col=0)\n",
        "    us_cities = us_cities.head()\n",
        "except IOError as e:\n",
        "    print(e)\n",
        "    \n",
        "us_cities"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "frEM7nhM_IuP"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aQopgfLU_IuP"
      },
      "source": [
        "                               country          subcountry  geonameid\n",
        "name\n",
        "les Escaldes                   Andorra  Escaldes-Engordany    3040051\n",
        "Andorra la Vella               Andorra    Andorra la Vella    3041563\n",
        "Umm al Qaywayn    United Arab Emirates      Umm al Qaywayn     290594\n",
        "Ras al-Khaimah    United Arab Emirates     Raʼs al Khaymah     291074\n",
        "Khawr Fakkān      United Arab Emirates        Ash Shāriqah     291696"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RSqU0h9Y_IuP"
      },
      "source": [
        "- There are more options available, in particular regarding datetime format. Check out the [documentation](http://pandas.pydata.org/pandas-docs/stable/io.html) for more details.\n",
        "\n",
        "## Combining `DataFrame`s\n",
        "\n",
        "#### SQL-like joins\n",
        "\n",
        "- One powerful feature of pandas is it's ability to perform SQL-like joins on `DataFrame`s. Various types of joins are supported: inner joins, left/right outer joins and full joins. To illustrate this, let's start by creating a couple of simple `DataFrame`s. \n",
        "- Here's the city_loc `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GI_Xcw3w_IuP"
      },
      "source": [
        "city_loc = pd.DataFrame(\n",
        "    [\n",
        "        [\"CA\", \"San Francisco\", 37.781334, -122.416728],\n",
        "        [\"NY\", \"New York\", 40.705649, -74.008344],\n",
        "        [\"FL\", \"Miami\", 25.791100, -80.320733],\n",
        "        [\"OH\", \"Cleveland\", 41.473508, -81.739791],\n",
        "        [\"UT\", \"Salt Lake City\", 40.755851, -111.896657]\n",
        "    ], columns=[\"state\", \"city\", \"lat\", \"lng\"])\n",
        "\n",
        "city_loc"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "huC2VdhG_IuP"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "reeY9Bd-_IuP"
      },
      "source": [
        "  state            city        lat         lng\n",
        "0    CA   San Francisco  37.781334 -122.416728\n",
        "1    NY        New York  40.705649  -74.008344\n",
        "2    FL           Miami  25.791100  -80.320733\n",
        "3    OH       Cleveland  41.473508  -81.739791\n",
        "4    UT  Salt Lake City  40.755851 -111.896657"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9Yicb9WJ_IuP"
      },
      "source": [
        "city_pop = pd.DataFrame(\n",
        "    [\n",
        "        [808976, \"San Francisco\", \"California\"],\n",
        "        [8363710, \"New York\", \"New-York\"],\n",
        "        [413201, \"Miami\", \"Florida\"],\n",
        "        [2242193, \"Houston\", \"Texas\"]\n",
        "    ], index=[3,4,5,6], columns=[\"population\", \"city\", \"state\"])\n",
        "\n",
        "city_pop"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PjIufVfw_IuP"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "azzlPD1y_IuP"
      },
      "source": [
        "   population           city       state\n",
        "3      808976  San Francisco  California\n",
        "4     8363710       New York    New-York\n",
        "5      413201          Miami     Florida\n",
        "6     2242193        Houston       Texas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4HUjTLE9_IuP"
      },
      "source": [
        "- Now let's join these `DataFrame`s using the `merge()` function:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "byVEh9Bo_IuQ"
      },
      "source": [
        "pd.merge(left=city_loc, right=city_pop, on=\"city\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h_Oai-Uy_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Zq1aiydg_IuQ"
      },
      "source": [
        "  state_x           city        lat         lng  population     state_y\n",
        "0      CA  San Francisco  37.781334 -122.416728      808976  California\n",
        "1      NY       New York  40.705649  -74.008344     8363710    New-York\n",
        "2      FL          Miami  25.791100  -80.320733      413201     Florida"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8ZmgkmRM_IuQ"
      },
      "source": [
        "- Note that both `DataFrame`s have a column named `state`, so in the result they got renamed to `state_x` and `state_y`.\n",
        "\n",
        "- Also, note that Cleveland, Salt Lake City and Houston were dropped because they don't exist in **both** `DataFrame`s. This is the equivalent of a SQL `INNER JOIN`. If you want a `FULL OUTER JOIN`, where no city gets dropped and `NaN` values are added, you must specify `how=\"outer\"`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "05I3T_eo_IuQ"
      },
      "source": [
        "all_cities = pd.merge(left=city_loc, right=city_pop, on=\"city\", how=\"outer\")\n",
        "all_cities"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "injUbdeK_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cDWgUjcL_IuQ"
      },
      "source": [
        "  state_x            city        lat         lng  population     state_y\n",
        "0      CA   San Francisco  37.781334 -122.416728    808976.0  California\n",
        "1      NY        New York  40.705649  -74.008344   8363710.0    New-York\n",
        "2      FL           Miami  25.791100  -80.320733    413201.0     Florida\n",
        "3      OH       Cleveland  41.473508  -81.739791         NaN         NaN\n",
        "4      UT  Salt Lake City  40.755851 -111.896657         NaN         NaN\n",
        "5     NaN         Houston        NaN         NaN   2242193.0       Texas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SUTeGglt_IuQ"
      },
      "source": [
        "- Of course `LEFT OUTER JOIN` is also available by setting `how=\"left\"`: only the cities present in the left `DataFrame` end up in the result. Similarly, with `how=\"right\"` only cities in the right `DataFrame` appear in the result. For example:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2FB_xVLG_IuQ"
      },
      "source": [
        "pd.merge(left=city_loc, right=city_pop, on=\"city\", how=\"right\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GYR4hB_I_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Rd_ZRQEM_IuQ"
      },
      "source": [
        "  state_x           city        lat         lng  population     state_y\n",
        "0      CA  San Francisco  37.781334 -122.416728      808976  California\n",
        "1      NY       New York  40.705649  -74.008344     8363710    New-York\n",
        "2      FL          Miami  25.791100  -80.320733      413201     Florida\n",
        "3     NaN        Houston        NaN         NaN     2242193       Texas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p5TBhcgf_IuQ"
      },
      "source": [
        "- If the key to join on is actually in one (or both) `DataFrame`'s index, you must use `left_index=True` and/or `right_index=True`. If the key column names differ, you must use `left_on` and `right_on`. For example:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "q2XrMieO_IuQ"
      },
      "source": [
        "city_pop2 = city_pop.copy()\n",
        "city_pop2.columns = [\"population\", \"name\", \"state\"]\n",
        "pd.merge(left=city_loc, right=city_pop2, left_on=\"city\", right_on=\"name\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ha5mchNf_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UZPvd40T_IuQ"
      },
      "source": [
        "  state_x           city        lat  ...  population           name     state_y\n",
        "0      CA  San Francisco  37.781334  ...      808976  San Francisco  California\n",
        "1      NY       New York  40.705649  ...     8363710       New York    New-York\n",
        "2      FL          Miami  25.791100  ...      413201          Miami     Florida\n",
        "\n",
        "[3 rows x 7 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "L97jPCXc_IuQ"
      },
      "source": [
        "#### Concatenation\n",
        "\n",
        "- Rather than joining `DataFrame`s, we may just want to concatenate them. That's what `concat()` is for:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_OBO0ACR_IuQ"
      },
      "source": [
        "result_concat = pd.concat([city_loc, city_pop])\n",
        "result_concat"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XV7S9-GG_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "U3kZonZO_IuQ"
      },
      "source": [
        "             city        lat         lng  population       state\n",
        "0   San Francisco  37.781334 -122.416728         NaN          CA\n",
        "1        New York  40.705649  -74.008344         NaN          NY\n",
        "2           Miami  25.791100  -80.320733         NaN          FL\n",
        "3       Cleveland  41.473508  -81.739791         NaN          OH\n",
        "4  Salt Lake City  40.755851 -111.896657         NaN          UT\n",
        "3   San Francisco        NaN         NaN    808976.0  California\n",
        "4        New York        NaN         NaN   8363710.0    New-York\n",
        "5           Miami        NaN         NaN    413201.0     Florida\n",
        "6         Houston        NaN         NaN   2242193.0       Texas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wcOSOST7_IuQ"
      },
      "source": [
        "- Note that this operation aligned the data horizontally (by columns) but not vertically (by rows). In this example, we end up with multiple rows having the same index (eg. 3). Pandas handles this rather gracefully:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "degnXCsQ_IuQ"
      },
      "source": [
        "result_concat.loc[3]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6Sqos9oU_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8FC12thB_IuQ"
      },
      "source": [
        "            city        lat        lng  population       state\n",
        "3      Cleveland  41.473508 -81.739791         NaN          OH\n",
        "3  San Francisco        NaN        NaN    808976.0  California"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JWetPvQu_IuQ"
      },
      "source": [
        "- Or you can tell pandas to just ignore the index:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bOfNUT8g_IuQ"
      },
      "source": [
        "pd.concat([city_loc, city_pop], ignore_index=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wvXlDWyh_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h1U99wkh_IuQ"
      },
      "source": [
        "             city        lat         lng  population       state\n",
        "0   San Francisco  37.781334 -122.416728         NaN          CA\n",
        "1        New York  40.705649  -74.008344         NaN          NY\n",
        "2           Miami  25.791100  -80.320733         NaN          FL\n",
        "3       Cleveland  41.473508  -81.739791         NaN          OH\n",
        "4  Salt Lake City  40.755851 -111.896657         NaN          UT\n",
        "5   San Francisco        NaN         NaN    808976.0  California\n",
        "6        New York        NaN         NaN   8363710.0    New-York\n",
        "7           Miami        NaN         NaN    413201.0     Florida\n",
        "8         Houston        NaN         NaN   2242193.0       Texas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rPaAnvfL_IuQ"
      },
      "source": [
        "- Notice that when a column does not exist in a `DataFrame`, it acts as if it was filled with `NaN` values. If we set `join=\"inner\"`, then only columns that exist in **both** `DataFrame`s are returned:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oVhbLafZ_IuQ"
      },
      "source": [
        "pd.concat([city_loc, city_pop], join=\"inner\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sIbiI2FD_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7uZk--kT_IuQ"
      },
      "source": [
        "        state            city\n",
        "0          CA   San Francisco\n",
        "1          NY        New York\n",
        "2          FL           Miami\n",
        "3          OH       Cleveland\n",
        "4          UT  Salt Lake City\n",
        "3  California   San Francisco\n",
        "4    New-York        New York\n",
        "5     Florida           Miami\n",
        "6       Texas         Houston"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "K7E4i8sO_IuQ"
      },
      "source": [
        "- You can concatenate `DataFrame`s horizontally instead of vertically by setting `axis=1`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "J2egH6Uu_IuQ"
      },
      "source": [
        "pd.concat([city_loc, city_pop], axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TPA4jwox_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nw2elfsV_IuQ"
      },
      "source": [
        "  state            city        lat  ...  population           city       state\n",
        "0    CA   San Francisco  37.781334  ...         NaN            NaN         NaN\n",
        "1    NY        New York  40.705649  ...         NaN            NaN         NaN\n",
        "2    FL           Miami  25.791100  ...         NaN            NaN         NaN\n",
        "3    OH       Cleveland  41.473508  ...    808976.0  San Francisco  California\n",
        "4    UT  Salt Lake City  40.755851  ...   8363710.0       New York    New-York\n",
        "5   NaN             NaN        NaN  ...    413201.0          Miami     Florida\n",
        "6   NaN             NaN        NaN  ...   2242193.0        Houston       Texas\n",
        "\n",
        "[7 rows x 7 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d80wxsSw_IuQ"
      },
      "source": [
        "- In this case it really does not make much sense because the indices do not align well (eg. Cleveland and San Francisco end up on the same row, because they shared the index label `3`). So let's reindex the `DataFrame`s by city name before concatenating:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tBDLQ-7v_IuQ"
      },
      "source": [
        "pd.concat([city_loc.set_index(\"city\"), city_pop.set_index(\"city\")], axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iYSMDZE__IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ILrDA3iQ_IuQ"
      },
      "source": [
        "               state        lat         lng  population       state\n",
        "Cleveland         OH  41.473508  -81.739791         NaN         NaN\n",
        "Houston          NaN        NaN         NaN   2242193.0       Texas\n",
        "Miami             FL  25.791100  -80.320733    413201.0     Florida\n",
        "New York          NY  40.705649  -74.008344   8363710.0    New-York\n",
        "Salt Lake City    UT  40.755851 -111.896657         NaN         NaN\n",
        "San Francisco     CA  37.781334 -122.416728    808976.0  California"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RaADgg9d_IuQ"
      },
      "source": [
        "- This looks a lot like a `FULL OUTER JOIN`, except that the `state` columns were not renamed to `state_x` and `state_y`, and the `city` column is now the index.\n",
        "- The `append()` method is a useful shorthand for concatenating `DataFrame`s vertically:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rKbReX22_IuQ"
      },
      "source": [
        "city_loc.append(city_pop)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VDdPT2s9_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tX0z7MzQ_IuQ"
      },
      "source": [
        "             city        lat         lng  population       state\n",
        "0   San Francisco  37.781334 -122.416728         NaN          CA\n",
        "1        New York  40.705649  -74.008344         NaN          NY\n",
        "2           Miami  25.791100  -80.320733         NaN          FL\n",
        "3       Cleveland  41.473508  -81.739791         NaN          OH\n",
        "4  Salt Lake City  40.755851 -111.896657         NaN          UT\n",
        "3   San Francisco        NaN         NaN    808976.0  California\n",
        "4        New York        NaN         NaN   8363710.0    New-York\n",
        "5           Miami        NaN         NaN    413201.0     Florida\n",
        "6         Houston        NaN         NaN   2242193.0       Texas"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZNjH7atd_IuQ"
      },
      "source": [
        "- As always in pandas, the `append()` method does **not** actually modify `city_loc`: it works on a copy and returns the modified copy.\n",
        "\n",
        "## Categories\n",
        "\n",
        "- It is quite frequent to have values that represent categories, for example `1` for female and `2` for male, or `\"A\"` for Good, `\"B\"` for Average, `\"C\"` for Bad. These categorical values can be hard to read and cumbersome to handle, but fortunately pandas makes it easy. To illustrate this, let's take the `city_pop` `DataFrame` we created earlier, and add a column that represents a category:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FZ8ksRwi_IuQ"
      },
      "source": [
        "city_eco = city_pop.copy()\n",
        "city_eco[\"eco_code\"] = [17, 17, 34, 20]\n",
        "city_eco"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1SXCNQWh_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C77zSYEp_IuQ"
      },
      "source": [
        "   population           city       state  eco_code\n",
        "3      808976  San Francisco  California        17\n",
        "4     8363710       New York    New-York        17\n",
        "5      413201          Miami     Florida        34\n",
        "6     2242193        Houston       Texas        20"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QJS1hWhi_IuQ"
      },
      "source": [
        "- Right now the `eco_code` column is full of apparently meaningless codes. Let's fix that. First, we will create a new categorical column based on the `eco_code`s:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wMle4dvM_IuQ"
      },
      "source": [
        "city_eco[\"economy\"] = city_eco[\"eco_code\"].astype('category')\n",
        "city_eco[\"economy\"].cat.categories"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QfqcwOqH_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "by_qkxk0_IuQ"
      },
      "source": [
        "Int64Index([17, 20, 34], dtype='int64')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "W_Bs0jaI_IuQ"
      },
      "source": [
        "- Now we can give each category a meaningful name:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MfMcrgVb_IuQ"
      },
      "source": [
        "city_eco[\"economy\"].cat.categories = [\"Finance\", \"Energy\", \"Tourism\"]\n",
        "city_eco"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k16fsE3j_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_kNXTezr_IuQ"
      },
      "source": [
        "   population           city       state  eco_code  economy\n",
        "3      808976  San Francisco  California        17  Finance\n",
        "4     8363710       New York    New-York        17  Finance\n",
        "5      413201          Miami     Florida        34  Tourism\n",
        "6     2242193        Houston       Texas        20   Energy"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OIg1lU6n_IuQ"
      },
      "source": [
        "- Note that categorical values are sorted according to their categorical order, *not** their alphabetical order:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jQvMDG42_IuQ"
      },
      "source": [
        "city_eco.sort_values(by=\"economy\", ascending=False)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gKX6zr7__IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "25AfwCaZ_IuQ"
      },
      "source": [
        "   population           city       state  eco_code  economy\n",
        "5      413201          Miami     Florida        34  Tourism\n",
        "6     2242193        Houston       Texas        20   Energy\n",
        "3      808976  San Francisco  California        17  Finance\n",
        "4     8363710       New York    New-York        17  Finance"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "98kYwLDC_IuQ"
      },
      "source": [
        "## Practical example using a sample dataset\n",
        "\n",
        "- To demonstrate the use of pandas, we'll be using the interview reviews scraped from Glassdoor in 2019. The data is available [here](../assets/pandas/interviews.csv).\n",
        "\n",
        "### Dataset at a glance\n",
        "\n",
        "- Let's check out the dataset :"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1pa_slwl_IuQ"
      },
      "source": [
        "import pandas as pd\n",
        "df = pd.read_csv(\"../assets/pandas/interviews.csv\")\n",
        "\n",
        "print(df.shape)\n",
        "df.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "60tcgfCB_IuQ"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "winr0LQG_IuR"
      },
      "source": [
        "  Company              Title                Job     Level          Date  Upvotes           Offer  Experience Difficulty                                             Review\n",
        "0   Apple  Software Engineer  Software Engineer  Engineer   Aug 7, 2019        0        No offer         0.0     Medium  Application  I applied through a staffing agen...\n",
        "1   Apple  Software Engineer  Software Engineer  Engineer   Aug 8, 2019        0  Accepted offer         1.0       Hard  Application  I applied online. The process too...\n",
        "2   Apple  Software Engineer  Software Engineer  Engineer           NaN        0  Declined offer         0.0     Medium  Application  The process took 4 weeks. I inter...\n",
        "3   Apple  Software Engineer  Software Engineer  Engineer           NaN        9  Declined offer        -1.0     Medium  Application  The process took a week. I interv...\n",
        "4   Apple  Software Engineer  Software Engineer  Engineer  May 29, 2009        2        No offer         0.0     Medium  Application  I applied through an employee ref..."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6eUt_9XI_IuR"
      },
      "source": [
        "### Iterating over rows\n",
        "\n",
        "#### `.apply()`\n",
        "\n",
        "- pandas documentation has [a warning box](https://pandas.pydata.org/pandas-docs/stable/user_guide/basics.html#iteration) that basically tells you not to iterate over rows because it's slow.\n",
        "- Before iterating over rows, think about what you want to do with each row, pack that into a function and use methods like `.apply()` to apply the function to all rows.\n",
        "- For example, to scale the \"Experience\" column by the number of \"Upvotes\" each review has, one way is to iterate over rows and multiple the \"Upvotes\" value by the \"Experience\" value of that row. But you can also use `.apply()` with a `lambda` function.\n",
        "- Run the following code snippet in a Colab/Jupyter notebook:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "21jdorr-_IuR"
      },
      "source": [
        "%timeit -n1 df.apply(lambda x: x[\"Experience\"] * x[\"Upvotes\"], axis=1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SX_z9GWO_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xlLByFWX_IuR"
      },
      "source": [
        "180 ms ± 16.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "x7pkclYm_IuR"
      },
      "source": [
        "#### `.iterrows()` and `.itertuples()`\n",
        "\n",
        "- If you really want to iterate over rows, one naive way is to use `.iterrows()`. It returns a generator that generates row by row and it's very slow. Run the following code snippet in a Colab/Jupyter notebook:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TOEAkhfB_IuR"
      },
      "source": [
        "%timeit -n1 [row for index, row in df.iterrows()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iSplyKp7_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "M-7_Zly5_IuR"
      },
      "source": [
        "1.42 s ± 107 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Sfvvjt7i_IuR"
      },
      "source": [
        "- Using `.iterrows()`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aoRN9Jpm_IuR"
      },
      "source": [
        "# This is what a row looks like as a pandas object\n",
        "for index, row in df.iterrows():\n",
        "    print(row)\n",
        "    break"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P6Pp3Ato_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wh8qpWMP_IuR"
      },
      "source": [
        "Company                                                   Apple\n",
        "Title                                         Software Engineer\n",
        "Job                                           Software Engineer\n",
        "Level                                                  Engineer\n",
        "Date                                                Aug 7, 2019\n",
        "Upvotes                                                       0\n",
        "Offer                                                  No offer\n",
        "Experience                                                    0\n",
        "Difficulty                                               Medium\n",
        "Review        Application  I applied through a staffing agen...\n",
        "Name: 0, dtype: object"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6saIj2vR_IuR"
      },
      "source": [
        "- `.itertuples()` returns rows in the `namedtuple` format. It still lets you access each row and it's about 40x faster than `.iterrows()`. Run the following code snippet in a Colab/Jupyter notebook:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "M8Zo7dNN_IuR"
      },
      "source": [
        "%timeit -n1 [row for row in df.itertuples()]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Si394Ldq_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WBNMFiev_IuR"
      },
      "source": [
        "24.2 ms ± 709 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_x5lPxGj_IuR"
      },
      "source": [
        "- Using `.iterrows()`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uKaynTZB_IuR"
      },
      "source": [
        "# This is what a row looks like as a namedtuple.\n",
        "for row in df.itertuples():\n",
        "    print(row)\n",
        "    break"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "w5btvgbc_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gH7tgops_IuR"
      },
      "source": [
        "Pandas(Index=0, Company='Apple', Title='Software Engineer', Job='Software Engineer', Level='Engineer', Date='Aug 7, 2019', Upvotes=0, Offer='No offer', Experience=0.0, Difficulty='Medium', Review='Application  I applied through a staffing agency. I interviewed at Apple (Sunnyvale, CA) in March 2019.  Interview  The interviewer asked me about my background. Asked few questions from the resume. Asked about my proficiency on data structures. Asked me how do you sort hashmap keys based on values.  Interview Questions Write a program that uses two threads to print the numbers from 1 to n.')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qA-FPXsk_IuR"
      },
      "source": [
        "#### Ordering slicing operations\n",
        "\n",
        "- Because pandas is column-major, if you want to do multiple slicing operations, always do the column-based slicing operations first.\n",
        "- For example, if you want to get the review from the first row of the data, there are two slicing operations:\n",
        "- get row (row-based operation)\n",
        "- get review (column-based operation)\n",
        "- Get row -> get review is 25x slower than get review -> get row.\n",
        "- **Note**: You can also just use `df.loc[0, \"Review\"]` to calculate the memory address to retrieve the item. Its performance is comparable to get review then get row."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HQtm_-hB_IuR"
      },
      "source": [
        "%timeit -n1000 df[\"Review\"][0]\n",
        "%timeit -n1000 df.iloc[0][\"Review\"]\n",
        "%timeit -n1000 df.loc[0, \"Review\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DlltCFcm_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aOZIHzVX_IuR"
      },
      "source": [
        "5.55 µs ± 1.05 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
        "136 µs ± 2.57 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n",
        "6.23 µs ± 264 ns per loop (mean ± std. dev. of 7 runs, 1000 loops each)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GNi6y001_IuR"
      },
      "source": [
        "### SettingWithCopyWarning\n",
        "\n",
        "- Sometimes, when you try to assign values to a subset of data in a DataFrame, you get `SettingWithCopyWarning`. Don't ignore the warning because it means sometimes, the assignment works (example 1), but sometimes, it doesn't (example 2).\n",
        "\n",
        "- Example 1: Changing the review of the first row:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "27BLDS_0_IuR"
      },
      "source": [
        "df[\"Review\"][0] = \"I like Orange better.\"\n",
        "# Even though with the warning, the assignment works. The review is updated.\n",
        "df.head(1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kNjxAwRs_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MvKtCuHI_IuR"
      },
      "source": [
        "/Users/chip/miniconda3/envs/py3/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
        "A value is trying to be set on a copy of a slice from a DataFrame\n",
        "\n",
        "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
        "\n",
        "  Company              Title                Job     Level         Date  Upvotes     Offer  Experience Difficulty                 Review\n",
        "0   Apple  Software Engineer  Software Engineer  Engineer  Aug 7, 2019        0  No offer         0.0     Medium  I like Orange better."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VvMAkvaW_IuR"
      },
      "source": [
        "- Example 2: Changing the company name Apple to Orange:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aCIRrMDy_IuR"
      },
      "source": [
        "df[df[\"Company\"] == \"Apple\"][\"Company\"] = \"Orange\"\n",
        "# With the warning, the assignment doesn't work. The company name is still Apple.\n",
        "df.head(1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DbArPb-1_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rZrD6BnT_IuR"
      },
      "source": [
        "/Users/chip/miniconda3/envs/py3/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
        "A value is trying to be set on a copy of a slice from a DataFrame.\n",
        "Try using .loc[row_indexer,col_indexer] = value instead\n",
        "\n",
        "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
        "\n",
        "  Company              Title                Job     Level         Date  Upvotes     Offer  Experience Difficulty                 Review\n",
        "0   Apple  Software Engineer  Software Engineer  Engineer  Aug 7, 2019        0  No offer         0.0     Medium  I like Orange better."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "b6rA_Ryg_IuR"
      },
      "source": [
        "#### `View` vs. `Copy`\n",
        "\n",
        "- To understand this weird behavior, we need to understand two concepts in pandas: `View` vs. `Copy`.\n",
        "- `View` is the actual `DataFrame` you want to work with.\n",
        "- `Copy` is a copy of that actual `DataFrame`, which will be thrown away as soon as the operation is done.\n",
        "\n",
        "- So if you try to do an assignment on a `Copy`, the assignment won't work. \n",
        "\n",
        "- `SettingWithCopyWarning` doesn't mean you're making changes to a `Copy`. It means that the thing you're making changes to might be a `Copy` or a `View`, and pandas can't tell you.\n",
        "\n",
        "- The ambiguity happens because of `__getitem__` operation. `__getitem__` sometimes returns a `Copy`, sometimes a `View`, and pandas makes no guarantee."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VD3nwPHv_IuR"
      },
      "source": [
        "# df[\"Review\"][0] = \"I like Orange better.\"\n",
        "# can be understood as\n",
        "# `df.__getitem__(\"Review\").__setitem__(0, \"I like Orange better.\")`"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dqPS0e1Y_IuR"
      },
      "source": [
        "# df[df[\"Company\"] == \"Apple\"][\"Company\"] = \"Orange\"\n",
        "# can be understood as\n",
        "# df.__getitem__(where df[\"Company\"] == \"Apple\").__setitem__(\"Company\", \"Orange\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vHJSNFaU_IuR"
      },
      "source": [
        "#### Solutions\n",
        "\n",
        "##### Combine all chained operations into one single operation\n",
        "\n",
        "- To avoid `__getitem__` ambiguity, you can combine all your operations into one single operation. `.loc[]` is usually great for that.\n",
        "- Changing the review of the first row:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fNdx5KpI_IuR"
      },
      "source": [
        "df.loc[0, \"Review\"] = \"Orange is love. Orange is life.\"\n",
        "df.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "S1KbAeKI_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UMu13Gmw_IuR"
      },
      "source": [
        "  Company              Title                Job     Level          Date  Upvotes           Offer  Experience Difficulty                                             Review\n",
        "0   Apple  Software Engineer  Software Engineer  Engineer   Aug 7, 2019        0        No offer         0.0     Medium                    Orange is love. Orange is life.\n",
        "1   Apple  Software Engineer  Software Engineer  Engineer   Aug 8, 2019        0  Accepted offer         1.0       Hard  Application  I applied online. The process too...\n",
        "2   Apple  Software Engineer  Software Engineer  Engineer           NaN        0  Declined offer         0.0     Medium  Application  The process took 4 weeks. I inter...\n",
        "3   Apple  Software Engineer  Software Engineer  Engineer           NaN        9  Declined offer        -1.0     Medium  Application  The process took a week. I interv...\n",
        "4   Apple  Software Engineer  Software Engineer  Engineer  May 29, 2009        2        No offer         0.0     Medium  Application  I applied through an employee ref..."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "woFmlkLB_IuR"
      },
      "source": [
        "- Changing the company name `Apple` to `Orange`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VzTIX1Fx_IuR"
      },
      "source": [
        "df.loc[df[\"Company\"] == \"Apple\", \"Company\"] = \"Orange\"\n",
        "df.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Wgft4w4g_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fUG7CME2_IuR"
      },
      "source": [
        "Company              Title                Job     Level          Date  Upvotes           Offer  Experience Difficulty                                             Review\n",
        "0  Orange  Software Engineer  Software Engineer  Engineer   Aug 7, 2019        0        No offer         0.0     Medium                    Orange is love. Orange is life.\n",
        "1  Orange  Software Engineer  Software Engineer  Engineer   Aug 8, 2019        0  Accepted offer         1.0       Hard  Application  I applied online. The process too...\n",
        "2  Orange  Software Engineer  Software Engineer  Engineer           NaN        0  Declined offer         0.0     Medium  Application  The process took 4 weeks. I inter...\n",
        "3  Orange  Software Engineer  Software Engineer  Engineer           NaN        9  Declined offer        -1.0     Medium  Application  The process took a week. I interv...\n",
        "4  Orange  Software Engineer  Software Engineer  Engineer  May 29, 2009        2        No offer         0.0     Medium  Application  I applied through an employee ref..."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YV80the-_IuR"
      },
      "source": [
        "##### Raise an error\n",
        "\n",
        "- `SettingWithCopyWarning` should ideally be an Exception instead of a warning. You can change this warning into an exception with pandas' magic `set_option()`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GfNiZIYo_IuR"
      },
      "source": [
        "pd.set_option(\"mode.chained_assignment\", \"raise\")\n",
        "# Running this will show you an Exception\n",
        "# df[\"Review\"][0] = \"I like Orange better.\""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bRWgIQip_IuR"
      },
      "source": [
        "### Indexing and slicing\n",
        "\n",
        "#### `.iloc[]`: selecting rows based on integer indices\n",
        "\n",
        "- `.iloc[]` lets you select rows by integer indices.\n",
        "- Accessing the third row of a `DataFrame`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qbij6zuO_IuR"
      },
      "source": [
        "df.iloc[3]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RBCOPuMr_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_ZHXoH4N_IuR"
      },
      "source": [
        "Company                                                  Orange\n",
        "Title                                         Software Engineer\n",
        "Job                                           Software Engineer\n",
        "Level                                                  Engineer\n",
        "Date                                                        NaN\n",
        "Upvotes                                                       9\n",
        "Offer                                            Declined offer\n",
        "Experience                                                   -1\n",
        "Difficulty                                               Medium\n",
        "Review        Application  The process took a week. I interv...\n",
        "Name: 3, dtype: object"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lA1DlTkO_IuR"
      },
      "source": [
        "- Slicing with `.iloc[]` is similar to slicing in Python. If you want a refresh on how slicing in Python works, see our [Python Tutorial](../python3).\n",
        "- Selecting the last 6 rows:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WBEjZagy_IuR"
      },
      "source": [
        "df.iloc[-6:]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ERsAXOdw_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-LvpM1tg_IuR"
      },
      "source": [
        "       Company              Title                Job     Level          Date  Upvotes           Offer  Experience Difficulty                                             Review\n",
        "17648  Tencent  Software Engineer  Software Engineer  Engineer   Nov 4, 2012        0        No offer         NaN        NaN  Application  I applied online. The process too...\n",
        "17649  Tencent  Software Engineer  Software Engineer  Engineer  May 25, 2012        0  Declined offer         0.0     Medium  Application  I applied online. The process too...\n",
        "17650  Tencent  Software Engineer  Software Engineer  Engineer  Mar 15, 2014        0        No offer         NaN        NaN  Application  I applied through college or univ...\n",
        "17651  Tencent  Software Engineer  Software Engineer  Engineer  Sep 22, 2015        0  Accepted offer         1.0     Medium  Application  I applied through college or univ...\n",
        "17652  Tencent  Software Engineer  Software Engineer  Engineer   Jul 4, 2017        0  Declined offer         1.0     Medium  Application  I applied through college or univ...\n",
        "17653  Tencent  Software Engineer  Software Engineer  Engineer  Sep 30, 2016        0  Declined offer         0.0       Easy  Application  I applied online. The process too..."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eztw2q8r_IuR"
      },
      "source": [
        "- Selecting 1 from every 2 rows in the last 6 rows:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ixeudWRV_IuR"
      },
      "source": [
        "df.iloc[-6::2]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WbvcsYRf_IuR"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-3gZ7l-P_IuS"
      },
      "source": [
        "    Company Title   Job Level   Date    Upvotes Offer   Experience  Difficulty  Review\n",
        "17648   Tencent Software Engineer   Software Engineer   Engineer    Nov 4, 2012 0   No offer    NaN NaN Application I applied online. The process too...\n",
        "17650   Tencent Software Engineer   Software Engineer   Engineer    Mar 15, 2014    0   No offer    NaN NaN Application I applied through college or univ...\n",
        "17652   Tencent Software Engineer   Software Engineer   Engineer    Jul 4, 2017 0   Declined offer  1.0 Medium  Application I applied through college or univ..."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aoAmFgtm_IuS"
      },
      "source": [
        "#### `.loc[]`: selecting rows by labels or boolean masks\n",
        "\n",
        "- `.loc[]` lets you select rows based on one of the two things:\n",
        "- boolean masks\n",
        "- labels \n",
        "\n",
        "##### Selecting rows by boolean masks\n",
        "\n",
        "- If you want to select all the rows where candidates declined offer, you can do it with two steps:\n",
        "1. Create a boolean mask on whether the \"Offer\" column equals to \"Declined offer\"\n",
        "2. Use that mask to select rows"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nc7EseKD_IuS"
      },
      "source": [
        "df.loc[df[\"Offer\"] == \"Declined offer\"]\n",
        "# This is equivalent to:\n",
        "# mask = df[\"Offer\"] == \"Declined offer\"\n",
        "# df.loc[mask]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NW39UZO7_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "g0duNA8H_IuS"
      },
      "source": [
        "       Company              Title                Job     Level          Date  Upvotes           Offer  Experience Difficulty                                             Review\n",
        "2       Orange  Software Engineer  Software Engineer  Engineer           NaN        0  Declined offer         0.0     Medium  Application  The process took 4 weeks. I inter...\n",
        "3       Orange  Software Engineer  Software Engineer  Engineer           NaN        9  Declined offer        -1.0     Medium  Application  The process took a week. I interv...\n",
        "7       Orange  Software Engineer  Software Engineer  Engineer  Jul 26, 2019        1  Declined offer        -1.0     Medium  Application  The process took 4+ weeks. I inte...\n",
        "17      Orange  Software Engineer  Software Engineer  Engineer  Feb 27, 2010        7  Declined offer        -1.0     Medium  Application  The process took 1 day. I intervi...\n",
        "65      Orange  Software Engineer  Software Engineer  Engineer   May 6, 2012        1  Declined offer         1.0       Easy  Application  The process took 2 days. I interv...\n",
        "...        ...                ...                ...       ...           ...      ...             ...         ...        ...                                                ...\n",
        "17643  Tencent  Software Engineer  Software Engineer  Engineer   Apr 9, 2016        0  Declined offer         1.0     Medium  Application  I applied online. I interviewed a...\n",
        "17646  Tencent  Software Engineer  Software Engineer  Engineer  May 28, 2010        0  Declined offer         0.0       Easy  Application  I applied through an employee ref...\n",
        "17649  Tencent  Software Engineer  Software Engineer  Engineer  May 25, 2012        0  Declined offer         0.0     Medium  Application  I applied online. The process too...\n",
        "17652  Tencent  Software Engineer  Software Engineer  Engineer   Jul 4, 2017        0  Declined offer         1.0     Medium  Application  I applied through college or univ...\n",
        "17653  Tencent  Software Engineer  Software Engineer  Engineer  Sep 30, 2016        0  Declined offer         0.0       Easy  Application  I applied online. The process too...\n",
        "\n",
        "[1135 rows × 10 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_sVal6Uq_IuS"
      },
      "source": [
        "##### Selecting rows by labels\n",
        "\n",
        "###### Creating labels\n",
        "\n",
        "- Currently, our `DataFrame` has no labels yet. To create labels, use `.set_index()`.\n",
        "\n",
        "1. Labels can be integers or strings\n",
        "2. A `DataFrame` can have multiple labels"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iRvQHSED_IuS"
      },
      "source": [
        "# Adding label \"Hardware\" if the company name is \"Orange\", \"Dell\", \"IDM\", or \"Siemens\".\n",
        "# \"Orange\" because we changed \"Apple\" to \"Orange\" above.\n",
        "# Adding label \"Software\" otherwise.\n",
        "\n",
        "def company_type(x):\n",
        "    hardware_companies = set([\"Orange\", \"Dell\", \"IBM\", \"Siemens\"])\n",
        "    return \"Hardware\" if x[\"Company\"] in hardware_companies else \"Software\"\n",
        "df[\"Type\"] = df.apply(lambda x: company_type(x), axis=1)\n",
        "\n",
        "# Setting \"Type\" to be labels. We call \"\"\n",
        "df = df.set_index(\"Type\")\n",
        "df\n",
        "# Label columns aren't considered part of the DataFrame's content.\n",
        "# After adding labels to your DataFrame, it still has 10 columns, same as before."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cn_NHhsZ_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Q5X6uR42_IuS"
      },
      "source": [
        "       Company              Title                Job     Level          Date  Upvotes           Offer  Experience Difficulty                                             Review\n",
        "0       Orange  Software Engineer  Software Engineer  Engineer   Aug 7, 2019        0        No offer         0.0     Medium                    Orange is love. Orange is life.\n",
        "1       Orange  Software Engineer  Software Engineer  Engineer   Aug 8, 2019        0  Accepted offer         1.0       Hard  Application  I applied online. The process too...\n",
        "2       Orange  Software Engineer  Software Engineer  Engineer           NaN        0  Declined offer         0.0     Medium  Application  The process took 4 weeks. I inter...\n",
        "3       Orange  Software Engineer  Software Engineer  Engineer           NaN        9  Declined offer        -1.0     Medium  Application  The process took a week. I interv...\n",
        "4       Orange  Software Engineer  Software Engineer  Engineer  May 29, 2009        2        No offer         0.0     Medium  Application  I applied through an employee ref...\n",
        "...        ...                ...                ...       ...           ...      ...             ...         ...        ...                                                ...\n",
        "17649  Tencent  Software Engineer  Software Engineer  Engineer  May 25, 2012        0  Declined offer         0.0     Medium  Application  I applied online. The process too...\n",
        "17650  Tencent  Software Engineer  Software Engineer  Engineer  Mar 15, 2014        0        No offer         NaN        NaN  Application  I applied through college or univ...\n",
        "17651  Tencent  Software Engineer  Software Engineer  Engineer  Sep 22, 2015        0  Accepted offer         1.0     Medium  Application  I applied through college or univ...\n",
        "17652  Tencent  Software Engineer  Software Engineer  Engineer   Jul 4, 2017        0  Declined offer         1.0     Medium  Application  I applied through college or univ...\n",
        "17653  Tencent  Software Engineer  Software Engineer  Engineer  Sep 30, 2016        0  Declined offer         0.0       Easy  Application  I applied online. The process too...\n",
        "\n",
        "[17654 rows x 10 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YVU6b-bK_IuS"
      },
      "source": [
        "- **Warning**: labels in `DataFrame` are stored as normal columns when you write the `DataFrame` to file using `.to_csv()`, and will need to be explicitly set after loading files, so if you send your CSV file to other people without explaining, they'll have no way of knowing which columns are labels. This might cause reproducibility issues. See [Stack Overflow answer](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples).\n",
        "\n",
        "###### Selecting rows by labels\n",
        "\n",
        "- Selecting rows with label \"Hardware\":"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cl0Naz1R_IuS"
      },
      "source": [
        "df.loc[\"Hardware\"]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "83N8k3I__IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WOByQLSA_IuS"
      },
      "source": [
        "    Company Title   Job Level   Date    Upvotes Offer   Experience  Difficulty  Review\n",
        "Type                                        \n",
        "Hardware    Orange  Software Engineer   Software Engineer   Engineer    Aug 7, 2019 0   No offer    0.0 Medium  Orange is love. Orange is life.\n",
        "Hardware    Orange  Software Engineer   Software Engineer   Engineer    Aug 8, 2019 0   Accepted offer  1.0 Hard    Application I applied online. The process too...\n",
        "Hardware    Orange  Software Engineer   Software Engineer   Engineer    NaN 0   Declined offer  0.0 Medium  Application The process took 4 weeks. I inter...\n",
        "Hardware    Orange  Software Engineer   Software Engineer   Engineer    NaN 9   Declined offer  -1.0    Medium  Application The process took a week. I interv...\n",
        "Hardware    Orange  Software Engineer   Software Engineer   Engineer    May 29, 2009    2   No offer    0.0 Medium  Application I applied through an employee ref...\n",
        "... ... ... ... ... ... ... ... ... ... ...\n",
        "Hardware    IBM Senior Software Engineer    Software Engineer   Senior  Sep 20, 2015    0   No offer    -1.0    Easy    Application I applied through a recruiter. Th...\n",
        "Hardware    IBM Senior Software Engineer    Software Engineer   Senior  Sep 14, 2015    0   Accepted offer  -1.0    Medium  Application I applied in-person. The process ...\n",
        "Hardware    IBM Senior Software Engineer    Software Engineer   Senior  Aug 6, 2015 0   Accepted offer  1.0 Hard    Application I applied through a recruiter. Th...\n",
        "Hardware    IBM Senior Software Engineer    Software Engineer   Senior  Dec 13, 2015    0   Accepted offer  1.0 Medium  Application I applied online. The process too...\n",
        "Hardware    IBM Senior Software Engineer    Software Engineer   Senior  Feb 15, 2016    11  Accepted offer  1.0 Easy    Application I applied online. The process too...\n",
        "\n",
        "[1676 rows × 10 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gfFwdrjB_IuS"
      },
      "source": [
        "- To drop a label, you need to use `reset_index` with `drop=True`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RGVDCXjD_IuS"
      },
      "source": [
        "df.reset_index(drop=True, inplace=True)\n",
        "df"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DW25s8-8_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gloGX0ve_IuS"
      },
      "source": [
        "    Company Title   Job Level   Date    Upvotes Offer   Experience  Difficulty  Review\n",
        "0   Orange  Software Engineer   Software Engineer   Engineer    Aug 7, 2019 0   No offer    0.0 Medium  Orange is love. Orange is life.\n",
        "1   Orange  Software Engineer   Software Engineer   Engineer    Aug 8, 2019 0   Accepted offer  1.0 Hard    Application I applied online. The process too...\n",
        "2   Orange  Software Engineer   Software Engineer   Engineer    NaN 0   Declined offer  0.0 Medium  Application The process took 4 weeks. I inter...\n",
        "3   Orange  Software Engineer   Software Engineer   Engineer    NaN 9   Declined offer  -1.0    Medium  Application The process took a week. I interv...\n",
        "4   Orange  Software Engineer   Software Engineer   Engineer    May 29, 2009    2   No offer    0.0 Medium  Application I applied through an employee ref...\n",
        "... ... ... ... ... ... ... ... ... ... ...\n",
        "17649   Tencent Software Engineer   Software Engineer   Engineer    May 25, 2012    0   Declined offer  0.0 Medium  Application I applied online. The process too...\n",
        "17650   Tencent Software Engineer   Software Engineer   Engineer    Mar 15, 2014    0   No offer    NaN NaN Application I applied through college or univ...\n",
        "17651   Tencent Software Engineer   Software Engineer   Engineer    Sep 22, 2015    0   Accepted offer  1.0 Medium  Application I applied through college or univ...\n",
        "17652   Tencent Software Engineer   Software Engineer   Engineer    Jul 4, 2017 0   Declined offer  1.0 Medium  Application I applied through college or univ...\n",
        "17653   Tencent Software Engineer   Software Engineer   Engineer    Sep 30, 2016    0   Declined offer  0.0 Easy    Application I applied online. The process too...\n",
        "\n",
        "[17654 rows × 10 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SiZirBHF_IuS"
      },
      "source": [
        "#### Slicing Series\n",
        "\n",
        "- Slicing pandas `Series` is similar to slicing in Python."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "A3duJJX6_IuS"
      },
      "source": [
        "series = df.Company\n",
        "# The first 1000 companies, picking every 100th companies\n",
        "series[:1000:100]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HkFXvi_r_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Gr-HDGzQ_IuS"
      },
      "source": [
        "0      Orange\n",
        "100    Orange\n",
        "200    Orange\n",
        "300    Orange\n",
        "400     Intel\n",
        "500     Intel\n",
        "600     Intel\n",
        "700     Intel\n",
        "800      Uber\n",
        "900      Uber\n",
        "Name: Company, dtype: object"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PFomUd9o_IuS"
      },
      "source": [
        "### Accessors\n",
        "\n",
        "#### string accessor\n",
        "\n",
        "- `.str` allows you to apply built-in string functions to all strings in a column (aka a pandas Series). These built-in functions come in handy when you want to do some basic string processing. For instance, if you want to lowercase all the reviews in the `Reviews` column:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dvm0myv-_IuS"
      },
      "source": [
        "df[\"Review\"].str.lower()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tjNXn9jQ_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GISLMljZ_IuS"
      },
      "source": [
        "0                          orange is love. orange is life.\n",
        "1        application  i applied online. the process too...\n",
        "2        application  the process took 4 weeks. i inter...\n",
        "3        application  the process took a week. i interv...\n",
        "4        application  i applied through an employee ref...\n",
        "                               ...                        \n",
        "17649    application  i applied online. the process too...\n",
        "17650    application  i applied through college or univ...\n",
        "17651    application  i applied through college or univ...\n",
        "17652    application  i applied through college or univ...\n",
        "17653    application  i applied online. the process too...\n",
        "Name: Review, Length: 17654, dtype: object"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6o1mTgqH_IuS"
      },
      "source": [
        "- Or if you want to get the length of all the reviews:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3cLcyCJp_IuS"
      },
      "source": [
        "df.Review.str.len()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2xnHgCN5_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "01l3IdyL_IuS"
      },
      "source": [
        "0         31\n",
        "1        670\n",
        "2        350\n",
        "3        807\n",
        "4        663\n",
        "        ... \n",
        "17649    470\n",
        "17650    394\n",
        "17651    524\n",
        "17652    391\n",
        "17653    784\n",
        "Name: Review, Length: 17654, dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BJLVompz_IuS"
      },
      "source": [
        "- `.str` can be very powerful if you use it with Regex. Imagine you want to get a sense of how long the interview process takes for each review. You notice that each review mentions how long it takes such as \"the process took 4 weeks\". So you use these heuristics:\n",
        "- A process is short if it takes days.\n",
        "- A process is average is if it takes weeks.\n",
        "- A process is long if it takes at least 4 weeks."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HdUrPe8t_IuS"
      },
      "source": [
        "df.loc[df[\"Review\"].str.contains(\"days\"), \"Process\"] = \"Short\"\n",
        "df.loc[df[\"Review\"].str.contains(\"week\"), \"Process\"] = \"Average\"\n",
        "df.loc[df[\"Review\"].str.contains(\"month|[4-9]+[^ ]* weeks|[1-9]\\d{1,}[^ ]* weeks\"), \"Process\"] = \"Long\"\n",
        "df[~df.Process.isna()][[\"Review\", \"Process\"]]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q_DkB5lR_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "R9ZLXDbx_IuS"
      },
      "source": [
        "    Review  Process\n",
        "1   Application I applied online. The process too...    Long\n",
        "2   Application The process took 4 weeks. I inter...    Long\n",
        "3   Application The process took a week. I interv...    Average\n",
        "5   Application I applied through college or univ...    Long\n",
        "6   Application The process took 2 days. I interv...    Short\n",
        "... ... ...\n",
        "17645   Application I applied online. The process too...    Average\n",
        "17647   Application I applied through college or univ...    Average\n",
        "17648   Application I applied online. The process too...    Short\n",
        "17649   Application I applied online. The process too...    Average\n",
        "17653   Application I applied online. The process too...    Average\n",
        "\n",
        "[12045 rows × 2 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jz76okRU_IuS"
      },
      "source": [
        "- We want to sanity check if `Process` corresponds to `Review`, but `Review` is cut off in the display above. To show longer columns, you can set `display.max_colwidth` to `100`. **Note**: set_option has several great options you should check out."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jHh-lk8u_IuS"
      },
      "source": [
        "pd.set_option('display.max_colwidth', 100)\n",
        "df[~df.Process.isna()][[\"Review\", \"Process\"]]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3X1nToc7_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0poIa3jG_IuS"
      },
      "source": [
        "Review  Process\n",
        "1   Application I applied online. The process took 2+ months. I interviewed at Apple (San Jose, CA)...  Long\n",
        "2   Application The process took 4 weeks. I interviewed at Apple (San Antonio, TX) in February 2016...  Long\n",
        "3   Application The process took a week. I interviewed at Apple (Cupertino, CA) in December 2008. ...   Average\n",
        "5   Application I applied through college or university. The process took 6 weeks. I interviewed at...  Long\n",
        "6   Application The process took 2 days. I interviewed at Apple (Cupertino, CA) in March 2009. Int...   Short\n",
        "... ... ...\n",
        "17645   Application I applied online. The process took a week. I interviewed at Tencent (Palo Alto, CA)...  Average\n",
        "17647   Application I applied through college or university. The process took 4+ weeks. I interviewed a...  Average\n",
        "17648   Application I applied online. The process took 2 days. I interviewed at Tencent. Interview I ...    Short\n",
        "17649   Application I applied online. The process took a week. I interviewed at Tencent (Beijing, Beiji...  Average\n",
        "17653   Application I applied online. The process took 3+ weeks. I interviewed at Tencent (Beijing, Bei...  Average\n",
        "\n",
        "[12045 rows × 2 columns]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "S2Gzs4k__IuS"
      },
      "source": [
        "- To see the built-in functions available for `.str`, use this:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rMczuwx9_IuS"
      },
      "source": [
        "pd.Series.str.__dict__.keys()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HgA9U39P_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4vSoqpyJ_IuS"
      },
      "source": [
        "dict_keys(['__module__', '__annotations__', '__doc__', '__init__', '_validate', '__getitem__', '__iter__', '_wrap_result', '_get_series_list', 'cat', 'split', 'rsplit', 'partition', 'rpartition', 'get', 'join', 'contains', 'match', 'fullmatch', 'replace', 'repeat', 'pad', 'center', 'ljust', 'rjust', 'zfill', 'slice', 'slice_replace', 'decode', 'encode', 'strip', 'lstrip', 'rstrip', 'wrap', 'get_dummies', 'translate', 'count', 'startswith', 'endswith', 'findall', 'extract', 'extractall', 'find', 'rfind', 'normalize', 'index', 'rindex', 'len', '_doc_args', 'lower', 'upper', 'title', 'capitalize', 'swapcase', 'casefold', 'isalnum', 'isalpha', 'isdigit', 'isspace', 'islower', 'isupper', 'istitle', 'isnumeric', 'isdecimal', '_make_accessor'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "In9LFyqk_IuS"
      },
      "source": [
        "#### Other accessors\n",
        "\n",
        "- pandas `Series` has three other accessors:\n",
        "- `.dt`: handles date formats.\n",
        "- `.cat`: handles categorical data.\n",
        "- `.sparse`: handles sparse matrices."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MS7kR7_1_IuS"
      },
      "source": [
        "pd.Series._accessors"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XSJXxdHt_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QleUtf-R_IuS"
      },
      "source": [
        "{'cat', 'dt', 'sparse', 'str'}"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wKrsGCHv_IuS"
      },
      "source": [
        "### Data exploration\n",
        "\n",
        "- When analyzing data, you might want to take a look at the data. pandas has some great built-in functions for that.\n",
        "\n",
        "#### `.head()`, `.tail()`, `.describe()`, `.info()`\n",
        "\n",
        "- You're probably familiar with `.head()` and `.tail()` methods for showing the first/last rows of `DataFrame`. By default, five rows are shown, but you can specify the exact number."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hup94nhu_IuS"
      },
      "source": [
        "df.tail(8)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eEMEYz1g_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TuKSSrAe_IuS"
      },
      "source": [
        "Company Title   Job Level   Date    Upvotes Offer   Experience  Difficulty  Review  Process\n",
        "17646   Tencent Software Engineer   Software Engineer   Engineer    May 28, 2010    0   Declined offer  0.0 Easy    Application I applied through an employee referral. The process took 1 day. I interviewed at Te...  NaN\n",
        "17647   Tencent Software Engineer   Software Engineer   Engineer    Apr 11, 2019    0   Accepted offer  1.0 Medium  Application I applied through college or university. The process took 4+ weeks. I interviewed a...  Average\n",
        "17648   Tencent Software Engineer   Software Engineer   Engineer    Nov 4, 2012 0   No offer    NaN NaN Application I applied online. The process took 2 days. I interviewed at Tencent. Interview I ...    Short\n",
        "17649   Tencent Software Engineer   Software Engineer   Engineer    May 25, 2012    0   Declined offer  0.0 Medium  Application I applied online. The process took a week. I interviewed at Tencent (Beijing, Beiji...  Average\n",
        "17650   Tencent Software Engineer   Software Engineer   Engineer    Mar 15, 2014    0   No offer    NaN NaN Application I applied through college or university. I interviewed at Tencent. Interview Prof...    NaN\n",
        "17651   Tencent Software Engineer   Software Engineer   Engineer    Sep 22, 2015    0   Accepted offer  1.0 Medium  Application I applied through college or university. The process took 1 day. I interviewed at T...  NaN\n",
        "17652   Tencent Software Engineer   Software Engineer   Engineer    Jul 4, 2017 0   Declined offer  1.0 Medium  Application I applied through college or university. I interviewed at Tencent (London, England ...  NaN\n",
        "17653   Tencent Software Engineer   Software Engineer   Engineer    Sep 30, 2016    0   Declined offer  0.0 Easy    Application I applied online. The process took 3+ weeks. I interviewed at Tencent (Beijing, Bei...  Average"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "A30FFlbz_IuS"
      },
      "source": [
        "- You can generate statistics about numeric columns using `.describe()`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wo9jwzkN_IuS"
      },
      "source": [
        "df.describe()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0mBjCdFo_IuS"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vapOX77l_IuS"
      },
      "source": [
        "Upvotes Experience\n",
        "count   17654.000000    16365.000000\n",
        "mean    2.298459    0.431714\n",
        "std 28.252562   0.759964\n",
        "min 0.000000    -1.000000\n",
        "25% 0.000000    0.000000\n",
        "50% 0.000000    1.000000\n",
        "75% 1.000000    1.000000\n",
        "max 1916.000000 1.000000"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xgj0PbDA_IuT"
      },
      "source": [
        "- However, note that `.describe()` ignores all non-numeric columns. It doesn't take into account NaN values. So, the number shown in `count` above is the number of non-NaN entries.\n",
        "\n",
        "- To show non-null count and types of all columns, use `.info()`. Note that pandas treats the `String` type as an object."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "I_pK_8XB_IuT"
      },
      "source": [
        "df.info()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i7AF3ZJx_IuT"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "u9NOenlV_IuT"
      },
      "source": [
        "<class 'pandas.core.frame.DataFrame'>\n",
        "RangeIndex: 17654 entries, 0 to 17653\n",
        "Data columns (total 11 columns):\n",
        " #   Column      Non-Null Count  Dtype  \n",
        "---  ------      --------------  -----  \n",
        " 0   Company     17654 non-null  object \n",
        " 1   Title       17654 non-null  object \n",
        " 2   Job         17654 non-null  object \n",
        " 3   Level       17654 non-null  object \n",
        " 4   Date        17652 non-null  object \n",
        " 5   Upvotes     17654 non-null  int64  \n",
        " 6   Offer       17654 non-null  object \n",
        " 7   Experience  16365 non-null  float64\n",
        " 8   Difficulty  16376 non-null  object \n",
        " 9   Review      17654 non-null  object \n",
        " 10  Process     12045 non-null  object \n",
        "dtypes: float64(1), int64(1), object(9)\n",
        "memory usage: 1.5+ MB"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VHR6xD6z_IuT"
      },
      "source": [
        "- You can also see how much space your DataFrame is taking up using :"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e5U66Hyc_IuT"
      },
      "source": [
        "import sys\n",
        "df.apply(sys.getsizeof)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6mqmCtIZ_IuT"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tJ3wkzT4_IuT"
      },
      "source": [
        "Company        1126274\n",
        "Title          1358305\n",
        "Job            1345085\n",
        "Level          1144784\n",
        "Date           1212964\n",
        "Upvotes         141384\n",
        "Offer          1184190\n",
        "Experience      141384\n",
        "Difficulty     1058008\n",
        "Review        20503467\n",
        "Process        1274180\n",
        "dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zcOGfRwA_IuT"
      },
      "source": [
        "#### Count unique values\n",
        "\n",
        "- You can get the number of unique values in a row (excluding NaN) with `nunique()`. For instance, to get the number of unique companies in our data:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "95yRC1VK_IuT"
      },
      "source": [
        "df.Company.nunique()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "21mRJ4bQ_IuT"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3IGSDEfk_IuT"
      },
      "source": [
        "28"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fR5h4Gct_IuT"
      },
      "source": [
        "- You can also see how many reviews are for each company, sorted in descending order:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "20jFKVyb_IuT"
      },
      "source": [
        "df.Company.value_counts()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jua8I5Qe_IuT"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-GNomFtx_IuT"
      },
      "source": [
        "Amazon        3469\n",
        "Google        3445\n",
        "Facebook      1817\n",
        "Microsoft     1790\n",
        "IBM            873\n",
        "Cisco          787\n",
        "Oracle         701\n",
        "Uber           445\n",
        "Yelp           404\n",
        "Orange         363\n",
        "Intel          338\n",
        "Salesforce     313\n",
        "SAP            275\n",
        "Twitter        258\n",
        "Dell           258\n",
        "Airbnb         233\n",
        "NVIDIA         229\n",
        "Adobe          211\n",
        "Intuit         203\n",
        "PayPal         193\n",
        "Siemens        182\n",
        "Square         177\n",
        "Samsung        159\n",
        "eBay           148\n",
        "Symantec       147\n",
        "Snap           113\n",
        "Netflix        109\n",
        "Tencent         14\n",
        "Name: Company, dtype: int64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FglVtjID_IuT"
      },
      "source": [
        "#### Plotting\n",
        "\n",
        "- If you want to see the break down of process lengths for different companies, you can use `.plot()` with `.groupby()`.\n",
        "- **Note**: Plotting in pandas is both mind-boggling and mind-blowing. If you're not familiar, you might want to check out some tutorials, e.g. [this simple tutorial](https://realpython.com/pandas-plot-python/) or [this saiyan-level pandas plotting with seaborn](https://jakevdp.github.io/PythonDataScienceHandbook/04.14-visualization-with-seaborn.html)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "A_2DJdUd_IuT"
      },
      "source": [
        "# Group the DataFrame by \"Company\" and \"Process\", count the number of elements,\n",
        "# then unstack by \"Process\", then plot a bar chart\n",
        "df.groupby([\"Company\", \"Process\"]).size().unstack(level=1).plot(kind=\"bar\", figsize=(15, 8))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2dQV9c4S_IuT"
      },
      "source": [
        "- which outputs:\n",
        "\n",
        "<img src=\"https://github.com/amanchadha/aman-ai/blob/assets/pandas/plot.png?raw=1\" align=\"center\" style=\"background-color: #fff; margin: 10px auto\" />\n",
        "\n",
        "### Common pitfalls\n",
        "\n",
        "- pandas is great for most day-to-day data analysis. There is an active community that is working on regular updates to the library. However, some of the design decisions can seen a bit questionable.\n",
        "- Some of the common pandas pitfalls:\n",
        "\n",
        "#### NaNs as floats\n",
        "\n",
        "- NaNs are stored as floats in pandas, so when an operation fails because of NaNs, it doesn't say that there's a NaN but because that operation isn't defined for floats.\n",
        "\n",
        "#### Changes aren't in-place by default\n",
        "\n",
        "- Most pandas operations aren't in-place by default, so if you make changes to your `DataFrame`, you need to assign the changes back to your DataFrame. You can make changes in-place by setting argument `inplace=True`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MdPLoXfu_IuT"
      },
      "source": [
        "# \"Process\" column is still in df\n",
        "df.drop(columns=[\"Process\"])\n",
        "df.columns"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WzuBwwzd_IuT"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nn5GX_Vg_IuT"
      },
      "source": [
        "Index(['Company', 'Title', 'Job', 'Level', 'Date', 'Upvotes', 'Offer',\n",
        "       'Experience', 'Difficulty', 'Review', 'Process'],\n",
        "      dtype='object')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GrKC_w8m_IuT"
      },
      "source": [
        "- To make changes to df, set `inplace=True`:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D1cHboY9_IuT"
      },
      "source": [
        "df.drop(columns=[\"Process\"], inplace=True)\n",
        "df.columns\n",
        "# This is equivalent to\n",
        "# df = df.drop(columns=[\"Process\"])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WY1-ve3F_IuT"
      },
      "source": [
        "- which outputs:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Vsh8zrrh_IuT"
      },
      "source": [
        "Index(['Company', 'Title', 'Job', 'Level', 'Date', 'Upvotes', 'Offer',\n",
        "       'Experience', 'Difficulty', 'Review'],\n",
        "      dtype='object')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pTH7mffa_IuT"
      },
      "source": [
        "#### Reproducibility issues\n",
        "\n",
        "- Especially with dumping and loading `DataFrame` to/from files. There are two main causes:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "scGoDXfs_IuT"
      },
      "source": [
        "- Problem with [labels](#creating-labels) (see the section about labels above).\n",
        "- [Weird rounding issues for floats](https://stackoverflow.com/questions/47368296/pandas-read-csv-file-with-float-values-results-in-weird-rounding-and-decimal-dig). "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NZjTZoG3_IuT"
      },
      "source": [
        "#### Not GPU compatible\n",
        "\n",
        "- pandas can't take advantage of GPUs, so if your computations are on on GPUs and your feature engineering is on CPUs, it can become a time bottleneck to move data from CPUs to GPUs. If you want something like pandas but works on GPUs, check out [dask](https://dask.org/) and [modin](https://github.com/modin-project/modin).\n",
        "\n",
        "## What next?\n",
        "\n",
        "- As you probably noticed by now, pandas is quite a large library with **many** features. Although we went through the most important features, there is still a lot to discover. Probably the best way to learn more is to get your hands dirty with some real-life data. It is also a good idea to go through pandas' excellent [documentation](http://pandas.pydata.org/pandas-docs/stable/index.html), in particular the [Cookbook](http://pandas.pydata.org/pandas-docs/stable/cookbook.html).\n",
        "\n",
        "## References and Credits\n",
        "\n",
        "- Aurélien Geron's [Notebook on Matplotlib](https://github.com/ageron/handson-ml2/blob/master/tools_matplotlib.ipynb) and [Hands-on Machine Learning with Scikit-Learn, Keras and TensorFlow](https://www.oreilly.com/library/view/hands-on-machine-learning/9781492032632/) served as a major inspiration for this tutorial.\n",
        "- [pandas.DataFrame.to_dict()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_dict.html)\n",
        "- [Constructing pandas DataFrame from values in variables gives “ValueError: If using all scalar values, you must pass an index”](https://stackoverflow.com/questions/17839973/constructing-pandas-dataframe-from-values-in-variables-gives-valueerror-if-usi)\n",
        "\n",
        "## Citation\n",
        "\n",
        "If you found our work useful, please cite it as:\n",
        "\n",
        "```\n",
        "@article{Chadha2020DistilledPandasTutorial,\n",
        "  title   = {Pandas Tutorial},\n",
        "  author  = {Chadha, Aman},\n",
        "  journal = {Distilled AI},\n",
        "  year    = {2020},\n",
        "  note    = {\\url{https://aman.ai}}\n",
        "}\n",
        "```"
      ]
    }
  ]
}