duckdb/tutorial/06-supported-apis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "33878454-9235-454f-a409-d96139cb77e9",
   "metadata": {},
   "source": [
    "# Tutorial 6: Supported APIs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f57d3a92-e6a3-4844-a35e-77bfb0d8dfad",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\"> <b>Before we get started: </b> \n",
    "    <ul style=\"list-style-type: none;margin: 0;padding: 0;\">\n",
    "        <li>✍️ To run this notebook, you need to have Ponder installed and set up on your machine. If you have not done so already, please refer to our <a href=\"https://docs.ponder.io/getting_started/quickstart.html\">Quickstart guide</a> to get started.</li>\n",
    "        <li>📖 Otherwise, if you're just interested in browsing through the tutorial, keep reading below!</li>\n",
    "    </ul>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "60f9ff72-a3f5-412a-a1c0-4b037711bb9c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-04-21 00:52:32,686 - <module ponder> - WARNING - New version of Ponder is available! `pip install --upgrade ponder` to install!\n"
     ]
    }
   ],
   "source": [
    "import ponder; ponder.init()\n",
    "import modin.pandas as pd\n",
    "import duckdb\n",
    "duckdb_con = duckdb.connect(\"../ponder.db\")\n",
    "ponder.configure(default_connection=duckdb_con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37e504e4-660d-4f0a-9446-7aad1ed3d603",
   "metadata": {},
   "source": [
    "Ponder aims to be a drop-in replacement for pandas, we are working to support as much of the pandas API as possible, but it is possible that certain pandas APIs are not currently supported in Ponder. You can find a full list of pandas APIs we support [here](https://docs.ponder.io/overviewAPI/dataframes.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "168044fa-6340-4288-8865-bad85c70d260",
   "metadata": {},
   "source": [
    "### What happens when an API is not supported in Ponder?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4d47ff3-3f11-41b1-b50f-3370e2ed99b3",
   "metadata": {},
   "source": [
    "In the case where you are using a function that is not yet supported, you will get an `NotImplementedError`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9f023bcb-8b11-4dcb-9bb1-bf1c6509ce90",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"https://raw.githubusercontent.com/ponder-org/ponder-datasets/main/tpch/orders.csv\", header=0)\n",
    "num_df = df.select_dtypes(\"number\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "615ba6d4-c050-476a-a925-92ebf46bfa36",
   "metadata": {},
   "source": [
    "For example, here we are using the `.corr()` function to compute the correlation matrix. This is not currently implemented in Ponder, so running this will throw a `NotImplementedError`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "728abee0-81d1-424f-85e5-89ac1723ec42",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "ename": "NotImplementedError",
     "evalue": "We don't yet support mismatched index objects",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNotImplementedError\u001b[0m                       Traceback (most recent call last)",
      "Input \u001b[0;32mIn [3]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0m \u001b[43mnum_df\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcorr\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[0;32m~/.virtualenvs/dev/lib/python3.10/site-packages/modin/logging/logger_decorator.py:128\u001b[0m, in \u001b[0;36menable_logging.<locals>.decorator.<locals>.run_and_log\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    113\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m    114\u001b[0m \u001b[38;5;124;03mCompute function with logging if Modin logging is enabled.\u001b[39;00m\n\u001b[1;32m    115\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    125\u001b[0m \u001b[38;5;124;03mAny\u001b[39;00m\n\u001b[1;32m    126\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m    127\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m LogMode\u001b[38;5;241m.\u001b[39mget() \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdisable\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m--> 128\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mobj\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    130\u001b[0m logger \u001b[38;5;241m=\u001b[39m get_logger()\n\u001b[1;32m    131\u001b[0m logger_level \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mgetattr\u001b[39m(logger, log_level)\n",
      "File \u001b[0;32m~/.virtualenvs/dev/lib/python3.10/site-packages/modin/pandas/dataframe.py:743\u001b[0m, in \u001b[0;36mDataFrame.corr\u001b[0;34m(self, method, min_periods, numeric_only)\u001b[0m\n\u001b[1;32m    735\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m numeric_only:\n\u001b[1;32m    736\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_default_to_pandas(\n\u001b[1;32m    737\u001b[0m         pandas\u001b[38;5;241m.\u001b[39mDataFrame\u001b[38;5;241m.\u001b[39mcorr,\n\u001b[1;32m    738\u001b[0m         method\u001b[38;5;241m=\u001b[39mmethod,\n\u001b[1;32m    739\u001b[0m         min_periods\u001b[38;5;241m=\u001b[39mmin_periods,\n\u001b[1;32m    740\u001b[0m         numeric_only\u001b[38;5;241m=\u001b[39mnumeric_only,\n\u001b[1;32m    741\u001b[0m     )\n\u001b[1;32m    742\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m__constructor__(\n\u001b[0;32m--> 743\u001b[0m     query_compiler\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_query_compiler\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcorr\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m    744\u001b[0m \u001b[43m        \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    745\u001b[0m \u001b[43m        \u001b[49m\u001b[43mmin_periods\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmin_periods\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m    746\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    747\u001b[0m )\n",
      "File \u001b[0;32m~/.virtualenvs/dev/lib/python3.10/site-packages/modin/logging/logger_decorator.py:128\u001b[0m, in \u001b[0;36menable_logging.<locals>.decorator.<locals>.run_and_log\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    113\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m    114\u001b[0m \u001b[38;5;124;03mCompute function with logging if Modin logging is enabled.\u001b[39;00m\n\u001b[1;32m    115\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    125\u001b[0m \u001b[38;5;124;03mAny\u001b[39;00m\n\u001b[1;32m    126\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[1;32m    127\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m LogMode\u001b[38;5;241m.\u001b[39mget() \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdisable\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m--> 128\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mobj\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    130\u001b[0m logger \u001b[38;5;241m=\u001b[39m get_logger()\n\u001b[1;32m    131\u001b[0m logger_level \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mgetattr\u001b[39m(logger, log_level)\n",
      "File \u001b[0;32m~/.virtualenvs/dev/lib/python3.10/site-packages/ponder/pushdown_service/common/query_compiler.py:1883\u001b[0m, in \u001b[0;36mcorr\u001b[0;34m(self, **kwargs)\u001b[0m\n",
      "File \u001b[0;32m~/.virtualenvs/dev/lib/python3.10/site-packages/ponder/pushdown_service/common/dataframe.py:1642\u001b[0m, in \u001b[0;36mreduce\u001b[0;34m(self, func, axis, dtypes, percentile, params_list)\u001b[0m\n",
      "File \u001b[0;32m~/.virtualenvs/dev/lib/python3.10/site-packages/ponder/pushdown_service/common/dataframe.py:1283\u001b[0m, in \u001b[0;36mconcat\u001b[0;34m(self, axis, others, how, sort)\u001b[0m\n",
      "\u001b[0;31mNotImplementedError\u001b[0m: We don't yet support mismatched index objects"
     ]
    }
   ],
   "source": [
    "num_df.corr()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "beac5319-9160-429d-af5c-41283a03e8fb",
   "metadata": {},
   "source": [
    "If you are running into this error, please send us the specific dataset and APIs that you’re using to support@ponder.io, so that we can suggest a possible solution. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1516af6-bcc4-428a-97a8-ca4292e86484",
   "metadata": {},
   "source": [
    "Note that in such cases, there may be ways to rewrite the query in a different way that leverages the [APIs we support](https://docs.ponder.io/overviewAPI/dataframes.html). "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db7862d2-8bf2-4d28-8446-382237f05ce9",
   "metadata": {},
   "source": [
    "### Workaround: Defaulting to Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65eec275-6dcf-4ec6-906d-457b71d4e8af",
   "metadata": {},
   "source": [
    "Alternatively, you can access the underlying pandas dataframe via the `_to_pandas` helper method. Note that when you call `_to_pandas`, your entire table from your warehouse is now pull into memory since pandas operates in memory. Beware that this can incur a high I/O cost if you have a very large table, so please do so with care. In this case, we have a very small table, so it is ok to default to pandas as a workaround."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b10980d3-d89b-4861-9b05-2ae42192bd01",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(145, 4)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca9564cd-ce8b-446f-a46d-c49e37034b6a",
   "metadata": {},
   "source": [
    "Our original dataframe is a Modin DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bcd2fd24-d5bd-4d4a-b74e-416dc13ed799",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "modin.pandas.dataframe.DataFrame"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(num_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdc52e24-d712-4d2b-b204-54c310e7feb8",
   "metadata": {},
   "source": [
    "`_to_pandas` returns a pandas DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "627d8616-1580-4fe3-9e76-a21069f027de",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(num_df._to_pandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c5c1441-5fe3-45d3-a2b0-4e8b528a8e19",
   "metadata": {
    "tags": []
   },
   "source": [
    "We can then default to using pandas's implementation for `.corr()` to get our result. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b9e5346f-b0c9-4469-b1f3-fb3c1cd1bb36",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>O_ORDERKEY</th>\n",
       "      <th>O_CUSTKEY</th>\n",
       "      <th>O_TOTALPRICE</th>\n",
       "      <th>O_SHIPPRIORITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>O_ORDERKEY</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.070673</td>\n",
       "      <td>0.169060</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>O_CUSTKEY</th>\n",
       "      <td>-0.070673</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.131396</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>O_TOTALPRICE</th>\n",
       "      <td>0.169060</td>\n",
       "      <td>0.131396</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>O_SHIPPRIORITY</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                O_ORDERKEY  O_CUSTKEY  O_TOTALPRICE  O_SHIPPRIORITY\n",
       "O_ORDERKEY        1.000000  -0.070673      0.169060             NaN\n",
       "O_CUSTKEY        -0.070673   1.000000      0.131396             NaN\n",
       "O_TOTALPRICE      0.169060   0.131396      1.000000             NaN\n",
       "O_SHIPPRIORITY         NaN        NaN           NaN             NaN"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_df._to_pandas().corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d65076e7-5d1f-4b21-b7bf-c2b31148a0c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "duckdb_con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51269034-9beb-436a-9eea-48e16454a62c",
   "metadata": {},
   "source": [
    "# Summary\n",
    "\n",
    "In this tutorial, we learned how you can work with APIs that are supported with Ponder and handle cases where the pandas API is not yet supported by Ponder.\n",
    "\n",
    "In our [next tutorial](https://github.com/ponder-org/ponder-notebooks/blob/main/duckdb/tutorial/07-library-integration.ipynb), we will discuss how you can use other Python libraries for visualization and machine learning alongside Ponder."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}