notebooks/getting_started/getting_started_bq_dataframes.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "ur8xi4C7S06n"
      },
      "outputs": [],
      "source": [
        "# Copyright 2023 Google LLC\n",
        "#\n",
        "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "#     https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JAPoU8Sm5E6e"
      },
      "source": [
        "# Get started with BigQuery DataFrames\n",
        "\n",
        "<table align=\"left\">\n",
        "\n",
        "  <td>\n",
        "    <a href=\"https://colab.research.google.com/github/googleapis/python-bigquery-dataframes/blob/main/notebooks/getting_started/getting_started_bq_dataframes.ipynb\">\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/colab-logo-32px.png\" alt=\"Colab logo\"> Run in Colab\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a href=\"https://github.com/googleapis/python-bigquery-dataframes/blob/main/notebooks/getting_started/getting_started_bq_dataframes.ipynb\">\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/github-logo-32px.png\" alt=\"GitHub logo\">\n",
        "      View on GitHub\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/googleapis/python-bigquery-dataframes/blob/main/notebooks/getting_started/getting_started_bq_dataframes.ipynb\">\n",
        "      <img src=\"https://lh3.googleusercontent.com/UiNooY4LUgW_oTvpsNhPpQzsstV5W8F7rYgxgGBD85cWJoLmrOzhVs_ksK_vgx40SHs7jCqkTkCk=e14-rj-sc0xffffff-h130-w32\" alt=\"Vertex AI logo\">\n",
        "      Open in Vertex AI Workbench\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a href=\"https://console.cloud.google.com/bigquery/import?url=https://github.com/googleapis/python-bigquery-dataframes/blob/main/notebooks/getting_started/getting_started_bq_dataframes.ipynb\">\n",
        "      <img src=\"https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcTW1gvOovVlbZAIZylUtf5Iu8-693qS1w5NJw&s\" alt=\"BQ logo\" width=\"35\">\n",
        "      Open in BQ Studio\n",
        "    </a>\n",
        "  </td>\n",
        "</table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "24743cf4a1e1"
      },
      "source": [
        "**_NOTE_**: This notebook has been tested in the following environment:\n",
        "\n",
        "* Python version = 3.10"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tvgnzT1CKxrO"
      },
      "source": [
        "## Overview\n",
        "\n",
        "Use this notebook to get started with BigQuery DataFrames, including setup, installation, and basic tutorials.\n",
        "\n",
        "BigQuery DataFrames provides a Pythonic DataFrame and machine learning (ML) API powered by the BigQuery engine.\n",
        "\n",
        "* `bigframes.pandas` provides a pandas-like API for analytics.\n",
        "* `bigframes.ml` provides a scikit-learn-like API for ML.\n",
        "\n",
        "Learn more about [BigQuery DataFrames](https://cloud.google.com/python/docs/reference/bigframes/latest)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d975e698c9a4"
      },
      "source": [
        "### Objective\n",
        "\n",
        "In this tutorial, you learn how to install BigQuery DataFrames, load data into a BigQuery DataFrames DataFrame, and inspect and manipulate the data using pandas and a custom Python function, running at BigQuery scale.\n",
        "\n",
        "The steps include:\n",
        "\n",
        "- Creating a BigQuery DataFrames DataFrame: Access data from a local CSV to create a BigQuery DataFrames DataFrame.\n",
        "- Inspecting and manipulating data: Use pandas to perform data cleaning and preparation on the DataFrame.\n",
        "- Deploying a custom function: Deploy a [remote function ](https://cloud.google.com/bigquery/docs/remote-functions)that runs a scalar Python function at BigQuery scale."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "08d289fa873f"
      },
      "source": [
        "### Dataset\n",
        "\n",
        "This tutorial uses the [```penguins``` table](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=ml_datasets&t=penguins) (a BigQuery public dataset), which contains data on a set of penguins including species, island of residence, weight, culmen length and depth, flipper length, and sex.\n",
        "\n",
        "The same dataset is also stored in a public Cloud Storage bucket as a CSV file so that you can use it to try ingesting data from a local environment."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aed92deeb4a0"
      },
      "source": [
        "### Costs\n",
        "\n",
        "This tutorial uses billable components of Google Cloud:\n",
        "\n",
        "* BigQuery (storage and compute)\n",
        "* Cloud Functions\n",
        "\n",
        "Learn about [BigQuery storage pricing](https://cloud.google.com/bigquery/pricing#storage),\n",
        "[BigQuery compute pricing](https://cloud.google.com/bigquery/pricing#analysis_pricing_models),\n",
        "and [Cloud Functions pricing](https://cloud.google.com/functions/pricing),\n",
        "and use the [Pricing Calculator](https://cloud.google.com/products/calculator/)\n",
        "to generate a cost estimate based on your projected usage."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i7EUnXsZhAGF"
      },
      "source": [
        "## Installation\n",
        "\n",
        "Install the following packages, which are required to run this notebook:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "mfPoOwPLGpSr"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: bigframes in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (0.25.0)\n",
            "Requirement already satisfied: cloudpickle>=2.0.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (3.0.0)\n",
            "Requirement already satisfied: fsspec>=2023.3.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (2024.2.0)\n",
            "Requirement already satisfied: gcsfs>=2023.3.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (2024.2.0)\n",
            "Requirement already satisfied: geopandas>=0.12.2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (0.14.3)\n",
            "Requirement already satisfied: google-auth<3.0dev,>=2.15.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (2.28.2)\n",
            "Requirement already satisfied: google-cloud-bigquery>=3.10.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (3.19.0)\n",
            "Requirement already satisfied: google-cloud-functions>=1.12.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (1.16.3)\n",
            "Requirement already satisfied: google-cloud-bigquery-connection>=1.12.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (1.15.3)\n",
            "Requirement already satisfied: google-cloud-iam>=2.12.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (2.14.3)\n",
            "Requirement already satisfied: google-cloud-resource-manager>=1.10.3 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (1.12.3)\n",
            "Requirement already satisfied: google-cloud-storage>=2.0.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (2.15.0)\n",
            "Requirement already satisfied: ibis-framework<9.0.0dev,>=8.0.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (8.0.0)\n",
            "Requirement already satisfied: pandas<2.1.4,>=1.5.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (2.1.3)\n",
            "Requirement already satisfied: pydata-google-auth>=1.8.2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (1.8.2)\n",
            "Requirement already satisfied: requests>=2.27.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (2.31.0)\n",
            "Requirement already satisfied: scikit-learn>=1.2.2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (1.4.1.post1)\n",
            "Requirement already satisfied: sqlalchemy<3.0dev,>=1.4 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (2.0.28)\n",
            "Requirement already satisfied: sqlglot<=20.11,>=20.8.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (20.11.0)\n",
            "Requirement already satisfied: tabulate>=0.9 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (0.9.0)\n",
            "Requirement already satisfied: ipywidgets>=7.7.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (8.1.2)\n",
            "Requirement already satisfied: humanize>=4.6.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (4.9.0)\n",
            "Requirement already satisfied: matplotlib>=3.7.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from bigframes) (3.8.3)\n",
            "Requirement already satisfied: aiohttp!=4.0.0a0,!=4.0.0a1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from gcsfs>=2023.3.0->bigframes) (3.9.3)\n",
            "Requirement already satisfied: decorator>4.1.2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from gcsfs>=2023.3.0->bigframes) (5.1.1)\n",
            "Requirement already satisfied: google-auth-oauthlib in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from gcsfs>=2023.3.0->bigframes) (1.2.0)\n",
            "Requirement already satisfied: fiona>=1.8.21 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from geopandas>=0.12.2->bigframes) (1.9.6)\n",
            "Requirement already satisfied: packaging in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from geopandas>=0.12.2->bigframes) (24.0)\n",
            "Requirement already satisfied: pyproj>=3.3.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from geopandas>=0.12.2->bigframes) (3.6.1)\n",
            "Requirement already satisfied: shapely>=1.8.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from geopandas>=0.12.2->bigframes) (2.0.3)\n",
            "Requirement already satisfied: cachetools<6.0,>=2.0.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-auth<3.0dev,>=2.15.0->bigframes) (5.3.3)\n",
            "Requirement already satisfied: pyasn1-modules>=0.2.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-auth<3.0dev,>=2.15.0->bigframes) (0.3.0)\n",
            "Requirement already satisfied: rsa<5,>=3.1.4 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-auth<3.0dev,>=2.15.0->bigframes) (4.9)\n",
            "Requirement already satisfied: google-api-core!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-cloud-bigquery>=3.10.0->google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (2.17.1)\n",
            "Requirement already satisfied: google-cloud-core<3.0.0dev,>=1.6.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery>=3.10.0->google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (2.4.1)\n",
            "Requirement already satisfied: google-resumable-media<3.0dev,>=0.6.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery>=3.10.0->google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (2.7.0)\n",
            "Requirement already satisfied: python-dateutil<3.0dev,>=2.7.2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery>=3.10.0->google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (2.9.0.post0)\n",
            "Requirement already satisfied: proto-plus<2.0.0dev,>=1.22.3 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery-connection>=1.12.0->bigframes) (1.23.0)\n",
            "Requirement already satisfied: protobuf!=3.20.0,!=3.20.1,!=4.21.0,!=4.21.1,!=4.21.2,!=4.21.3,!=4.21.4,!=4.21.5,<5.0.0dev,>=3.19.5 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery-connection>=1.12.0->bigframes) (4.25.3)\n",
            "Requirement already satisfied: grpc-google-iam-v1<1.0.0dev,>=0.12.4 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery-connection>=1.12.0->bigframes) (0.13.0)\n",
            "Requirement already satisfied: google-cloud-bigquery-storage<3.0.0dev,>=2.6.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (2.24.0)\n",
            "Requirement already satisfied: grpcio<2.0dev,>=1.47.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (1.62.1)\n",
            "Requirement already satisfied: pyarrow>=3.0.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (15.0.1)\n",
            "Requirement already satisfied: db-dtypes<2.0.0dev,>=0.3.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (1.2.0)\n",
            "Requirement already satisfied: google-crc32c<2.0dev,>=1.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-cloud-storage>=2.0.0->bigframes) (1.5.0)\n",
            "Requirement already satisfied: atpublic<5,>=2.3 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (4.0)\n",
            "Requirement already satisfied: bidict<1,>=0.22.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (0.23.1)\n",
            "Requirement already satisfied: multipledispatch<2,>=0.6 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (1.0.0)\n",
            "Requirement already satisfied: numpy<2,>=1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (1.26.4)\n",
            "Requirement already satisfied: parsy<3,>=2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (2.1)\n",
            "Requirement already satisfied: pyarrow-hotfix<1,>=0.4 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (0.6)\n",
            "Requirement already satisfied: pytz>=2022.7 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (2024.1)\n",
            "Requirement already satisfied: rich<14,>=12.4.4 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (13.7.1)\n",
            "Requirement already satisfied: toolz<1,>=0.11 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (0.12.1)\n",
            "Requirement already satisfied: typing-extensions<5,>=4.3.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (4.10.0)\n",
            "Requirement already satisfied: comm>=0.1.3 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipywidgets>=7.7.1->bigframes) (0.2.2)\n",
            "Requirement already satisfied: ipython>=6.1.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipywidgets>=7.7.1->bigframes) (8.22.2)\n",
            "Requirement already satisfied: traitlets>=4.3.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipywidgets>=7.7.1->bigframes) (5.14.2)\n",
            "Requirement already satisfied: widgetsnbextension~=4.0.10 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipywidgets>=7.7.1->bigframes) (4.0.10)\n",
            "Requirement already satisfied: jupyterlab-widgets~=3.0.10 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipywidgets>=7.7.1->bigframes) (3.0.10)\n",
            "Requirement already satisfied: contourpy>=1.0.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from matplotlib>=3.7.1->bigframes) (1.2.0)\n",
            "Requirement already satisfied: cycler>=0.10 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from matplotlib>=3.7.1->bigframes) (0.12.1)\n",
            "Requirement already satisfied: fonttools>=4.22.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from matplotlib>=3.7.1->bigframes) (4.49.0)\n",
            "Requirement already satisfied: kiwisolver>=1.3.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from matplotlib>=3.7.1->bigframes) (1.4.5)\n",
            "Requirement already satisfied: pillow>=8 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from matplotlib>=3.7.1->bigframes) (10.2.0)\n",
            "Requirement already satisfied: pyparsing>=2.3.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from matplotlib>=3.7.1->bigframes) (3.1.2)\n",
            "Requirement already satisfied: tzdata>=2022.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from pandas<2.1.4,>=1.5.0->bigframes) (2024.1)\n",
            "Requirement already satisfied: setuptools in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from pydata-google-auth>=1.8.2->bigframes) (69.2.0)\n",
            "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from requests>=2.27.1->bigframes) (3.3.2)\n",
            "Requirement already satisfied: idna<4,>=2.5 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from requests>=2.27.1->bigframes) (3.6)\n",
            "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from requests>=2.27.1->bigframes) (2.2.1)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from requests>=2.27.1->bigframes) (2024.2.2)\n",
            "Requirement already satisfied: scipy>=1.6.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from scikit-learn>=1.2.2->bigframes) (1.12.0)\n",
            "Requirement already satisfied: joblib>=1.2.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from scikit-learn>=1.2.2->bigframes) (1.3.2)\n",
            "Requirement already satisfied: threadpoolctl>=2.0.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from scikit-learn>=1.2.2->bigframes) (3.3.0)\n",
            "Requirement already satisfied: greenlet!=0.4.17 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from sqlalchemy<3.0dev,>=1.4->bigframes) (3.0.3)\n",
            "Requirement already satisfied: aiosignal>=1.1.2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from aiohttp!=4.0.0a0,!=4.0.0a1->gcsfs>=2023.3.0->bigframes) (1.3.1)\n",
            "Requirement already satisfied: attrs>=17.3.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from aiohttp!=4.0.0a0,!=4.0.0a1->gcsfs>=2023.3.0->bigframes) (23.2.0)\n",
            "Requirement already satisfied: frozenlist>=1.1.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from aiohttp!=4.0.0a0,!=4.0.0a1->gcsfs>=2023.3.0->bigframes) (1.4.1)\n",
            "Requirement already satisfied: multidict<7.0,>=4.5 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from aiohttp!=4.0.0a0,!=4.0.0a1->gcsfs>=2023.3.0->bigframes) (6.0.5)\n",
            "Requirement already satisfied: yarl<2.0,>=1.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from aiohttp!=4.0.0a0,!=4.0.0a1->gcsfs>=2023.3.0->bigframes) (1.9.4)\n",
            "Requirement already satisfied: click~=8.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from fiona>=1.8.21->geopandas>=0.12.2->bigframes) (8.1.7)\n",
            "Requirement already satisfied: click-plugins>=1.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from fiona>=1.8.21->geopandas>=0.12.2->bigframes) (1.1.1)\n",
            "Requirement already satisfied: cligj>=0.5 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from fiona>=1.8.21->geopandas>=0.12.2->bigframes) (0.7.2)\n",
            "Requirement already satisfied: six in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from fiona>=1.8.21->geopandas>=0.12.2->bigframes) (1.16.0)\n",
            "Requirement already satisfied: googleapis-common-protos<2.0.dev0,>=1.56.2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-api-core!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-cloud-bigquery>=3.10.0->google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (1.63.0)\n",
            "Requirement already satisfied: grpcio-status<2.0.dev0,>=1.33.2 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-cloud-bigquery>=3.10.0->google-cloud-bigquery[bqstorage,pandas]>=3.10.0->bigframes) (1.62.1)\n",
            "Requirement already satisfied: requests-oauthlib>=0.7.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from google-auth-oauthlib->gcsfs>=2023.3.0->bigframes) (1.4.0)\n",
            "Requirement already satisfied: jedi>=0.16 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (0.19.1)\n",
            "Requirement already satisfied: matplotlib-inline in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (0.1.6)\n",
            "Requirement already satisfied: prompt-toolkit<3.1.0,>=3.0.41 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (3.0.43)\n",
            "Requirement already satisfied: pygments>=2.4.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (2.17.2)\n",
            "Requirement already satisfied: stack-data in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (0.6.3)\n",
            "Requirement already satisfied: pexpect>4.3 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (4.9.0)\n",
            "Requirement already satisfied: pyasn1<0.6.0,>=0.4.6 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from pyasn1-modules>=0.2.1->google-auth<3.0dev,>=2.15.0->bigframes) (0.5.1)\n",
            "Requirement already satisfied: markdown-it-py>=2.2.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from rich<14,>=12.4.4->ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (3.0.0)\n",
            "Requirement already satisfied: parso<0.9.0,>=0.8.3 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from jedi>=0.16->ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (0.8.3)\n",
            "Requirement already satisfied: mdurl~=0.1 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from markdown-it-py>=2.2.0->rich<14,>=12.4.4->ibis-framework<9.0.0dev,>=8.0.0->ibis-framework[bigquery]<9.0.0dev,>=8.0.0->bigframes) (0.1.2)\n",
            "Requirement already satisfied: ptyprocess>=0.5 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from pexpect>4.3->ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (0.7.0)\n",
            "Requirement already satisfied: wcwidth in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from prompt-toolkit<3.1.0,>=3.0.41->ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (0.2.13)\n",
            "Requirement already satisfied: oauthlib>=3.0.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib->gcsfs>=2023.3.0->bigframes) (3.2.2)\n",
            "Requirement already satisfied: executing>=1.2.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (2.0.1)\n",
            "Requirement already satisfied: asttokens>=2.1.0 in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (2.4.1)\n",
            "Requirement already satisfied: pure-eval in /usr/local/google/home/swast/envs/bigframes/lib/python3.11/site-packages (from stack-data->ipython>=6.1.0->ipywidgets>=7.7.1->bigframes) (0.2.2)\n"
          ]
        }
      ],
      "source": [
        "!pip install bigframes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "58707a750154"
      },
      "source": [
        "### Colab only\n",
        "\n",
        "Uncomment and run the following cell to restart the kernel:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "f200f10a1da3"
      },
      "outputs": [],
      "source": [
        "# Automatically restart kernel after installs so that your environment can access the new packages\n",
        "# import IPython\n",
        "\n",
        "# app = IPython.Application.instance()\n",
        "# app.kernel.do_shutdown(True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BF1j6f9HApxa"
      },
      "source": [
        "## Before you begin\n",
        "\n",
        "Complete the tasks in this section to set up your environment."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Yq7zKYWelRQP"
      },
      "source": [
        "### Set up your Google Cloud project\n",
        "\n",
        "**The following steps are required, regardless of your notebook environment.**\n",
        "\n",
        "1. [Select or create a Google Cloud project](https://console.cloud.google.com/cloud-resource-manager). When you first create an account, you get a $300 credit towards your compute/storage costs.\n",
        "\n",
        "2. [Make sure that billing is enabled for your project](https://cloud.google.com/billing/docs/how-to/modify-project).\n",
        "\n",
        "3. [Click here](https://console.cloud.google.com/flows/enableapi?apiid=bigquery.googleapis.com,bigqueryconnection.googleapis.com,cloudfunctions.googleapis.com,run.googleapis.com,artifactregistry.googleapis.com,cloudbuild.googleapis.com,cloudresourcemanager.googleapis.com) to enable the following APIs:\n",
        "\n",
        "  * BigQuery API\n",
        "  * BigQuery Connection API\n",
        "  * Cloud Functions API\n",
        "  * Cloud Run API\n",
        "  * Artifact Registry API\n",
        "  * Cloud Build API\n",
        "  * Cloud Resource Manager API\n",
        "\n",
        "4. If you are running this notebook locally, install the [Cloud SDK](https://cloud.google.com/sdk)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WReHDGG5g0XY"
      },
      "source": [
        "#### Set your project ID\n",
        "\n",
        "If you don't know your project ID, try the following:\n",
        "* Run `gcloud config list`.\n",
        "* Run `gcloud projects list`.\n",
        "* See the support page: [Locate the project ID](https://support.google.com/googleapi/answer/7014113)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "oM1iC_MfAts1"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Updated property [core/project].\n",
            "\n",
            "\n",
            "To take a quick anonymous survey, run:\n",
            "  $ gcloud survey\n",
            "\n"
          ]
        }
      ],
      "source": [
        "PROJECT_ID = \"\"  # @param {type:\"string\"}\n",
        "\n",
        "# Set the project id\n",
        "! gcloud config set project {PROJECT_ID}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "region"
      },
      "source": [
        "#### Set the region\n",
        "\n",
        "You can also change the `REGION` variable used by BigQuery. Learn more about [BigQuery regions](https://cloud.google.com/bigquery/docs/locations#supported_locations)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "eF-Twtc4XGem"
      },
      "outputs": [],
      "source": [
        "REGION = \"US\"  # @param {type: \"string\"}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sBCra4QMA2wR"
      },
      "source": [
        "### Authenticate your Google Cloud account\n",
        "\n",
        "Depending on your Jupyter environment, you might have to manually authenticate. Follow the relevant instructions below."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "74ccc9e52986"
      },
      "source": [
        "**Vertex AI Workbench**\n",
        "\n",
        "Do nothing, you are already authenticated."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "de775a3773ba"
      },
      "source": [
        "**Local JupyterLab instance**\n",
        "\n",
        "Uncomment and run the following cell:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "id": "254614fa0c46"
      },
      "outputs": [],
      "source": [
        "# ! gcloud auth login"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ef21552ccea8"
      },
      "source": [
        "**Colab**\n",
        "\n",
        "Uncomment and run the following cell:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "id": "603adbbf0532"
      },
      "outputs": [],
      "source": [
        "# from google.colab import auth\n",
        "# auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "960505627ddf"
      },
      "source": [
        "### Import libraries"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "PyQmSRbKA8r-"
      },
      "outputs": [],
      "source": [
        "import bigframes.pandas as bpd"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "init_aip:mbsdk,all"
      },
      "source": [
        "\n",
        "### Set BigQuery DataFrames options"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "id": "NPPMuw2PXGeo"
      },
      "outputs": [],
      "source": [
        "# Note: The project option is not required in all environments.\n",
        "# On BigQuery Studio, the project ID is automatically detected.\n",
        "bpd.options.bigquery.project = PROJECT_ID\n",
        "\n",
        "# Note: The location option is not required.\n",
        "# It defaults to the location of the first table or query\n",
        "# passed to read_gbq(). For APIs where a location can't be\n",
        "# auto-detected, the location defaults to the \"US\" location.\n",
        "bpd.options.bigquery.location = REGION"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pDfrKwMKE_dK"
      },
      "source": [
        "If you want to reset the location of the created DataFrame or Series objects, reset the session by executing `bpd.close_session()`. After that, you can reuse `bpd.options.bigquery.location` to specify another location."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-19Uiwoo9pP4"
      },
      "source": [
        "## See the power of BigQuery DataFrames first-hand\n",
        "\n",
        "BigQuery DataFrames enables you to interact with datasets of any size, so that you can explore, transform, and understand even your biggest datasets using familiar tools like pandas and scikit-learn."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KMX4D2uoBwM0"
      },
      "source": [
        "For example, take the BigQuery sample table `bigquery-samples.wikipedia_pageviews.200809h`, which is ~60 GB is size. This is not a dataset you'd likely be able process in pandas without extra infrastructure.\n",
        "\n",
        "With BigQuery DataFrames, however, computation is handled by BigQuery's highly scalable compute engine, meaning you can focus on doing data science without hitting size limitations."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i98c46p1CXoV"
      },
      "source": [
        "If you'd like to try creating a BigQuery DataFrames DataFrame from this table, uncomment and run the next cell to load the table using the `read_gbq` method.\n",
        "\n",
        "> Note: Keep in mind that running these operations will count against your monthly [free tier allowance in BigQuery](https://cloud.google.com/bigquery/pricing#free-tier)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "id": "Vyex9BQI-BNa"
      },
      "outputs": [],
      "source": [
        "# bq_df_sample = bpd.read_gbq(\"bigquery-samples.wikipedia_pageviews.200809h\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gE6CEALjDZZV"
      },
      "source": [
        "No problem! BigQuery DataFrames makes a DataFrame, `bq_df_sample`, containing the entirety of the source table of data."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T6lAIeelDwLz"
      },
      "source": [
        "Uncomment and run the following cell to see pandas in action over your new BigQuery DataFrames DataFrame.\n",
        "\n",
        "This code uses regex to filter the DataFrame to include only rows with Wikipedia page titles containing the word \"Google\", sums the total views by page title, and then returns the top 100 results."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "id": "XfGq5apK-D_e"
      },
      "outputs": [],
      "source": [
        "# bq_df_sample[bq_df_sample.title.str.contains(r\"[Gg]oogle\")]\\\n",
        "# .groupby(['title'], as_index=False)['views'].sum(numeric_only=True)\\\n",
        "# .sort_values('views', ascending=False)\\\n",
        "# .head(100)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i6XV-HTN-IFF"
      },
      "source": [
        "In addition to giving you access to pandas, BigQuery DataFrames also enables you to build ML models, run inference, and deploy and run your own Python functions at scale. You'll see examples throughout this and other notebooks in this GitHub repo.\n",
        "\n",
        "Now you'll move to the smaller `penguins` dataset for the remainder of this getting started guide."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9EMAqR37AfLS"
      },
      "source": [
        "## Create a BigQuery DataFrames DataFrame\n",
        "\n",
        "You can create a BigQuery DataFrames DataFrame by reading data from any of the following locations:\n",
        "\n",
        "* A local data file\n",
        "* Data stored in a BigQuery table\n",
        "* A data file stored in Cloud Storage\n",
        "* An in-memory pandas DataFrame\n",
        "\n",
        "The following sections show how to use the first two options."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iZDjzglh9eWZ"
      },
      "source": [
        "### Create a DataFrame from a local file\n",
        "\n",
        "Use the instructions in the following sections to create a BigQuery DataFrames DataFrame from a local file.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8Jry3NoFv3Wm"
      },
      "source": [
        "#### Get the CSV file\n",
        "\n",
        "First, copy and paste the following link into a new browser window to download the CSV file of the penguin data to your local machine:\n",
        "\n",
        "> http://storage.googleapis.com/cloud-samples-data/vertex-ai/bigframe/penguins.csv\n",
        "\n",
        "Next, upload the local CSV file to your notebook environment, using the relevant instructions for your environment:\n",
        "\n",
        "**Vertex AI Workbench or a local JupyterLab instance**\n",
        "\n",
        "1. Follow these [directions](https://jupyterlab.readthedocs.io/en/latest/user/files.html#uploading-and-downloading) to upload the file from your machine to your notebook environment by using the UI.\n",
        "2. Uncomment the next cell, set the variable `fn` to match the path to your file, and then run the cell."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "id": "SvyXzkRl783u"
      },
      "outputs": [],
      "source": [
        "# BigQuery DataFrames can read directly from GCS.\n",
        "fn = 'gs://cloud-samples-data/vertex-ai/bigframe/penguins.csv'\n",
        "\n",
        "# Or from a local file.\n",
        "# fn = 'penguins.csv'"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yqcuF1JNvFse"
      },
      "source": [
        "**Colab**\n",
        "\n",
        "Uncomment and run the following cell:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "id": "3QHQYlnoBLpt"
      },
      "outputs": [],
      "source": [
        "# from google.colab import files\n",
        "# uploaded = files.upload()\n",
        "# for fn in uploaded.keys():\n",
        "#  print('User uploaded file \"{name}\" with length {length} bytes'.format(\n",
        "#      name=fn, length=len(uploaded[fn])))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sJsrwAQY_H6g"
      },
      "source": [
        "#### Create a DataFrame\n",
        "\n",
        "Create a BigQuery DataFrames DataFrame from the uploaded CSV file:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "id": "EDAaIwHpQCDZ"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Load job d578c399-e2e5-4f6b-ba28-59d0686a91e7 is DONE. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:d578c399-e2e5-4f6b-ba28-59d0686a91e7&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# If order is not important, use the \"bigquery\" engine to\n",
        "# allow BigQuery DataFrames to read directly from GCS.\n",
        "df_from_local = bpd.read_csv(fn, engine=\"bigquery\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U-RVfNCu_h_h"
      },
      "source": [
        "Take a look at the first few rows of the DataFrame:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "id": "_gPD0Zn1Stdb"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job f50a129b-4a51-4c21-b155-ab1e85c1403e is DONE. 28.9 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:f50a129b-4a51-4c21-b155-ab1e85c1403e&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job b0d65008-f9f1-4fec-8620-42f307390049 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:b0d65008-f9f1-4fec-8620-42f307390049&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job a83d72e8-0cb8-44e9-ad0b-6fe3726ed1e9 is DONE. 501 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:a83d72e8-0cb8-44e9-ad0b-6fe3726ed1e9&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>species</th>\n",
              "      <th>island</th>\n",
              "      <th>culmen_length_mm</th>\n",
              "      <th>culmen_depth_mm</th>\n",
              "      <th>flipper_length_mm</th>\n",
              "      <th>body_mass_g</th>\n",
              "      <th>sex</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Gentoo penguin (Pygoscelis papua)</td>\n",
              "      <td>Biscoe</td>\n",
              "      <td>50.5</td>\n",
              "      <td>15.9</td>\n",
              "      <td>225</td>\n",
              "      <td>5400</td>\n",
              "      <td>MALE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Gentoo penguin (Pygoscelis papua)</td>\n",
              "      <td>Biscoe</td>\n",
              "      <td>45.1</td>\n",
              "      <td>14.5</td>\n",
              "      <td>215</td>\n",
              "      <td>5000</td>\n",
              "      <td>FEMALE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Adelie Penguin (Pygoscelis adeliae)</td>\n",
              "      <td>Torgersen</td>\n",
              "      <td>41.4</td>\n",
              "      <td>18.5</td>\n",
              "      <td>202</td>\n",
              "      <td>3875</td>\n",
              "      <td>MALE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Adelie Penguin (Pygoscelis adeliae)</td>\n",
              "      <td>Torgersen</td>\n",
              "      <td>38.6</td>\n",
              "      <td>17.0</td>\n",
              "      <td>188</td>\n",
              "      <td>2900</td>\n",
              "      <td>FEMALE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Gentoo penguin (Pygoscelis papua)</td>\n",
              "      <td>Biscoe</td>\n",
              "      <td>46.5</td>\n",
              "      <td>14.8</td>\n",
              "      <td>217</td>\n",
              "      <td>5200</td>\n",
              "      <td>FEMALE</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 7 columns</p>\n",
              "</div>[5 rows x 7 columns in total]"
            ],
            "text/plain": [
              "                               species     island  culmen_length_mm  \\\n",
              "0    Gentoo penguin (Pygoscelis papua)     Biscoe              50.5   \n",
              "1    Gentoo penguin (Pygoscelis papua)     Biscoe              45.1   \n",
              "2  Adelie Penguin (Pygoscelis adeliae)  Torgersen              41.4   \n",
              "3  Adelie Penguin (Pygoscelis adeliae)  Torgersen              38.6   \n",
              "4    Gentoo penguin (Pygoscelis papua)     Biscoe              46.5   \n",
              "\n",
              "   culmen_depth_mm  flipper_length_mm  body_mass_g     sex  \n",
              "0             15.9                225         5400    MALE  \n",
              "1             14.5                215         5000  FEMALE  \n",
              "2             18.5                202         3875    MALE  \n",
              "3             17.0                188         2900  FEMALE  \n",
              "4             14.8                217         5200  FEMALE  \n",
              "\n",
              "[5 rows x 7 columns]"
            ]
          },
          "execution_count": 15,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df_from_local.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rK0lNJmz_xkA"
      },
      "source": [
        "### Ingest data from a DataFrame to a BigQuery table\n",
        "\n",
        "BigQuery DataFrames lets you create a BigQuery table from a BigQuery DataFrames DataFrame on-the-fly."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "V1DWpmSCAEql"
      },
      "source": [
        "First, create a BigQuery dataset to house the table. Choose a name for your dataset, or keep the suggestion of `birds`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "id": "ZSP7gt13QrQt"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Dataset birds created.\n"
          ]
        }
      ],
      "source": [
        "DATASET_ID = \"birds\"  # @param {type:\"string\"}\n",
        "\n",
        "from google.cloud import bigquery\n",
        "client = bigquery.Client(project=PROJECT_ID)\n",
        "dataset = bigquery.Dataset(PROJECT_ID + \".\" + DATASET_ID)\n",
        "dataset.location = REGION\n",
        "dataset = client.create_dataset(dataset, exists_ok=True)\n",
        "print(f\"Dataset {dataset.dataset_id} created.\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jd0dFISwAPPa"
      },
      "source": [
        "Next, use the `to_gbq` method to create a BigQuery table from the DataFrame:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "id": "oP1NIAmUBjop"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 49702108-948c-4a60-a66e-16a3ed6bc102 is DONE. 28.9 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:49702108-948c-4a60-a66e-16a3ed6bc102&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/plain": [
              "'swast-scratch.birds.penguins'"
            ]
          },
          "execution_count": 17,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df_from_local.to_gbq(\n",
        "    PROJECT_ID + \".\" + DATASET_ID + \".penguins\",\n",
        "    if_exists=\"replace\",\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kfF6fnmmAZEK"
      },
      "source": [
        "### Create a DataFrame from BigQuery data\n",
        "You can create a BigQuery DataFrames DataFrame from a BigQuery table by using the `read_gbq` method and referencing either an entire table or a SQL query."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TEy5jHJDD6hx"
      },
      "source": [
        "Create a BigQuery DataFrames DataFrame from the BigQuery table you created in the previous section, and view a few rows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "id": "IBuo-d6dWfsA"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 05a6288d-3774-41d0-9884-6bbb5af28942 is DONE. 28.9 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:05a6288d-3774-41d0-9884-6bbb5af28942&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 45383ce0-0ca1-4c16-9832-739e9d325673 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:45383ce0-0ca1-4c16-9832-739e9d325673&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 2f672140-ddc6-43b6-b79a-318f29bb9239 is DONE. 501 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:2f672140-ddc6-43b6-b79a-318f29bb9239&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>species</th>\n",
              "      <th>island</th>\n",
              "      <th>culmen_length_mm</th>\n",
              "      <th>culmen_depth_mm</th>\n",
              "      <th>flipper_length_mm</th>\n",
              "      <th>body_mass_g</th>\n",
              "      <th>sex</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Gentoo penguin (Pygoscelis papua)</td>\n",
              "      <td>Biscoe</td>\n",
              "      <td>50.5</td>\n",
              "      <td>15.9</td>\n",
              "      <td>225</td>\n",
              "      <td>5400</td>\n",
              "      <td>MALE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Gentoo penguin (Pygoscelis papua)</td>\n",
              "      <td>Biscoe</td>\n",
              "      <td>45.1</td>\n",
              "      <td>14.5</td>\n",
              "      <td>215</td>\n",
              "      <td>5000</td>\n",
              "      <td>FEMALE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Adelie Penguin (Pygoscelis adeliae)</td>\n",
              "      <td>Torgersen</td>\n",
              "      <td>41.4</td>\n",
              "      <td>18.5</td>\n",
              "      <td>202</td>\n",
              "      <td>3875</td>\n",
              "      <td>MALE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Adelie Penguin (Pygoscelis adeliae)</td>\n",
              "      <td>Torgersen</td>\n",
              "      <td>38.6</td>\n",
              "      <td>17.0</td>\n",
              "      <td>188</td>\n",
              "      <td>2900</td>\n",
              "      <td>FEMALE</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Gentoo penguin (Pygoscelis papua)</td>\n",
              "      <td>Biscoe</td>\n",
              "      <td>46.5</td>\n",
              "      <td>14.8</td>\n",
              "      <td>217</td>\n",
              "      <td>5200</td>\n",
              "      <td>FEMALE</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 7 columns</p>\n",
              "</div>[5 rows x 7 columns in total]"
            ],
            "text/plain": [
              "                               species     island  culmen_length_mm  \\\n",
              "0    Gentoo penguin (Pygoscelis papua)     Biscoe              50.5   \n",
              "1    Gentoo penguin (Pygoscelis papua)     Biscoe              45.1   \n",
              "2  Adelie Penguin (Pygoscelis adeliae)  Torgersen              41.4   \n",
              "3  Adelie Penguin (Pygoscelis adeliae)  Torgersen              38.6   \n",
              "4    Gentoo penguin (Pygoscelis papua)     Biscoe              46.5   \n",
              "\n",
              "   culmen_depth_mm  flipper_length_mm  body_mass_g     sex  \n",
              "0             15.9                225         5400    MALE  \n",
              "1             14.5                215         5000  FEMALE  \n",
              "2             18.5                202         3875    MALE  \n",
              "3             17.0                188         2900  FEMALE  \n",
              "4             14.8                217         5200  FEMALE  \n",
              "\n",
              "[5 rows x 7 columns]"
            ]
          },
          "execution_count": 18,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "query_or_table = f\"\"\"{PROJECT_ID}.{DATASET_ID}.penguins\"\"\"\n",
        "bq_df = bpd.read_gbq(query_or_table)\n",
        "bq_df.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rwPLjqW2Ajzh"
      },
      "source": [
        "## Inspect and manipulate data in BigQuery DataFrames"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bExmYlL_ELtV"
      },
      "source": [
        "### Using pandas\n",
        "\n",
        "You can use pandas as you normally would on the BigQuery DataFrames DataFrame, but calculations happen in the BigQuery query engine instead of your local environment. There are 150+ pandas functions supported in BigQuery DataFrames. You can view the list in [the documentation](https://cloud.google.com/python/docs/reference/bigframes/latest)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZHFUc3Q_FHc1"
      },
      "source": [
        "To see this in action, inspect one of the columns (or series) of the BigQuery DataFrames DataFrame:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {
        "id": "6i6HkFJZa8na"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 5c454fa1-a01b-4e95-b947-6f02554a8461 is DONE. 28.9 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:5c454fa1-a01b-4e95-b947-6f02554a8461&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 2cffe5c7-c0c6-4495-ad67-1f5fb55654fd is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:2cffe5c7-c0c6-4495-ad67-1f5fb55654fd&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 96b4dead-f526-4be3-b24d-5d7aec99eeeb is DONE. 240 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:96b4dead-f526-4be3-b24d-5d7aec99eeeb&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/plain": [
              "0    5400\n",
              "1    5000\n",
              "2    3875\n",
              "3    2900\n",
              "4    5200\n",
              "5    3725\n",
              "6    2975\n",
              "7    4150\n",
              "8    5300\n",
              "9    4150\n",
              "Name: body_mass_g, dtype: Int64"
            ]
          },
          "execution_count": 19,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "bq_df[\"body_mass_g\"].head(10)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EJIZJaNXFQzh"
      },
      "source": [
        "Compute the mean of this series:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "id": "YKwCW7Nsavap"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 635d000c-14ca-4ecf-bc32-1527821cba28 is DONE. 2.7 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:635d000c-14ca-4ecf-bc32-1527821cba28&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "average_body_mass: 4201.754385964917\n"
          ]
        }
      ],
      "source": [
        "average_body_mass = bq_df[\"body_mass_g\"].mean()\n",
        "print(f\"average_body_mass: {average_body_mass}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DSs1cnca-MOU"
      },
      "source": [
        "Calculate the mean `body_mass_g` by `species` using the `groupby` operation:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "id": "4PyKMR61-Mjy"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job d22d8e48-26a0-4cfb-83fc-3e52b834f487 is DONE. 15.6 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:d22d8e48-26a0-4cfb-83fc-3e52b834f487&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 64fff5f3-7106-4003-9241-a9b09afed781 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:64fff5f3-7106-4003-9241-a9b09afed781&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job c3d566cc-bed1-4361-96ef-f06956982916 is DONE. 163 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:c3d566cc-bed1-4361-96ef-f06956982916&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>body_mass_g</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>species</th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Adelie Penguin (Pygoscelis adeliae)</th>\n",
              "      <td>3700.662252</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Chinstrap penguin (Pygoscelis antarctica)</th>\n",
              "      <td>3733.088235</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Gentoo penguin (Pygoscelis papua)</th>\n",
              "      <td>5076.01626</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>3 rows × 1 columns</p>\n",
              "</div>[3 rows x 1 columns in total]"
            ],
            "text/plain": [
              "                                           body_mass_g\n",
              "species                                               \n",
              "Adelie Penguin (Pygoscelis adeliae)        3700.662252\n",
              "Chinstrap penguin (Pygoscelis antarctica)  3733.088235\n",
              "Gentoo penguin (Pygoscelis papua)           5076.01626\n",
              "\n",
              "[3 rows x 1 columns]"
            ]
          },
          "execution_count": 21,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "bq_df[[\"species\", \"body_mass_g\"]].groupby(by=bq_df[\"species\"]).mean(numeric_only=True).head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6sf9kZ2C9Ixe"
      },
      "source": [
        "You can confirm that the calculations were run in BigQuery by clicking \"Open job\" from the previous cells' output. This takes you to the BigQuery console to view the SQL statement and job details."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cWVNZ8D_FUtT"
      },
      "source": [
        "### Using custom functions\n",
        "\n",
        "Running your own Python functions (or being able to bring your packages) and using them at scale is a challenge many data scientists face. BigQuery DataFrames makes it easy to deploy [remote functions](https://cloud.google.com/python/docs/reference/bigframes/latest/bigframes.pandas#bigframes_pandas_remote_function) that run scalar Python functions at BigQuery scale. These functions are persisted as [BigQuery remote functions](https://cloud.google.com/bigquery/docs/remote-functions) that you can then re-use."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "import sys\n",
        "\n",
        "# Python 3.13 is not yet a supported runtime for remote functions.\n",
        "# See: https://cloud.google.com/functions/docs/runtime-support#python for the supported runtimes.\n",
        "if sys.version_info >= (3, 13, 0):\n",
        "    sys.exit(0)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zjw8toUbHuRD"
      },
      "source": [
        "Running the cell below creates a custom function using the `remote_function` method. This function categorizes a value into one of two buckets: >= 4000 or <4000.\n",
        "\n",
        "> Note: Creating a function requires a [BigQuery connection](https://cloud.google.com/bigquery/docs/remote-functions#create_a_remote_function). This code assumes a pre-created connection named `bigframes-default-connection`. If\n",
        "the connection is not already created, BigQuery DataFrames attempts to create one assuming the [necessary APIs\n",
        "and IAM permissions](https://cloud.google.com/python/docs/reference/bigframes/latest/bigframes.pandas#bigframes_pandas_remote_function) are set up in the project.\n",
        "\n",
        "This cell takes a few minutes to run because it creates the BigQuery connection (if applicable) and deploys the Cloud Function."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "id": "rSWTOG-vb2Fc"
      },
      "outputs": [],
      "source": [
        "@bpd.remote_function(cloud_function_service_account=\"default\")\n",
        "def get_bucket(num: float) -> str:\n",
        "  if not num: return \"NA\"\n",
        "  boundary = 4000\n",
        "  return \"at_or_above_4000\" if num >= boundary else \"below_4000\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "N7JH0BI5IOpK"
      },
      "source": [
        "The custom function is deployed as a Cloud Function, and is then integrated with BigQuery as a remote function.\n",
        "\n",
        "Save both of the function names so that you can clean them up at the end of this notebook."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "id": "6ejPXoyEQpWE"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Cloud Function Name projects/swast-scratch/locations/us-central1/functions/bigframes-71a76285da23f28be467ed16826f7276\n",
            "Remote Function Name swast-scratch._63cfa399614a54153cc386c27d6c0c6fdb249f9e.bigframes_71a76285da23f28be467ed16826f7276\n"
          ]
        }
      ],
      "source": [
        "CLOUD_FUNCTION_NAME = format(get_bucket.bigframes_cloud_function)\n",
        "print(\"Cloud Function Name \" + CLOUD_FUNCTION_NAME)\n",
        "REMOTE_FUNCTION_NAME = format(get_bucket.bigframes_remote_function)\n",
        "print(\"Remote Function Name \" + REMOTE_FUNCTION_NAME)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vHV3JqKjJHsH"
      },
      "source": [
        "Apply the custom function to the BigQuery DataFrames DataFrame to bucketize the `body_mass_g` value of the penguins:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "id": "NxSd9WZFcIji"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 9925acd1-d1e7-4746-90d6-4ce8c2ca30a8 is DONE. 28.9 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:9925acd1-d1e7-4746-90d6-4ce8c2ca30a8&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 2f10b5cd-80bb-4697-9c61-b7848ce15c81 is DONE. 39.6 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:2f10b5cd-80bb-4697-9c61-b7848ce15c81&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 29266b33-3945-44c0-943b-3d6365b9cc7a is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:29266b33-3945-44c0-943b-3d6365b9cc7a&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 19ecf156-8940-4c02-b20e-3e52e18c7239 is DONE. 396 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=swast-scratch&j=bq:US:19ecf156-8940-4c02-b20e-3e52e18c7239&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>body_mass_g</th>\n",
              "      <th>body_mass_bucket</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>5400</td>\n",
              "      <td>at_or_above_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>5000</td>\n",
              "      <td>at_or_above_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3875</td>\n",
              "      <td>below_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2900</td>\n",
              "      <td>below_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5200</td>\n",
              "      <td>at_or_above_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>3725</td>\n",
              "      <td>below_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>2975</td>\n",
              "      <td>below_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>4150</td>\n",
              "      <td>at_or_above_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>5300</td>\n",
              "      <td>at_or_above_4000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>4150</td>\n",
              "      <td>at_or_above_4000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>10 rows × 2 columns</p>\n",
              "</div>[10 rows x 2 columns in total]"
            ],
            "text/plain": [
              "   body_mass_g  body_mass_bucket\n",
              "0         5400  at_or_above_4000\n",
              "1         5000  at_or_above_4000\n",
              "2         3875        below_4000\n",
              "3         2900        below_4000\n",
              "4         5200  at_or_above_4000\n",
              "5         3725        below_4000\n",
              "6         2975        below_4000\n",
              "7         4150  at_or_above_4000\n",
              "8         5300  at_or_above_4000\n",
              "9         4150  at_or_above_4000\n",
              "\n",
              "[10 rows x 2 columns]"
            ]
          },
          "execution_count": 24,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "bq_df = bq_df.assign(body_mass_bucket=bq_df['body_mass_g'].apply(get_bucket))\n",
        "bq_df[['body_mass_g', 'body_mass_bucket']].head(10)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wCsmt0IwFkDy"
      },
      "source": [
        "## Summary and next steps\n",
        "\n",
        "You've created BigQuery DataFrames DataFrames, and inspected and manipulated data with pandas and custom remote functions at BigQuery scale and speed.\n",
        "\n",
        "Learn more about BigQuery DataFrames in the [documentation](https://cloud.google.com/python/docs/reference/bigframes/latest) and find more sample notebooks in the [GitHub repo](https://github.com/googleapis/python-bigquery-dataframes/tree/main/notebooks), including an introductory notebook for `bigframes.ml`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TpV-iwP9qw9c"
      },
      "source": [
        "### Cleaning up\n",
        "\n",
        "To clean up all Google Cloud resources used in this project, you can [delete the Google Cloud\n",
        "project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects) you used for the tutorial.\n",
        "\n",
        "Otherwise, you can uncomment the remaining cells and run them to delete the individual resources you created in this tutorial:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "# Delete the temporary cloud artifacts created during the bigframes session \n",
        "bpd.close_session()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "id": "sx_vKniMq9ZX"
      },
      "outputs": [],
      "source": [
        "# # Delete the BigQuery dataset\n",
        "# from google.cloud import bigquery\n",
        "# client = bigquery.Client(project=PROJECT_ID)\n",
        "# client.delete_dataset(\n",
        "#  DATASET_ID, delete_contents=True, not_found_ok=True\n",
        "# )\n",
        "# print(\"Deleted dataset '{}'.\".format(DATASET_ID))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "metadata": {
        "id": "_dTCXvCxtPw9"
      },
      "outputs": [],
      "source": [
        "# # Delete the BigQuery Connection\n",
        "# from google.cloud import bigquery_connection_v1 as bq_connection\n",
        "# client = bq_connection.ConnectionServiceClient()\n",
        "# CONNECTION_ID = f\"projects/{PROJECT_ID}/locations/{REGION}/connections/bigframes-default-connection\"\n",
        "# client.delete_connection(name=CONNECTION_ID)\n",
        "# print(\"Deleted connection '{}'.\".format(CONNECTION_ID))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "metadata": {
        "id": "EDAIIfcpwNOF"
      },
      "outputs": [],
      "source": [
        "# # Delete the Cloud Function\n",
        "# ! gcloud functions delete {CLOUD_FUNCTION_NAME} --quiet"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "id": "QwumLUKmVpuH"
      },
      "outputs": [],
      "source": [
        "# # Delete the Remote Function\n",
        "# REMOTE_FUNCTION_NAME = REMOTE_FUNCTION_NAME.replace(PROJECT_ID + \".\", \"\")\n",
        "# ! bq rm --routine --force=true {REMOTE_FUNCTION_NAME}"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.10.15"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}