duckdb/tutorial/01-getting-started.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "57c6b83b-d5e8-47f6-81b9-d2c4761ae9e8",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Tutorial 1: Getting Started"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03f3ff58-5551-492a-9e0f-848848316bd5",
   "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": "markdown",
   "id": "206c8590-c96c-4ef3-ae36-75192e1806d8",
   "metadata": {},
   "source": [
    "In this tutorial, we will walk through how you can get started running pandas on DuckDB using Ponder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6898ca91-e27e-4d18-b510-f7582684c095",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-05-11 15:10:59,344 - authenticate_and_verify - WARNING - New version of Ponder is available! `pip install --upgrade ponder` to install!\n"
     ]
    }
   ],
   "source": [
    "import ponder\n",
    "ponder.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8534cd18-131a-486c-b6f2-def39dab5b6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os; os.chdir(\"..\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fab1d66c-9b59-439c-85be-ce4d2e9315ac",
   "metadata": {},
   "source": [
    "### Uploading Example Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b3a9ac7-08a8-4b7d-8b2f-750693998a00",
   "metadata": {},
   "source": [
    "We will be using a few example datasets for the tutorial. You can run this python script to populate the required datasets to your database. This will add the following tables to your database populated with example datasets: \n",
    "\n",
    "- [PONDER_CITIBIKE](https://raw.githubusercontent.com/ponder-org/ponder-datasets/main/citibike_trial.csv)\n",
    "- [PONDER_BOOKS](https://github.com/ponder-org/ponder-datasets/blob/main/books.csv)\n",
    "- [PONDER_CUSTOMER](https://raw.githubusercontent.com/ponder-org/ponder-datasets/main/tpch/customer.csv)\n",
    "- [PONDER_ORDER](https://raw.githubusercontent.com/ponder-org/ponder-datasets/main/tpch/orders.csv)\n",
    "- [PONDER_PART](https://raw.githubusercontent.com/ponder-org/ponder-datasets/main/tpch/part.csv)\n",
    "- [PONDER_SUPPLIER](https://raw.githubusercontent.com/ponder-org/ponder-datasets/main/tpch/supplier.csv)\n",
    "\n",
    "Note that you only need to run the following script once for the tables to get populated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "501993f3-c06d-477c-be1e-df0196710ed2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-04-21 06:02:31,002 - <module ponder> - WARNING - New version of Ponder is available! `pip install --upgrade ponder` to install!\n",
      "Uploaded dataset to PONDER_CITIBIKE\n",
      "Uploaded dataset to PONDER_BOOKS\n",
      "Uploaded dataset to PONDER_CUSTOMER\n",
      "Uploaded dataset to PONDER_ORDER\n",
      "Uploaded dataset to PONDER_PART\n",
      "Uploaded dataset to PONDER_SUPPLIER\n"
     ]
    }
   ],
   "source": [
    "!python populate_datasets.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed59fe47-7e6a-49e5-ab5d-be9f08e9704b",
   "metadata": {},
   "source": [
    "### Connecting to DuckDB"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7e111fd-481d-4d9b-b3de-65c6f654e5e0",
   "metadata": {},
   "source": [
    "Ponder uses your database as an engine, so we need to establish a connection with DuckDB in order to start querying the data. The code below shows how you can configure the database connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "646cff05-eda8-4d8f-8bf7-ec0cf8f27c06",
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "duckdb_con = duckdb.connect(\"ponder.db\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c24f089-1302-4110-b46e-142328dec773",
   "metadata": {},
   "source": [
    "First let's take a look at the tables that are preloaded into this database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5c86cc2d-ff6c-4225-b22f-5a3aa7fb693f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>database_name</th>\n",
       "      <th>database_oid</th>\n",
       "      <th>schema_name</th>\n",
       "      <th>schema_oid</th>\n",
       "      <th>table_name</th>\n",
       "      <th>table_oid</th>\n",
       "      <th>internal</th>\n",
       "      <th>temporary</th>\n",
       "      <th>has_primary_key</th>\n",
       "      <th>estimated_size</th>\n",
       "      <th>column_count</th>\n",
       "      <th>index_count</th>\n",
       "      <th>check_constraint_count</th>\n",
       "      <th>sql</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ponder</td>\n",
       "      <td>4</td>\n",
       "      <td>main</td>\n",
       "      <td>856</td>\n",
       "      <td>PONDER_CITIBIKE</td>\n",
       "      <td>876</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>118865</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>CREATE TABLE \"PONDER_CITIBIKE\"(tripduration DO...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ponder</td>\n",
       "      <td>4</td>\n",
       "      <td>main</td>\n",
       "      <td>856</td>\n",
       "      <td>PONDER_BOOKS</td>\n",
       "      <td>874</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11123</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>CREATE TABLE \"PONDER_BOOKS\"(\"bookID\" BIGINT, t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ponder</td>\n",
       "      <td>4</td>\n",
       "      <td>main</td>\n",
       "      <td>856</td>\n",
       "      <td>PONDER_CUSTOMER</td>\n",
       "      <td>872</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>100</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>CREATE TABLE \"PONDER_CUSTOMER\"(\"C_CUSTKEY\" BIG...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ponder</td>\n",
       "      <td>4</td>\n",
       "      <td>main</td>\n",
       "      <td>856</td>\n",
       "      <td>PONDER_ORDERS</td>\n",
       "      <td>870</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>145</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>CREATE TABLE \"PONDER_ORDERS\"(\"O_ORDERKEY\" BIGI...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ponder</td>\n",
       "      <td>4</td>\n",
       "      <td>main</td>\n",
       "      <td>856</td>\n",
       "      <td>PONDER_PART</td>\n",
       "      <td>868</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>3893</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>CREATE TABLE \"PONDER_PART\"(\"P_PARTKEY\" BIGINT,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ponder</td>\n",
       "      <td>4</td>\n",
       "      <td>main</td>\n",
       "      <td>856</td>\n",
       "      <td>PONDER_SUPPLIER</td>\n",
       "      <td>866</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>3255</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>CREATE TABLE \"PONDER_SUPPLIER\"(\"S_SUPPKEY\" BIG...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  database_name  database_oid schema_name  schema_oid       table_name  \\\n",
       "0        ponder             4        main         856  PONDER_CITIBIKE   \n",
       "1        ponder             4        main         856     PONDER_BOOKS   \n",
       "2        ponder             4        main         856  PONDER_CUSTOMER   \n",
       "3        ponder             4        main         856    PONDER_ORDERS   \n",
       "4        ponder             4        main         856      PONDER_PART   \n",
       "5        ponder             4        main         856  PONDER_SUPPLIER   \n",
       "\n",
       "   table_oid  internal  temporary  has_primary_key  estimated_size  \\\n",
       "0        876     False      False            False          118865   \n",
       "1        874     False      False            False           11123   \n",
       "2        872     False      False            False             100   \n",
       "3        870     False      False            False             145   \n",
       "4        868     False      False            False            3893   \n",
       "5        866     False      False            False            3255   \n",
       "\n",
       "   column_count  index_count  check_constraint_count  \\\n",
       "0            15            0                       0   \n",
       "1            12            0                       0   \n",
       "2             8            0                       0   \n",
       "3             9            0                       0   \n",
       "4             9            0                       0   \n",
       "5             7            0                       0   \n",
       "\n",
       "                                                 sql  \n",
       "0  CREATE TABLE \"PONDER_CITIBIKE\"(tripduration DO...  \n",
       "1  CREATE TABLE \"PONDER_BOOKS\"(\"bookID\" BIGINT, t...  \n",
       "2  CREATE TABLE \"PONDER_CUSTOMER\"(\"C_CUSTKEY\" BIG...  \n",
       "3  CREATE TABLE \"PONDER_ORDERS\"(\"O_ORDERKEY\" BIGI...  \n",
       "4  CREATE TABLE \"PONDER_PART\"(\"P_PARTKEY\" BIGINT,...  \n",
       "5  CREATE TABLE \"PONDER_SUPPLIER\"(\"S_SUPPKEY\" BIG...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckdb_con.execute('SELECT * FROM duckdb_tables;').df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e0274eb-36b8-47ea-9110-bbd802d0643e",
   "metadata": {},
   "source": [
    "### Starting Pondering 🎉"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d82a1849-d53c-4c63-a31e-747445fabec2",
   "metadata": {},
   "source": [
    "Now that we have the connection initialized. Let's read the **PONDER_BOOKS** table that already exists in your database. This dataset comes from the [Goodreads dataset from Kaggle](https://www.kaggle.com/datasets/jealousleopard/goodreadsbooks) and contains a books and their review information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8797cd9c-e2b0-4589-8f60-8583e1347b1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import modin.pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6f7737dd-03ae-4b6f-aeaf-378609b9d5d2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.read_sql(\"PONDER_BOOKS\", duckdb_con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47a74a49-45b5-4070-ace6-5b36f6edadad",
   "metadata": {},
   "source": [
    "Let's first print out the dataframe and take a look at the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0fab392c-75d4-4a3b-b0b7-f16f72205ba5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bookID</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>language_code</th>\n",
       "      <th>num_pages</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>text_reviews_count</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Harry Potter and the Half-Blood Prince (Harry ...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.57</td>\n",
       "      <td>0439785960</td>\n",
       "      <td>9780439785969</td>\n",
       "      <td>eng</td>\n",
       "      <td>652</td>\n",
       "      <td>2095690</td>\n",
       "      <td>27591</td>\n",
       "      <td>9/16/2006</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Har...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.49</td>\n",
       "      <td>0439358078</td>\n",
       "      <td>9780439358071</td>\n",
       "      <td>eng</td>\n",
       "      <td>870</td>\n",
       "      <td>2153167</td>\n",
       "      <td>29221</td>\n",
       "      <td>9/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Harry Potter and the Chamber of Secrets (Harry...</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>4.42</td>\n",
       "      <td>0439554896</td>\n",
       "      <td>9780439554893</td>\n",
       "      <td>eng</td>\n",
       "      <td>352</td>\n",
       "      <td>6333</td>\n",
       "      <td>244</td>\n",
       "      <td>11/1/2003</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.56</td>\n",
       "      <td>043965548X</td>\n",
       "      <td>9780439655484</td>\n",
       "      <td>eng</td>\n",
       "      <td>435</td>\n",
       "      <td>2339585</td>\n",
       "      <td>36325</td>\n",
       "      <td>5/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Harry Potter Boxed Set  Books 1-5 (Harry Potte...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.78</td>\n",
       "      <td>0439682584</td>\n",
       "      <td>9780439682589</td>\n",
       "      <td>eng</td>\n",
       "      <td>2690</td>\n",
       "      <td>41428</td>\n",
       "      <td>164</td>\n",
       "      <td>9/13/2004</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11118</th>\n",
       "      <td>45631</td>\n",
       "      <td>Expelled from Eden: A William T. Vollmann Reader</td>\n",
       "      <td>William T. Vollmann/Larry McCaffery/Michael He...</td>\n",
       "      <td>4.06</td>\n",
       "      <td>1560254416</td>\n",
       "      <td>9781560254416</td>\n",
       "      <td>eng</td>\n",
       "      <td>512</td>\n",
       "      <td>156</td>\n",
       "      <td>20</td>\n",
       "      <td>12/21/2004</td>\n",
       "      <td>Da Capo Press</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11119</th>\n",
       "      <td>45633</td>\n",
       "      <td>You Bright and Risen Angels</td>\n",
       "      <td>William T. Vollmann</td>\n",
       "      <td>4.08</td>\n",
       "      <td>0140110879</td>\n",
       "      <td>9780140110876</td>\n",
       "      <td>eng</td>\n",
       "      <td>635</td>\n",
       "      <td>783</td>\n",
       "      <td>56</td>\n",
       "      <td>12/1/1988</td>\n",
       "      <td>Penguin Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11120</th>\n",
       "      <td>45634</td>\n",
       "      <td>The Ice-Shirt (Seven Dreams #1)</td>\n",
       "      <td>William T. Vollmann</td>\n",
       "      <td>3.96</td>\n",
       "      <td>0140131965</td>\n",
       "      <td>9780140131963</td>\n",
       "      <td>eng</td>\n",
       "      <td>415</td>\n",
       "      <td>820</td>\n",
       "      <td>95</td>\n",
       "      <td>8/1/1993</td>\n",
       "      <td>Penguin Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11121</th>\n",
       "      <td>45639</td>\n",
       "      <td>Poor People</td>\n",
       "      <td>William T. Vollmann</td>\n",
       "      <td>3.72</td>\n",
       "      <td>0060878827</td>\n",
       "      <td>9780060878825</td>\n",
       "      <td>eng</td>\n",
       "      <td>434</td>\n",
       "      <td>769</td>\n",
       "      <td>139</td>\n",
       "      <td>2/27/2007</td>\n",
       "      <td>Ecco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11122</th>\n",
       "      <td>45641</td>\n",
       "      <td>Las aventuras de Tom Sawyer</td>\n",
       "      <td>Mark Twain</td>\n",
       "      <td>3.91</td>\n",
       "      <td>8497646983</td>\n",
       "      <td>9788497646987</td>\n",
       "      <td>spa</td>\n",
       "      <td>272</td>\n",
       "      <td>113</td>\n",
       "      <td>12</td>\n",
       "      <td>5/28/2006</td>\n",
       "      <td>Edimat Libros</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11123 rows x 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       bookID                                              title  \\\n",
       "0           1  Harry Potter and the Half-Blood Prince (Harry ...   \n",
       "1           2  Harry Potter and the Order of the Phoenix (Har...   \n",
       "2           4  Harry Potter and the Chamber of Secrets (Harry...   \n",
       "3           5  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "4           8  Harry Potter Boxed Set  Books 1-5 (Harry Potte...   \n",
       "...       ...                                                ...   \n",
       "11118   45631   Expelled from Eden: A William T. Vollmann Reader   \n",
       "11119   45633                        You Bright and Risen Angels   \n",
       "11120   45634                    The Ice-Shirt (Seven Dreams #1)   \n",
       "11121   45639                                        Poor People   \n",
       "11122   45641                        Las aventuras de Tom Sawyer   \n",
       "\n",
       "                                                 authors  average_rating  \\\n",
       "0                             J.K. Rowling/Mary GrandPré            4.57   \n",
       "1                             J.K. Rowling/Mary GrandPré            4.49   \n",
       "2                                           J.K. Rowling            4.42   \n",
       "3                             J.K. Rowling/Mary GrandPré            4.56   \n",
       "4                             J.K. Rowling/Mary GrandPré            4.78   \n",
       "...                                                  ...             ...   \n",
       "11118  William T. Vollmann/Larry McCaffery/Michael He...            4.06   \n",
       "11119                                William T. Vollmann            4.08   \n",
       "11120                                William T. Vollmann            3.96   \n",
       "11121                                William T. Vollmann            3.72   \n",
       "11122                                         Mark Twain            3.91   \n",
       "\n",
       "             isbn         isbn13 language_code  num_pages  ratings_count  \\\n",
       "0      0439785960  9780439785969           eng        652        2095690   \n",
       "1      0439358078  9780439358071           eng        870        2153167   \n",
       "2      0439554896  9780439554893           eng        352           6333   \n",
       "3      043965548X  9780439655484           eng        435        2339585   \n",
       "4      0439682584  9780439682589           eng       2690          41428   \n",
       "...           ...            ...           ...        ...            ...   \n",
       "11118  1560254416  9781560254416           eng        512            156   \n",
       "11119  0140110879  9780140110876           eng        635            783   \n",
       "11120  0140131965  9780140131963           eng        415            820   \n",
       "11121  0060878827  9780060878825           eng        434            769   \n",
       "11122  8497646983  9788497646987           spa        272            113   \n",
       "\n",
       "       text_reviews_count publication_date        publisher  \n",
       "0                   27591        9/16/2006  Scholastic Inc.  \n",
       "1                   29221         9/1/2004  Scholastic Inc.  \n",
       "2                     244        11/1/2003       Scholastic  \n",
       "3                   36325         5/1/2004  Scholastic Inc.  \n",
       "4                     164        9/13/2004       Scholastic  \n",
       "...                   ...              ...              ...  \n",
       "11118                  20       12/21/2004    Da Capo Press  \n",
       "11119                  56        12/1/1988    Penguin Books  \n",
       "11120                  95         8/1/1993    Penguin Books  \n",
       "11121                 139        2/27/2007             Ecco  \n",
       "11122                  12        5/28/2006    Edimat Libros  \n",
       "\n",
       "[11123 rows x 12 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ecb9f61c-798c-44c0-b3a3-02b0ccab8d10",
   "metadata": {},
   "source": [
    "Now we can start hacking away with pandas! Note that every single operations you are doing here with pandas is directly being run on DuckDB."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1c0962d-4af0-4eb0-84ed-7f9acc04bee3",
   "metadata": {},
   "source": [
    "First, let's take a look at the basic statistics around the numerical columns in our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ecf838cd-a015-4878-91c0-2f1b96bee621",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bookID</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>num_pages</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>text_reviews_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>11123.000000</td>\n",
       "      <td>11123.000000</td>\n",
       "      <td>1.112300e+04</td>\n",
       "      <td>11123.000000</td>\n",
       "      <td>1.112300e+04</td>\n",
       "      <td>11123.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>21310.856963</td>\n",
       "      <td>3.934075</td>\n",
       "      <td>9.759880e+12</td>\n",
       "      <td>336.405556</td>\n",
       "      <td>1.794285e+04</td>\n",
       "      <td>542.048099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>13094.727252</td>\n",
       "      <td>0.350485</td>\n",
       "      <td>4.429758e+11</td>\n",
       "      <td>241.152626</td>\n",
       "      <td>1.124992e+05</td>\n",
       "      <td>2576.619589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.987060e+09</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>10277.500000</td>\n",
       "      <td>3.770000</td>\n",
       "      <td>9.780345e+12</td>\n",
       "      <td>192.000000</td>\n",
       "      <td>1.040000e+02</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>20287.000000</td>\n",
       "      <td>3.960000</td>\n",
       "      <td>9.780582e+12</td>\n",
       "      <td>299.000000</td>\n",
       "      <td>7.450000e+02</td>\n",
       "      <td>47.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>32104.500000</td>\n",
       "      <td>4.140000</td>\n",
       "      <td>9.780873e+12</td>\n",
       "      <td>416.000000</td>\n",
       "      <td>5.000500e+03</td>\n",
       "      <td>238.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>45641.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>9.790008e+12</td>\n",
       "      <td>6576.000000</td>\n",
       "      <td>4.597666e+06</td>\n",
       "      <td>94265.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             bookID  average_rating        isbn13     num_pages  \\\n",
       "count  11123.000000    11123.000000  1.112300e+04  11123.000000   \n",
       "mean   21310.856963        3.934075  9.759880e+12    336.405556   \n",
       "std    13094.727252        0.350485  4.429758e+11    241.152626   \n",
       "min        1.000000        0.000000  8.987060e+09      0.000000   \n",
       "25%    10277.500000        3.770000  9.780345e+12    192.000000   \n",
       "50%    20287.000000        3.960000  9.780582e+12    299.000000   \n",
       "75%    32104.500000        4.140000  9.780873e+12    416.000000   \n",
       "max    45641.000000        5.000000  9.790008e+12   6576.000000   \n",
       "\n",
       "       ratings_count  text_reviews_count  \n",
       "count   1.112300e+04        11123.000000  \n",
       "mean    1.794285e+04          542.048099  \n",
       "std     1.124992e+05         2576.619589  \n",
       "min     0.000000e+00            0.000000  \n",
       "25%     1.040000e+02            9.000000  \n",
       "50%     7.450000e+02           47.000000  \n",
       "75%     5.000500e+03          238.000000  \n",
       "max     4.597666e+06        94265.000000  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48aecf38-8090-4b74-b7e3-a4964a6e5410",
   "metadata": {},
   "source": [
    "Let's say we want to normalize the numerical columns by doing a standard z-score normalization (where $\\mu$ is the mean and $\\sigma$ is the standard deviation). \n",
    "\n",
    "$$ x' = \\frac{x-\\mu}{\\sigma}$$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a028cf8e-c887-4ee4-aabb-66e5b80ea1cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bookID</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>num_pages</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>text_reviews_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1.627362</td>\n",
       "      <td>1.814412</td>\n",
       "      <td>0.046412</td>\n",
       "      <td>1.308692</td>\n",
       "      <td>18.469003</td>\n",
       "      <td>10.497845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1.627285</td>\n",
       "      <td>1.586157</td>\n",
       "      <td>0.046411</td>\n",
       "      <td>2.212684</td>\n",
       "      <td>18.979913</td>\n",
       "      <td>11.130456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1.627133</td>\n",
       "      <td>1.386434</td>\n",
       "      <td>0.046412</td>\n",
       "      <td>0.064666</td>\n",
       "      <td>-0.103199</td>\n",
       "      <td>-0.115674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1.627056</td>\n",
       "      <td>1.785880</td>\n",
       "      <td>0.046412</td>\n",
       "      <td>0.408847</td>\n",
       "      <td>20.636974</td>\n",
       "      <td>13.887557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-1.626827</td>\n",
       "      <td>2.413581</td>\n",
       "      <td>0.046412</td>\n",
       "      <td>9.759771</td>\n",
       "      <td>0.208758</td>\n",
       "      <td>-0.146723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11118</th>\n",
       "      <td>1.857247</td>\n",
       "      <td>0.359287</td>\n",
       "      <td>0.048942</td>\n",
       "      <td>0.728147</td>\n",
       "      <td>-0.158107</td>\n",
       "      <td>-0.202610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11119</th>\n",
       "      <td>1.857400</td>\n",
       "      <td>0.416350</td>\n",
       "      <td>0.045736</td>\n",
       "      <td>1.238197</td>\n",
       "      <td>-0.152533</td>\n",
       "      <td>-0.188638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11120</th>\n",
       "      <td>1.857476</td>\n",
       "      <td>0.073968</td>\n",
       "      <td>0.045736</td>\n",
       "      <td>0.325912</td>\n",
       "      <td>-0.152204</td>\n",
       "      <td>-0.173502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11121</th>\n",
       "      <td>1.857858</td>\n",
       "      <td>-0.610797</td>\n",
       "      <td>0.045557</td>\n",
       "      <td>0.404700</td>\n",
       "      <td>-0.152658</td>\n",
       "      <td>-0.156425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11122</th>\n",
       "      <td>1.858011</td>\n",
       "      <td>-0.068691</td>\n",
       "      <td>0.064603</td>\n",
       "      <td>-0.267074</td>\n",
       "      <td>-0.158489</td>\n",
       "      <td>-0.205715</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11123 rows x 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         bookID  average_rating    isbn13  num_pages  ratings_count  \\\n",
       "0     -1.627362        1.814412  0.046412   1.308692      18.469003   \n",
       "1     -1.627285        1.586157  0.046411   2.212684      18.979913   \n",
       "2     -1.627133        1.386434  0.046412   0.064666      -0.103199   \n",
       "3     -1.627056        1.785880  0.046412   0.408847      20.636974   \n",
       "4     -1.626827        2.413581  0.046412   9.759771       0.208758   \n",
       "...         ...             ...       ...        ...            ...   \n",
       "11118  1.857247        0.359287  0.048942   0.728147      -0.158107   \n",
       "11119  1.857400        0.416350  0.045736   1.238197      -0.152533   \n",
       "11120  1.857476        0.073968  0.045736   0.325912      -0.152204   \n",
       "11121  1.857858       -0.610797  0.045557   0.404700      -0.152658   \n",
       "11122  1.858011       -0.068691  0.064603  -0.267074      -0.158489   \n",
       "\n",
       "       text_reviews_count  \n",
       "0               10.497845  \n",
       "1               11.130456  \n",
       "2               -0.115674  \n",
       "3               13.887557  \n",
       "4               -0.146723  \n",
       "...                   ...  \n",
       "11118           -0.202610  \n",
       "11119           -0.188638  \n",
       "11120           -0.173502  \n",
       "11121           -0.156425  \n",
       "11122           -0.205715  \n",
       "\n",
       "[11123 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = df.select_dtypes(include='number').columns\n",
    "(df[x] - df[x].mean())/df[x].std()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83575259-23e6-4d80-bb0f-ec43b2a7d35d",
   "metadata": {},
   "source": [
    "Next, let's look at all the columns that are non-numerical:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c1797fd3-0523-47b2-8c36-aec684d6d173",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>isbn</th>\n",
       "      <th>language_code</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Harry Potter and the Half-Blood Prince (Harry ...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>0439785960</td>\n",
       "      <td>eng</td>\n",
       "      <td>9/16/2006</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Har...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>0439358078</td>\n",
       "      <td>eng</td>\n",
       "      <td>9/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Harry Potter and the Chamber of Secrets (Harry...</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>0439554896</td>\n",
       "      <td>eng</td>\n",
       "      <td>11/1/2003</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>043965548X</td>\n",
       "      <td>eng</td>\n",
       "      <td>5/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harry Potter Boxed Set  Books 1-5 (Harry Potte...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>0439682584</td>\n",
       "      <td>eng</td>\n",
       "      <td>9/13/2004</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title  \\\n",
       "0  Harry Potter and the Half-Blood Prince (Harry ...   \n",
       "1  Harry Potter and the Order of the Phoenix (Har...   \n",
       "2  Harry Potter and the Chamber of Secrets (Harry...   \n",
       "3  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "4  Harry Potter Boxed Set  Books 1-5 (Harry Potte...   \n",
       "\n",
       "                      authors        isbn language_code publication_date  \\\n",
       "0  J.K. Rowling/Mary GrandPré  0439785960           eng        9/16/2006   \n",
       "1  J.K. Rowling/Mary GrandPré  0439358078           eng         9/1/2004   \n",
       "2                J.K. Rowling  0439554896           eng        11/1/2003   \n",
       "3  J.K. Rowling/Mary GrandPré  043965548X           eng         5/1/2004   \n",
       "4  J.K. Rowling/Mary GrandPré  0439682584           eng        9/13/2004   \n",
       "\n",
       "         publisher  \n",
       "0  Scholastic Inc.  \n",
       "1  Scholastic Inc.  \n",
       "2       Scholastic  \n",
       "3  Scholastic Inc.  \n",
       "4       Scholastic  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select_dtypes(include='object').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "782c2dda-9f85-4852-9555-b0018a658388",
   "metadata": {},
   "source": [
    "We see that there are 27 different languages represented by `language_code` in this dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a84b0b11-b424-4f8a-abe9-cab832d4ab0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['eng', 'en-US', 'fre', 'spa', 'en-GB', 'mul', 'grc', 'enm',\n",
       "       'en-CA', 'ger', 'jpn', 'ara', 'nl', 'zho', 'lat', 'por', 'srp',\n",
       "       'ita', 'rus', 'msa', 'glg', 'wel', 'swe', 'nor', 'tur', 'gla',\n",
       "       'ale'], dtype=object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.language_code.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64b438d8-f1ee-419d-8665-2b94a3c3deaf",
   "metadata": {},
   "source": [
    "To feed this into a machine learning model, we want to [one-hot encode](https://en.wikipedia.org/wiki/One-hot) this catagorical column to a set of binary features. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "31e817ea-5b64-4ea6-bd1b-21e8b73d92bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bookID</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>num_pages</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>text_reviews_count</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>...</th>\n",
       "      <th>language_code_nl</th>\n",
       "      <th>language_code_nor</th>\n",
       "      <th>language_code_por</th>\n",
       "      <th>language_code_rus</th>\n",
       "      <th>language_code_spa</th>\n",
       "      <th>language_code_srp</th>\n",
       "      <th>language_code_swe</th>\n",
       "      <th>language_code_tur</th>\n",
       "      <th>language_code_wel</th>\n",
       "      <th>language_code_zho</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Harry Potter and the Half-Blood Prince (Harry ...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.57</td>\n",
       "      <td>0439785960</td>\n",
       "      <td>9780439785969</td>\n",
       "      <td>652</td>\n",
       "      <td>2095690</td>\n",
       "      <td>27591</td>\n",
       "      <td>9/16/2006</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Har...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.49</td>\n",
       "      <td>0439358078</td>\n",
       "      <td>9780439358071</td>\n",
       "      <td>870</td>\n",
       "      <td>2153167</td>\n",
       "      <td>29221</td>\n",
       "      <td>9/1/2004</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Harry Potter and the Chamber of Secrets (Harry...</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>4.42</td>\n",
       "      <td>0439554896</td>\n",
       "      <td>9780439554893</td>\n",
       "      <td>352</td>\n",
       "      <td>6333</td>\n",
       "      <td>244</td>\n",
       "      <td>11/1/2003</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.56</td>\n",
       "      <td>043965548X</td>\n",
       "      <td>9780439655484</td>\n",
       "      <td>435</td>\n",
       "      <td>2339585</td>\n",
       "      <td>36325</td>\n",
       "      <td>5/1/2004</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Harry Potter Boxed Set  Books 1-5 (Harry Potte...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.78</td>\n",
       "      <td>0439682584</td>\n",
       "      <td>9780439682589</td>\n",
       "      <td>2690</td>\n",
       "      <td>41428</td>\n",
       "      <td>164</td>\n",
       "      <td>9/13/2004</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11118</th>\n",
       "      <td>45631</td>\n",
       "      <td>Expelled from Eden: A William T. Vollmann Reader</td>\n",
       "      <td>William T. Vollmann/Larry McCaffery/Michael He...</td>\n",
       "      <td>4.06</td>\n",
       "      <td>1560254416</td>\n",
       "      <td>9781560254416</td>\n",
       "      <td>512</td>\n",
       "      <td>156</td>\n",
       "      <td>20</td>\n",
       "      <td>12/21/2004</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11119</th>\n",
       "      <td>45633</td>\n",
       "      <td>You Bright and Risen Angels</td>\n",
       "      <td>William T. Vollmann</td>\n",
       "      <td>4.08</td>\n",
       "      <td>0140110879</td>\n",
       "      <td>9780140110876</td>\n",
       "      <td>635</td>\n",
       "      <td>783</td>\n",
       "      <td>56</td>\n",
       "      <td>12/1/1988</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11120</th>\n",
       "      <td>45634</td>\n",
       "      <td>The Ice-Shirt (Seven Dreams #1)</td>\n",
       "      <td>William T. Vollmann</td>\n",
       "      <td>3.96</td>\n",
       "      <td>0140131965</td>\n",
       "      <td>9780140131963</td>\n",
       "      <td>415</td>\n",
       "      <td>820</td>\n",
       "      <td>95</td>\n",
       "      <td>8/1/1993</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11121</th>\n",
       "      <td>45639</td>\n",
       "      <td>Poor People</td>\n",
       "      <td>William T. Vollmann</td>\n",
       "      <td>3.72</td>\n",
       "      <td>0060878827</td>\n",
       "      <td>9780060878825</td>\n",
       "      <td>434</td>\n",
       "      <td>769</td>\n",
       "      <td>139</td>\n",
       "      <td>2/27/2007</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11122</th>\n",
       "      <td>45641</td>\n",
       "      <td>Las aventuras de Tom Sawyer</td>\n",
       "      <td>Mark Twain</td>\n",
       "      <td>3.91</td>\n",
       "      <td>8497646983</td>\n",
       "      <td>9788497646987</td>\n",
       "      <td>272</td>\n",
       "      <td>113</td>\n",
       "      <td>12</td>\n",
       "      <td>5/28/2006</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11123 rows x 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       bookID                                              title  \\\n",
       "0           1  Harry Potter and the Half-Blood Prince (Harry ...   \n",
       "1           2  Harry Potter and the Order of the Phoenix (Har...   \n",
       "2           4  Harry Potter and the Chamber of Secrets (Harry...   \n",
       "3           5  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "4           8  Harry Potter Boxed Set  Books 1-5 (Harry Potte...   \n",
       "...       ...                                                ...   \n",
       "11118   45631   Expelled from Eden: A William T. Vollmann Reader   \n",
       "11119   45633                        You Bright and Risen Angels   \n",
       "11120   45634                    The Ice-Shirt (Seven Dreams #1)   \n",
       "11121   45639                                        Poor People   \n",
       "11122   45641                        Las aventuras de Tom Sawyer   \n",
       "\n",
       "                                                 authors  average_rating  \\\n",
       "0                             J.K. Rowling/Mary GrandPré            4.57   \n",
       "1                             J.K. Rowling/Mary GrandPré            4.49   \n",
       "2                                           J.K. Rowling            4.42   \n",
       "3                             J.K. Rowling/Mary GrandPré            4.56   \n",
       "4                             J.K. Rowling/Mary GrandPré            4.78   \n",
       "...                                                  ...             ...   \n",
       "11118  William T. Vollmann/Larry McCaffery/Michael He...            4.06   \n",
       "11119                                William T. Vollmann            4.08   \n",
       "11120                                William T. Vollmann            3.96   \n",
       "11121                                William T. Vollmann            3.72   \n",
       "11122                                         Mark Twain            3.91   \n",
       "\n",
       "             isbn         isbn13  num_pages  ratings_count  \\\n",
       "0      0439785960  9780439785969        652        2095690   \n",
       "1      0439358078  9780439358071        870        2153167   \n",
       "2      0439554896  9780439554893        352           6333   \n",
       "3      043965548X  9780439655484        435        2339585   \n",
       "4      0439682584  9780439682589       2690          41428   \n",
       "...           ...            ...        ...            ...   \n",
       "11118  1560254416  9781560254416        512            156   \n",
       "11119  0140110879  9780140110876        635            783   \n",
       "11120  0140131965  9780140131963        415            820   \n",
       "11121  0060878827  9780060878825        434            769   \n",
       "11122  8497646983  9788497646987        272            113   \n",
       "\n",
       "       text_reviews_count publication_date  ... language_code_nl  \\\n",
       "0                   27591        9/16/2006  ...                0   \n",
       "1                   29221         9/1/2004  ...                0   \n",
       "2                     244        11/1/2003  ...                0   \n",
       "3                   36325         5/1/2004  ...                0   \n",
       "4                     164        9/13/2004  ...                0   \n",
       "...                   ...              ...  ...              ...   \n",
       "11118                  20       12/21/2004  ...                0   \n",
       "11119                  56        12/1/1988  ...                0   \n",
       "11120                  95         8/1/1993  ...                0   \n",
       "11121                 139        2/27/2007  ...                0   \n",
       "11122                  12        5/28/2006  ...                0   \n",
       "\n",
       "       language_code_nor  language_code_por  language_code_rus  \\\n",
       "0                      0                  0                  0   \n",
       "1                      0                  0                  0   \n",
       "2                      0                  0                  0   \n",
       "3                      0                  0                  0   \n",
       "4                      0                  0                  0   \n",
       "...                  ...                ...                ...   \n",
       "11118                  0                  0                  0   \n",
       "11119                  0                  0                  0   \n",
       "11120                  0                  0                  0   \n",
       "11121                  0                  0                  0   \n",
       "11122                  0                  0                  0   \n",
       "\n",
       "       language_code_spa  language_code_srp  language_code_swe  \\\n",
       "0                      0                  0                  0   \n",
       "1                      0                  0                  0   \n",
       "2                      0                  0                  0   \n",
       "3                      0                  0                  0   \n",
       "4                      0                  0                  0   \n",
       "...                  ...                ...                ...   \n",
       "11118                  0                  0                  0   \n",
       "11119                  0                  0                  0   \n",
       "11120                  0                  0                  0   \n",
       "11121                  0                  0                  0   \n",
       "11122                  1                  0                  0   \n",
       "\n",
       "       language_code_tur  language_code_wel  language_code_zho  \n",
       "0                      0                  0                  0  \n",
       "1                      0                  0                  0  \n",
       "2                      0                  0                  0  \n",
       "3                      0                  0                  0  \n",
       "4                      0                  0                  0  \n",
       "...                  ...                ...                ...  \n",
       "11118                  0                  0                  0  \n",
       "11119                  0                  0                  0  \n",
       "11120                  0                  0                  0  \n",
       "11121                  0                  0                  0  \n",
       "11122                  0                  0                  0  \n",
       "\n",
       "[11123 rows x 38 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "encoded_df = pd.get_dummies(df, columns=\"language_code\")\n",
    "encoded_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aeb61a1d-7453-4996-bba1-d503027f2b98",
   "metadata": {},
   "source": [
    "We select out only the columns with names matching \"language\". This leaves us with all the converted binary columns, which is often referred to as the indicator matrix. This can be an input to a machine learning model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5acd2087-4127-47b6-8d8b-c3182c5700ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>language_code_ale</th>\n",
       "      <th>language_code_ara</th>\n",
       "      <th>language_code_en-CA</th>\n",
       "      <th>language_code_en-GB</th>\n",
       "      <th>language_code_en-US</th>\n",
       "      <th>language_code_eng</th>\n",
       "      <th>language_code_enm</th>\n",
       "      <th>language_code_fre</th>\n",
       "      <th>language_code_ger</th>\n",
       "      <th>language_code_gla</th>\n",
       "      <th>...</th>\n",
       "      <th>language_code_nl</th>\n",
       "      <th>language_code_nor</th>\n",
       "      <th>language_code_por</th>\n",
       "      <th>language_code_rus</th>\n",
       "      <th>language_code_spa</th>\n",
       "      <th>language_code_srp</th>\n",
       "      <th>language_code_swe</th>\n",
       "      <th>language_code_tur</th>\n",
       "      <th>language_code_wel</th>\n",
       "      <th>language_code_zho</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11118</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11119</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11120</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11121</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11122</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11123 rows x 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       language_code_ale  language_code_ara  language_code_en-CA  \\\n",
       "0                      0                  0                    0   \n",
       "1                      0                  0                    0   \n",
       "2                      0                  0                    0   \n",
       "3                      0                  0                    0   \n",
       "4                      0                  0                    0   \n",
       "...                  ...                ...                  ...   \n",
       "11118                  0                  0                    0   \n",
       "11119                  0                  0                    0   \n",
       "11120                  0                  0                    0   \n",
       "11121                  0                  0                    0   \n",
       "11122                  0                  0                    0   \n",
       "\n",
       "       language_code_en-GB  language_code_en-US  language_code_eng  \\\n",
       "0                        0                    0                  1   \n",
       "1                        0                    0                  1   \n",
       "2                        0                    0                  1   \n",
       "3                        0                    0                  1   \n",
       "4                        0                    0                  1   \n",
       "...                    ...                  ...                ...   \n",
       "11118                    0                    0                  1   \n",
       "11119                    0                    0                  1   \n",
       "11120                    0                    0                  1   \n",
       "11121                    0                    0                  1   \n",
       "11122                    0                    0                  0   \n",
       "\n",
       "       language_code_enm  language_code_fre  language_code_ger  \\\n",
       "0                      0                  0                  0   \n",
       "1                      0                  0                  0   \n",
       "2                      0                  0                  0   \n",
       "3                      0                  0                  0   \n",
       "4                      0                  0                  0   \n",
       "...                  ...                ...                ...   \n",
       "11118                  0                  0                  0   \n",
       "11119                  0                  0                  0   \n",
       "11120                  0                  0                  0   \n",
       "11121                  0                  0                  0   \n",
       "11122                  0                  0                  0   \n",
       "\n",
       "       language_code_gla  ...  language_code_nl  language_code_nor  \\\n",
       "0                      0  ...                 0                  0   \n",
       "1                      0  ...                 0                  0   \n",
       "2                      0  ...                 0                  0   \n",
       "3                      0  ...                 0                  0   \n",
       "4                      0  ...                 0                  0   \n",
       "...                  ...  ...               ...                ...   \n",
       "11118                  0  ...                 0                  0   \n",
       "11119                  0  ...                 0                  0   \n",
       "11120                  0  ...                 0                  0   \n",
       "11121                  0  ...                 0                  0   \n",
       "11122                  0  ...                 0                  0   \n",
       "\n",
       "       language_code_por  language_code_rus  language_code_spa  \\\n",
       "0                      0                  0                  0   \n",
       "1                      0                  0                  0   \n",
       "2                      0                  0                  0   \n",
       "3                      0                  0                  0   \n",
       "4                      0                  0                  0   \n",
       "...                  ...                ...                ...   \n",
       "11118                  0                  0                  0   \n",
       "11119                  0                  0                  0   \n",
       "11120                  0                  0                  0   \n",
       "11121                  0                  0                  0   \n",
       "11122                  0                  0                  1   \n",
       "\n",
       "       language_code_srp  language_code_swe  language_code_tur  \\\n",
       "0                      0                  0                  0   \n",
       "1                      0                  0                  0   \n",
       "2                      0                  0                  0   \n",
       "3                      0                  0                  0   \n",
       "4                      0                  0                  0   \n",
       "...                  ...                ...                ...   \n",
       "11118                  0                  0                  0   \n",
       "11119                  0                  0                  0   \n",
       "11120                  0                  0                  0   \n",
       "11121                  0                  0                  0   \n",
       "11122                  0                  0                  0   \n",
       "\n",
       "       language_code_wel  language_code_zho  \n",
       "0                      0                  0  \n",
       "1                      0                  0  \n",
       "2                      0                  0  \n",
       "3                      0                  0  \n",
       "4                      0                  0  \n",
       "...                  ...                ...  \n",
       "11118                  0                  0  \n",
       "11119                  0                  0  \n",
       "11120                  0                  0  \n",
       "11121                  0                  0  \n",
       "11122                  0                  0  \n",
       "\n",
       "[11123 rows x 27 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "indicator_matrix= encoded_df.filter(regex=\"language\")\n",
    "indicator_matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "163c9d50-b9a6-47c2-83aa-bcc2337c412c",
   "metadata": {},
   "outputs": [],
   "source": [
    "duckdb_con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b1d6204-cb95-479b-8979-72a215d49fc2",
   "metadata": {},
   "source": [
    "### Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "468ef8db-786d-4e92-89b8-0034dcd6cb2c",
   "metadata": {},
   "source": [
    "In this tutorial, we saw how you can get started in running common data science operations in pandas directly on your `PONDER_BOOK` table in DuckDB.\n",
    "\n",
    "That means that every single operation that you performed in this tutorial is being executed directly in your database! The only data that is being pulled out of your database is the few lines of results that is printed in the notebook!\n",
    "\n",
    "In our [next tutorial](https://github.com/ponder-org/ponder-notebooks/blob/main/duckdb/tutorial/02-primer-to-ponder.ipynb), we will share more details on how Ponder works and how you can leverage Ponder to accelerate your data science workflow!\n"
   ]
  }
 ],
 "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
}