{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "d10bfca4", "metadata": {}, "outputs": [], "source": [ "# Copyright 2025 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", "id": "acca43ae", "metadata": {}, "source": [ "# Demo to Show Anywidget mode" ] }, { "cell_type": "code", "execution_count": 2, "id": "ca22f059", "metadata": {}, "outputs": [], "source": [ "import bigframes.pandas as bpd" ] }, { "cell_type": "markdown", "id": "04406a4d", "metadata": {}, "source": [ "This notebook demonstrates the **anywidget** display mode for BigQuery DataFrames. This mode provides an interactive table experience for exploring your data directly within the notebook.\n", "\n", "**Key features:**\n", "- **Rich DataFrames & Series:** Both DataFrames and Series are displayed as interactive widgets.\n", "- **Pagination:** Navigate through large datasets page by page without overwhelming the output.\n", "- **Column Sorting:** Click column headers to toggle between ascending, descending, and unsorted views. Use **Shift + Click** to sort by multiple columns.\n", "- **Column Resizing:** Drag the dividers between column headers to adjust their width.\n", "- **Max Columns Control:** Limit the number of displayed columns to improve performance and readability for wide datasets." ] }, { "cell_type": "code", "execution_count": 3, "id": "1bc5aaf3", "metadata": {}, "outputs": [], "source": [ "bpd.options.bigquery.ordering_mode = \"partial\"\n", "bpd.options.display.render_mode = \"anywidget\"" ] }, { "cell_type": "markdown", "id": "0a354c69", "metadata": {}, "source": [ "Load Sample Data" ] }, { "cell_type": "markdown", "id": "interactive-df-header", "metadata": {}, "source": [ "## 1. Interactive DataFrame Display\n", "Loading a dataset from BigQuery automatically renders the interactive widget." ] }, { "cell_type": "code", "execution_count": 4, "id": "f289d250", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " Query processed 0 Bytes in a moment of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "state gender year name number\n", " AL F 1910 Cora 61\n", " AL F 1910 Anna 74\n", " AR F 1910 Willie 132\n", " CO F 1910 Anna 42\n", " FL F 1910 Louise 70\n", " GA F 1910 Catherine 57\n", " IL F 1910 Jessie 43\n", " IN F 1910 Anna 100\n", " IN F 1910 Pauline 77\n", " IN F 1910 Beulah 39\n", "...\n", "\n", "[5552452 rows x 5 columns]\n" ] } ], "source": [ "df = bpd.read_gbq(\"bigquery-public-data.usa_names.usa_1910_2013\")\n", "print(df)" ] }, { "cell_type": "code", "execution_count": 5, "id": "220340b0", "metadata": {}, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "d75a0d81724f4776ae1a592369e78946", "version_major": 2, "version_minor": 1 }, "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
stategenderyearnamenumber
0ALF1910Annie482
1ALF1910Myrtle104
2ARF1910Lillian56
3CTF1910Anne38
4CTF1910Frances45
5FLF1910Margaret53
6GAF1910Mae73
7GAF1910Beatrice96
8GAF1910Lola47
9IAF1910Viola49
\n", "

10 rows × 5 columns

\n", "
[5552452 rows x 5 columns in total]" ], "text/plain": [ "state gender year name number\n", " AL F 1910 Annie 482\n", " AL F 1910 Myrtle 104\n", " AR F 1910 Lillian 56\n", " CT F 1910 Anne 38\n", " CT F 1910 Frances 45\n", " FL F 1910 Margaret 53\n", " GA F 1910 Mae 73\n", " GA F 1910 Beatrice 96\n", " GA F 1910 Lola 47\n", " IA F 1910 Viola 49\n", "...\n", "\n", "[5552452 rows x 5 columns]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df" ] }, { "cell_type": "markdown", "id": "3a73e472", "metadata": {}, "source": [ "## 2. Interactive Series Display\n", "BigQuery DataFrames `Series` objects now also support the full interactive widget experience, including pagination and formatting." ] }, { "cell_type": "code", "execution_count": 6, "id": "42bb02ab", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " Query processed 171.4 MB in 46 seconds of slot time. [Job bigframes-dev:US.dcf260e0-eaad-4979-9ec6-12f2436698e4 details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " Query processed 88.8 MB in a moment of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "Name: year, dtype: Int64\n", "...\n", "\n", "[5552452 rows]\n" ] } ], "source": [ "test_series = df[\"year\"]\n", "# Displaying the series triggers the interactive widget\n", "print(test_series)" ] }, { "cell_type": "markdown", "id": "7bcf1bb7", "metadata": {}, "source": [ "Display with Pagination" ] }, { "cell_type": "code", "execution_count": 7, "id": "da23e0f3", "metadata": {}, "outputs": [ { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "8e1b0e50cacb4315a231913b321cff55", "version_major": 2, "version_minor": 1 }, "text/html": [ "
0    1910\n",
       "1    1910\n",
       "2    1910\n",
       "3    1910\n",
       "4    1910\n",
       "5    1910\n",
       "6    1910\n",
       "7    1910\n",
       "8    1910\n",
       "9    1910

[5552452 rows]

" ], "text/plain": [ "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "1910\n", "Name: year, dtype: Int64\n", "...\n", "\n", "[5552452 rows]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "test_series" ] }, { "cell_type": "markdown", "id": "sorting-intro", "metadata": {}, "source": [ "### Sorting by Column(s)\n", "You can sort the table by clicking on the headers of columns that have orderable data types (like numbers, strings, and dates). Non-orderable columns (like arrays or structs) do not have sorting controls.\n", "\n", "#### Single-Column Sorting\n", "The sorting control cycles through three states:\n", "- **Unsorted (no indicator by default, ● on hover):** The default state. Click the header to sort in ascending order.\n", "- **Ascending (▲):** The data is sorted from smallest to largest. Click again to sort in descending order.\n", "- **Descending (▼):** The data is sorted from largest to smallest. Click again to return to the unsorted state.\n", "\n", "#### Multi-Column Sorting\n", "You can sort by multiple columns to further refine your view:\n", "- **Shift + Click:** Hold the `Shift` key while clicking additional column headers to add them to the sort order. \n", "- Each column in a multi-sort also cycles through the three states (Ascending, Descending, Unsorted).\n", "- **Indicator visibility:** Sorting indicators (▲, ▼) are always visible for all columns currently included in the sort. The unsorted indicator (●) is only visible when you hover over an unsorted column header." ] }, { "cell_type": "markdown", "id": "adjustable-width-intro", "metadata": {}, "source": [ "### Adjustable Column Widths\n", "You can easily adjust the width of any column in the table. Simply hover your mouse over the vertical dividers between column headers. When the cursor changes to a resize icon, click and drag to expand or shrink the column to your desired width. This allows for better readability and customization of your table view.\n", "\n", "### Control Maximum Columns\n", "You can control the number of columns displayed in the widget using the **Max columns** dropdown in the footer. This is useful for wide DataFrames where you want to focus on a subset of columns or improve rendering performance. Options include 3, 5, 7, 10, 20, or All." ] }, { "cell_type": "markdown", "id": "bb15bab6", "metadata": {}, "source": [ "Programmatic Navigation Demo" ] }, { "cell_type": "markdown", "id": "programmatic-header", "metadata": {}, "source": [ "## 3. Programmatic Widget Control\n", "You can also instantiate the `TableWidget` directly for more control, such as checking page counts or driving navigation programmatically." ] }, { "cell_type": "code", "execution_count": 8, "id": "6920d49b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Total pages: 555246\n" ] }, { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "b7f188a72de440359e402d8e41de26a9", "version_major": 2, "version_minor": 1 }, "text/plain": [ "" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from bigframes.display.anywidget import TableWidget\n", "import math\n", " \n", "# Create widget programmatically \n", "widget = TableWidget(df)\n", "print(f\"Total pages: {math.ceil(widget.row_count / widget.page_size)}\")\n", " \n", "# Display the widget\n", "widget" ] }, { "cell_type": "markdown", "id": "02cbd1be", "metadata": {}, "source": [ "Test Navigation Programmatically" ] }, { "cell_type": "code", "execution_count": 9, "id": "12b68f15", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Current page: 0\n", "After next: 1\n", "After prev: 0\n" ] } ], "source": [ "# Simulate button clicks programmatically\n", "print(\"Current page:\", widget.page)\n", "\n", "# Go to next page\n", "widget.page = 1\n", "print(\"After next:\", widget.page)\n", "\n", "# Go to previous page\n", "widget.page = 0\n", "print(\"After prev:\", widget.page)" ] }, { "cell_type": "markdown", "id": "9d310138", "metadata": {}, "source": [ "## 4. Edge Cases\n", "The widget handles small datasets gracefully, disabling unnecessary pagination controls." ] }, { "cell_type": "code", "execution_count": 10, "id": "a9d5d13a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Small dataset pages: 1\n" ] }, { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "cf507362c97b4ccf9084997d03d65290", "version_major": 2, "version_minor": 1 }, "text/plain": [ "" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Test with very small dataset\n", "small_df = df.sort_values([\"name\", \"year\", \"state\"]).head(5)\n", "small_widget = TableWidget(small_df)\n", "print(f\"Small dataset pages: {math.ceil(small_widget.row_count / small_widget.page_size)}\")\n", "small_widget" ] }, { "cell_type": "markdown", "id": "added-cell-2", "metadata": {}, "source": [ "### Displaying Generative AI results containing JSON\n", "The `AI.GENERATE` function in BigQuery returns results in a JSON column. While BigQuery's JSON type is not natively supported by the underlying Arrow `to_pandas_batches()` method used in anywidget mode ([Apache Arrow issue #45262](https://github.com/apache/arrow/issues/45262)), BigQuery Dataframes automatically converts JSON columns to strings for display. This allows you to view the results of generative AI functions seamlessly." ] }, { "cell_type": "markdown", "id": "ai-header", "metadata": {}, "source": [ "## 5. Advanced Data Types (JSON/Structs)\n", "The `AI.GENERATE` function in BigQuery returns results in a JSON column. BigQuery Dataframes automatically handles complex types like JSON strings for display, allowing you to view generative AI results seamlessly." ] }, { "cell_type": "code", "execution_count": 11, "id": "added-cell-1", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " Query processed 85.9 kB in 28 seconds of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "b9dd4b812443455ba32ec71723331a10", "version_major": 2, "version_minor": 1 }, "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
resultgcs_pathissuerlanguagepublication_dateclass_internationalclass_usapplication_numberfiling_datepriority_date_eurepresentative_line_1_euapplicant_line_1inventor_line_1title_line_1number
0{'application_number': None, 'class_internatio...gs://gcs-public-data--labeled-patents/espacene...EUDE29.08.018E04H 6/12<NA>18157874.121.02.201822.02.2017Liedtke & Partner PatentanwälteSHB Hebezeugbau GmbHVOLGER, AlexanderSTEUERUNGSSYSTEM FÜR AUTOMATISCHE PARKHÄUSEREP 3 366 869 A1
1{'application_number': None, 'class_internatio...gs://gcs-public-data--labeled-patents/espacene...EUDE03.10.2018G06F 11/30<NA>18157347.819.02.201831.03.2017Hoffmann EitleFUJITSU LIMITEDKukihara, KensukeMETHOD EXECUTED BY A COMPUTER, INFORMATION PRO...EP 3 382 553 A1
2{'application_number': None, 'class_internatio...gs://gcs-public-data--labeled-patents/espacene...EUDE03.10.2018A01K 31/00<NA>18171005.405.02.201505.02.2014Stork Bamberger PatentanwälteLinco Food Systems A/SThrane, UffeMASTHÄHNCHENCONTAINER ALS BESTANDTEIL EINER E...EP 3 381 276 A1
3{'application_number': None, 'class_internatio...gs://gcs-public-data--labeled-patents/espacene...EUDE03.10.2018H05B 6/12<NA>18165514.303.04.201830.03.2017<NA>BSH Hausgeräte GmbHAcero Acero, JesusVORRICHTUNG ZUR INDUKTIVEN ENERGIEÜBERTRAGUNGEP 3 383 141 A2
4{'application_number': None, 'class_internatio...gs://gcs-public-data--labeled-patents/espacene...EUDE03.10.2018H01L 21/20<NA>18166536.516.02.2016<NA>Scheider, Sascha et alEV Group E. Thallner GmbHKurz, FlorianVORRICHTUNG ZUM BONDEN VON SUBSTRATENEP 3 382 744 A1
\n", "

5 rows × 15 columns

\n", "
[5 rows x 15 columns in total]" ], "text/plain": [ " result \\\n", "0 {'application_number': None, 'class_internatio... \n", "1 {'application_number': None, 'class_internatio... \n", "2 {'application_number': None, 'class_internatio... \n", "3 {'application_number': None, 'class_internatio... \n", "4 {'application_number': None, 'class_internatio... \n", "\n", " gcs_path issuer language \\\n", "0 gs://gcs-public-data--labeled-patents/espacene... EU DE \n", "1 gs://gcs-public-data--labeled-patents/espacene... EU DE \n", "2 gs://gcs-public-data--labeled-patents/espacene... EU DE \n", "3 gs://gcs-public-data--labeled-patents/espacene... EU DE \n", "4 gs://gcs-public-data--labeled-patents/espacene... EU DE \n", "\n", " publication_date class_international class_us application_number \\\n", "0 29.08.018 E04H 6/12 18157874.1 \n", "1 03.10.2018 G06F 11/30 18157347.8 \n", "2 03.10.2018 A01K 31/00 18171005.4 \n", "3 03.10.2018 H05B 6/12 18165514.3 \n", "4 03.10.2018 H01L 21/20 18166536.5 \n", "\n", " filing_date priority_date_eu representative_line_1_eu \\\n", "0 21.02.2018 22.02.2017 Liedtke & Partner Patentanwälte \n", "1 19.02.2018 31.03.2017 Hoffmann Eitle \n", "2 05.02.2015 05.02.2014 Stork Bamberger Patentanwälte \n", "3 03.04.2018 30.03.2017 \n", "4 16.02.2016 Scheider, Sascha et al \n", "\n", " applicant_line_1 inventor_line_1 \\\n", "0 SHB Hebezeugbau GmbH VOLGER, Alexander \n", "1 FUJITSU LIMITED Kukihara, Kensuke \n", "2 Linco Food Systems A/S Thrane, Uffe \n", "3 BSH Hausgeräte GmbH Acero Acero, Jesus \n", "4 EV Group E. Thallner GmbH Kurz, Florian \n", "\n", " title_line_1 number \n", "0 STEUERUNGSSYSTEM FÜR AUTOMATISCHE PARKHÄUSER EP 3 366 869 A1 \n", "1 METHOD EXECUTED BY A COMPUTER, INFORMATION PRO... EP 3 382 553 A1 \n", "2 MASTHÄHNCHENCONTAINER ALS BESTANDTEIL EINER E... EP 3 381 276 A1 \n", "3 VORRICHTUNG ZUR INDUKTIVEN ENERGIEÜBERTRAGUNG EP 3 383 141 A2 \n", "4 VORRICHTUNG ZUM BONDEN VON SUBSTRATEN EP 3 382 744 A1 \n", "\n", "[5 rows x 15 columns]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bpd._read_gbq_colab(\"\"\"\n", " SELECT\n", " AI.GENERATE(\n", " prompt=>(\\\"Extract the values.\\\", OBJ.GET_ACCESS_URL(OBJ.FETCH_METADATA(OBJ.MAKE_REF(gcs_path, \\\"us.conn\\\")), \\\"r\\\")),\n", " connection_id=>\\\"bigframes-dev.us.bigframes-default-connection\\\",\n", " output_schema=>\\\"publication_date string, class_international string, application_number string, filing_date string\\\") AS result,\n", " *\n", " FROM `bigquery-public-data.labeled_patents.extracted_data`\n", " LIMIT 5;\n", "\"\"\")" ] } ], "metadata": { "kernelspec": { "display_name": "venv (3.13.0)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.0" } }, "nbformat": 4, "nbformat_minor": 5 }