2.7 Shopee - EDA and Extract parquets.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": [
        "t8T0-0-06Vd7",
        "ygUI9E94rmid",
        "tSxCJM1VoIXZ",
        "THVQk773F5gx"
      ]
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "### Mount Colab to Google Drive"
      ],
      "metadata": {
        "id": "kdL1C6n1jlM0"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LRjihK5Ke0kX",
        "outputId": "0b24c4e8-79b9-4505-daa1-83effbfd5fcf"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
          ]
        }
      ],
      "source": [
        "from google.colab import drive\n",
        "from pathlib import Path\n",
        "drive.mount('/content/drive')"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#@markdown To access data saved on Shared Folder, add them to \"My drive\" as shortcut first\n",
        "root_path=\"/content/drive/My Drive/\" #@param {type:\"string\"}\n",
        "_root = Path(root_path)\n",
        "#!ls \"$root_path\""
      ],
      "metadata": {
        "id": "JqCp10zLhdbk",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Import librabries"
      ],
      "metadata": {
        "id": "t8T0-0-06Vd7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "pd.set_option('display.max_columns', None)\n",
        "import numpy as np\n",
        "import sqlite3 as lite"
      ],
      "metadata": {
        "id": "V4fZBnq96bAh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import io\n",
        "import pyarrow as pa\n",
        "import pyarrow.compute as pc\n",
        "import pyarrow.parquet as pq"
      ],
      "metadata": {
        "id": "i50x9D2ltNmp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Create/Connect to a sqlite3 database"
      ],
      "metadata": {
        "id": "ygUI9E94rmid"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "db_path = \"Main.db\" #@param {type:\"string\"}"
      ],
      "metadata": {
        "id": "dgjWQPNJr2JH",
        "cellView": "form"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#@markdown Connect to sqlite3 database (which reside on Google Drive)\n",
        "# Create parent path to the DB first if not exist\n",
        "_db_path = _root.joinpath(db_path)\n",
        "Path(_db_path).parent.mkdir(parents=True, exist_ok=True)\n",
        "\n",
        "try:\n",
        "    conn = lite.connect(_db_path)\n",
        "    print(f\"sqlite3 {lite.version} has connected to database successfully\")\n",
        "except lite.Error as e:\n",
        "    print(\"Error: \", e)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "cellView": "form",
        "id": "7P549JEFpPpS",
        "outputId": "30e22d9d-1d68-4852-f01e-6b192263064a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "sqlite3 2.6.0 has connected to database successfully\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Helper Function"
      ],
      "metadata": {
        "id": "tSxCJM1VoIXZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def ArrowSchemaToPyDict(dt):\n",
        "  # If this data type is schema or struct\n",
        "  # then iterate each field inside\n",
        "  if isinstance(dt, (pa.lib.Schema, pa.lib.StructType)):\n",
        "    schema = {}\n",
        "    for field in dt:\n",
        "      schema[field.name] = ArrowSchemaToPyDict(field.type)\n",
        "    return schema\n",
        "\n",
        "  # If this data type is list\n",
        "  # then wrap them in a list of struct\n",
        "  elif isinstance(dt, pa.lib.ListType):\n",
        "    return [ArrowSchemaToPyDict(dt.value_field.type)]\n",
        "\n",
        "  # If this data type is arrow defined\n",
        "  # then return string value\n",
        "  elif isinstance(dt, pa.lib.DataType):\n",
        "    return str(dt)"
      ],
      "metadata": {
        "id": "6ce0fea7oK8X"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def PyDictToArrowSchema(dt, level=0):\n",
        "  if level == 0:\n",
        "    return pa.schema([\n",
        "        (field, PyDictToArrowSchema(dt[field], level=level+1)) for field in dt.keys()\n",
        "    ])\n",
        "  \n",
        "  if isinstance(dt, dict):\n",
        "    return pa.struct([\n",
        "        (field, PyDictToArrowSchema(dt[field], level=level+1)) for field in dt.keys()\n",
        "    ])\n",
        "  elif isinstance(dt, list):\n",
        "    return pa.list_(PyDictToArrowSchema(dt[0], level=level+1))\n",
        "  elif isinstance(dt, str):\n",
        "    map = {\n",
        "        'string': pa.string(),\n",
        "        'int64': pa.int64(),\n",
        "        'bool': pa.bool_(),\n",
        "        'null': pa.null(),\n",
        "        'double': pa.float64()\n",
        "    }\n",
        "    return map[str(dt)]"
      ],
      "metadata": {
        "id": "HT_88ny6ZOLo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def ArrowSchemaToDefinition(dt):\n",
        "  # If this data type is schema or struct\n",
        "  # then iterate each field inside\n",
        "  if isinstance(dt, pa.lib.Schema):\n",
        "    def_str = f\"\"\"pa.schema([\n",
        "      {','.join([\n",
        "          'pa.field(\"'+field.name+'\", '+ArrowSchemaToDefinition(field.type)+')' \n",
        "          for field in dt\n",
        "      ])}\n",
        "    ])\"\"\"\n",
        "  elif isinstance(dt, pa.lib.StructType):\n",
        "    def_str = f\"\"\"pa.struct([\n",
        "      {','.join([\n",
        "          'pa.field(\"'+field.name+'\", '+ArrowSchemaToDefinition(field.type)+')' \n",
        "          for field in dt\n",
        "      ])}\n",
        "    ])\"\"\"\n",
        "\n",
        "  # If this data type is list\n",
        "  # then wrap them in a list of struct\n",
        "  elif isinstance(dt, pa.lib.ListType):\n",
        "    def_str = f\"\"\"pa.list_({ArrowSchemaToDefinition(dt.value_field.type)})\"\"\"\n",
        "    \n",
        "  # If this data type is arrow defined\n",
        "  # then return string value\n",
        "  elif isinstance(dt, pa.lib.DataType):\n",
        "    map = {\n",
        "        'string': 'pa.string()',\n",
        "        'int64': 'pa.int64()',\n",
        "        'bool': 'pa.bool_()',\n",
        "        'null': 'pa.null()',\n",
        "        'double': 'pa.float64()'\n",
        "    }\n",
        "    def_str = map[str(dt)]\n",
        "\n",
        "  return def_str"
      ],
      "metadata": {
        "id": "BEH8BI3J0sWO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Stolen from https://stackoverflow.com/questions/71035754/pyarrow-drop-a-column-in-a-nested-structure/71039389#71039389?newreg=0e5baf2fd7184da8adad65c8e1789db3\n",
        "# and improve\n",
        "def ArrowDropNull(array):\n",
        "    # Catch table first\n",
        "    if isinstance(array, pa.Table):\n",
        "        names = []\n",
        "        arrays = []\n",
        "        for field in array.schema:\n",
        "          data = ArrowDropNull(array[field.name])\n",
        "          if data is not None:\n",
        "            names.append(field.name)\n",
        "            arrays.append(data)\n",
        "        return pa.Table.from_arrays(arrays, names)\n",
        "\n",
        "    # Catch null type second\n",
        "    if pa.types.is_null(array.type): return None\n",
        "\n",
        "    # Catch other types later\n",
        "    if pa.types.is_struct(array.type):\n",
        "        # Bắt buộc phải gom chunked array -> StructArray\n",
        "        if isinstance(array, pa.ChunkedArray):\n",
        "            array = array.combine_chunks()\n",
        "        names = []\n",
        "        arrays = []\n",
        "        for index, field in enumerate(array.type):\n",
        "          data = ArrowDropNull(array.field(index))\n",
        "          if data is not None:\n",
        "            names.append(field.name)\n",
        "            arrays.append(data)        \n",
        "        if len(arrays) == 0: return None  \n",
        "        else: return pa.StructArray.from_arrays(arrays, names)\n",
        "\n",
        "    elif pa.types.is_list(array.type):\n",
        "        if isinstance(array, pa.ChunkedArray):\n",
        "            array = array.combine_chunks()\n",
        "        offset = array.offsets\n",
        "        value = ArrowDropNull(array.values)\n",
        "        if value is None: return None\n",
        "        else: return pa.ListArray.from_arrays(offset, value)\n",
        "    else:\n",
        "        return array"
      ],
      "metadata": {
        "id": "2uEto8ymhla6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def ArrowTableExplode(df, col):\n",
        "  \"\"\"\n",
        "  Explode a column in Table, replace the original column and return a new table\n",
        "  \"\"\"\n",
        "  exploded_arr = df.column(col).combine_chunks()\n",
        "  idxs = exploded_arr.value_parent_indices()\n",
        "  exploded_arr = exploded_arr.flatten()\n",
        "\n",
        "  col_idx = df.column_names.index(col)\n",
        "  df = df.drop([col])\n",
        "  return df\\\n",
        "    .take(idxs)\\\n",
        "    .add_column(col_idx, col, exploded_arr)"
      ],
      "metadata": {
        "id": "zidGjVAwxZs1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Main"
      ],
      "metadata": {
        "id": "anf7ecYTMFr6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "pd.read_sql(\"SELECT name FROM sqlite_master WHERE type='table';\", conn)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 426
        },
        "id": "dRx7NfblxtMP",
        "outputId": "9690f1eb-4c12-4e8f-ba02-016ac25b5ff8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                        name\n",
              "0           DIM_ACTIVE_HOURS\n",
              "1        DIM_SHOPEE_CATEGORY\n",
              "2         DIM_SHOPEE_CUISINE\n",
              "3   DIM_SHOPEE_ROOT_CATEGORY\n",
              "4          DIM_FOODY_CUISINE\n",
              "5         DIM_FOODY_CATEGORY\n",
              "6         DIM_FOODY_AUDIENCE\n",
              "7              RESTAURANT_bk\n",
              "8           DIM_SHOPEE_BRAND\n",
              "9                 RESTAURANT\n",
              "10         DIM_SHOPEE_DISHES\n",
              "11       DIM_SHOPEE_DISHES_t"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-19bf915e-8f95-4223-b68c-49bd74cfa360\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>DIM_ACTIVE_HOURS</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>DIM_SHOPEE_CATEGORY</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>DIM_SHOPEE_CUISINE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>DIM_SHOPEE_ROOT_CATEGORY</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>DIM_FOODY_CUISINE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>DIM_FOODY_CATEGORY</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>DIM_FOODY_AUDIENCE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>RESTAURANT_bk</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>DIM_SHOPEE_BRAND</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>RESTAURANT</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>DIM_SHOPEE_DISHES</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>DIM_SHOPEE_DISHES_t</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-19bf915e-8f95-4223-b68c-49bd74cfa360')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-19bf915e-8f95-4223-b68c-49bd74cfa360 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-19bf915e-8f95-4223-b68c-49bd74cfa360');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Shopee"
      ],
      "metadata": {
        "id": "givtRl7S_vFc"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Extract image information to database"
      ],
      "metadata": {
        "id": "H1UNMq5gBvxv"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dishes = pq.read_table(\n",
        "    _root.joinpath(\"Data/2 - cleaned data/Foundation/2022.11.26.restaurant_dishes.parquet\"), \n",
        "    memory_map=True, \n",
        "    columns=['dish_id', 'pictures'])"
      ],
      "metadata": {
        "id": "19VPcx2oB0Za"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "db_dishes = pd.read_sql(\"SELECT * FROM DIM_SHOPEE_DISHES\", conn)"
      ],
      "metadata": {
        "id": "ZRoWjazyC1bg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dishes = ArrowTableExplode(fnd_dishes, 'pictures').flatten()"
      ],
      "metadata": {
        "id": "qllBAWaoCIz9"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish_pictures = fnd_dishes\\\n",
        "  .sort_by([\n",
        "      (\"pictures.width\", \"descending\"), \n",
        "      (\"pictures.height\", \"descending\")\n",
        "    ])\\\n",
        "  .group_by(\"dish_id\")\\\n",
        "  .aggregate([\n",
        "      (\"pictures.url\", \"one\")\n",
        "    ]).to_pandas()"
      ],
      "metadata": {
        "id": "dHQ9YMVbDOwH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish_pictures.columns"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qpVVDuTtH-06",
        "outputId": "dbf21162-19fd-4832-c79a-ec3f70cc92c6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['pictures.url_one', 'dish_id'], dtype='object')"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish_pictures.columns = ['url', 'dish_id']"
      ],
      "metadata": {
        "id": "cKQ83ev8H5p6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "t = db_dishes.merge(fnd_dish_pictures, how='left', on=\"dish_id\")"
      ],
      "metadata": {
        "id": "XtEir5GVE_ly"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "t.to_sql('DIM_SHOPEE_DISHES',conn, if_exists='replace',index=False)"
      ],
      "metadata": {
        "id": "rr0jamLrFQHd"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "pd.read_sql(\"SELECT * FROM DIM_SHOPEE_DISHES LIMIT 10\", conn)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 548
        },
        "id": "GbJnXBbmGgiU",
        "outputId": "caf86449-7973-4d21-86bc-c687539b6678"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   catalog_id  dish_total_order catalog_name  catalog_rank  \\\n",
              "0     2635001             160.0      Món Hot         -11.0   \n",
              "1     2635001             136.0      Món Hot         -11.0   \n",
              "2     2635001             126.0      Món Hot         -11.0   \n",
              "3     2635001               4.0      Món Hot         -11.0   \n",
              "4     2635001               4.0      Món Hot         -11.0   \n",
              "5     2635001               2.0      Món Hot         -11.0   \n",
              "6     2635001               1.0      Món Hot         -11.0   \n",
              "7     2635001               1.0      Món Hot         -11.0   \n",
              "8     2635001               0.0      Món Hot         -11.0   \n",
              "9     2360173              27.0  NOWFOOD DAY         -10.0   \n",
              "\n",
              "  catalog_partner_catalog_id catalog_description  dish_restaurant_id  \\\n",
              "0                       None                None           1114911.0   \n",
              "1                       None                None           1114911.0   \n",
              "2                       None                None           1114911.0   \n",
              "3                       None                None           1114911.0   \n",
              "4                       None                None           1114911.0   \n",
              "5                       None                None           1114911.0   \n",
              "6                       None                None           1114911.0   \n",
              "7                       None                None           1114911.0   \n",
              "8                       None                None           1114911.0   \n",
              "9                       None                None           1114911.0   \n",
              "\n",
              "       dish_id                            dish_name dish_partner_dish_id  \\\n",
              "0   46050027.0                              Trà Đào                 None   \n",
              "1   28885958.0                    Trà Sữa Nọng Xanh                 None   \n",
              "2   46050017.0                              Trà Vải                 None   \n",
              "3  121626885.0    Cà Phê Sữa Tươi Hạnh Nhân Phin Di                 None   \n",
              "4  121637344.0      Sữa Tươi Trân Châu Đường Đen HQ                 None   \n",
              "5  121638144.0                      Cà Phê Sữa Tươi                 None   \n",
              "6  118070637.0                            Combo 110                 None   \n",
              "7  121636221.0          Ca Cao Dằm Cốt Dừa Tuổi Thơ                 None   \n",
              "8  121627002.0            Cà Phê Sữa Tươi Sương Sáo                 None   \n",
              "9   31028098.0  Combo: 2 Ly Trà Sữa Lài Thạch Trứng                 None   \n",
              "\n",
              "   dish_listing_status                                   dish_description  \\\n",
              "0                    1                         Trà đào kèm miếng đào tươi   \n",
              "1                    1  Trà Sữa Nọng Xanh là trà sữa vị thái xanh đã b...   \n",
              "2                    1                      Trà vải tươi mát kèm trái vải   \n",
              "3                    1                                               None   \n",
              "4                    1                                               None   \n",
              "5                    1                                               None   \n",
              "6                    1  1 Trà sữa trân châu phô mai\\n1 Trà sữa cục xì ...   \n",
              "7                    1                                               None   \n",
              "8                    1                                               None   \n",
              "9                    1                                               None   \n",
              "\n",
              "   dish_total_like  dish_rank dish_picture_label  dish_is_hidden  dish_price  \\\n",
              "0              4.0        0.0               None               0     24000.0   \n",
              "1             55.0        0.0               None               0     24000.0   \n",
              "2              3.0        0.0               None               0     24000.0   \n",
              "3              0.0        0.0               None               0     29000.0   \n",
              "4              0.0        0.0               None               0     35000.0   \n",
              "5              0.0        0.0               None               0     25000.0   \n",
              "6              0.0        0.0               None               0    145000.0   \n",
              "7              0.0        0.0               None               0     29000.0   \n",
              "8              0.0        0.0               None               0     29000.0   \n",
              "9              4.0        0.0               None               0     58000.0   \n",
              "\n",
              "   dish_is_group_discount_item    dishes_property_info  \\\n",
              "0                            1  {\"has_alcohol\": false}   \n",
              "1                            1  {\"has_alcohol\": false}   \n",
              "2                            1  {\"has_alcohol\": false}   \n",
              "3                            1  {\"has_alcohol\": false}   \n",
              "4                            1  {\"has_alcohol\": false}   \n",
              "5                            1  {\"has_alcohol\": false}   \n",
              "6                            1  {\"has_alcohol\": false}   \n",
              "7                            1  {\"has_alcohol\": false}   \n",
              "8                            1  {\"has_alcohol\": false}   \n",
              "9                            1  {\"has_alcohol\": false}   \n",
              "\n",
              "                                                 url  \n",
              "0  https://images.foody.vn/res/g112/1114911/s1242...  \n",
              "1  https://images.foody.vn/res/g103/1025330/s1242...  \n",
              "2  https://images.foody.vn/res/g112/1114911/s1242...  \n",
              "3                                               None  \n",
              "4                                               None  \n",
              "5                                               None  \n",
              "6  https://images.foody.vn/res/g103/1025330/s1242...  \n",
              "7                                               None  \n",
              "8                                               None  \n",
              "9                                               None  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-203062df-ebf4-4d19-a0a4-5a16f6e5a0c5\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>catalog_id</th>\n",
              "      <th>dish_total_order</th>\n",
              "      <th>catalog_name</th>\n",
              "      <th>catalog_rank</th>\n",
              "      <th>catalog_partner_catalog_id</th>\n",
              "      <th>catalog_description</th>\n",
              "      <th>dish_restaurant_id</th>\n",
              "      <th>dish_id</th>\n",
              "      <th>dish_name</th>\n",
              "      <th>dish_partner_dish_id</th>\n",
              "      <th>dish_listing_status</th>\n",
              "      <th>dish_description</th>\n",
              "      <th>dish_total_like</th>\n",
              "      <th>dish_rank</th>\n",
              "      <th>dish_picture_label</th>\n",
              "      <th>dish_is_hidden</th>\n",
              "      <th>dish_price</th>\n",
              "      <th>dish_is_group_discount_item</th>\n",
              "      <th>dishes_property_info</th>\n",
              "      <th>url</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2635001</td>\n",
              "      <td>160.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>46050027.0</td>\n",
              "      <td>Trà Đào</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>Trà đào kèm miếng đào tươi</td>\n",
              "      <td>4.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>24000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>https://images.foody.vn/res/g112/1114911/s1242...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2635001</td>\n",
              "      <td>136.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>28885958.0</td>\n",
              "      <td>Trà Sữa Nọng Xanh</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>Trà Sữa Nọng Xanh là trà sữa vị thái xanh đã b...</td>\n",
              "      <td>55.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>24000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>https://images.foody.vn/res/g103/1025330/s1242...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2635001</td>\n",
              "      <td>126.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>46050017.0</td>\n",
              "      <td>Trà Vải</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>Trà vải tươi mát kèm trái vải</td>\n",
              "      <td>3.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>24000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>https://images.foody.vn/res/g112/1114911/s1242...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2635001</td>\n",
              "      <td>4.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>121626885.0</td>\n",
              "      <td>Cà Phê Sữa Tươi Hạnh Nhân Phin Di</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>29000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2635001</td>\n",
              "      <td>4.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>121637344.0</td>\n",
              "      <td>Sữa Tươi Trân Châu Đường Đen HQ</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>35000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>2635001</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>121638144.0</td>\n",
              "      <td>Cà Phê Sữa Tươi</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>25000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2635001</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>118070637.0</td>\n",
              "      <td>Combo 110</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>1 Trà sữa trân châu phô mai\\n1 Trà sữa cục xì ...</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>145000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>https://images.foody.vn/res/g103/1025330/s1242...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>2635001</td>\n",
              "      <td>1.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>121636221.0</td>\n",
              "      <td>Ca Cao Dằm Cốt Dừa Tuổi Thơ</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>29000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>2635001</td>\n",
              "      <td>0.0</td>\n",
              "      <td>Món Hot</td>\n",
              "      <td>-11.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>121627002.0</td>\n",
              "      <td>Cà Phê Sữa Tươi Sương Sáo</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>29000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>2360173</td>\n",
              "      <td>27.0</td>\n",
              "      <td>NOWFOOD DAY</td>\n",
              "      <td>-10.0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>1114911.0</td>\n",
              "      <td>31028098.0</td>\n",
              "      <td>Combo: 2 Ly Trà Sữa Lài Thạch Trứng</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>4.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>58000.0</td>\n",
              "      <td>1</td>\n",
              "      <td>{\"has_alcohol\": false}</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-203062df-ebf4-4d19-a0a4-5a16f6e5a0c5')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-203062df-ebf4-4d19-a0a4-5a16f6e5a0c5 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-203062df-ebf4-4d19-a0a4-5a16f6e5a0c5');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "with conn:\n",
        "    cur = conn.cursor()\n",
        "    cur.execute(\"DROP TABLE DIM_SHOPEE_DISHES_t\")"
      ],
      "metadata": {
        "id": "jfruw72LHAeD"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### FND Shopee dish (2022/11/26 - 06)"
      ],
      "metadata": {
        "id": "THVQk773F5gx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "data_date = \"2022.12.06\"\n",
        "#\"2022.12.03\"\n",
        "#\"2022.11.29\"\n",
        "#\"2022.11.26\""
      ],
      "metadata": {
        "id": "JuZ0zGyYirXJ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "stg_dishes = pq.read_table(_root.joinpath(f\"Data/2 - cleaned data/Staging/{data_date}.dishes.parquet\"), memory_map=True)\n",
        "print(pa.total_allocated_bytes()/1024/1024, \"MB\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rqtXOQnZx0rD",
        "outputId": "d9c4f31c-1737-4d8b-a50a-b620c31c7f17"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "5262.9560546875 MB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "stg_dishes = stg_dishes.flatten()\n",
        "stg_dishes = stg_dishes.drop(['msg','code'])\n",
        "stg_dishes = ArrowTableExplode(stg_dishes, 'data.catalogs').flatten()"
      ],
      "metadata": {
        "id": "5ZctbLKDyHSa"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Extract catalogs"
      ],
      "metadata": {
        "id": "1KD2J1RzG8El"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_catalog = pa.table([\n",
        "    stg_dishes['restaurant_id'],\n",
        "    stg_dishes['data.catalogs.restaurant_id'],\n",
        "    stg_dishes['data.catalogs.id'],\n",
        "    stg_dishes['data.catalogs.name'],\n",
        "    stg_dishes['data.catalogs.partner_catalog_id'],\n",
        "    stg_dishes['data.catalogs.rank'],\n",
        "    stg_dishes['data.catalogs.sort_type'],\n",
        "    stg_dishes['data.catalogs.is_group_discount'],\n",
        "], names=[\n",
        "    \"restaurant_id\",\n",
        "    \"restaurant_id_rep\",\n",
        "    \"catalog_id\",\n",
        "    \"catalog_name\",\n",
        "    \"partner_catalog_id\",\n",
        "    \"rank\",\n",
        "    \"sort_type\",\n",
        "    \"is_group_discount\"\n",
        "])"
      ],
      "metadata": {
        "id": "hNUIMNvO0ixT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Check key không khớp giữa 2 level"
      ],
      "metadata": {
        "id": "6ETEhMzC7WBs"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_catalog.filter(pc.not_equal(fnd_catalog[\"restaurant_id\"], fnd_catalog[\"restaurant_id_rep\"])).to_pandas()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 332
        },
        "id": "sjONaBOr51Bs",
        "outputId": "b4d519a8-bd0e-43b6-ca46-22bb5e0b5c5f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   restaurant_id  restaurant_id_rep  catalog_id               catalog_name  \\\n",
              "0           1361                813           9                       Menu   \n",
              "1           1517                857           1                  Breakfast   \n",
              "2           5523              13358         445                    Sinh tố   \n",
              "3           5523               1350         291                        Gỏi   \n",
              "4           5523               3244         179      Thịt gà (Gà thả vườn)   \n",
              "5           5523               3599         459  Món Khác - Various Dishes   \n",
              "6           5523               5997         502     Cafe - Trà - Chocolate   \n",
              "7           5523               4335         368                         Bò   \n",
              "8           5523                 96         342                 Bia - Beer   \n",
              "\n",
              "  partner_catalog_id  rank  sort_type is_group_discount  \n",
              "0                        1          1              None  \n",
              "1                        1          1              None  \n",
              "2                        0          1              None  \n",
              "3                        2          1              None  \n",
              "4                        2          1              None  \n",
              "5                       10          1              None  \n",
              "6                       12          1              None  \n",
              "7                       27          1              None  \n",
              "8                       29          1              None  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-09e29dcc-91b8-4bf7-9525-8f31453cb7dc\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>restaurant_id</th>\n",
              "      <th>restaurant_id_rep</th>\n",
              "      <th>catalog_id</th>\n",
              "      <th>catalog_name</th>\n",
              "      <th>partner_catalog_id</th>\n",
              "      <th>rank</th>\n",
              "      <th>sort_type</th>\n",
              "      <th>is_group_discount</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1361</td>\n",
              "      <td>813</td>\n",
              "      <td>9</td>\n",
              "      <td>Menu</td>\n",
              "      <td></td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1517</td>\n",
              "      <td>857</td>\n",
              "      <td>1</td>\n",
              "      <td>Breakfast</td>\n",
              "      <td></td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>5523</td>\n",
              "      <td>13358</td>\n",
              "      <td>445</td>\n",
              "      <td>Sinh tố</td>\n",
              "      <td></td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>5523</td>\n",
              "      <td>1350</td>\n",
              "      <td>291</td>\n",
              "      <td>Gỏi</td>\n",
              "      <td></td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5523</td>\n",
              "      <td>3244</td>\n",
              "      <td>179</td>\n",
              "      <td>Thịt gà (Gà thả vườn)</td>\n",
              "      <td></td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>5523</td>\n",
              "      <td>3599</td>\n",
              "      <td>459</td>\n",
              "      <td>Món Khác - Various Dishes</td>\n",
              "      <td></td>\n",
              "      <td>10</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>5523</td>\n",
              "      <td>5997</td>\n",
              "      <td>502</td>\n",
              "      <td>Cafe - Trà - Chocolate</td>\n",
              "      <td></td>\n",
              "      <td>12</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>5523</td>\n",
              "      <td>4335</td>\n",
              "      <td>368</td>\n",
              "      <td>Bò</td>\n",
              "      <td></td>\n",
              "      <td>27</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>5523</td>\n",
              "      <td>96</td>\n",
              "      <td>342</td>\n",
              "      <td>Bia - Beer</td>\n",
              "      <td></td>\n",
              "      <td>29</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-09e29dcc-91b8-4bf7-9525-8f31453cb7dc')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-09e29dcc-91b8-4bf7-9525-8f31453cb7dc button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-09e29dcc-91b8-4bf7-9525-8f31453cb7dc');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 116
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_catalog.filter(pc.field(\"catalog_id\") == 342).to_pandas()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 81
        },
        "id": "cFImosX1-XLV",
        "outputId": "13d909fe-21b1-456b-a5d5-c37352fafba9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   restaurant_id  restaurant_id_rep  catalog_id catalog_name  \\\n",
              "0           5523                 96         342   Bia - Beer   \n",
              "\n",
              "  partner_catalog_id  rank  sort_type is_group_discount  \n",
              "0                       29          1              None  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-8d15cbfa-c477-44eb-9eff-4473c2629ec7\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>restaurant_id</th>\n",
              "      <th>restaurant_id_rep</th>\n",
              "      <th>catalog_id</th>\n",
              "      <th>catalog_name</th>\n",
              "      <th>partner_catalog_id</th>\n",
              "      <th>rank</th>\n",
              "      <th>sort_type</th>\n",
              "      <th>is_group_discount</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>5523</td>\n",
              "      <td>96</td>\n",
              "      <td>342</td>\n",
              "      <td>Bia - Beer</td>\n",
              "      <td></td>\n",
              "      <td>29</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8d15cbfa-c477-44eb-9eff-4473c2629ec7')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-8d15cbfa-c477-44eb-9eff-4473c2629ec7 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-8d15cbfa-c477-44eb-9eff-4473c2629ec7');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 117
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "pd.read_sql_query(\"select * from restaurant where restaurant_id=5523\", conn)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 197
        },
        "id": "rQ2zoG9l_ODw",
        "outputId": "29cfe964-782c-4a1f-83b1-79390be4287a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   restaurant_id restaurant_url                              restaurant_name  \\\n",
              "0           5523   five-oysters  Five Oysters - Authentic Vietnamese Cuisine   \n",
              "\n",
              "  name_en restaurant_short_description                       address_detail  \\\n",
              "0    None                         None  234 - 236 Bùi Viện, P. Phạm Ngũ Lão   \n",
              "\n",
              "  address_district address_city        lat         lon  brand_id  \\\n",
              "0           Quận 1      TP. HCM  10.765773  106.691027        -1   \n",
              "\n",
              "   delivery_avg_price delivery_fees  delivery_has_contract  delivery_id  \\\n",
              "0               58750            []                      1          925   \n",
              "\n",
              "   delivery_merchant_limit_distance  delivery_merchant_time  \\\n",
              "0                                -1                      12   \n",
              "\n",
              "  delivery_payment_methods  delivery_prepare_duration delivery_service_by  \\\n",
              "0             [6, 1, 4, 8]                         12          ShopeeFood   \n",
              "\n",
              "   delivery_setting_limit_distance  delivery_shipping_fee_minimum  \\\n",
              "0                             7000                        16000.0   \n",
              "\n",
              "   delivery_shipping_fee_rate  limit_distance  asap_is_available  has_phone  \\\n",
              "0                        1.02           20000                  0          1   \n",
              "\n",
              "   is_city_alert  is_display_cutlery  is_quality_merchant  contract_type  \\\n",
              "0              0                   1                    0              1   \n",
              "\n",
              "   position_verifie  foody_service_id  parent_category_id  min_order_value  \\\n",
              "0                 1                 1                2387            20000   \n",
              "\n",
              "   price_range_max_price  price_range_min_price  total_like  total_order  \\\n",
              "0               110000.0                35000.0         0.0        249.0   \n",
              "\n",
              "   rating_avg  rating_total_review  promotion_count  \\\n",
              "0         2.3                    3                5   \n",
              "\n",
              "                    area_address          status  min_price  max_price  \\\n",
              "0  Khu vực Phố Tây Phạm Ngũ Lão   Đang hoạt động    35000.0   110000.0   \n",
              "\n",
              "   average_rating  position_rating  price_rating  quality_rating  \\\n",
              "0             8.0              7.9           7.8             8.2   \n",
              "\n",
              "   service_rating  space_rating  checked_in_count  favourite_count  \\\n",
              "0             8.3           8.1                16               85   \n",
              "\n",
              "   review_average_count  review_bad_count  review_count  \\\n",
              "0                   2.0               1.0          39.0   \n",
              "\n",
              "   review_excellent_count  review_good_count  view_count  wanted_count  \\\n",
              "0                    12.0               24.0     12661.0         144.0   \n",
              "\n",
              "   picture_count  \n",
              "0          306.0  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-feb628a5-0db6-4663-ba00-97f241efb763\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>restaurant_id</th>\n",
              "      <th>restaurant_url</th>\n",
              "      <th>restaurant_name</th>\n",
              "      <th>name_en</th>\n",
              "      <th>restaurant_short_description</th>\n",
              "      <th>address_detail</th>\n",
              "      <th>address_district</th>\n",
              "      <th>address_city</th>\n",
              "      <th>lat</th>\n",
              "      <th>lon</th>\n",
              "      <th>brand_id</th>\n",
              "      <th>delivery_avg_price</th>\n",
              "      <th>delivery_fees</th>\n",
              "      <th>delivery_has_contract</th>\n",
              "      <th>delivery_id</th>\n",
              "      <th>delivery_merchant_limit_distance</th>\n",
              "      <th>delivery_merchant_time</th>\n",
              "      <th>delivery_payment_methods</th>\n",
              "      <th>delivery_prepare_duration</th>\n",
              "      <th>delivery_service_by</th>\n",
              "      <th>delivery_setting_limit_distance</th>\n",
              "      <th>delivery_shipping_fee_minimum</th>\n",
              "      <th>delivery_shipping_fee_rate</th>\n",
              "      <th>limit_distance</th>\n",
              "      <th>asap_is_available</th>\n",
              "      <th>has_phone</th>\n",
              "      <th>is_city_alert</th>\n",
              "      <th>is_display_cutlery</th>\n",
              "      <th>is_quality_merchant</th>\n",
              "      <th>contract_type</th>\n",
              "      <th>position_verifie</th>\n",
              "      <th>foody_service_id</th>\n",
              "      <th>parent_category_id</th>\n",
              "      <th>min_order_value</th>\n",
              "      <th>price_range_max_price</th>\n",
              "      <th>price_range_min_price</th>\n",
              "      <th>total_like</th>\n",
              "      <th>total_order</th>\n",
              "      <th>rating_avg</th>\n",
              "      <th>rating_total_review</th>\n",
              "      <th>promotion_count</th>\n",
              "      <th>area_address</th>\n",
              "      <th>status</th>\n",
              "      <th>min_price</th>\n",
              "      <th>max_price</th>\n",
              "      <th>average_rating</th>\n",
              "      <th>position_rating</th>\n",
              "      <th>price_rating</th>\n",
              "      <th>quality_rating</th>\n",
              "      <th>service_rating</th>\n",
              "      <th>space_rating</th>\n",
              "      <th>checked_in_count</th>\n",
              "      <th>favourite_count</th>\n",
              "      <th>review_average_count</th>\n",
              "      <th>review_bad_count</th>\n",
              "      <th>review_count</th>\n",
              "      <th>review_excellent_count</th>\n",
              "      <th>review_good_count</th>\n",
              "      <th>view_count</th>\n",
              "      <th>wanted_count</th>\n",
              "      <th>picture_count</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>5523</td>\n",
              "      <td>five-oysters</td>\n",
              "      <td>Five Oysters - Authentic Vietnamese Cuisine</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>234 - 236 Bùi Viện, P. Phạm Ngũ Lão</td>\n",
              "      <td>Quận 1</td>\n",
              "      <td>TP. HCM</td>\n",
              "      <td>10.765773</td>\n",
              "      <td>106.691027</td>\n",
              "      <td>-1</td>\n",
              "      <td>58750</td>\n",
              "      <td>[]</td>\n",
              "      <td>1</td>\n",
              "      <td>925</td>\n",
              "      <td>-1</td>\n",
              "      <td>12</td>\n",
              "      <td>[6, 1, 4, 8]</td>\n",
              "      <td>12</td>\n",
              "      <td>ShopeeFood</td>\n",
              "      <td>7000</td>\n",
              "      <td>16000.0</td>\n",
              "      <td>1.02</td>\n",
              "      <td>20000</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>2387</td>\n",
              "      <td>20000</td>\n",
              "      <td>110000.0</td>\n",
              "      <td>35000.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>249.0</td>\n",
              "      <td>2.3</td>\n",
              "      <td>3</td>\n",
              "      <td>5</td>\n",
              "      <td>Khu vực Phố Tây Phạm Ngũ Lão</td>\n",
              "      <td>Đang hoạt động</td>\n",
              "      <td>35000.0</td>\n",
              "      <td>110000.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>7.9</td>\n",
              "      <td>7.8</td>\n",
              "      <td>8.2</td>\n",
              "      <td>8.3</td>\n",
              "      <td>8.1</td>\n",
              "      <td>16</td>\n",
              "      <td>85</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>39.0</td>\n",
              "      <td>12.0</td>\n",
              "      <td>24.0</td>\n",
              "      <td>12661.0</td>\n",
              "      <td>144.0</td>\n",
              "      <td>306.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-feb628a5-0db6-4663-ba00-97f241efb763')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-feb628a5-0db6-4663-ba00-97f241efb763 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-feb628a5-0db6-4663-ba00-97f241efb763');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 118
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "pd.read_sql_query(\"select * from restaurant where restaurant_id=96\", conn)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 113
        },
        "id": "NgKUCihB_2Gl",
        "outputId": "0637a709-d539-49e0-a615-39d747cc96c3"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: [restaurant_id, restaurant_url, restaurant_name, name_en, restaurant_short_description, address_detail, address_district, address_city, lat, lon, brand_id, delivery_avg_price, delivery_fees, delivery_has_contract, delivery_id, delivery_merchant_limit_distance, delivery_merchant_time, delivery_payment_methods, delivery_prepare_duration, delivery_service_by, delivery_setting_limit_distance, delivery_shipping_fee_minimum, delivery_shipping_fee_rate, limit_distance, asap_is_available, has_phone, is_city_alert, is_display_cutlery, is_quality_merchant, contract_type, position_verifie, foody_service_id, parent_category_id, min_order_value, price_range_max_price, price_range_min_price, total_like, total_order, rating_avg, rating_total_review, promotion_count, area_address, status, min_price, max_price, average_rating, position_rating, price_rating, quality_rating, service_rating, space_rating, checked_in_count, favourite_count, review_average_count, review_bad_count, review_count, review_excellent_count, review_good_count, view_count, wanted_count, picture_count]\n",
              "Index: []"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-4c14539c-5e07-484e-9bc0-29055bdfc33d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>restaurant_id</th>\n",
              "      <th>restaurant_url</th>\n",
              "      <th>restaurant_name</th>\n",
              "      <th>name_en</th>\n",
              "      <th>restaurant_short_description</th>\n",
              "      <th>address_detail</th>\n",
              "      <th>address_district</th>\n",
              "      <th>address_city</th>\n",
              "      <th>lat</th>\n",
              "      <th>lon</th>\n",
              "      <th>brand_id</th>\n",
              "      <th>delivery_avg_price</th>\n",
              "      <th>delivery_fees</th>\n",
              "      <th>delivery_has_contract</th>\n",
              "      <th>delivery_id</th>\n",
              "      <th>delivery_merchant_limit_distance</th>\n",
              "      <th>delivery_merchant_time</th>\n",
              "      <th>delivery_payment_methods</th>\n",
              "      <th>delivery_prepare_duration</th>\n",
              "      <th>delivery_service_by</th>\n",
              "      <th>delivery_setting_limit_distance</th>\n",
              "      <th>delivery_shipping_fee_minimum</th>\n",
              "      <th>delivery_shipping_fee_rate</th>\n",
              "      <th>limit_distance</th>\n",
              "      <th>asap_is_available</th>\n",
              "      <th>has_phone</th>\n",
              "      <th>is_city_alert</th>\n",
              "      <th>is_display_cutlery</th>\n",
              "      <th>is_quality_merchant</th>\n",
              "      <th>contract_type</th>\n",
              "      <th>position_verifie</th>\n",
              "      <th>foody_service_id</th>\n",
              "      <th>parent_category_id</th>\n",
              "      <th>min_order_value</th>\n",
              "      <th>price_range_max_price</th>\n",
              "      <th>price_range_min_price</th>\n",
              "      <th>total_like</th>\n",
              "      <th>total_order</th>\n",
              "      <th>rating_avg</th>\n",
              "      <th>rating_total_review</th>\n",
              "      <th>promotion_count</th>\n",
              "      <th>area_address</th>\n",
              "      <th>status</th>\n",
              "      <th>min_price</th>\n",
              "      <th>max_price</th>\n",
              "      <th>average_rating</th>\n",
              "      <th>position_rating</th>\n",
              "      <th>price_rating</th>\n",
              "      <th>quality_rating</th>\n",
              "      <th>service_rating</th>\n",
              "      <th>space_rating</th>\n",
              "      <th>checked_in_count</th>\n",
              "      <th>favourite_count</th>\n",
              "      <th>review_average_count</th>\n",
              "      <th>review_bad_count</th>\n",
              "      <th>review_count</th>\n",
              "      <th>review_excellent_count</th>\n",
              "      <th>review_good_count</th>\n",
              "      <th>view_count</th>\n",
              "      <th>wanted_count</th>\n",
              "      <th>picture_count</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4c14539c-5e07-484e-9bc0-29055bdfc33d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-4c14539c-5e07-484e-9bc0-29055bdfc33d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-4c14539c-5e07-484e-9bc0-29055bdfc33d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 119
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Check catalog id duplicate"
      ],
      "metadata": {
        "id": "a7ryQ5Qn7ZWP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_catalog.num_rows - pc.count_distinct(fnd_catalog[\"catalog_id\"]).as_py()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "E5M7nYne7PzF",
        "outputId": "250de03b-064b-4ab0-d700-575db62d6913"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "3283"
            ]
          },
          "metadata": {},
          "execution_count": 120
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_catalog.group_by([\"catalog_id\"]).aggregate([\n",
        "    (\"catalog_id\", \"count\")\n",
        "]).filter(pc.field(\"catalog_id_count\") > 1).to_pandas()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 112
        },
        "id": "q55I7k8j0bub",
        "outputId": "40484451-1571-492b-f7c0-ad0516008ffd"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   catalog_id_count  catalog_id\n",
              "0                 2         179\n",
              "1              3283          -1"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-a0b80783-8c9a-4714-a496-eda70043bc5c\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>catalog_id_count</th>\n",
              "      <th>catalog_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2</td>\n",
              "      <td>179</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>3283</td>\n",
              "      <td>-1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a0b80783-8c9a-4714-a496-eda70043bc5c')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-a0b80783-8c9a-4714-a496-eda70043bc5c button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-a0b80783-8c9a-4714-a496-eda70043bc5c');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 121
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_catalog.filter(pc.field(\"catalog_id\") == 179).to_pandas()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 112
        },
        "id": "hqZlswrtAdTu",
        "outputId": "f15291d4-66d8-478d-9dd3-6c0d701e8ad8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   restaurant_id  restaurant_id_rep  catalog_id           catalog_name  \\\n",
              "0           3244               3244         179  Thịt gà (Gà thả vườn)   \n",
              "1           5523               3244         179  Thịt gà (Gà thả vườn)   \n",
              "\n",
              "  partner_catalog_id  rank  sort_type is_group_discount  \n",
              "0                        2          1              None  \n",
              "1                        2          1              None  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-41f49532-58b8-47a5-92d9-a4270f97e6e8\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>restaurant_id</th>\n",
              "      <th>restaurant_id_rep</th>\n",
              "      <th>catalog_id</th>\n",
              "      <th>catalog_name</th>\n",
              "      <th>partner_catalog_id</th>\n",
              "      <th>rank</th>\n",
              "      <th>sort_type</th>\n",
              "      <th>is_group_discount</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>3244</td>\n",
              "      <td>3244</td>\n",
              "      <td>179</td>\n",
              "      <td>Thịt gà (Gà thả vườn)</td>\n",
              "      <td></td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>5523</td>\n",
              "      <td>3244</td>\n",
              "      <td>179</td>\n",
              "      <td>Thịt gà (Gà thả vườn)</td>\n",
              "      <td></td>\n",
              "      <td>2</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-41f49532-58b8-47a5-92d9-a4270f97e6e8')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-41f49532-58b8-47a5-92d9-a4270f97e6e8 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-41f49532-58b8-47a5-92d9-a4270f97e6e8');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 122
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Check xem nếu trùng id thì các field khác có trùng k"
      ],
      "metadata": {
        "id": "cC2GJ8DcAnZT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "c = fnd_catalog.column_names\n",
        "c"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yewxGWTUBnKE",
        "outputId": "dc5924a5-384b-4649-d20b-5350f9a284b8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['restaurant_id',\n",
              " 'restaurant_id_rep',\n",
              " 'catalog_id',\n",
              " 'catalog_name',\n",
              " 'partner_catalog_id',\n",
              " 'rank',\n",
              " 'sort_type',\n",
              " 'is_group_discount']"
            ]
          },
          "metadata": {},
          "execution_count": 123
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "for x in c:\n",
        "  print(\"Checking \", x, \", number of invalidated catalogs: \",\n",
        "  fnd_catalog.group_by([\"catalog_id\"]).aggregate([\n",
        "    (x, \"count_distinct\")\n",
        "  ]).filter(pc.field(f\"{x}_count_distinct\") > 1).num_rows)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Wt0bD_eGBhxc",
        "outputId": "117bc2c5-28a6-4558-f8ea-36c711ffaf28"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Checking  restaurant_id , number of invalidated catalogs:  2\n",
            "Checking  restaurant_id_rep , number of invalidated catalogs:  0\n",
            "Checking  catalog_id , number of invalidated catalogs:  0\n",
            "Checking  catalog_name , number of invalidated catalogs:  0\n",
            "Checking  partner_catalog_id , number of invalidated catalogs:  0\n",
            "Checking  rank , number of invalidated catalogs:  0\n",
            "Checking  sort_type , number of invalidated catalogs:  0\n",
            "Checking  is_group_discount , number of invalidated catalogs:  0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Drop dòng trùng"
      ],
      "metadata": {
        "id": "XinChZ4TFLjS"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_catalog.group_by(fnd_catalog.column_names).aggregate([(\"catalog_id\",\"count\")]).num_rows - fnd_catalog.num_rows"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fImEP0KdFM1H",
        "outputId": "deca51dd-1d8f-4d38-cc0c-250c3a8ef0cf"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0"
            ]
          },
          "metadata": {},
          "execution_count": 125
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_catalog = fnd_catalog.group_by(fnd_catalog.column_names).aggregate([(\"catalog_id\",\"count\")]).drop([\"catalog_id_count\"])"
      ],
      "metadata": {
        "id": "0y9Gyl27GhhP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Save the table"
      ],
      "metadata": {
        "id": "3qFYgdVAE-sS"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "pq.write_table(fnd_catalog, _root.joinpath(f\"Data/2 - cleaned data/Foundation/{data_date}.restaurant_catalogs.parquet\"))"
      ],
      "metadata": {
        "id": "4bWnglnLFATe"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Extract dish information"
      ],
      "metadata": {
        "id": "SMkahG7RHpcv"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish = pa.table([\n",
        "    stg_dishes['restaurant_id'],\n",
        "    stg_dishes['data.catalogs.id'],\n",
        "    stg_dishes[\"data.catalogs.dishes\"]\n",
        "], names=[\n",
        "    \"restaurant_id\",\n",
        "    \"catalog_id\",\n",
        "    \"dishes\"\n",
        "])"
      ],
      "metadata": {
        "id": "giiYtD4LIGiT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Flatten và rename cột"
      ],
      "metadata": {
        "id": "Nq40dozYL7qg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish = ArrowTableExplode(fnd_dish, \"dishes\").flatten()"
      ],
      "metadata": {
        "id": "Xz2Rp1xZJhHP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "f = lambda s: s.replace(\"dishes.\", \"\") if s != \"dishes.id\" else \"dish_id\"\n",
        "new_c = [f(c) for c in fnd_dish.column_names]\n",
        "if new_c[0] == \"restaurant_id\":\n",
        "  new_c[0] = \"primary_restaurant_id\"\n",
        "else:\n",
        "  raise Exception(\"Schema have changed compared to schema of data collected in the date 2022/11/26, please recheck\")\n",
        "\n",
        "if new_c[1] == \"catalog_id\":\n",
        "  new_c[1] = \"primary_catalog_id\"\n",
        "else:\n",
        "  raise Exception(\"Schema have changed compared to schema of data collected in the date 2022/11/26, please recheck\")\n",
        "\n",
        "new_c"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DDsapY_5J0TG",
        "outputId": "d336517e-8961-4554-ce90-3dc94f0281f8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['primary_restaurant_id',\n",
              " 'primary_catalog_id',\n",
              " 'listing_status',\n",
              " 'partner_dish_id',\n",
              " 'description',\n",
              " 'total_like',\n",
              " 'restaurant_id',\n",
              " 'rank',\n",
              " 'dish_id',\n",
              " 'property_info',\n",
              " 'catalog_id',\n",
              " 'stock_info',\n",
              " 'is_hidden',\n",
              " 'sale_time_info',\n",
              " 'price',\n",
              " 'is_group_discount_item',\n",
              " 'name',\n",
              " 'pictures',\n",
              " 'discount_price',\n",
              " 'is_searchable',\n",
              " 'discount_remaining_quantity',\n",
              " 'limit_type',\n",
              " 'picture_label',\n",
              " 'is_discount_topping',\n",
              " 'discount_item_tag']"
            ]
          },
          "metadata": {},
          "execution_count": 130
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish = fnd_dish.rename_columns(new_c)"
      ],
      "metadata": {
        "id": "T-CIultPK4j4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish = fnd_dish.flatten()"
      ],
      "metadata": {
        "id": "nbyWfZCme1ED"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "f = lambda s: s.replace(\".\", \"_\")\n",
        "new_c = [f(c) for c in fnd_dish.column_names]\n",
        "fnd_dish = fnd_dish.rename_columns(new_c)\n",
        "new_c"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AdTuNYWcfMfa",
        "outputId": "32c69e06-419d-43e9-fddb-ea216463f13d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['primary_restaurant_id',\n",
              " 'primary_catalog_id',\n",
              " 'listing_status',\n",
              " 'partner_dish_id',\n",
              " 'description',\n",
              " 'total_like',\n",
              " 'restaurant_id',\n",
              " 'rank',\n",
              " 'dish_id',\n",
              " 'property_info_has_alcohol',\n",
              " 'catalog_id',\n",
              " 'stock_info_start_time',\n",
              " 'stock_info_is_out_stocked',\n",
              " 'stock_info_end_time',\n",
              " 'is_hidden',\n",
              " 'sale_time_info_loop_sale_days',\n",
              " 'sale_time_info_is_in_sale_time',\n",
              " 'sale_time_info_custom_sale_days',\n",
              " 'price',\n",
              " 'is_group_discount_item',\n",
              " 'name',\n",
              " 'pictures',\n",
              " 'discount_price',\n",
              " 'is_searchable',\n",
              " 'discount_remaining_quantity',\n",
              " 'limit_type',\n",
              " 'picture_label_photos',\n",
              " 'picture_label_label_position',\n",
              " 'is_discount_topping',\n",
              " 'discount_item_tag']"
            ]
          },
          "metadata": {},
          "execution_count": 133
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Xử lý catalog:\n",
        "- Trong hierarchy cũ là catalog tới dish nên bị duplicate dish\n",
        "- Trong phần này cần transform lại mỗi dòng là 1 unique dish của restaurant và danh sách các catalog mà dish này thuộc về"
      ],
      "metadata": {
        "id": "ciGOzr3QNBNF"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Kiểm tra xem cùng dish_id thì có cùng data k (integrity)"
      ],
      "metadata": {
        "id": "F_WqjEDjQ24f"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "for x in fnd_dish.column_names:\n",
        "  try:\n",
        "    print(\"Checking \", x, \", number of invalidated dishes: \",\n",
        "    fnd_dish.group_by([\"dish_id\"]).aggregate([\n",
        "      (x, \"count_distinct\")\n",
        "    ]).filter(pc.field(f\"{x}_count_distinct\") > 1).num_rows)\n",
        "  except pa.ArrowNotImplementedError:\n",
        "    print(\"Fail to check \", x, \", this could be because of this column is of type struct\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "m8wkT04XNl_2",
        "outputId": "8af3367f-df1c-4bed-fdb5-89bc99da56a0"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Checking  primary_restaurant_id , number of invalidated dishes:  0\n",
            "Checking  primary_catalog_id , number of invalidated dishes:  16069\n",
            "Checking  listing_status , number of invalidated dishes:  0\n",
            "Checking  partner_dish_id , number of invalidated dishes:  0\n",
            "Checking  description , number of invalidated dishes:  0\n",
            "Checking  total_like , number of invalidated dishes:  0\n",
            "Checking  restaurant_id , number of invalidated dishes:  0\n",
            "Checking  rank , number of invalidated dishes:  0\n",
            "Checking  dish_id , number of invalidated dishes:  0\n",
            "Checking  property_info_has_alcohol , number of invalidated dishes:  0\n",
            "Checking  catalog_id , number of invalidated dishes:  0\n",
            "Checking  stock_info_start_time , number of invalidated dishes:  0\n",
            "Checking  stock_info_is_out_stocked , number of invalidated dishes:  0\n",
            "Checking  stock_info_end_time , number of invalidated dishes:  0\n",
            "Checking  is_hidden , number of invalidated dishes:  0\n",
            "Fail to check  sale_time_info_loop_sale_days , this could be because of this column is of type struct\n",
            "Checking  sale_time_info_is_in_sale_time , number of invalidated dishes:  0\n",
            "Fail to check  sale_time_info_custom_sale_days , this could be because of this column is of type struct\n",
            "Checking  price , number of invalidated dishes:  0\n",
            "Checking  is_group_discount_item , number of invalidated dishes:  0\n",
            "Checking  name , number of invalidated dishes:  0\n",
            "Fail to check  pictures , this could be because of this column is of type struct\n",
            "Checking  discount_price , number of invalidated dishes:  0\n",
            "Checking  is_searchable , number of invalidated dishes:  0\n",
            "Checking  discount_remaining_quantity , number of invalidated dishes:  0\n",
            "Checking  limit_type , number of invalidated dishes:  0\n",
            "Fail to check  picture_label_photos , this could be because of this column is of type struct\n",
            "Checking  picture_label_label_position , number of invalidated dishes:  0\n",
            "Checking  is_discount_topping , number of invalidated dishes:  0\n",
            "Checking  discount_item_tag , number of invalidated dishes:  0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Kiểm tra xem restaurant_id, catalog_id bên ngoài và bên trong có khớp k"
      ],
      "metadata": {
        "id": "4YlyDALVQ8-h"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish.filter(\n",
        "    pc.field(\"restaurant_id\") != pc.field(\"primary_restaurant_id\")\n",
        ").to_pandas()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 113
        },
        "id": "ImR7bq8PREHA",
        "outputId": "d6fd1598-6d8d-4172-fcc7-733067262fb4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: [primary_restaurant_id, primary_catalog_id, listing_status, partner_dish_id, description, total_like, restaurant_id, rank, dish_id, property_info_has_alcohol, catalog_id, stock_info_start_time, stock_info_is_out_stocked, stock_info_end_time, is_hidden, sale_time_info_loop_sale_days, sale_time_info_is_in_sale_time, sale_time_info_custom_sale_days, price, is_group_discount_item, name, pictures, discount_price, is_searchable, discount_remaining_quantity, limit_type, picture_label_photos, picture_label_label_position, is_discount_topping, discount_item_tag]\n",
              "Index: []"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d69c1b23-c07a-4f00-bf9d-b00a0d7ee62a\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>primary_restaurant_id</th>\n",
              "      <th>primary_catalog_id</th>\n",
              "      <th>listing_status</th>\n",
              "      <th>partner_dish_id</th>\n",
              "      <th>description</th>\n",
              "      <th>total_like</th>\n",
              "      <th>restaurant_id</th>\n",
              "      <th>rank</th>\n",
              "      <th>dish_id</th>\n",
              "      <th>property_info_has_alcohol</th>\n",
              "      <th>catalog_id</th>\n",
              "      <th>stock_info_start_time</th>\n",
              "      <th>stock_info_is_out_stocked</th>\n",
              "      <th>stock_info_end_time</th>\n",
              "      <th>is_hidden</th>\n",
              "      <th>sale_time_info_loop_sale_days</th>\n",
              "      <th>sale_time_info_is_in_sale_time</th>\n",
              "      <th>sale_time_info_custom_sale_days</th>\n",
              "      <th>price</th>\n",
              "      <th>is_group_discount_item</th>\n",
              "      <th>name</th>\n",
              "      <th>pictures</th>\n",
              "      <th>discount_price</th>\n",
              "      <th>is_searchable</th>\n",
              "      <th>discount_remaining_quantity</th>\n",
              "      <th>limit_type</th>\n",
              "      <th>picture_label_photos</th>\n",
              "      <th>picture_label_label_position</th>\n",
              "      <th>is_discount_topping</th>\n",
              "      <th>discount_item_tag</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d69c1b23-c07a-4f00-bf9d-b00a0d7ee62a')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d69c1b23-c07a-4f00-bf9d-b00a0d7ee62a button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d69c1b23-c07a-4f00-bf9d-b00a0d7ee62a');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 135
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish.filter(\n",
        "    pc.field(\"catalog_id\") != pc.field(\"primary_catalog_id\")\n",
        ").to_pandas()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "p8Hby7g4RfuD",
        "outputId": "7c75b550-e976-40f8-fe69-894455afe344"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       primary_restaurant_id  primary_catalog_id  listing_status  \\\n",
              "0                    1056007                  -1            True   \n",
              "1                    1056007                  -1            True   \n",
              "2                    1076537                  -1            True   \n",
              "3                    1080967                  -1            True   \n",
              "4                    1084977                  -1            True   \n",
              "...                      ...                 ...             ...   \n",
              "16064             1000033440                  -1            True   \n",
              "16065                1126084                  -1            True   \n",
              "16066                1126087                  -1            True   \n",
              "16067             1000032932                  -1            True   \n",
              "16068             1000032932                  -1            True   \n",
              "\n",
              "      partner_dish_id                                        description  \\\n",
              "0                                 Topping trân châu. Lượng đường cố định   \n",
              "1                      Khách hàng vui lòng ghi chú lại Tên Vị muốn đặ...   \n",
              "2                                 Topping trân châu. Lượng đường cố định   \n",
              "3                                                                          \n",
              "4                      4 viên sushi cá hồi - tôm thẻ, 6 viên sushi ma...   \n",
              "...               ...                                                ...   \n",
              "16064                                                                      \n",
              "16065                                                                      \n",
              "16066                                                                      \n",
              "16067                                                                      \n",
              "16068                                              Combo xôi mặn + CoCa    \n",
              "\n",
              "       total_like  restaurant_id  rank   dish_id  property_info_has_alcohol  \\\n",
              "0               6        1056007     0  27032187                      False   \n",
              "1               0        1056007     0  23084533                      False   \n",
              "2               1        1076537     0  27032208                      False   \n",
              "3               0        1080967     0  87032839                      False   \n",
              "4               6        1084977     0  16782868                      False   \n",
              "...           ...            ...   ...       ...                        ...   \n",
              "16064           0     1000033440     0  24993127                      False   \n",
              "16065           0        1126084     0  69462744                      False   \n",
              "16066           0        1126087     0  69462747                      False   \n",
              "16067           1     1000032932     0  23802555                      False   \n",
              "16068           0     1000032932     0  25694205                      False   \n",
              "\n",
              "       catalog_id  stock_info_start_time  stock_info_is_out_stocked  \\\n",
              "0         2905616                      0                      False   \n",
              "1         2134639                      0                      False   \n",
              "2         2905637                      0                      False   \n",
              "3         3977684                      0                      False   \n",
              "4         5977918                      0                      False   \n",
              "...           ...                    ...                        ...   \n",
              "16064     2205644                      0                      False   \n",
              "16065     3435674                      0                      False   \n",
              "16066     3435677                      0                      False   \n",
              "16067     2164952                      0                      False   \n",
              "16068     2164964                      0                      False   \n",
              "\n",
              "       stock_info_end_time  is_hidden  \\\n",
              "0                        0      False   \n",
              "1                        0      False   \n",
              "2                        0      False   \n",
              "3                        0      False   \n",
              "4                        0      False   \n",
              "...                    ...        ...   \n",
              "16064                    0      False   \n",
              "16065                    0      False   \n",
              "16066                    0      False   \n",
              "16067                    0      False   \n",
              "16068                    0      False   \n",
              "\n",
              "                           sale_time_info_loop_sale_days  \\\n",
              "0      [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "1      [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "2      [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "3      [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "4      [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "...                                                  ...   \n",
              "16064  [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "16065  [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "16066  [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "16067  [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "16068  [{'time_for_sales': [{'start_time_sec': 0, 'en...   \n",
              "\n",
              "       sale_time_info_is_in_sale_time sale_time_info_custom_sale_days  \\\n",
              "0                                True                            None   \n",
              "1                                True                            None   \n",
              "2                                True                            None   \n",
              "3                                True                            None   \n",
              "4                                True                            None   \n",
              "...                               ...                             ...   \n",
              "16064                            True                            None   \n",
              "16065                            True                            None   \n",
              "16066                            True                            None   \n",
              "16067                            True                            None   \n",
              "16068                            True                            None   \n",
              "\n",
              "          price  is_group_discount_item  \\\n",
              "0       28000.0                    True   \n",
              "1      169000.0                    True   \n",
              "2       28000.0                    True   \n",
              "3      130000.0                    True   \n",
              "4      159000.0                    True   \n",
              "...         ...                     ...   \n",
              "16064   25000.0                    True   \n",
              "16065   25000.0                    True   \n",
              "16066   25000.0                    True   \n",
              "16067   27000.0                    True   \n",
              "16068   32000.0                    True   \n",
              "\n",
              "                                                name  \\\n",
              "0                                  Trà Sữa Trân Châu   \n",
              "1                       Combo 04 chai (gồm đủ 04 vị)   \n",
              "2                                  Trà Sữa Trân Châu   \n",
              "3      2 CƠM BA CHỈ CHIÊN GIÒN MẮM TỎI + 2 TRÀ CHANH   \n",
              "4                                     Combo Sushi C5   \n",
              "...                                              ...   \n",
              "16064                                Bánh tráng trộn   \n",
              "16065                            Sữa Chua Trân Châu    \n",
              "16066                            Sữa Chua Trân Châu    \n",
              "16067                               Bánh mì thịt chả   \n",
              "16068                                  Xôi mặn +coca   \n",
              "\n",
              "                                                pictures  discount_price  \\\n",
              "0      [{'url': 'https://images.foody.vn/res/g98/9786...          7000.0   \n",
              "1      [{'url': 'https://images.foody.vn/res/g98/9786...        112000.0   \n",
              "2      [{'url': 'https://images.foody.vn/res/g98/9786...         12000.0   \n",
              "3      [{'url': 'https://images.foody.vn/res/g109/108...        112000.0   \n",
              "4      [{'url': 'https://images.foody.vn/res/g109/108...        112000.0   \n",
              "...                                                  ...             ...   \n",
              "16064                                               None         12000.0   \n",
              "16065                                               None         12000.0   \n",
              "16066                                               None         12000.0   \n",
              "16067                                               None         25000.0   \n",
              "16068                                               None         30000.0   \n",
              "\n",
              "       is_searchable  discount_remaining_quantity  limit_type  \\\n",
              "0              False                          1.0         2.0   \n",
              "1              False                          1.0         3.0   \n",
              "2              False                          1.0         3.0   \n",
              "3              False                          1.0         3.0   \n",
              "4              False                          1.0         3.0   \n",
              "...              ...                          ...         ...   \n",
              "16064          False                          1.0         3.0   \n",
              "16065          False                          1.0         1.0   \n",
              "16066          False                          1.0         1.0   \n",
              "16067          False                          NaN         NaN   \n",
              "16068          False                          NaN         NaN   \n",
              "\n",
              "      picture_label_photos  picture_label_label_position is_discount_topping  \\\n",
              "0                     None                           NaN                None   \n",
              "1                     None                           NaN                None   \n",
              "2                     None                           NaN                None   \n",
              "3                     None                           NaN                None   \n",
              "4                     None                           NaN                None   \n",
              "...                    ...                           ...                 ...   \n",
              "16064                 None                           NaN                None   \n",
              "16065                 None                           NaN                None   \n",
              "16066                 None                           NaN                None   \n",
              "16067                 None                           NaN                None   \n",
              "16068                 None                           NaN                None   \n",
              "\n",
              "      discount_item_tag  \n",
              "0                  None  \n",
              "1                  None  \n",
              "2                  None  \n",
              "3                  None  \n",
              "4                  None  \n",
              "...                 ...  \n",
              "16064              None  \n",
              "16065              None  \n",
              "16066              None  \n",
              "16067              None  \n",
              "16068              None  \n",
              "\n",
              "[16069 rows x 30 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d4e78566-c2ef-46c4-ba9a-f253b19fa363\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>primary_restaurant_id</th>\n",
              "      <th>primary_catalog_id</th>\n",
              "      <th>listing_status</th>\n",
              "      <th>partner_dish_id</th>\n",
              "      <th>description</th>\n",
              "      <th>total_like</th>\n",
              "      <th>restaurant_id</th>\n",
              "      <th>rank</th>\n",
              "      <th>dish_id</th>\n",
              "      <th>property_info_has_alcohol</th>\n",
              "      <th>catalog_id</th>\n",
              "      <th>stock_info_start_time</th>\n",
              "      <th>stock_info_is_out_stocked</th>\n",
              "      <th>stock_info_end_time</th>\n",
              "      <th>is_hidden</th>\n",
              "      <th>sale_time_info_loop_sale_days</th>\n",
              "      <th>sale_time_info_is_in_sale_time</th>\n",
              "      <th>sale_time_info_custom_sale_days</th>\n",
              "      <th>price</th>\n",
              "      <th>is_group_discount_item</th>\n",
              "      <th>name</th>\n",
              "      <th>pictures</th>\n",
              "      <th>discount_price</th>\n",
              "      <th>is_searchable</th>\n",
              "      <th>discount_remaining_quantity</th>\n",
              "      <th>limit_type</th>\n",
              "      <th>picture_label_photos</th>\n",
              "      <th>picture_label_label_position</th>\n",
              "      <th>is_discount_topping</th>\n",
              "      <th>discount_item_tag</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1056007</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td>Topping trân châu. Lượng đường cố định</td>\n",
              "      <td>6</td>\n",
              "      <td>1056007</td>\n",
              "      <td>0</td>\n",
              "      <td>27032187</td>\n",
              "      <td>False</td>\n",
              "      <td>2905616</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>28000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Trà Sữa Trân Châu</td>\n",
              "      <td>[{'url': 'https://images.foody.vn/res/g98/9786...</td>\n",
              "      <td>7000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>1.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1056007</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td>Khách hàng vui lòng ghi chú lại Tên Vị muốn đặ...</td>\n",
              "      <td>0</td>\n",
              "      <td>1056007</td>\n",
              "      <td>0</td>\n",
              "      <td>23084533</td>\n",
              "      <td>False</td>\n",
              "      <td>2134639</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>169000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Combo 04 chai (gồm đủ 04 vị)</td>\n",
              "      <td>[{'url': 'https://images.foody.vn/res/g98/9786...</td>\n",
              "      <td>112000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>1.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1076537</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td>Topping trân châu. Lượng đường cố định</td>\n",
              "      <td>1</td>\n",
              "      <td>1076537</td>\n",
              "      <td>0</td>\n",
              "      <td>27032208</td>\n",
              "      <td>False</td>\n",
              "      <td>2905637</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>28000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Trà Sữa Trân Châu</td>\n",
              "      <td>[{'url': 'https://images.foody.vn/res/g98/9786...</td>\n",
              "      <td>12000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>1.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1080967</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td></td>\n",
              "      <td>0</td>\n",
              "      <td>1080967</td>\n",
              "      <td>0</td>\n",
              "      <td>87032839</td>\n",
              "      <td>False</td>\n",
              "      <td>3977684</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>130000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>2 CƠM BA CHỈ CHIÊN GIÒN MẮM TỎI + 2 TRÀ CHANH</td>\n",
              "      <td>[{'url': 'https://images.foody.vn/res/g109/108...</td>\n",
              "      <td>112000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>1.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1084977</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td>4 viên sushi cá hồi - tôm thẻ, 6 viên sushi ma...</td>\n",
              "      <td>6</td>\n",
              "      <td>1084977</td>\n",
              "      <td>0</td>\n",
              "      <td>16782868</td>\n",
              "      <td>False</td>\n",
              "      <td>5977918</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>159000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Combo Sushi C5</td>\n",
              "      <td>[{'url': 'https://images.foody.vn/res/g109/108...</td>\n",
              "      <td>112000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>1.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</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",
              "      <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>16064</th>\n",
              "      <td>1000033440</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td></td>\n",
              "      <td>0</td>\n",
              "      <td>1000033440</td>\n",
              "      <td>0</td>\n",
              "      <td>24993127</td>\n",
              "      <td>False</td>\n",
              "      <td>2205644</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>25000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Bánh tráng trộn</td>\n",
              "      <td>None</td>\n",
              "      <td>12000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>1.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16065</th>\n",
              "      <td>1126084</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td></td>\n",
              "      <td>0</td>\n",
              "      <td>1126084</td>\n",
              "      <td>0</td>\n",
              "      <td>69462744</td>\n",
              "      <td>False</td>\n",
              "      <td>3435674</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>25000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Sữa Chua Trân Châu</td>\n",
              "      <td>None</td>\n",
              "      <td>12000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16066</th>\n",
              "      <td>1126087</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td></td>\n",
              "      <td>0</td>\n",
              "      <td>1126087</td>\n",
              "      <td>0</td>\n",
              "      <td>69462747</td>\n",
              "      <td>False</td>\n",
              "      <td>3435677</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>25000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Sữa Chua Trân Châu</td>\n",
              "      <td>None</td>\n",
              "      <td>12000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16067</th>\n",
              "      <td>1000032932</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td></td>\n",
              "      <td>1</td>\n",
              "      <td>1000032932</td>\n",
              "      <td>0</td>\n",
              "      <td>23802555</td>\n",
              "      <td>False</td>\n",
              "      <td>2164952</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>27000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Bánh mì thịt chả</td>\n",
              "      <td>None</td>\n",
              "      <td>25000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16068</th>\n",
              "      <td>1000032932</td>\n",
              "      <td>-1</td>\n",
              "      <td>True</td>\n",
              "      <td></td>\n",
              "      <td>Combo xôi mặn + CoCa</td>\n",
              "      <td>0</td>\n",
              "      <td>1000032932</td>\n",
              "      <td>0</td>\n",
              "      <td>25694205</td>\n",
              "      <td>False</td>\n",
              "      <td>2164964</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'time_for_sales': [{'start_time_sec': 0, 'en...</td>\n",
              "      <td>True</td>\n",
              "      <td>None</td>\n",
              "      <td>32000.0</td>\n",
              "      <td>True</td>\n",
              "      <td>Xôi mặn +coca</td>\n",
              "      <td>None</td>\n",
              "      <td>30000.0</td>\n",
              "      <td>False</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>16069 rows × 30 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d4e78566-c2ef-46c4-ba9a-f253b19fa363')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d4e78566-c2ef-46c4-ba9a-f253b19fa363 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d4e78566-c2ef-46c4-ba9a-f253b19fa363');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 136
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Gom các catalog_id vô thành list, rồi distinct lại phần dish information"
      ],
      "metadata": {
        "id": "jf3UMxreR_cn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Tui bị ngu\n",
        "# t = fnd_dish\\\n",
        "#   .group_by([\"primary_restaurant_id\", \"dish_id\"])\\\n",
        "#   .aggregate([\n",
        "#       (\"primary_catalog_id\", \"count\"),\n",
        "#       (\"catalog_id\", \"count\"),\n",
        "#   ])\\\n",
        "#   .sort_by([\n",
        "#       (\"primary_restaurant_id\", \"ascending\"), \n",
        "#       (\"dish_id\", \"ascending\")\n",
        "#   ])\n",
        "# t = t.set_column(0, \"primary_catalog_id_offset\", pc.cumulative_sum(t[\"primary_catalog_id_count\"], start=-1))\n",
        "# t = t.set_column(1, \"catalog_id_offset\", pc.cumulative_sum(t[\"catalog_id_count\"], start=-1))\n",
        "# t"
      ],
      "metadata": {
        "id": "EpCFXqZHVYT6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Tui bị ngu...\n",
        "# primary_catalog_id_arr = pa.ListArray.from_arrays(\n",
        "#     offsets=t[\"primary_catalog_id_offset\"].combine_chunks(),\n",
        "#     values=fnd_dish.sort_by([\n",
        "#       (\"primary_restaurant_id\", \"ascending\"), \n",
        "#       (\"dish_id\", \"ascending\")\n",
        "#     ]).column(\"primary_catalog_id\").combine_chunks()\n",
        "# )\n",
        "# catalog_id_arr = pa.ListArray.from_arrays(\n",
        "#     offsets=t[\"catalog_id_offset\"].combine_chunks(),\n",
        "#     values=fnd_dish.sort_by([\n",
        "#       (\"primary_restaurant_id\", \"ascending\"), \n",
        "#       (\"dish_id\", \"ascending\")\n",
        "#     ]).column(\"catalog_id\").combine_chunks()\n",
        "# )"
      ],
      "metadata": {
        "id": "3BpMrKqJaKN_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Lấy 1 giá trị cho mỗi dish_id (xử lý cho case cột kiểu struct)"
      ],
      "metadata": {
        "id": "B2YUl1ollctr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "dishes_first_rows = fnd_dish\\\n",
        "  .append_column(\"index\", pa.array(range(fnd_dish.num_rows)))\\\n",
        "  .group_by([\"dish_id\"])\\\n",
        "  .aggregate([(\"index\", \"one\")])"
      ],
      "metadata": {
        "id": "PW4Z_GT9lS1B"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dishes_first_rows"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5dmEIdWVmCtH",
        "outputId": "74f665d9-576c-4802-b600-67dd736f78a2"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyarrow.Table\n",
              "index_one: int64\n",
              "dish_id: int64\n",
              "----\n",
              "index_one: [[0,1,2,3,4,...,1870579,1870580,1870581,1870582,1870583]]\n",
              "dish_id: [[5326376,2408862,5326377,5325374,2449356,...,121625879,91725428,91725429,91725430,91725431]]"
            ]
          },
          "metadata": {},
          "execution_count": 140
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "special_cols = [\"dish_id\",\n",
        "\"sale_time_info_loop_sale_days\",\n",
        "\"sale_time_info_custom_sale_days\",\n",
        "\"pictures\",\n",
        "\"picture_label_photos\" # ommited in data 2022.12.03 (this version doesn't have this column)\n",
        "]\n",
        "special_cols = {\n",
        "    k: fnd_dish[k].take(dishes_first_rows[\"index_one\"]) for k in special_cols\n",
        "}\n",
        "special_cols = pa.Table.from_pydict(special_cols).sort_by(\"dish_id\")"
      ],
      "metadata": {
        "id": "zOjakYt1lks6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "agg = [(x, \"one\") \n",
        " for x in fnd_dish.column_names \n",
        " if x not in (\n",
        "     \"primary_restaurant_id\", \n",
        "     \"dish_id\", \n",
        "     \"primary_catalog_id\", \n",
        "     \"catalog_id\",\n",
        "     \"sale_time_info_loop_sale_days\",\n",
        "     \"sale_time_info_custom_sale_days\",\n",
        "     \"pictures\",\n",
        "     \"picture_label_photos\")]\n",
        "agg = [(\"primary_catalog_id\", \"list\"), (\"catalog_id\", \"list\")] + agg\n",
        "agg"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-POy1YpJdcEQ",
        "outputId": "776aa493-7f17-4184-d82f-45ab8e0e1869"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('primary_catalog_id', 'list'),\n",
              " ('catalog_id', 'list'),\n",
              " ('listing_status', 'one'),\n",
              " ('partner_dish_id', 'one'),\n",
              " ('description', 'one'),\n",
              " ('total_like', 'one'),\n",
              " ('restaurant_id', 'one'),\n",
              " ('rank', 'one'),\n",
              " ('property_info_has_alcohol', 'one'),\n",
              " ('stock_info_start_time', 'one'),\n",
              " ('stock_info_is_out_stocked', 'one'),\n",
              " ('stock_info_end_time', 'one'),\n",
              " ('is_hidden', 'one'),\n",
              " ('sale_time_info_is_in_sale_time', 'one'),\n",
              " ('price', 'one'),\n",
              " ('is_group_discount_item', 'one'),\n",
              " ('name', 'one'),\n",
              " ('discount_price', 'one'),\n",
              " ('is_searchable', 'one'),\n",
              " ('discount_remaining_quantity', 'one'),\n",
              " ('limit_type', 'one'),\n",
              " ('picture_label_label_position', 'one'),\n",
              " ('is_discount_topping', 'one'),\n",
              " ('discount_item_tag', 'one')]"
            ]
          },
          "metadata": {},
          "execution_count": 142
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish = fnd_dish\\\n",
        "  .group_by([\"primary_restaurant_id\", \"dish_id\"])\\\n",
        "  .aggregate(agg).sort_by(\"dish_id\")"
      ],
      "metadata": {
        "id": "gAtL-jpaSdcl"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "for c in special_cols.column_names:\n",
        "  if c != \"dish_id\":\n",
        "    fnd_dish = fnd_dish.append_column(c, special_cols[c])"
      ],
      "metadata": {
        "id": "8mYhLL9VhRXK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "fnd_dish.schema"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8fPK5G09nHmg",
        "outputId": "bfd29ebd-f78f-48d1-edda-9beca978e318"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "primary_catalog_id_list: list<item: int64>\n",
              "  child 0, item: int64\n",
              "catalog_id_list: list<item: int64>\n",
              "  child 0, item: int64\n",
              "listing_status_one: bool\n",
              "partner_dish_id_one: string\n",
              "description_one: string\n",
              "total_like_one: int64\n",
              "restaurant_id_one: int64\n",
              "rank_one: int64\n",
              "property_info_has_alcohol_one: bool\n",
              "stock_info_start_time_one: int64\n",
              "stock_info_is_out_stocked_one: bool\n",
              "stock_info_end_time_one: int64\n",
              "is_hidden_one: bool\n",
              "sale_time_info_is_in_sale_time_one: bool\n",
              "price_one: double\n",
              "is_group_discount_item_one: bool\n",
              "name_one: string\n",
              "discount_price_one: double\n",
              "is_searchable_one: bool\n",
              "discount_remaining_quantity_one: int64\n",
              "limit_type_one: int64\n",
              "picture_label_label_position_one: int64\n",
              "is_discount_topping_one: bool\n",
              "discount_item_tag_one: string\n",
              "primary_restaurant_id: int64\n",
              "dish_id: int64\n",
              "sale_time_info_loop_sale_days: list<element: struct<time_for_sales: list<element: struct<start_time_sec: int64, end_time_sec: int64>>, weekday: int64>>\n",
              "  child 0, element: struct<time_for_sales: list<element: struct<start_time_sec: int64, end_time_sec: int64>>, weekday: int64>\n",
              "      child 0, time_for_sales: list<element: struct<start_time_sec: int64, end_time_sec: int64>>\n",
              "          child 0, element: struct<start_time_sec: int64, end_time_sec: int64>\n",
              "              child 0, start_time_sec: int64\n",
              "              child 1, end_time_sec: int64\n",
              "      child 1, weekday: int64\n",
              "sale_time_info_custom_sale_days: list<element: struct<custom_date: int64, time_for_sales: list<element: struct<start_time_sec: int64, end_time_sec: int64>>>>\n",
              "  child 0, element: struct<custom_date: int64, time_for_sales: list<element: struct<start_time_sec: int64, end_time_sec: int64>>>\n",
              "      child 0, custom_date: int64\n",
              "      child 1, time_for_sales: list<element: struct<start_time_sec: int64, end_time_sec: int64>>\n",
              "          child 0, element: struct<start_time_sec: int64, end_time_sec: int64>\n",
              "              child 0, start_time_sec: int64\n",
              "              child 1, end_time_sec: int64\n",
              "pictures: list<element: struct<url: string, width: int64, height: int64>>\n",
              "  child 0, element: struct<url: string, width: int64, height: int64>\n",
              "      child 0, url: string\n",
              "      child 1, width: int64\n",
              "      child 2, height: int64\n",
              "picture_label_photos: list<element: struct<width: int64, value: string, height: int64>>\n",
              "  child 0, element: struct<width: int64, value: string, height: int64>\n",
              "      child 0, width: int64\n",
              "      child 1, value: string\n",
              "      child 2, height: int64"
            ]
          },
          "metadata": {},
          "execution_count": 145
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "pq.write_table(fnd_dish, _root.joinpath(f\"Data/2 - cleaned data/Foundation/{data_date}.restaurant_dishes.parquet\"))"
      ],
      "metadata": {
        "id": "ZfNAMGYGiNto"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}