# Copyright 2025 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import bigframes.pandas as bpd

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.

Key features:

  • Rich DataFrames & Series: Both DataFrames and Series are displayed as interactive widgets.

  • Pagination: Navigate through large datasets page by page without overwhelming the output.

  • Column Sorting: Click column headers to toggle between ascending, descending, and unsorted views. Use Shift + Click to sort by multiple columns.

  • Column Resizing: Drag the dividers between column headers to adjust their width.

  • Max Columns Control: Limit the number of displayed columns to improve performance and readability for wide datasets.

bpd.options.bigquery.ordering_mode = "partial"
bpd.options.display.render_mode = "anywidget"

Load Sample Data

1. Interactive DataFrame Display#

Loading a dataset from BigQuery automatically renders the interactive widget.

df = bpd.read_gbq("bigquery-public-data.usa_names.usa_1910_2013")
print(df)

Query processed 0 Bytes in a moment of slot time. 

state gender  year      name  number
   AL      F  1910      Cora      61
   AL      F  1910      Anna      74
   AR      F  1910    Willie     132
   CO      F  1910      Anna      42
   FL      F  1910    Louise      70
   GA      F  1910 Catherine      57
   IL      F  1910    Jessie      43
   IN      F  1910      Anna     100
   IN      F  1910   Pauline      77
   IN      F  1910    Beulah      39
...

[5552452 rows x 5 columns]

2. Interactive Series Display#

BigQuery DataFrames Series objects now also support the full interactive widget experience, including pagination and formatting.

test_series = df["year"]
# Displaying the series triggers the interactive widget
print(test_series)

Query processed 88.8 MB in a moment of slot time. 

1910
1910
1910
1910
1910
1910
1910
1910
1910
1910
Name: year, dtype: Int64
...

[5552452 rows]

Display with Pagination

Sorting by Column(s)#

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.

Single-Column Sorting#

The sorting control cycles through three states:

  • Unsorted (no indicator by default, ● on hover): The default state. Click the header to sort in ascending order.

  • Ascending (▲): The data is sorted from smallest to largest. Click again to sort in descending order.

  • Descending (▼): The data is sorted from largest to smallest. Click again to return to the unsorted state.

Multi-Column Sorting#

You can sort by multiple columns to further refine your view:

  • Shift + Click: Hold the Shift key while clicking additional column headers to add them to the sort order.

  • Each column in a multi-sort also cycles through the three states (Ascending, Descending, Unsorted).

  • 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.

Adjustable Column Widths#

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.

Control Maximum Columns#

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.

Programmatic Navigation Demo

3. Programmatic Widget Control#

You can also instantiate the TableWidget directly for more control, such as checking page counts or driving navigation programmatically.

from bigframes.display.anywidget import TableWidget
import math
  
# Create widget programmatically  
widget = TableWidget(df)
print(f"Total pages: {math.ceil(widget.row_count / widget.page_size)}")
  
# Display the widget
widget

Test Navigation Programmatically

# Simulate button clicks programmatically
print("Current page:", widget.page)

# Go to next page
widget.page = 1
print("After next:", widget.page)

# Go to previous page
widget.page = 0
print("After prev:", widget.page)

Current page: 0
After next: 1
After prev: 0

4. Edge Cases#

The widget handles small datasets gracefully, disabling unnecessary pagination controls.

# Test with very small dataset
small_df = df.sort_values(["name", "year", "state"]).head(5)
small_widget = TableWidget(small_df)
print(f"Small dataset pages: {math.ceil(small_widget.row_count / small_widget.page_size)}")
small_widget

Displaying Generative AI results containing JSON#

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), BigQuery Dataframes automatically converts JSON columns to strings for display. This allows you to view the results of generative AI functions seamlessly.

5. Advanced Data Types (JSON/Structs)#

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.

bpd._read_gbq_colab("""
  SELECT
    AI.GENERATE(
      prompt=>(\"Extract the values.\", OBJ.GET_ACCESS_URL(OBJ.FETCH_METADATA(OBJ.MAKE_REF(gcs_path, \"us.conn\")), \"r\")),
      connection_id=>\"bigframes-dev.us.bigframes-default-connection\",
      output_schema=>\"publication_date string, class_international string, application_number string, filing_date string\") AS result,
    *
  FROM `bigquery-public-data.labeled_patents.extracted_data`
  LIMIT 5;
""")

Query processed 85.9 kB in 28 seconds of slot time.