14_FireDucks_vs_Pandas_vs_Polars_vs_DuckDB.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# FireDucks_vs_Pandas_vs_Polars_vs_DuckDB"
      ],
      "metadata": {
        "id": "HzFlyf8GIW60"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "FqF2aCCPUShb",
        "outputId": "ae090f2a-ac10-466a-9f06-f522efd7d11e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: fireducks in /usr/local/lib/python3.10/dist-packages (1.1.3)\n",
            "Requirement already satisfied: firefw==1.1.3 in /usr/local/lib/python3.10/dist-packages (from fireducks) (1.1.3)\n",
            "Requirement already satisfied: pandas<2.3.0,>=1.5.3 in /usr/local/lib/python3.10/dist-packages (from fireducks) (2.2.2)\n",
            "Requirement already satisfied: pyarrow<18.2,>=18.1 in /usr/local/lib/python3.10/dist-packages (from fireducks) (18.1.0)\n",
            "Requirement already satisfied: numpy>=1.22.4 in /usr/local/lib/python3.10/dist-packages (from pandas<2.3.0,>=1.5.3->fireducks) (1.26.4)\n",
            "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.10/dist-packages (from pandas<2.3.0,>=1.5.3->fireducks) (2.9.0.post0)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas<2.3.0,>=1.5.3->fireducks) (2024.2)\n",
            "Requirement already satisfied: tzdata>=2022.7 in /usr/local/lib/python3.10/dist-packages (from pandas<2.3.0,>=1.5.3->fireducks) (2024.2)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.8.2->pandas<2.3.0,>=1.5.3->fireducks) (1.17.0)\n",
            "Requirement already satisfied: polars in /usr/local/lib/python3.10/dist-packages (1.17.1)\n",
            "Requirement already satisfied: duckdb in /usr/local/lib/python3.10/dist-packages (1.1.3)\n"
          ]
        }
      ],
      "source": [
        "!pip install fireducks\n",
        "!pip install polars -U\n",
        "!pip install duckdb"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "3roUn9XiUE8G",
        "outputId": "6a87aba5-2d31-4619-d8b0-1de220eb381e",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "('taxi.csv', <http.client.HTTPMessage at 0x787a8e4578e0>)"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ],
      "source": [
        "# download the dataset:\n",
        "\n",
        "import urllib.request\n",
        "s3_path = \"https://modin-datasets.s3.amazonaws.com/testing/yellow_tripdata_2015-01.csv\"\n",
        "urllib.request.urlretrieve(s3_path, \"taxi.csv\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# FireDucks\n",
        "\n",
        "At its core, FireDucks is heavily driven by lazy execution, unlike Pandas, which executes right away.\n",
        "\n",
        "This allows FireDucks to build a logical execution plan and apply possible optimizations."
      ],
      "metadata": {
        "id": "lvwoJMg82ubd"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "fzuTMjVznk84"
      },
      "outputs": [],
      "source": [
        "import fireducks.pandas as pd\n",
        "import time"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "start = time.time()\n",
        "\n",
        "df = pd.read_csv(\"taxi.csv\")\n",
        "\n",
        "big_df = pd.concat([df for _ in range(20)])\n",
        "\n",
        "df = df.drop(columns = [\"congestion_surcharge\", \"airport_fee\"])\n",
        "\n",
        "new_df = df.sort_values(by = \"fare_amount\").reset_index(drop=True)\n",
        "\n",
        "t1 = big_df.groupby(\"PULocationID\").mta_tax.mean().reset_index()\n",
        "t2 = big_df.groupby(\"PULocationID\").tip_amount.mean().reset_index()\n",
        "t3 = big_df.groupby(\"PULocationID\").tolls_amount.mean().reset_index()\n",
        "t4 = big_df.groupby(\"PULocationID\").trip_distance.mean().reset_index()\n",
        "t5 = big_df.groupby(\"DOLocationID\").mta_tax.mean().reset_index()\n",
        "t6 = big_df.groupby(\"DOLocationID\").tip_amount.mean().reset_index()\n",
        "t7 = big_df.groupby(\"DOLocationID\").tolls_amount.mean().reset_index()\n",
        "t8 = big_df.groupby(\"DOLocationID\").trip_distance.mean().reset_index()\n",
        "\n",
        "a = (t1._evaluate(), t2._evaluate(), t3._evaluate(), t4._evaluate(),\n",
        "     t5._evaluate(), t6._evaluate(), t7._evaluate(), t8._evaluate())\n",
        "\n",
        "print(f\"FireDucks run-time = {round(time.time()-start, 4)} seconds\")"
      ],
      "metadata": {
        "id": "u-FT1j8W2f2c",
        "outputId": "0b4a9f5b-de86-4205-f02b-3e382118ea64",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "FireDucks run-time = 2.6883 seconds\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Pandas\n",
        "\n",
        "Restart the session and run Pandas code"
      ],
      "metadata": {
        "id": "1p0vHm4p2xqe"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import time"
      ],
      "metadata": {
        "id": "vtNqCfQD2qjO"
      },
      "execution_count": 10,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "start = time.time()\n",
        "\n",
        "df = pd.read_csv(\"taxi.csv\")\n",
        "\n",
        "big_df = pd.concat([df for _ in range(20)])\n",
        "\n",
        "df = df.drop(columns = [\"congestion_surcharge\", \"airport_fee\"])\n",
        "\n",
        "new_df = df.sort_values(by = \"fare_amount\").reset_index(drop=True)\n",
        "\n",
        "t1 = big_df.groupby(\"PULocationID\").mta_tax.mean().reset_index()\n",
        "t2 = big_df.groupby(\"PULocationID\").tip_amount.mean().reset_index()\n",
        "t3 = big_df.groupby(\"PULocationID\").tolls_amount.mean().reset_index()\n",
        "t4 = big_df.groupby(\"PULocationID\").trip_distance.mean().reset_index()\n",
        "t5 = big_df.groupby(\"DOLocationID\").mta_tax.mean().reset_index()\n",
        "t6 = big_df.groupby(\"DOLocationID\").tip_amount.mean().reset_index()\n",
        "t7 = big_df.groupby(\"DOLocationID\").tolls_amount.mean().reset_index()\n",
        "t8 = big_df.groupby(\"DOLocationID\").trip_distance.mean().reset_index()\n",
        "\n",
        "a = (t1, t2, t3, t4,t5, t6, t7, t8)\n",
        "\n",
        "print(f\"Pandas run-time = {round(time.time()-start, 4)} seconds\")"
      ],
      "metadata": {
        "id": "JDxw2EbM2zpG",
        "outputId": "dd0d7e75-fe67-4049-f948-15237e3129ef",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Pandas run-time = 13.5233 seconds\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Polars"
      ],
      "metadata": {
        "id": "opySzXzA3TEj"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import polars as pl\n",
        "import time"
      ],
      "metadata": {
        "id": "50zpBDkw3Fo1"
      },
      "execution_count": 12,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "start = time.time()\n",
        "\n",
        "df = pl.scan_csv(\"taxi.csv\")\n",
        "\n",
        "big_df = pl.concat([df for _ in range(20)])\n",
        "\n",
        "df = df.drop([\"congestion_surcharge\", \"airport_fee\"])\n",
        "\n",
        "new_df = df.sort(by = \"fare_amount\")\n",
        "\n",
        "t1 = big_df.group_by(\"PULocationID\").agg(pl.mean(\"mta_tax\"))\n",
        "t2 = big_df.group_by(\"PULocationID\").agg(pl.mean(\"tip_amount\"))\n",
        "t3 = big_df.group_by(\"PULocationID\").agg(pl.mean(\"tolls_amount\"))\n",
        "t4 = big_df.group_by(\"PULocationID\").agg(pl.mean(\"trip_distance\"))\n",
        "t5 = big_df.group_by(\"DOLocationID\").agg(pl.mean(\"mta_tax\"))\n",
        "t6 = big_df.group_by(\"DOLocationID\").agg(pl.mean(\"tip_amount\"))\n",
        "t7 = big_df.group_by(\"DOLocationID\").agg(pl.mean(\"tolls_amount\"))\n",
        "t8 = big_df.group_by(\"DOLocationID\").agg(pl.mean(\"trip_distance\"))\n",
        "\n",
        "a = (t1.collect(), t2.collect(), t3.collect(), t4.collect(),\n",
        "     t5.collect(), t6.collect(), t7.collect(), t8.collect())\n",
        "\n",
        "print(f\"Polars run-time = {round(time.time()-start, 4)} seconds\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LGTg9gyD3hgS",
        "outputId": "c8628c4c-3368-48e5-de39-6e16e39ef71a"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Polars run-time = 29.1342 seconds\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# DuckDB"
      ],
      "metadata": {
        "id": "MjeDwpNt5dU4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import duckdb\n",
        "import time"
      ],
      "metadata": {
        "id": "dfYda5XL5gEk"
      },
      "execution_count": 16,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "start = time.time()\n",
        "\n",
        "con = duckdb.connect(database=':memory:')\n",
        "con.execute(\"CREATE TABLE taxi AS SELECT * FROM read_csv_auto('taxi.csv')\")\n",
        "\n",
        "con.execute(f\"CREATE TABLE big_taxi AS {'SELECT * FROM taxi UNION ALL '*19} SELECT * FROM taxi\")\n",
        "\n",
        "con.execute(\"CREATE TABLE taxi_reduced AS SELECT * EXCLUDE (congestion_surcharge, airport_fee) FROM taxi\")\n",
        "\n",
        "sorted_df = con.execute(\"SELECT * FROM taxi_reduced ORDER BY fare_amount\").fetch_df()\n",
        "\n",
        "t1 = con.execute(\"SELECT PULocationID, AVG(mta_tax) FROM big_taxi GROUP BY PULocationID\")\n",
        "t2 = con.execute(\"SELECT PULocationID, AVG(tip_amount) FROM big_taxi GROUP BY PULocationID\")\n",
        "t3 = con.execute(\"SELECT PULocationID, AVG(tolls_amount) FROM big_taxi GROUP BY PULocationID\")\n",
        "t4 = con.execute(\"SELECT PULocationID, AVG(trip_distance) FROM big_taxi GROUP BY PULocationID\")\n",
        "t5 = con.execute(\"SELECT DOLocationID, AVG(mta_tax) FROM big_taxi GROUP BY DOLocationID\")\n",
        "t6 = con.execute(\"SELECT DOLocationID, AVG(tip_amount) FROM big_taxi GROUP BY DOLocationID\")\n",
        "t7 = con.execute(\"SELECT DOLocationID, AVG(tolls_amount) FROM big_taxi GROUP BY DOLocationID\")\n",
        "t8 = con.execute(\"SELECT DOLocationID, AVG(trip_distance) FROM big_taxi GROUP BY DOLocationID\")\n",
        "\n",
        "a = (t1.fetch_df(), t2.fetch_df(), t3.fetch_df(), t4.fetch_df(),\n",
        "     t5.fetch_df(), t6.fetch_df(), t7.fetch_df(), t8.fetch_df())\n",
        "\n",
        "print(f\"DuckDB run-time = {round(time.time()-start, 4)} seconds\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 66,
          "referenced_widgets": [
            "39f97b6b61cf44fa9cea820f8bc8ff2a",
            "5d62742ce1c34ac492ac1e3bb37ecea6",
            "20f16364c9714509850bd60def69b472"
          ]
        },
        "id": "vjDHQaHR4Hgf",
        "outputId": "a676240a-9618-4967-efad-8bb1ad987a61"
      },
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
            ],
            "application/vnd.jupyter.widget-view+json": {
              "version_major": 2,
              "version_minor": 0,
              "model_id": "39f97b6b61cf44fa9cea820f8bc8ff2a"
            }
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "DuckDB run-time = 7.7602 seconds\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Let's understand the changes"
      ],
      "metadata": {
        "id": "s7VFB1UoqHHl"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%load_ext fireducks.pandas\n",
        "import pandas as pd\n",
        "pd.__name__"
      ],
      "metadata": {
        "id": "OETCJInQoq-L",
        "outputId": "a06ef247-6937-404b-fdd0-d0d39a20562f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        }
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'fireducks.pandas'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%fireducks.profile\n",
        "t1 = time.time()\n",
        "df = pd.read_csv(\"taxi.csv\").groupby(\"PULocationID\").tip_amount.mean().reset_index() # nothing is executed, because there is no explicit evaluation\n",
        "t2 = time.time()\n",
        "print(f\"execution time: {t2 - t1} sec\")"
      ],
      "metadata": {
        "id": "c7qgzZcVpCwV",
        "outputId": "3504827d-4395-4412-d321-ccc6867aa1d0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 115
        }
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "execution time: 0.01436614990234375 sec\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x787a5c11b700>"
            ],
            "text/html": [
              "<style type=\"text/css\">\n",
              "#T_ede8a caption {\n",
              "  text-align: center;\n",
              "  color: salmon;\n",
              "}\n",
              "</style>\n",
              "<table id=\"T_ede8a\" class=\"dataframe\">\n",
              "  <caption>profiling-summary:: total: 9.8942 msec (fallback: 0.0 msec)</caption>\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th id=\"T_ede8a_level0_col0\" class=\"col_heading level0 col0\" >name</th>\n",
              "      <th id=\"T_ede8a_level0_col1\" class=\"col_heading level0 col1\" >type</th>\n",
              "      <th id=\"T_ede8a_level0_col2\" class=\"col_heading level0 col2\" >n_calls</th>\n",
              "      <th id=\"T_ede8a_level0_col3\" class=\"col_heading level0 col3\" >duration (msec)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_ede8a_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_ede8a_row0_col0\" class=\"data row0 col0\" >read_csv_metadata</td>\n",
              "      <td id=\"T_ede8a_row0_col1\" class=\"data row0 col1\" >kernel</td>\n",
              "      <td id=\"T_ede8a_row0_col2\" class=\"data row0 col2\" >1</td>\n",
              "      <td id=\"T_ede8a_row0_col3\" class=\"data row0 col3\" >9.894156</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%fireducks.profile # shows execution time for each kernel-methods used in this cell expression (if executed)\n",
        "t1 = time.time()\n",
        "df = pd.read_csv(\"taxi.csv\").groupby(\"PULocationID\").tip_amount.mean().reset_index()._evaluate() # similar to polars collect(), but adding this will make it different than pandas code\n",
        "t2 = time.time()\n",
        "print(f\"execution time: {t2 - t1} sec\")"
      ],
      "metadata": {
        "id": "Iux1gR0hpE-7",
        "outputId": "d46694cc-e437-4cd3-bb8e-705abfef1a22",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 209
        }
      },
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "execution time: 0.7365067005157471 sec\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7876d139e1d0>"
            ],
            "text/html": [
              "<style type=\"text/css\">\n",
              "#T_b42df caption {\n",
              "  text-align: center;\n",
              "  color: salmon;\n",
              "}\n",
              "</style>\n",
              "<table id=\"T_b42df\" class=\"dataframe\">\n",
              "  <caption>profiling-summary:: total: 723.4036 msec (fallback: 0.0 msec)</caption>\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th id=\"T_b42df_level0_col0\" class=\"col_heading level0 col0\" >name</th>\n",
              "      <th id=\"T_b42df_level0_col1\" class=\"col_heading level0 col1\" >type</th>\n",
              "      <th id=\"T_b42df_level0_col2\" class=\"col_heading level0 col2\" >n_calls</th>\n",
              "      <th id=\"T_b42df_level0_col3\" class=\"col_heading level0 col3\" >duration (msec)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_b42df_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_b42df_row0_col0\" class=\"data row0 col0\" >read_csv_with_metadata</td>\n",
              "      <td id=\"T_b42df_row0_col1\" class=\"data row0 col1\" >kernel</td>\n",
              "      <td id=\"T_b42df_row0_col2\" class=\"data row0 col2\" >1</td>\n",
              "      <td id=\"T_b42df_row0_col3\" class=\"data row0 col3\" >641.244002</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b42df_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_b42df_row1_col0\" class=\"data row1 col0\" >groupby_select_agg</td>\n",
              "      <td id=\"T_b42df_row1_col1\" class=\"data row1 col1\" >kernel</td>\n",
              "      <td id=\"T_b42df_row1_col2\" class=\"data row1 col2\" >1</td>\n",
              "      <td id=\"T_b42df_row1_col3\" class=\"data row1 col3\" >72.598443</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b42df_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_b42df_row2_col0\" class=\"data row2 col0\" >read_csv_metadata</td>\n",
              "      <td id=\"T_b42df_row2_col1\" class=\"data row2 col1\" >kernel</td>\n",
              "      <td id=\"T_b42df_row2_col2\" class=\"data row2 col2\" >1</td>\n",
              "      <td id=\"T_b42df_row2_col3\" class=\"data row2 col3\" >9.540949</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b42df_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_b42df_row3_col0\" class=\"data row3 col0\" >reset_index</td>\n",
              "      <td id=\"T_b42df_row3_col1\" class=\"data row3 col1\" >kernel</td>\n",
              "      <td id=\"T_b42df_row3_col2\" class=\"data row3 col2\" >1</td>\n",
              "      <td id=\"T_b42df_row3_col3\" class=\"data row3 col3\" >0.013033</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%fireducks.profile # shows execution time for each kernel-methods used in this cell expression (if executed)\n",
        "t1 = time.time()\n",
        "df = pd.read_csv(\"taxi.csv\").groupby(\"PULocationID\").tip_amount.mean().reset_index()\n",
        "print(df.shape) # \"shape\" is a method that will trigger the evaluation, so you may use it if you don't want to use _evaluate() that is a non-pandas method\n",
        "t2 = time.time()\n",
        "print(f\"execution time: {t2 - t1} sec\")"
      ],
      "metadata": {
        "id": "wZoOLg_wviRa",
        "outputId": "f5c1a13f-5344-4c19-e51e-c6c3075d4d07",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 258
        }
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(252, 2)\n",
            "execution time: 0.6593942642211914 sec\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x7876d1309900>"
            ],
            "text/html": [
              "<style type=\"text/css\">\n",
              "#T_7a5ce caption {\n",
              "  text-align: center;\n",
              "  color: salmon;\n",
              "}\n",
              "</style>\n",
              "<table id=\"T_7a5ce\" class=\"dataframe\">\n",
              "  <caption>profiling-summary:: total: 644.8501 msec (fallback: 0.0 msec)</caption>\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th id=\"T_7a5ce_level0_col0\" class=\"col_heading level0 col0\" >name</th>\n",
              "      <th id=\"T_7a5ce_level0_col1\" class=\"col_heading level0 col1\" >type</th>\n",
              "      <th id=\"T_7a5ce_level0_col2\" class=\"col_heading level0 col2\" >n_calls</th>\n",
              "      <th id=\"T_7a5ce_level0_col3\" class=\"col_heading level0 col3\" >duration (msec)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_7a5ce_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_7a5ce_row0_col0\" class=\"data row0 col0\" >read_csv_with_metadata</td>\n",
              "      <td id=\"T_7a5ce_row0_col1\" class=\"data row0 col1\" >kernel</td>\n",
              "      <td id=\"T_7a5ce_row0_col2\" class=\"data row0 col2\" >1</td>\n",
              "      <td id=\"T_7a5ce_row0_col3\" class=\"data row0 col3\" >632.789556</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_7a5ce_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_7a5ce_row1_col0\" class=\"data row1 col0\" >read_csv_metadata</td>\n",
              "      <td id=\"T_7a5ce_row1_col1\" class=\"data row1 col1\" >kernel</td>\n",
              "      <td id=\"T_7a5ce_row1_col2\" class=\"data row1 col2\" >1</td>\n",
              "      <td id=\"T_7a5ce_row1_col3\" class=\"data row1 col3\" >8.916092</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_7a5ce_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_7a5ce_row2_col0\" class=\"data row2 col0\" >groupby_select_agg</td>\n",
              "      <td id=\"T_7a5ce_row2_col1\" class=\"data row2 col1\" >kernel</td>\n",
              "      <td id=\"T_7a5ce_row2_col2\" class=\"data row2 col2\" >1</td>\n",
              "      <td id=\"T_7a5ce_row2_col3\" class=\"data row2 col3\" >3.127504</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_7a5ce_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_7a5ce_row3_col0\" class=\"data row3 col0\" >reset_index</td>\n",
              "      <td id=\"T_7a5ce_row3_col1\" class=\"data row3 col1\" >kernel</td>\n",
              "      <td id=\"T_7a5ce_row3_col2\" class=\"data row3 col2\" >1</td>\n",
              "      <td id=\"T_7a5ce_row3_col3\" class=\"data row3 col3\" >0.010204</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_7a5ce_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
              "      <td id=\"T_7a5ce_row4_col0\" class=\"data row4 col0\" >get_shape</td>\n",
              "      <td id=\"T_7a5ce_row4_col1\" class=\"data row4 col1\" >kernel</td>\n",
              "      <td id=\"T_7a5ce_row4_col2\" class=\"data row4 col2\" >1</td>\n",
              "      <td id=\"T_7a5ce_row4_col3\" class=\"data row4 col3\" >0.000986</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## For Polars\n",
        "\n",
        "\n",
        "read_csv() in polars is a eager method, so it will stop its optimization.\n",
        "Calling read_csv().lazy() will also stop optimization related to data-load.\n",
        "So better to use scan_csv() to make the comparison looks similar to FireDucks. Also in case of polars-lazy mode, in order to trigger the execution you need to invoke collect() method explicitly. Hence modified it as follows:"
      ],
      "metadata": {
        "id": "neykhXQvqWUM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "t1 = time.time()\n",
        "df = pl.scan_csv(\"taxi.csv\").group_by(\"PULocationID\").agg(pl.mean(\"tip_amount\")).collect()\n",
        "t2 = time.time()\n",
        "print(f\"execution time: {t2 - t1} sec\")"
      ],
      "metadata": {
        "id": "6Cpt3Dv5qmS-",
        "outputId": "f0d52042-c524-49a0-c7e6-3d469963c6b7",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "execution time: 0.8736696243286133 sec\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# to check polars kernel wise execution time:\n",
        "t1 = time.time()\n",
        "df = pl.scan_csv(\"taxi.csv\").group_by(\"PULocationID\").agg(pl.mean(\"tip_amount\")).profile()\n",
        "t2 = time.time()\n",
        "print(f\"execution time: {t2 - t1} sec\")\n",
        "df[1].with_columns(((pl.col(\"end\") - pl.col(\"start\")) / 1e2).alias(\"duration(msec)\"))"
      ],
      "metadata": {
        "id": "0tnxb1Tfq3KD",
        "outputId": "1371906f-121c-44c9-e912-17f6a1b8bfcd",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 241
        }
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "execution time: 0.8473613262176514 sec\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (4, 4)\n",
              "┌─────────────────────────────────┬────────┬────────┬────────────────┐\n",
              "│ node                            ┆ start  ┆ end    ┆ duration(msec) │\n",
              "│ ---                             ┆ ---    ┆ ---    ┆ ---            │\n",
              "│ str                             ┆ u64    ┆ u64    ┆ f64            │\n",
              "╞═════════════════════════════════╪════════╪════════╪════════════════╡\n",
              "│ optimization                    ┆ 0      ┆ 7      ┆ 0.07           │\n",
              "│ csv(taxi.csv)                   ┆ 7      ┆ 551143 ┆ 5511.36        │\n",
              "│ simple-projection(tip_amount, … ┆ 551147 ┆ 551155 ┆ 0.08           │\n",
              "│ group_by_partitioned(PULocatio… ┆ 551162 ┆ 845466 ┆ 2943.04        │\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: (4, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>node</th><th>start</th><th>end</th><th>duration(msec)</th></tr><tr><td>str</td><td>u64</td><td>u64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;optimization&quot;</td><td>0</td><td>7</td><td>0.07</td></tr><tr><td>&quot;csv(taxi.csv)&quot;</td><td>7</td><td>551143</td><td>5511.36</td></tr><tr><td>&quot;simple-projection(tip_amount, …</td><td>551147</td><td>551155</td><td>0.08</td></tr><tr><td>&quot;group_by_partitioned(PULocatio…</td><td>551162</td><td>845466</td><td>2943.04</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Now let's see an interesting difference\n",
        "\n",
        "When you use FireDucks, it can reuse already evaluated expression,\n",
        "\n",
        "but it seems like Polars execute entire lazy-expression for each collect. Hence it becomes slower."
      ],
      "metadata": {
        "id": "hNfruKw2ruMt"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%fireducks.profile\n",
        "# You can see read_csv() is called only once, whereas groupby, reset_index() are called two times.\n",
        "# read_csv() is called at the time of computing \"r1\", # and it has been automatically cached by the\n",
        "# FireDucks compiler to be reused in the computation for \"r2\"\n",
        "t1 = time.time()\n",
        "df = pd.read_csv(\"taxi.csv\")\n",
        "r1 = df.groupby(\"PULocationID\").tip_amount.mean().reset_index()._evaluate()\n",
        "r2 = df.groupby(\"PULocationID\").tolls_amount.mean().reset_index()._evaluate()\n",
        "t2 = time.time()\n",
        "print(f\"execution time: {t2 - t1} sec\")"
      ],
      "metadata": {
        "id": "GXE_hQILr9Me",
        "outputId": "64118c9b-3c0a-4dc1-dd2d-de53a52c492a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 209
        }
      },
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "execution time: 0.6885213851928711 sec\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<pandas.io.formats.style.Styler at 0x787a5c14a1a0>"
            ],
            "text/html": [
              "<style type=\"text/css\">\n",
              "#T_7464f caption {\n",
              "  text-align: center;\n",
              "  color: salmon;\n",
              "}\n",
              "</style>\n",
              "<table id=\"T_7464f\" class=\"dataframe\">\n",
              "  <caption>profiling-summary:: total: 673.4323 msec (fallback: 0.0 msec)</caption>\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th id=\"T_7464f_level0_col0\" class=\"col_heading level0 col0\" >name</th>\n",
              "      <th id=\"T_7464f_level0_col1\" class=\"col_heading level0 col1\" >type</th>\n",
              "      <th id=\"T_7464f_level0_col2\" class=\"col_heading level0 col2\" >n_calls</th>\n",
              "      <th id=\"T_7464f_level0_col3\" class=\"col_heading level0 col3\" >duration (msec)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_7464f_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_7464f_row0_col0\" class=\"data row0 col0\" >read_csv_with_metadata</td>\n",
              "      <td id=\"T_7464f_row0_col1\" class=\"data row0 col1\" >kernel</td>\n",
              "      <td id=\"T_7464f_row0_col2\" class=\"data row0 col2\" >1</td>\n",
              "      <td id=\"T_7464f_row0_col3\" class=\"data row0 col3\" >590.419729</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_7464f_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_7464f_row1_col0\" class=\"data row1 col0\" >groupby_select_agg</td>\n",
              "      <td id=\"T_7464f_row1_col1\" class=\"data row1 col1\" >kernel</td>\n",
              "      <td id=\"T_7464f_row1_col2\" class=\"data row1 col2\" >2</td>\n",
              "      <td id=\"T_7464f_row1_col3\" class=\"data row1 col3\" >74.102473</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_7464f_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_7464f_row2_col0\" class=\"data row2 col0\" >read_csv_metadata</td>\n",
              "      <td id=\"T_7464f_row2_col1\" class=\"data row2 col1\" >kernel</td>\n",
              "      <td id=\"T_7464f_row2_col2\" class=\"data row2 col2\" >1</td>\n",
              "      <td id=\"T_7464f_row2_col3\" class=\"data row2 col3\" >8.881185</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_7464f_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_7464f_row3_col0\" class=\"data row3 col0\" >reset_index</td>\n",
              "      <td id=\"T_7464f_row3_col1\" class=\"data row3 col1\" >kernel</td>\n",
              "      <td id=\"T_7464f_row3_col2\" class=\"data row3 col2\" >2</td>\n",
              "      <td id=\"T_7464f_row3_col3\" class=\"data row3 col3\" >0.016977</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# to check polars kernel wise execution time:\n",
        "t1 = time.time()\n",
        "df = pl.scan_csv(\"taxi.csv\")\n",
        "r1 = df.group_by(\"PULocationID\").agg(pl.mean(\"tip_amount\")).profile()\n",
        "r2 = df.group_by(\"PULocationID\").agg(pl.mean(\"tolls_amount\")).profile()\n",
        "t2 = time.time()\n",
        "print(f\"execution time: {t2 - t1} sec\")"
      ],
      "metadata": {
        "id": "l41dhvQTsNIl",
        "outputId": "2851acfe-2ec3-4b06-ee21-e79bcc74c1ca",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "execution time: 1.79921293258667 sec\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "r1[1].with_columns(((pl.col(\"end\") - pl.col(\"start\")) / 1e2).alias(\"duration(msec)\")) # for r1: it will execute everything (read-csv, groupby-projection)"
      ],
      "metadata": {
        "id": "dD9Jk1jcsU8Z",
        "outputId": "da311020-f107-4ca0-8d0f-feccadf73671",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 223
        }
      },
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (4, 4)\n",
              "┌─────────────────────────────────┬────────┬────────┬────────────────┐\n",
              "│ node                            ┆ start  ┆ end    ┆ duration(msec) │\n",
              "│ ---                             ┆ ---    ┆ ---    ┆ ---            │\n",
              "│ str                             ┆ u64    ┆ u64    ┆ f64            │\n",
              "╞═════════════════════════════════╪════════╪════════╪════════════════╡\n",
              "│ optimization                    ┆ 0      ┆ 6      ┆ 0.06           │\n",
              "│ csv(taxi.csv)                   ┆ 6      ┆ 599871 ┆ 5998.65        │\n",
              "│ simple-projection(tip_amount, … ┆ 599873 ┆ 599883 ┆ 0.1            │\n",
              "│ group_by_partitioned(PULocatio… ┆ 599889 ┆ 900963 ┆ 3010.74        │\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: (4, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>node</th><th>start</th><th>end</th><th>duration(msec)</th></tr><tr><td>str</td><td>u64</td><td>u64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;optimization&quot;</td><td>0</td><td>6</td><td>0.06</td></tr><tr><td>&quot;csv(taxi.csv)&quot;</td><td>6</td><td>599871</td><td>5998.65</td></tr><tr><td>&quot;simple-projection(tip_amount, …</td><td>599873</td><td>599883</td><td>0.1</td></tr><tr><td>&quot;group_by_partitioned(PULocatio…</td><td>599889</td><td>900963</td><td>3010.74</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "r2[1].with_columns(((pl.col(\"end\") - pl.col(\"start\")) / 1e2).alias(\"duration(msec)\")) # for r2: it will again execute everything (read-csv, groupby-projection)"
      ],
      "metadata": {
        "id": "3laf6iJCsaf9",
        "outputId": "4e5dfa0e-f985-425d-859e-22d4d38749e7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 223
        }
      },
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "shape: (4, 4)\n",
              "┌─────────────────────────────────┬────────┬────────┬────────────────┐\n",
              "│ node                            ┆ start  ┆ end    ┆ duration(msec) │\n",
              "│ ---                             ┆ ---    ┆ ---    ┆ ---            │\n",
              "│ str                             ┆ u64    ┆ u64    ┆ f64            │\n",
              "╞═════════════════════════════════╪════════╪════════╪════════════════╡\n",
              "│ optimization                    ┆ 0      ┆ 4      ┆ 0.04           │\n",
              "│ csv(taxi.csv)                   ┆ 4      ┆ 593568 ┆ 5935.64        │\n",
              "│ simple-projection(tolls_amount… ┆ 593569 ┆ 593579 ┆ 0.1            │\n",
              "│ group_by_partitioned(PULocatio… ┆ 593586 ┆ 895620 ┆ 3020.34        │\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: (4, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>node</th><th>start</th><th>end</th><th>duration(msec)</th></tr><tr><td>str</td><td>u64</td><td>u64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;optimization&quot;</td><td>0</td><td>4</td><td>0.04</td></tr><tr><td>&quot;csv(taxi.csv)&quot;</td><td>4</td><td>593568</td><td>5935.64</td></tr><tr><td>&quot;simple-projection(tolls_amount…</td><td>593569</td><td>593579</td><td>0.1</td></tr><tr><td>&quot;group_by_partitioned(PULocatio…</td><td>593586</td><td>895620</td><td>3020.34</td></tr></tbody></table></div>"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "PBseNOfRJRNQ"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "gpuType": "V28"
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    },
    "accelerator": "TPU",
    "widgets": {
      "application/vnd.jupyter.widget-state+json": {
        "39f97b6b61cf44fa9cea820f8bc8ff2a": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "FloatProgressModel",
          "model_module_version": "1.5.0",
          "state": {
            "_dom_classes": [],
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "FloatProgressModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/controls",
            "_view_module_version": "1.5.0",
            "_view_name": "ProgressView",
            "bar_style": "",
            "description": "",
            "description_tooltip": null,
            "layout": "IPY_MODEL_5d62742ce1c34ac492ac1e3bb37ecea6",
            "max": 100,
            "min": 0,
            "orientation": "horizontal",
            "style": "IPY_MODEL_20f16364c9714509850bd60def69b472",
            "value": 100
          }
        },
        "5d62742ce1c34ac492ac1e3bb37ecea6": {
          "model_module": "@jupyter-widgets/base",
          "model_name": "LayoutModel",
          "model_module_version": "1.2.0",
          "state": {
            "_model_module": "@jupyter-widgets/base",
            "_model_module_version": "1.2.0",
            "_model_name": "LayoutModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "LayoutView",
            "align_content": null,
            "align_items": null,
            "align_self": null,
            "border": null,
            "bottom": null,
            "display": null,
            "flex": null,
            "flex_flow": null,
            "grid_area": null,
            "grid_auto_columns": null,
            "grid_auto_flow": null,
            "grid_auto_rows": null,
            "grid_column": null,
            "grid_gap": null,
            "grid_row": null,
            "grid_template_areas": null,
            "grid_template_columns": null,
            "grid_template_rows": null,
            "height": null,
            "justify_content": null,
            "justify_items": null,
            "left": null,
            "margin": null,
            "max_height": null,
            "max_width": null,
            "min_height": null,
            "min_width": null,
            "object_fit": null,
            "object_position": null,
            "order": null,
            "overflow": null,
            "overflow_x": null,
            "overflow_y": null,
            "padding": null,
            "right": null,
            "top": null,
            "visibility": null,
            "width": "auto"
          }
        },
        "20f16364c9714509850bd60def69b472": {
          "model_module": "@jupyter-widgets/controls",
          "model_name": "ProgressStyleModel",
          "model_module_version": "1.5.0",
          "state": {
            "_model_module": "@jupyter-widgets/controls",
            "_model_module_version": "1.5.0",
            "_model_name": "ProgressStyleModel",
            "_view_count": null,
            "_view_module": "@jupyter-widgets/base",
            "_view_module_version": "1.2.0",
            "_view_name": "StyleView",
            "bar_color": "black",
            "description_width": ""
          }
        }
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}