Python Data Management Part 4.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/AKerby/dsci_325_module6_basic_data_management_in_python/blob/main/Python%20Data%20Management%20Part%204.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "d3c269f2",
      "metadata": {
        "id": "d3c269f2"
      },
      "source": [
        "# Advanced Filters\n",
        "\n",
        "In this section, we will take a closer look at common filtering patterns.  Note that this list is based on the Common Filter Operations section of the [SQL Alchemy tutorial](https://docs.sqlalchemy.org/en/latest/orm/tutorial.html) from the SQL Alchemy documentation, which is copyright © by SQLAlchemy authors and contributors. SQLAlchemy and its documentation are licensed under the MIT license."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "id": "4cc5408e",
      "metadata": {
        "id": "4cc5408e"
      },
      "outputs": [],
      "source": [
        "import polars as pl\n",
        "pl.Config.with_columns_kwargs = True\n",
        "\n",
        "import seaborn as sns\n",
        "%matplotlib inline"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "bff24d8c",
      "metadata": {
        "id": "bff24d8c"
      },
      "source": [
        "## Common Filter Operators\n",
        "\n",
        "Most filters consist of the following operations.\n",
        "\n",
        "* Equals/not equals and other inequalities\n",
        "* Like/ilike\n",
        "* In/not in\n",
        "* Is Null/is not null\n",
        "* And/or\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "afa0ad23",
      "metadata": {
        "id": "afa0ad23"
      },
      "source": [
        "## How we will proceed\n",
        "\n",
        "Let's look at how each of the operations is performed in polars.  We need a dataset that is ripe for filtering, so we will return to the super hero data set.  Who doesn't love a super hero?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "id": "01e07ef2",
      "metadata": {
        "scrolled": true,
        "id": "01e07ef2",
        "outputId": "448d8fea-e9b2-4a12-ee3f-81c020a4c2ee",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 255
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (5, 11)\n",
              "┌─────┬─────────────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name        ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---         ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str         ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪═════════════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 0   ┆ A-Bomb      ┆ Male   ┆ yellow    ┆ … ┆ Marvel Comics     ┆ null       ┆ good      ┆ 441.0  │\n",
              "│ 1   ┆ Abe Sapien  ┆ Male   ┆ blue      ┆ … ┆ Dark Horse Comics ┆ blue       ┆ good      ┆ 65.0   │\n",
              "│ 2   ┆ Abin Sur    ┆ Male   ┆ blue      ┆ … ┆ DC Comics         ┆ red        ┆ good      ┆ 90.0   │\n",
              "│ 3   ┆ Abomination ┆ Male   ┆ green     ┆ … ┆ Marvel Comics     ┆ null       ┆ bad       ┆ 441.0  │\n",
              "│ 4   ┆ Abraxas     ┆ Male   ┆ blue      ┆ … ┆ Marvel Comics     ┆ null       ┆ bad       ┆ null   │\n",
              "└─────┴─────────────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (5, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>&quot;A-Bomb&quot;</td><td>&quot;Male&quot;</td><td>&quot;yellow&quot;</td><td>&quot;Human&quot;</td><td>&quot;No Hair&quot;</td><td>203.0</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>441.0</td></tr><tr><td>1</td><td>&quot;Abe Sapien&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Icthyo Sapien&quot;</td><td>&quot;No Hair&quot;</td><td>191.0</td><td>&quot;Dark Horse Com…</td><td>&quot;blue&quot;</td><td>&quot;good&quot;</td><td>65.0</td></tr><tr><td>2</td><td>&quot;Abin Sur&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Ungaran&quot;</td><td>&quot;No Hair&quot;</td><td>185.0</td><td>&quot;DC Comics&quot;</td><td>&quot;red&quot;</td><td>&quot;good&quot;</td><td>90.0</td></tr><tr><td>3</td><td>&quot;Abomination&quot;</td><td>&quot;Male&quot;</td><td>&quot;green&quot;</td><td>&quot;Human / Radiat…</td><td>&quot;No Hair&quot;</td><td>203.0</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;bad&quot;</td><td>441.0</td></tr><tr><td>4</td><td>&quot;Abraxas&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Cosmic Entity&quot;</td><td>&quot;Black&quot;</td><td>null</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;bad&quot;</td><td>null</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 3
        }
      ],
      "source": [
        "url = \"https://github.com/AKerby/dsci_325_module6_basic_data_management_in_python/raw/main/sample_data/heroes_information.csv\"\n",
        "heroes = (pl.read_csv(url, null_values=['-', '-99.0', ''])\n",
        "          .rename({'':'ID'})\n",
        "         )\n",
        "heroes.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "b94909c0",
      "metadata": {
        "id": "b94909c0"
      },
      "source": [
        "## Common Filter Operators - Equality and Inequality\n",
        "\n",
        "In all three frameworks, equalities/inequalities are performed using the regular Python operators on column expressions."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "ed8cc118",
      "metadata": {
        "id": "ed8cc118"
      },
      "source": [
        "#### Check equality using `==`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "4e0ef0b3",
      "metadata": {
        "id": "4e0ef0b3",
        "outputId": "5262b1ce-b95f-4694-b4cf-bded5d9d4f9f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬────────────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name       ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---        ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str        ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪════════════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 1   ┆ Abe Sapien ┆ Male   ┆ blue      ┆ … ┆ Dark Horse Comics ┆ blue       ┆ good      ┆ 65.0   │\n",
              "│ 2   ┆ Abin Sur   ┆ Male   ┆ blue      ┆ … ┆ DC Comics         ┆ red        ┆ good      ┆ 90.0   │\n",
              "└─────┴────────────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;Abe Sapien&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Icthyo Sapien&quot;</td><td>&quot;No Hair&quot;</td><td>191.0</td><td>&quot;Dark Horse Com…</td><td>&quot;blue&quot;</td><td>&quot;good&quot;</td><td>65.0</td></tr><tr><td>2</td><td>&quot;Abin Sur&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Ungaran&quot;</td><td>&quot;No Hair&quot;</td><td>185.0</td><td>&quot;DC Comics&quot;</td><td>&quot;red&quot;</td><td>&quot;good&quot;</td><td>90.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('Eye color') == 'blue')\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "b68b366f",
      "metadata": {
        "id": "b68b366f"
      },
      "source": [
        "#### Check not equal using `!=`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b19f451c",
      "metadata": {
        "id": "b19f451c",
        "outputId": "ab768434-aa5f-47d4-ba7e-3993caaaf7cd",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬─────────────┬────────┬───────────┬───┬───────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name        ┆ Gender ┆ Eye color ┆ … ┆ Publisher     ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---         ┆ ---    ┆ ---       ┆   ┆ ---           ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str         ┆ str    ┆ str       ┆   ┆ str           ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪═════════════╪════════╪═══════════╪═══╪═══════════════╪════════════╪═══════════╪════════╡\n",
              "│ 0   ┆ A-Bomb      ┆ Male   ┆ yellow    ┆ … ┆ Marvel Comics ┆ null       ┆ good      ┆ 441.0  │\n",
              "│ 3   ┆ Abomination ┆ Male   ┆ green     ┆ … ┆ Marvel Comics ┆ null       ┆ bad       ┆ 441.0  │\n",
              "└─────┴─────────────┴────────┴───────────┴───┴───────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>&quot;A-Bomb&quot;</td><td>&quot;Male&quot;</td><td>&quot;yellow&quot;</td><td>&quot;Human&quot;</td><td>&quot;No Hair&quot;</td><td>203.0</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>441.0</td></tr><tr><td>3</td><td>&quot;Abomination&quot;</td><td>&quot;Male&quot;</td><td>&quot;green&quot;</td><td>&quot;Human / Radiat…</td><td>&quot;No Hair&quot;</td><td>203.0</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;bad&quot;</td><td>441.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('Eye color') != 'blue')\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "570a46f1",
      "metadata": {
        "id": "570a46f1"
      },
      "source": [
        "#### Other inequalities"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "a32cf3db",
      "metadata": {
        "id": "a32cf3db",
        "outputId": "683a387c-d6b1-4560-c6da-598e29b05e42",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬───────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name  ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---   ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str   ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪═══════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 17  ┆ Alien ┆ Male   ┆ null      ┆ … ┆ Dark Horse Comics ┆ black      ┆ bad       ┆ 169.0  │\n",
              "│ 19  ┆ Amazo ┆ Male   ┆ red       ┆ … ┆ DC Comics         ┆ null       ┆ bad       ┆ 173.0  │\n",
              "└─────┴───────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>17</td><td>&quot;Alien&quot;</td><td>&quot;Male&quot;</td><td>null</td><td>&quot;Xenomorph XX12…</td><td>&quot;No Hair&quot;</td><td>244.0</td><td>&quot;Dark Horse Com…</td><td>&quot;black&quot;</td><td>&quot;bad&quot;</td><td>169.0</td></tr><tr><td>19</td><td>&quot;Amazo&quot;</td><td>&quot;Male&quot;</td><td>&quot;red&quot;</td><td>&quot;Android&quot;</td><td>null</td><td>257.0</td><td>&quot;DC Comics&quot;</td><td>null</td><td>&quot;bad&quot;</td><td>173.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('Height') > 200)\n",
        " .filter(pl.col('Weight') <= 440)\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "3c889f80",
      "metadata": {
        "id": "3c889f80"
      },
      "source": [
        "## Reminder - Referencing Constructed Column\n",
        "\n",
        "Recall that we can also reference newly constructed columns by using `pl.col('name')`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "3933088e",
      "metadata": {
        "id": "3933088e",
        "outputId": "df554676-b447-4b62-c305-6f926b324b01",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 12)\n",
              "┌─────┬────────────┬────────┬───────────┬───┬────────────┬───────────┬────────┬───────────┐\n",
              "│ ID  ┆ name       ┆ Gender ┆ Eye color ┆ … ┆ Skin color ┆ Alignment ┆ Weight ┆ Weight_kg │\n",
              "│ --- ┆ ---        ┆ ---    ┆ ---       ┆   ┆ ---        ┆ ---       ┆ ---    ┆ ---       │\n",
              "│ i64 ┆ str        ┆ str    ┆ str       ┆   ┆ str        ┆ str       ┆ f64    ┆ f64       │\n",
              "╞═════╪════════════╪════════╪═══════════╪═══╪════════════╪═══════════╪════════╪═══════════╡\n",
              "│ 1   ┆ Abe Sapien ┆ Male   ┆ blue      ┆ … ┆ blue       ┆ good      ┆ 65.0   ┆ 29.483807 │\n",
              "│ 2   ┆ Abin Sur   ┆ Male   ┆ blue      ┆ … ┆ red        ┆ good      ┆ 90.0   ┆ 40.823732 │\n",
              "└─────┴────────────┴────────┴───────────┴───┴────────────┴───────────┴────────┴───────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 12)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th><th>Weight_kg</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;Abe Sapien&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Icthyo Sapien&quot;</td><td>&quot;No Hair&quot;</td><td>191.0</td><td>&quot;Dark Horse Com…</td><td>&quot;blue&quot;</td><td>&quot;good&quot;</td><td>65.0</td><td>29.483807</td></tr><tr><td>2</td><td>&quot;Abin Sur&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Ungaran&quot;</td><td>&quot;No Hair&quot;</td><td>185.0</td><td>&quot;DC Comics&quot;</td><td>&quot;red&quot;</td><td>&quot;good&quot;</td><td>90.0</td><td>40.823732</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ],
      "source": [
        "(heroes\n",
        " .with_columns(Weight_kg = pl.col('Weight')/2.2046)\n",
        " .filter(pl.col('Weight_kg') <= 200)\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "c914fcda",
      "metadata": {
        "id": "c914fcda"
      },
      "source": [
        "## <font color=\"red\"> Exercise 6.4.1 - The Super Hero Dating Game - Part 1</font>\n",
        "\n",
        "Yesterday, you notice a singles add in the local paper that reads\n",
        "\n",
        "> SWF looking for BESHM (blue-eyed super hero).  Must be tall (70+ inches).  Only interested in bad boys! Must list height (in inches) in reply!\n",
        "\n",
        "Write a query in each framework to help find candidates for this personal add."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "04d98bd8",
      "metadata": {
        "id": "04d98bd8"
      },
      "outputs": [],
      "source": [
        "# Your solution here"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "8b325552",
      "metadata": {
        "id": "8b325552"
      },
      "source": [
        "## Common Filter Operations -  LIKE and ILIKE\n",
        "\n",
        "`LIKE` and `ILIKE` are both SQL idioms that\n",
        "\n",
        "* is used to match string patterns\n",
        "* Uses the `%` wildcard\n",
        "    * like `*` in a regular expression\n",
        "* `LIKE` is case-sensitive\n",
        "* `ILIKE` is case-insensitive\n",
        "    * Actual details are platform dependent"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1f06615f",
      "metadata": {
        "id": "1f06615f"
      },
      "source": [
        "### `polars` lacks `LIKE`/`ILIKE`\n",
        "\n",
        "Instead we use\n",
        "\n",
        "* `str.starts_with`\n",
        "* `str.ends_with`\n",
        "* `str.contains`"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "66ffe484",
      "metadata": {
        "id": "66ffe484"
      },
      "source": [
        "#### `LIKE 'Super%'`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "bdb165e2",
      "metadata": {
        "id": "bdb165e2",
        "outputId": "d9ea5fe2-f75c-46ca-b806-ad3426c32dec",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬────────────────┬────────┬───────────┬───┬───────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name           ┆ Gender ┆ Eye color ┆ … ┆ Publisher ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---            ┆ ---    ┆ ---       ┆   ┆ ---       ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str            ┆ str    ┆ str       ┆   ┆ str       ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪════════════════╪════════╪═══════════╪═══╪═══════════╪════════════╪═══════════╪════════╡\n",
              "│ 643 ┆ Superboy       ┆ Male   ┆ blue      ┆ … ┆ DC Comics ┆ null       ┆ good      ┆ 68.0   │\n",
              "│ 644 ┆ Superboy-Prime ┆ Male   ┆ blue      ┆ … ┆ DC Comics ┆ null       ┆ bad       ┆ 77.0   │\n",
              "└─────┴────────────────┴────────┴───────────┴───┴───────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>643</td><td>&quot;Superboy&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>null</td><td>&quot;Black&quot;</td><td>170.0</td><td>&quot;DC Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>68.0</td></tr><tr><td>644</td><td>&quot;Superboy-Prime…</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Kryptonian&quot;</td><td>&quot;Black / Blue&quot;</td><td>180.0</td><td>&quot;DC Comics&quot;</td><td>null</td><td>&quot;bad&quot;</td><td>77.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('name').str.starts_with('Super'))\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e588c675",
      "metadata": {
        "id": "e588c675"
      },
      "source": [
        "#### `LIKE '%boy'`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f18de23d",
      "metadata": {
        "id": "f18de23d",
        "outputId": "4497c919-1103-40f0-b388-cbfbc64494f7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬───────────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name      ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---       ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str       ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪═══════════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 142 ┆ Bumbleboy ┆ Male   ┆ null      ┆ … ┆ Marvel Comics     ┆ null       ┆ good      ┆ null   │\n",
              "│ 321 ┆ Hellboy   ┆ Male   ┆ gold      ┆ … ┆ Dark Horse Comics ┆ null       ┆ good      ┆ 158.0  │\n",
              "└─────┴───────────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>142</td><td>&quot;Bumbleboy&quot;</td><td>&quot;Male&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>null</td></tr><tr><td>321</td><td>&quot;Hellboy&quot;</td><td>&quot;Male&quot;</td><td>&quot;gold&quot;</td><td>&quot;Demon&quot;</td><td>&quot;Black&quot;</td><td>259.0</td><td>&quot;Dark Horse Com…</td><td>null</td><td>&quot;good&quot;</td><td>158.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('name').str.ends_with('boy'))\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "75fcd7e4",
      "metadata": {
        "id": "75fcd7e4"
      },
      "source": [
        "#### `LIKE '%boy%'`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "2fcebeff",
      "metadata": {
        "id": "2fcebeff",
        "outputId": "4d9e3fad-0db5-458e-9f70-670368ee1468",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬───────────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name      ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---       ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str       ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪═══════════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 142 ┆ Bumbleboy ┆ Male   ┆ null      ┆ … ┆ Marvel Comics     ┆ null       ┆ good      ┆ null   │\n",
              "│ 321 ┆ Hellboy   ┆ Male   ┆ gold      ┆ … ┆ Dark Horse Comics ┆ null       ┆ good      ┆ 158.0  │\n",
              "└─────┴───────────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>142</td><td>&quot;Bumbleboy&quot;</td><td>&quot;Male&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>null</td></tr><tr><td>321</td><td>&quot;Hellboy&quot;</td><td>&quot;Male&quot;</td><td>&quot;gold&quot;</td><td>&quot;Demon&quot;</td><td>&quot;Black&quot;</td><td>259.0</td><td>&quot;Dark Horse Com…</td><td>null</td><td>&quot;good&quot;</td><td>158.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('name').str.contains('boy'))\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "4a37843e",
      "metadata": {
        "id": "4a37843e"
      },
      "source": [
        "#### `ILIKE` using `str.to_lowercase()`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f33ab806",
      "metadata": {
        "id": "f33ab806",
        "outputId": "026b8901-96a7-4dc3-c766-65d64d40f6b1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 192
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (3, 11)\n",
              "┌─────┬───────────┬────────┬───────────┬───┬───────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name      ┆ Gender ┆ Eye color ┆ … ┆ Publisher     ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---       ┆ ---    ┆ ---       ┆   ┆ ---           ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str       ┆ str    ┆ str       ┆   ┆ str           ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪═══════════╪════════╪═══════════╪═══╪═══════════════╪════════════╪═══════════╪════════╡\n",
              "│ 46  ┆ Astro Boy ┆ Male   ┆ brown     ┆ … ┆ null          ┆ null       ┆ good      ┆ null   │\n",
              "│ 75  ┆ Beast Boy ┆ Male   ┆ green     ┆ … ┆ DC Comics     ┆ green      ┆ good      ┆ 68.0   │\n",
              "│ 142 ┆ Bumbleboy ┆ Male   ┆ null      ┆ … ┆ Marvel Comics ┆ null       ┆ good      ┆ null   │\n",
              "└─────┴───────────┴────────┴───────────┴───┴───────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (3, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>46</td><td>&quot;Astro Boy&quot;</td><td>&quot;Male&quot;</td><td>&quot;brown&quot;</td><td>null</td><td>&quot;Black&quot;</td><td>null</td><td>null</td><td>null</td><td>&quot;good&quot;</td><td>null</td></tr><tr><td>75</td><td>&quot;Beast Boy&quot;</td><td>&quot;Male&quot;</td><td>&quot;green&quot;</td><td>&quot;Human&quot;</td><td>&quot;Green&quot;</td><td>173.0</td><td>&quot;DC Comics&quot;</td><td>&quot;green&quot;</td><td>&quot;good&quot;</td><td>68.0</td></tr><tr><td>142</td><td>&quot;Bumbleboy&quot;</td><td>&quot;Male&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>null</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('name').str.to_lowercase().str.contains('boy'))\n",
        " .head(3))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "3dcd6de5",
      "metadata": {
        "id": "3dcd6de5"
      },
      "source": [
        "## Cry 'Havoc!,' and let slip the dogs of RegEx\n",
        "\n",
        "Many `polars` string methods, like `contains` accept regular expressions."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "a9930c34",
      "metadata": {
        "id": "a9930c34",
        "outputId": "20e9ea87-90fa-40f5-b657-096f6ddbbae6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 255
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (5, 11)\n",
              "┌─────┬────────────────┬────────┬───────────┬───┬───────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name           ┆ Gender ┆ Eye color ┆ … ┆ Publisher     ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---            ┆ ---    ┆ ---       ┆   ┆ ---           ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str            ┆ str    ┆ str       ┆   ┆ str           ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪════════════════╪════════╪═══════════╪═══╪═══════════════╪════════════╪═══════════╪════════╡\n",
              "│ 75  ┆ Beast Boy      ┆ Male   ┆ green     ┆ … ┆ DC Comics     ┆ green      ┆ good      ┆ 68.0   │\n",
              "│ 142 ┆ Bumbleboy      ┆ Male   ┆ null      ┆ … ┆ Marvel Comics ┆ null       ┆ good      ┆ null   │\n",
              "│ 183 ┆ Colossal Boy   ┆ Male   ┆ null      ┆ … ┆ DC Comics     ┆ null       ┆ good      ┆ null   │\n",
              "│ 643 ┆ Superboy       ┆ Male   ┆ blue      ┆ … ┆ DC Comics     ┆ null       ┆ good      ┆ 68.0   │\n",
              "│ 644 ┆ Superboy-Prime ┆ Male   ┆ blue      ┆ … ┆ DC Comics     ┆ null       ┆ bad       ┆ 77.0   │\n",
              "└─────┴────────────────┴────────┴───────────┴───┴───────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (5, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>75</td><td>&quot;Beast Boy&quot;</td><td>&quot;Male&quot;</td><td>&quot;green&quot;</td><td>&quot;Human&quot;</td><td>&quot;Green&quot;</td><td>173.0</td><td>&quot;DC Comics&quot;</td><td>&quot;green&quot;</td><td>&quot;good&quot;</td><td>68.0</td></tr><tr><td>142</td><td>&quot;Bumbleboy&quot;</td><td>&quot;Male&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>null</td></tr><tr><td>183</td><td>&quot;Colossal Boy&quot;</td><td>&quot;Male&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>&quot;DC Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>null</td></tr><tr><td>643</td><td>&quot;Superboy&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>null</td><td>&quot;Black&quot;</td><td>170.0</td><td>&quot;DC Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>68.0</td></tr><tr><td>644</td><td>&quot;Superboy-Prime…</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Kryptonian&quot;</td><td>&quot;Black / Blue&quot;</td><td>180.0</td><td>&quot;DC Comics&quot;</td><td>null</td><td>&quot;bad&quot;</td><td>77.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('Publisher').str.contains('DC|Marvel'))\n",
        " .filter(pl.col('name').str.contains('\\s[Bb]oy|\\wboy'))\n",
        " .head()\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "5f209e15",
      "metadata": {
        "id": "5f209e15"
      },
      "source": [
        "### Getting help on `polar` string methods.\n",
        "\n",
        "Currently, it is difficult to find help on the various `polars` string methods.  More information can be obtains as follows."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "id": "41a87ada",
      "metadata": {
        "id": "41a87ada",
        "outputId": "9a69767f-7e57-49bf-a84a-0effd4ee53f0",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['__class__',\n",
              " '__delattr__',\n",
              " '__dict__',\n",
              " '__dir__',\n",
              " '__doc__',\n",
              " '__eq__',\n",
              " '__format__',\n",
              " '__ge__',\n",
              " '__getattribute__',\n",
              " '__gt__',\n",
              " '__hash__',\n",
              " '__init__',\n",
              " '__init_subclass__',\n",
              " '__le__',\n",
              " '__lt__',\n",
              " '__module__',\n",
              " '__ne__',\n",
              " '__new__',\n",
              " '__reduce__',\n",
              " '__reduce_ex__',\n",
              " '__repr__',\n",
              " '__setattr__',\n",
              " '__sizeof__',\n",
              " '__str__',\n",
              " '__subclasshook__',\n",
              " '__weakref__',\n",
              " '_accessor',\n",
              " '_pyexpr',\n",
              " 'concat',\n",
              " 'contains',\n",
              " 'contains_any',\n",
              " 'count_match',\n",
              " 'count_matches',\n",
              " 'decode',\n",
              " 'encode',\n",
              " 'ends_with',\n",
              " 'explode',\n",
              " 'extract',\n",
              " 'extract_all',\n",
              " 'extract_groups',\n",
              " 'json_decode',\n",
              " 'json_extract',\n",
              " 'json_path_match',\n",
              " 'len_bytes',\n",
              " 'len_chars',\n",
              " 'lengths',\n",
              " 'ljust',\n",
              " 'lstrip',\n",
              " 'n_chars',\n",
              " 'pad_end',\n",
              " 'pad_start',\n",
              " 'parse_int',\n",
              " 'replace',\n",
              " 'replace_all',\n",
              " 'replace_many',\n",
              " 'reverse',\n",
              " 'rjust',\n",
              " 'rstrip',\n",
              " 'slice',\n",
              " 'split',\n",
              " 'split_exact',\n",
              " 'splitn',\n",
              " 'starts_with',\n",
              " 'strip',\n",
              " 'strip_chars',\n",
              " 'strip_chars_end',\n",
              " 'strip_chars_start',\n",
              " 'strip_prefix',\n",
              " 'strip_suffix',\n",
              " 'strptime',\n",
              " 'to_date',\n",
              " 'to_datetime',\n",
              " 'to_decimal',\n",
              " 'to_integer',\n",
              " 'to_lowercase',\n",
              " 'to_time',\n",
              " 'to_titlecase',\n",
              " 'to_uppercase',\n",
              " 'zfill']"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ],
      "source": [
        "dir(pl.col('species_id').str) # list all the str functions"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "id": "e58c00b0",
      "metadata": {
        "id": "e58c00b0",
        "outputId": "dc266f6b-c806-40f2-ddf5-d8c5ff0bc7d2",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Help on method contains in module polars.expr.string:\n",
            "\n",
            "contains(pattern: 'str | Expr', *, literal: 'bool' = False, strict: 'bool' = True) -> 'Expr' method of polars.expr.string.ExprStringNameSpace instance\n",
            "    Check if string contains a substring that matches a regex.\n",
            "    \n",
            "    Parameters\n",
            "    ----------\n",
            "    pattern\n",
            "        A valid regular expression pattern, compatible with the `regex crate\n",
            "        <https://docs.rs/regex/latest/regex/>`_.\n",
            "    literal\n",
            "        Treat `pattern` as a literal string, not as a regular expression.\n",
            "    strict\n",
            "        Raise an error if the underlying pattern is not a valid regex,\n",
            "        otherwise mask out with a null value.\n",
            "    \n",
            "    Notes\n",
            "    -----\n",
            "    To modify regular expression behaviour (such as case-sensitivity) with\n",
            "    flags, use the inline `(?iLmsuxU)` syntax. For example:\n",
            "    \n",
            "    >>> pl.DataFrame({\"s\": [\"AAA\", \"aAa\", \"aaa\"]}).with_columns(\n",
            "    ...     default_match=pl.col(\"s\").str.contains(\"AA\"),\n",
            "    ...     insensitive_match=pl.col(\"s\").str.contains(\"(?i)AA\"),\n",
            "    ... )\n",
            "    shape: (3, 3)\n",
            "    ┌─────┬───────────────┬───────────────────┐\n",
            "    │ s   ┆ default_match ┆ insensitive_match │\n",
            "    │ --- ┆ ---           ┆ ---               │\n",
            "    │ str ┆ bool          ┆ bool              │\n",
            "    ╞═════╪═══════════════╪═══════════════════╡\n",
            "    │ AAA ┆ true          ┆ true              │\n",
            "    │ aAa ┆ false         ┆ true              │\n",
            "    │ aaa ┆ false         ┆ true              │\n",
            "    └─────┴───────────────┴───────────────────┘\n",
            "    \n",
            "    See the regex crate's section on `grouping and flags\n",
            "    <https://docs.rs/regex/latest/regex/#grouping-and-flags>`_ for\n",
            "    additional information about the use of inline expression modifiers.\n",
            "    \n",
            "    See Also\n",
            "    --------\n",
            "    starts_with : Check if string values start with a substring.\n",
            "    ends_with : Check if string values end with a substring.\n",
            "    \n",
            "    Examples\n",
            "    --------\n",
            "    >>> df = pl.DataFrame({\"a\": [\"Crab\", \"cat and dog\", \"rab$bit\", None]})\n",
            "    >>> df.select(\n",
            "    ...     pl.col(\"a\"),\n",
            "    ...     pl.col(\"a\").str.contains(\"cat|bit\").alias(\"regex\"),\n",
            "    ...     pl.col(\"a\").str.contains(\"rab$\", literal=True).alias(\"literal\"),\n",
            "    ... )\n",
            "    shape: (4, 3)\n",
            "    ┌─────────────┬───────┬─────────┐\n",
            "    │ a           ┆ regex ┆ literal │\n",
            "    │ ---         ┆ ---   ┆ ---     │\n",
            "    │ str         ┆ bool  ┆ bool    │\n",
            "    ╞═════════════╪═══════╪═════════╡\n",
            "    │ Crab        ┆ false ┆ false   │\n",
            "    │ cat and dog ┆ true  ┆ false   │\n",
            "    │ rab$bit     ┆ true  ┆ true    │\n",
            "    │ null        ┆ null  ┆ null    │\n",
            "    └─────────────┴───────┴─────────┘\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Get help on one of the methods.\n",
        "help(pl.col('species_id').str.contains)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "ee89917a",
      "metadata": {
        "id": "ee89917a"
      },
      "source": [
        "## Common Filter Operations -  `IN` and `NOT IN`\n",
        "\n",
        "`SQL` has `IN` and `NOT IN`, which are used to check if a value is in/not in a collection."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "42450859",
      "metadata": {
        "id": "42450859"
      },
      "source": [
        "### Using  `IN`/`NOT IN` in `polars`\n",
        "\n",
        "* `polars` uses the column `pl.col('name').is_in` method\n",
        "* Prepend column expression with `~` for `NOT IN`"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "22520c87",
      "metadata": {
        "id": "22520c87"
      },
      "source": [
        "#### `IN`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "bb599ac2",
      "metadata": {
        "id": "bb599ac2",
        "outputId": "a0bc8921-77fc-4883-af9e-105756dc3b2b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬──────────┬────────┬───────────┬───┬───────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name     ┆ Gender ┆ Eye color ┆ … ┆ Publisher     ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---      ┆ ---    ┆ ---       ┆   ┆ ---           ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str      ┆ str    ┆ str       ┆   ┆ str           ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪══════════╪════════╪═══════════╪═══╪═══════════════╪════════════╪═══════════╪════════╡\n",
              "│ 0   ┆ A-Bomb   ┆ Male   ┆ yellow    ┆ … ┆ Marvel Comics ┆ null       ┆ good      ┆ 441.0  │\n",
              "│ 2   ┆ Abin Sur ┆ Male   ┆ blue      ┆ … ┆ DC Comics     ┆ red        ┆ good      ┆ 90.0   │\n",
              "└─────┴──────────┴────────┴───────────┴───┴───────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>&quot;A-Bomb&quot;</td><td>&quot;Male&quot;</td><td>&quot;yellow&quot;</td><td>&quot;Human&quot;</td><td>&quot;No Hair&quot;</td><td>203.0</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>441.0</td></tr><tr><td>2</td><td>&quot;Abin Sur&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Ungaran&quot;</td><td>&quot;No Hair&quot;</td><td>185.0</td><td>&quot;DC Comics&quot;</td><td>&quot;red&quot;</td><td>&quot;good&quot;</td><td>90.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('Publisher').is_in(['DC Comics', 'Marvel Comics']))\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e22126b3",
      "metadata": {
        "id": "e22126b3"
      },
      "source": [
        "#### `NOT IN`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "8ca101d3",
      "metadata": {
        "id": "8ca101d3",
        "outputId": "d3762ab6-994a-41eb-b5c5-d3b5f32a842f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬─────────────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name        ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---         ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str         ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪═════════════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 1   ┆ Abe Sapien  ┆ Male   ┆ blue      ┆ … ┆ Dark Horse Comics ┆ blue       ┆ good      ┆ 65.0   │\n",
              "│ 6   ┆ Adam Monroe ┆ Male   ┆ blue      ┆ … ┆ NBC - Heroes      ┆ null       ┆ good      ┆ null   │\n",
              "└─────┴─────────────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;Abe Sapien&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Icthyo Sapien&quot;</td><td>&quot;No Hair&quot;</td><td>191.0</td><td>&quot;Dark Horse Com…</td><td>&quot;blue&quot;</td><td>&quot;good&quot;</td><td>65.0</td></tr><tr><td>6</td><td>&quot;Adam Monroe&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>null</td><td>&quot;Blond&quot;</td><td>null</td><td>&quot;NBC - Heroes&quot;</td><td>null</td><td>&quot;good&quot;</td><td>null</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(~pl.col('Publisher').is_in(['DC Comics', 'Marvel Comics']))\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "264701b8",
      "metadata": {
        "id": "264701b8"
      },
      "source": [
        "## <font color=\"red\"> Exercise 6.4.2 - The Super Hero Dating Game - Part 2</font>\n",
        "\n",
        "Yesterday, you notice another singles add in the local paper, which reads\n",
        "\n",
        "> SBiM looking for SyFy super hero (will also consider Star Wars, Star Trek, or NBC - Heroes).  Eye color must be either blue or brown and last name must start with either B or P.\n",
        "\n",
        "Write a query in `dfply` to help find candidates for this personal add."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "4bcbbc18",
      "metadata": {
        "id": "4bcbbc18"
      },
      "outputs": [],
      "source": [
        "# Your solution here"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "19d6a60c",
      "metadata": {
        "id": "19d6a60c"
      },
      "source": [
        "## Common Filter Operators - IS NULL/IS NOT NULL\n",
        "\n",
        "`SQL` has `IS NULL` and `IS NOT NULL`, which are used to check for missing values."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7adb941d",
      "metadata": {
        "id": "7adb941d"
      },
      "source": [
        "### Using  `IS NULL`/`IS NOT NULL` in `polars`\n",
        "\n",
        "`polars` use the column `is_null`/`is_not_null` methods"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "de203f18",
      "metadata": {
        "id": "de203f18"
      },
      "source": [
        "#### `IS NULL`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f9efcc58",
      "metadata": {
        "id": "f9efcc58",
        "outputId": "c3c694e6-3ab6-4a82-ceec-e591b99a2209",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬─────────────┬────────┬───────────┬───┬───────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name        ┆ Gender ┆ Eye color ┆ … ┆ Publisher     ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---         ┆ ---    ┆ ---       ┆   ┆ ---           ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str         ┆ str    ┆ str       ┆   ┆ str           ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪═════════════╪════════╪═══════════╪═══╪═══════════════╪════════════╪═══════════╪════════╡\n",
              "│ 0   ┆ A-Bomb      ┆ Male   ┆ yellow    ┆ … ┆ Marvel Comics ┆ null       ┆ good      ┆ 441.0  │\n",
              "│ 3   ┆ Abomination ┆ Male   ┆ green     ┆ … ┆ Marvel Comics ┆ null       ┆ bad       ┆ 441.0  │\n",
              "└─────┴─────────────┴────────┴───────────┴───┴───────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>&quot;A-Bomb&quot;</td><td>&quot;Male&quot;</td><td>&quot;yellow&quot;</td><td>&quot;Human&quot;</td><td>&quot;No Hair&quot;</td><td>203.0</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>441.0</td></tr><tr><td>3</td><td>&quot;Abomination&quot;</td><td>&quot;Male&quot;</td><td>&quot;green&quot;</td><td>&quot;Human / Radiat…</td><td>&quot;No Hair&quot;</td><td>203.0</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;bad&quot;</td><td>441.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('Skin color').is_null())\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "2f06d84e",
      "metadata": {
        "id": "2f06d84e"
      },
      "source": [
        "#### `IS NOT NULL`  using `is_not_null`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "4df2bf79",
      "metadata": {
        "id": "4df2bf79",
        "outputId": "83c090e5-93f1-4aa5-ff7d-bd8bb129f9c7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬────────────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name       ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---        ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str        ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪════════════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 1   ┆ Abe Sapien ┆ Male   ┆ blue      ┆ … ┆ Dark Horse Comics ┆ blue       ┆ good      ┆ 65.0   │\n",
              "│ 2   ┆ Abin Sur   ┆ Male   ┆ blue      ┆ … ┆ DC Comics         ┆ red        ┆ good      ┆ 90.0   │\n",
              "└─────┴────────────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;Abe Sapien&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Icthyo Sapien&quot;</td><td>&quot;No Hair&quot;</td><td>191.0</td><td>&quot;Dark Horse Com…</td><td>&quot;blue&quot;</td><td>&quot;good&quot;</td><td>65.0</td></tr><tr><td>2</td><td>&quot;Abin Sur&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Ungaran&quot;</td><td>&quot;No Hair&quot;</td><td>185.0</td><td>&quot;DC Comics&quot;</td><td>&quot;red&quot;</td><td>&quot;good&quot;</td><td>90.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('Skin color').is_not_null())\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "75b44e24",
      "metadata": {
        "id": "75b44e24"
      },
      "source": [
        "## Common Filter Operations - `AND`/ `OR`\n",
        "\n",
        "`SQL` has `AND` and `OR`, which are used to check for missing values."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "69cad664",
      "metadata": {
        "id": "69cad664"
      },
      "source": [
        "### Using  `AND`/`OR` in `polars`\n",
        "\n",
        " In `polars`, we use\n",
        "\n",
        " * The `&` operator for AND\n",
        " * the `|` operator for OR\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "32404b35",
      "metadata": {
        "id": "32404b35"
      },
      "source": [
        "#### `AND` using `&`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "42da1f1c",
      "metadata": {
        "id": "42da1f1c",
        "outputId": "eb284079-f0c5-4163-cd6a-1953a4fd1457",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬────────────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name       ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---        ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str        ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪════════════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 1   ┆ Abe Sapien ┆ Male   ┆ blue      ┆ … ┆ Dark Horse Comics ┆ blue       ┆ good      ┆ 65.0   │\n",
              "│ 2   ┆ Abin Sur   ┆ Male   ┆ blue      ┆ … ┆ DC Comics         ┆ red        ┆ good      ┆ 90.0   │\n",
              "└─────┴────────────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;Abe Sapien&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Icthyo Sapien&quot;</td><td>&quot;No Hair&quot;</td><td>191.0</td><td>&quot;Dark Horse Com…</td><td>&quot;blue&quot;</td><td>&quot;good&quot;</td><td>65.0</td></tr><tr><td>2</td><td>&quot;Abin Sur&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Ungaran&quot;</td><td>&quot;No Hair&quot;</td><td>185.0</td><td>&quot;DC Comics&quot;</td><td>&quot;red&quot;</td><td>&quot;good&quot;</td><td>90.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ],
      "source": [
        "(heroes\n",
        " .filter((pl.col('Hair color') == 'No Hair') & (pl.col('Eye color') == 'blue'))\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "253d5e98",
      "metadata": {
        "id": "253d5e98"
      },
      "source": [
        "#### `AND` using multiple `filter` calls"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "5391fb9a",
      "metadata": {
        "id": "5391fb9a",
        "outputId": "94f01830-0ca9-4f0a-f464-6ec5662f30e3"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "\n",
              "    .dataframe td {\n",
              "        white-space: pre;\n",
              "    }\n",
              "\n",
              "    .dataframe td {\n",
              "        padding-top: 0;\n",
              "    }\n",
              "\n",
              "    .dataframe td {\n",
              "        padding-bottom: 0;\n",
              "    }\n",
              "\n",
              "    .dataframe td {\n",
              "        line-height: 95%;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\" >\n",
              "<small>shape: (2, 11)</small>\n",
              "<thead>\n",
              "<tr>\n",
              "<th>\n",
              "ID\n",
              "</th>\n",
              "<th>\n",
              "name\n",
              "</th>\n",
              "<th>\n",
              "Gender\n",
              "</th>\n",
              "<th>\n",
              "Eye color\n",
              "</th>\n",
              "<th>\n",
              "Race\n",
              "</th>\n",
              "<th>\n",
              "Hair color\n",
              "</th>\n",
              "<th>\n",
              "Height\n",
              "</th>\n",
              "<th>\n",
              "Publisher\n",
              "</th>\n",
              "<th>\n",
              "Skin color\n",
              "</th>\n",
              "<th>\n",
              "Alignment\n",
              "</th>\n",
              "<th>\n",
              "Weight\n",
              "</th>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>\n",
              "i64\n",
              "</td>\n",
              "<td>\n",
              "str\n",
              "</td>\n",
              "<td>\n",
              "str\n",
              "</td>\n",
              "<td>\n",
              "str\n",
              "</td>\n",
              "<td>\n",
              "str\n",
              "</td>\n",
              "<td>\n",
              "str\n",
              "</td>\n",
              "<td>\n",
              "f64\n",
              "</td>\n",
              "<td>\n",
              "str\n",
              "</td>\n",
              "<td>\n",
              "str\n",
              "</td>\n",
              "<td>\n",
              "str\n",
              "</td>\n",
              "<td>\n",
              "f64\n",
              "</td>\n",
              "</tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr>\n",
              "<td>\n",
              "1\n",
              "</td>\n",
              "<td>\n",
              "&quot;Abe Sapien&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;Male&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;blue&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;Icthyo Sapien&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;No Hair&quot;\n",
              "</td>\n",
              "<td>\n",
              "191.0\n",
              "</td>\n",
              "<td>\n",
              "&quot;Dark Horse Com...\n",
              "</td>\n",
              "<td>\n",
              "&quot;blue&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;good&quot;\n",
              "</td>\n",
              "<td>\n",
              "65.0\n",
              "</td>\n",
              "</tr>\n",
              "<tr>\n",
              "<td>\n",
              "2\n",
              "</td>\n",
              "<td>\n",
              "&quot;Abin Sur&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;Male&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;blue&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;Ungaran&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;No Hair&quot;\n",
              "</td>\n",
              "<td>\n",
              "185.0\n",
              "</td>\n",
              "<td>\n",
              "&quot;DC Comics&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;red&quot;\n",
              "</td>\n",
              "<td>\n",
              "&quot;good&quot;\n",
              "</td>\n",
              "<td>\n",
              "90.0\n",
              "</td>\n",
              "</tr>\n",
              "</tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬────────────┬────────┬───────────┬─────┬────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name       ┆ Gender ┆ Eye color ┆ ... ┆ Publisher  ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---        ┆ ---    ┆ ---       ┆     ┆ ---        ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str        ┆ str    ┆ str       ┆     ┆ str        ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪════════════╪════════╪═══════════╪═════╪════════════╪════════════╪═══════════╪════════╡\n",
              "│ 1   ┆ Abe Sapien ┆ Male   ┆ blue      ┆ ... ┆ Dark Horse ┆ blue       ┆ good      ┆ 65.0   │\n",
              "│     ┆            ┆        ┆           ┆     ┆ Comics     ┆            ┆           ┆        │\n",
              "├╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤\n",
              "│ 2   ┆ Abin Sur   ┆ Male   ┆ blue      ┆ ... ┆ DC Comics  ┆ red        ┆ good      ┆ 90.0   │\n",
              "└─────┴────────────┴────────┴───────────┴─────┴────────────┴────────────┴───────────┴────────┘"
            ]
          },
          "execution_count": 109,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "(heroes\n",
        " .filter(pl.col('Hair color') == 'No Hair')\n",
        " .filter(pl.col('Eye color') == 'blue')\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1c05e51a",
      "metadata": {
        "id": "1c05e51a"
      },
      "source": [
        "#### `OR`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "69d0e340",
      "metadata": {
        "id": "69d0e340",
        "outputId": "f006caf0-7f18-4480-a213-444ed57469bd",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 161
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (2, 11)\n",
              "┌─────┬────────────┬────────┬───────────┬───┬───────────────────┬────────────┬───────────┬────────┐\n",
              "│ ID  ┆ name       ┆ Gender ┆ Eye color ┆ … ┆ Publisher         ┆ Skin color ┆ Alignment ┆ Weight │\n",
              "│ --- ┆ ---        ┆ ---    ┆ ---       ┆   ┆ ---               ┆ ---        ┆ ---       ┆ ---    │\n",
              "│ i64 ┆ str        ┆ str    ┆ str       ┆   ┆ str               ┆ str        ┆ str       ┆ f64    │\n",
              "╞═════╪════════════╪════════╪═══════════╪═══╪═══════════════════╪════════════╪═══════════╪════════╡\n",
              "│ 0   ┆ A-Bomb     ┆ Male   ┆ yellow    ┆ … ┆ Marvel Comics     ┆ null       ┆ good      ┆ 441.0  │\n",
              "│ 1   ┆ Abe Sapien ┆ Male   ┆ blue      ┆ … ┆ Dark Horse Comics ┆ blue       ┆ good      ┆ 65.0   │\n",
              "└─────┴────────────┴────────┴───────────┴───┴───────────────────┴────────────┴───────────┴────────┘"
            ],
            "text/html": [
              "<div><style>\n",
              ".dataframe > thead > tr,\n",
              ".dataframe > tbody > tr {\n",
              "  text-align: right;\n",
              "  white-space: pre-wrap;\n",
              "}\n",
              "</style>\n",
              "<small>shape: (2, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ID</th><th>name</th><th>Gender</th><th>Eye color</th><th>Race</th><th>Hair color</th><th>Height</th><th>Publisher</th><th>Skin color</th><th>Alignment</th><th>Weight</th></tr><tr><td>i64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>0</td><td>&quot;A-Bomb&quot;</td><td>&quot;Male&quot;</td><td>&quot;yellow&quot;</td><td>&quot;Human&quot;</td><td>&quot;No Hair&quot;</td><td>203.0</td><td>&quot;Marvel Comics&quot;</td><td>null</td><td>&quot;good&quot;</td><td>441.0</td></tr><tr><td>1</td><td>&quot;Abe Sapien&quot;</td><td>&quot;Male&quot;</td><td>&quot;blue&quot;</td><td>&quot;Icthyo Sapien&quot;</td><td>&quot;No Hair&quot;</td><td>191.0</td><td>&quot;Dark Horse Com…</td><td>&quot;blue&quot;</td><td>&quot;good&quot;</td><td>65.0</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ],
      "source": [
        "(heroes\n",
        " .filter((pl.col('Hair color') == 'No Hair') | (pl.col('Eye color') == 'blue'))\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "8cb2ee16",
      "metadata": {
        "id": "8cb2ee16"
      },
      "source": [
        "#### Important NOTE\n",
        "You need to break the Python habit of using `and` and `or` (hard to do)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "28b5239a",
      "metadata": {
        "id": "28b5239a",
        "outputId": "0d0a8970-ae66-4fff-b251-09decc6f97f1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 341
        }
      },
      "outputs": [
        {
          "output_type": "error",
          "ename": "TypeError",
          "evalue": "the truth value of an Expr is ambiguous\n\nHint: use '&' or '|' to logically combine Expr, not 'and'/'or', and use `x.is_in([y,z])` instead of `x in [y,z]` to check membership.",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-21-4d424e3768b3>\u001b[0m in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m (heroes\n\u001b[0;32m----> 2\u001b[0;31m  \u001b[0;34m.\u001b[0m\u001b[0mfilter\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpl\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcol\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Hair color'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'No Hair'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mpl\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcol\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'Eye color'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'blue'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m  .head(2))\n",
            "\u001b[0;32m/usr/local/lib/python3.10/dist-packages/polars/expr/expr.py\u001b[0m in \u001b[0;36m__bool__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    145\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    146\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__bool__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0mNoReturn\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 147\u001b[0;31m         raise TypeError(\n\u001b[0m\u001b[1;32m    148\u001b[0m             \u001b[0;34m\"the truth value of an Expr is ambiguous\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    149\u001b[0m             \u001b[0;34m\"\\n\\nHint: use '&' or '|' to logically combine Expr, not 'and'/'or', and\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mTypeError\u001b[0m: the truth value of an Expr is ambiguous\n\nHint: use '&' or '|' to logically combine Expr, not 'and'/'or', and use `x.is_in([y,z])` instead of `x in [y,z]` to check membership."
          ]
        }
      ],
      "source": [
        "(heroes\n",
        " .filter((pl.col('Hair color') == 'No Hair') and (pl.col('Eye color') == 'blue'))\n",
        " .head(2))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "360c70d0",
      "metadata": {
        "id": "360c70d0"
      },
      "source": [
        "## <font color=\"red\"> Exercise 6.4.3 - The Super Hero Dating Game - Part 3</font>\n",
        "\n",
        "Yesterday, you notice one more singles add in the local paper, which read\n",
        "\n",
        "> W4A (Woman for Androgynous) looking for super hero.  Must be either God/Eternal/Cosmic Entity; or have no body hair.  Bad heroes need not reply.\n",
        "\n",
        "Write a query in all three frameworks to help find candidates for this personal add.  You should complete each query with **exactly one filter_by/where**."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "18e5bf62",
      "metadata": {
        "id": "18e5bf62"
      },
      "outputs": [],
      "source": [
        "# Your solution here"
      ]
    }
  ],
  "metadata": {
    "celltoolbar": "Slideshow",
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.9.12"
    },
    "varInspector": {
      "cols": {
        "lenName": 16,
        "lenType": 16,
        "lenVar": 40
      },
      "kernels_config": {
        "python": {
          "delete_cmd_postfix": "",
          "delete_cmd_prefix": "del ",
          "library": "var_list.py",
          "varRefreshCmd": "print(var_dic_list())"
        },
        "r": {
          "delete_cmd_postfix": ") ",
          "delete_cmd_prefix": "rm(",
          "library": "var_list.r",
          "varRefreshCmd": "cat(var_dic_list()) "
        }
      },
      "types_to_exclude": [
        "module",
        "function",
        "builtin_function_or_method",
        "instance",
        "_Feature"
      ],
      "window_display": false
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}