{ "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 171.4 MB in 19 seconds of slot time. [Job bigframes-dev:US.50efe672-74c6-4292-98d9-520cba9ca516 details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "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]\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": "b1080dddbe4140d2b88ef85566e52955", "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
0ALF1910Lillian99
1ALF1910Ruby204
2ALF1910Helen76
3ALF1910Eunice41
4ARF1910Dora42
5CAF1910Edna62
6CAF1910Helen239
7COF1910Alice46
8FLF1910Willie71
9FLF1910Thelma65
\n", "

10 rows × 5 columns

\n", "
[5552452 rows x 5 columns in total]" ], "text/plain": [ "state gender year name number\n", " AL F 1910 Lillian 99\n", " AL F 1910 Ruby 204\n", " AL F 1910 Helen 76\n", " AL F 1910 Eunice 41\n", " AR F 1910 Dora 42\n", " CA F 1910 Edna 62\n", " CA F 1910 Helen 239\n", " CO F 1910 Alice 46\n", " FL F 1910 Willie 71\n", " FL F 1910 Thelma 65\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 44.4 MB in a moment of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "1967\n", "1981\n", "2009\n", "1956\n", "1960\n", "2001\n", "2009\n", "2003\n", "1985\n", "1993\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": "46e836f10d9e47afb4d82b5c7da69660", "version_major": 2, "version_minor": 1 }, "text/html": [ "
0    1910\n",
       "1    1912\n",
       "2    1912\n",
       "3    1911\n",
       "4    1912\n",
       "5    1910\n",
       "6    1913\n",
       "7    1912\n",
       "8    1913\n",
       "9    1913

[5552452 rows]

" ], "text/plain": [ "1910\n", "1912\n", "1912\n", "1911\n", "1912\n", "1910\n", "1913\n", "1912\n", "1913\n", "1913\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": "6e5f603b56fb408bb1ea41519ea8702e", "version_major": 2, "version_minor": 1 }, "text/plain": [ "" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import math\n", "\n", "from bigframes.display.anywidget import TableWidget\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": "20c94621c4ae4eb5a94fd3596ae8c236", "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": "75000341", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " Query processed 0 Bytes in a moment of slot time. [Job bigframes-dev:US.job_cpfa9oehjApkQgrbTrKRxTpEtuQX details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "application/vnd.jupyter.widget-view+json": { "model_id": "d5bf0a9438954c6890b5d8cd16bff7cd", "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\":\"18157874.1\",\"class_inte...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\":\"18165514.3\",\"class_inte...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
2{\"application_number\":\"18157347.8\",\"class_inte...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
3{\"application_number\":\"18166536.5\",\"class_inte...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
4{\"application_number\":\"18171005.4\",\"class_inte...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
\n", "

5 rows × 15 columns

\n", "
[5 rows x 15 columns in total]" ], "text/plain": [ " result \\\n", "{\"application_number\":\"18157874.1\",\"class_inter... \n", "{\"application_number\":\"18165514.3\",\"class_inter... \n", "{\"application_number\":\"18157347.8\",\"class_inter... \n", "{\"application_number\":\"18166536.5\",\"class_inter... \n", "{\"application_number\":\"18171005.4\",\"class_inter... \n", "\n", " gcs_path issuer language \\\n", "gs://gcs-public-data--labeled-patents/espacenet... EU DE \n", "gs://gcs-public-data--labeled-patents/espacenet... EU DE \n", "gs://gcs-public-data--labeled-patents/espacenet... EU DE \n", "gs://gcs-public-data--labeled-patents/espacenet... EU DE \n", "gs://gcs-public-data--labeled-patents/espacenet... EU DE \n", "\n", "publication_date class_international class_us application_number filing_date \\\n", " 29.08.018 E04H 6/12 18157874.1 21.02.2018 \n", " 03.10.2018 H05B 6/12 18165514.3 03.04.2018 \n", " 03.10.2018 G06F 11/30 18157347.8 19.02.2018 \n", " 03.10.2018 H01L 21/20 18166536.5 16.02.2016 \n", " 03.10.2018 A01K 31/00 18171005.4 05.02.2015 \n", "\n", "priority_date_eu representative_line_1_eu applicant_line_1 \\\n", " 22.02.2017 Liedtke & Partner Patentanwälte SHB Hebezeugbau GmbH \n", " 30.03.2017 BSH Hausgeräte GmbH \n", " 31.03.2017 Hoffmann Eitle FUJITSU LIMITED \n", " Scheider, Sascha et al EV Group E. Thallner GmbH \n", " 05.02.2014 Stork Bamberger Patentanwälte Linco Food Systems A/S \n", "\n", " inventor_line_1 title_line_1 \\\n", " VOLGER, Alexander STEUERUNGSSYSTEM FÜR AUTOMATISCHE PARKHÄUSER \n", "Acero Acero, Jesus VORRICHTUNG ZUR INDUKTIVEN ENERGIEÜBERTRAGUNG \n", " Kukihara, Kensuke METHOD EXECUTED BY A COMPUTER, INFORMATION PROC... \n", " Kurz, Florian VORRICHTUNG ZUM BONDEN VON SUBSTRATEN \n", " Thrane, Uffe MASTHÄHNCHENCONTAINER ALS BESTANDTEIL EINER EI... \n", "\n", " number \n", "EP 3 366 869 A1 \n", "EP 3 383 141 A2 \n", "EP 3 382 553 A1 \n", "EP 3 382 744 A1 \n", "EP 3 381 276 A1 \n", "\n", "[5 rows x 15 columns]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bpd.read_gbq(\"\"\"\n", " SELECT\n", " AI.GENERATE(\n", " prompt=>(\"Extract the values.\", OBJ.GET_ACCESS_URL(OBJ.FETCH_METADATA(OBJ.MAKE_REF(gcs_path, \"us.bigframes-default-connection\")), \"r\")),\n", " connection_id=>\"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", "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 }