{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import datetime as dt\n",
"import numpy as np\n",
"import pandas as pd\n",
"import panel as pn\n",
"\n",
"np.random.seed(7)\n",
"pn.extension('tabulator')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The `Tabulator` widget allows displaying and editing a pandas DataFrame. The `Tabulator` is a largely backward compatible replacement for the [`DataFrame`](./DataFrame.ipynb) widget and will eventually replace it. It is built on the **version 5.5** of the [Tabulator](http://tabulator.info/) library, which provides for a wide range of features.\n",
"\n",
"Discover more on using widgets to add interactivity to your applications in the [how-to guides on interactivity](../how_to/interactivity/index.md). Alternatively, learn [how to set up callbacks and (JS-)links between parameters](../../how_to/links/index.md) or [how to use them as part of declarative UIs with Param](../../how_to/param/index.html).\n",
"\n",
"#### Parameters:\n",
"\n",
"For details on other options for customizing the component see the [layout](../../how_to/layout/index.md) and [styling](../../how_to/styling/index.md) how-to guides.\n",
"\n",
"##### Core\n",
"\n",
"* **`aggregators`** (`dict`): A dictionary mapping from index name to an aggregator to be used for `hierarchical` multi-indexes (valid aggregators include 'min', 'max', 'mean' and 'sum'). If separate aggregators for different columns are required the dictionary may be nested as `{index_name: {column_name: aggregator}}`\n",
"* **`buttons`** (`dict`): A dictionary of buttons to add to the table mapping from column name to the HTML contents of the button cell, e.g. `{'print': ''}`. Buttons are added after all data columns.\n",
"* **`configuration`** (`dict`): A dictionary mapping used to specify *Tabulator* options not explicitly exposed by Panel.\n",
"* **`editors`** (`dict`): A dictionary mapping from column name to a bokeh `CellEditor` instance or *Tabulator* editor specification.\n",
"* **`embed_content`** (`boolean`): Whether to embed the `row_content` or to dynamically fetch it when a row is expanded.\n",
"* **`expanded`** (`list`): The currently expanded rows as a list of integer indexes.\n",
"* **`filters`** (`list`): A list of client-side filter definitions that are applied to the table.\n",
"* **`formatters`** (`dict`): A dictionary mapping from column name to a bokeh `CellFormatter` instance or *Tabulator* formatter specification.\n",
"* **`frozen_columns`** (`list` or `dict`): Defines the frozen columns:\n",
" * `list`\n",
" List of columns to freeze, preventing them from scrolling out of frame. Column can be specified by name or index.\n",
" * `dict`\n",
" Dict of columns to freeze and the position in table (`'left'` or `'right'`) to freeze them in. Column names or index can be used as keys. If value does not match\n",
" `left` or `right` then the default behaviour is to not be frozen at all.\n",
"* **`frozen_rows`**: (`list`): List of rows to freeze, preventing them from scrolling out of frame. Rows can be specified by positive or negative index.\n",
"* **`groupby`** (`list`): Groups rows in the table by one or more columns.\n",
"* **`header_align`** (`dict` or `str`): A mapping from column name to header alignment or a fixed header alignment, which should be one of `'left'`, `'center'`, `'right'`.\n",
"* **`header_filters`** (`boolean`/`dict`): A boolean enabling filters in the column headers or a dictionary providing filter definitions for specific columns.\n",
"* **`hidden_columns`** (`list`): List of columns to hide.\n",
"* **`hierarchical`** (boolean, default=False): Whether to render multi-indexes as hierarchical index (note hierarchical must be enabled during instantiation and cannot be modified later)\n",
"* **`layout`** (`str`, `default='fit_data_table'`): Describes the column layout mode with one of the following options `'fit_columns'`, `'fit_data'`, `'fit_data_stretch'`, `'fit_data_fill'`, `'fit_data_table'`. \n",
"* **`page`** (`int`, `default=1`): Current page, if pagination is enabled.\n",
"* **`page_size`** (`int`, `default=20`): Number of rows on each page, if pagination is enabled.\n",
"* **`pagination`** (`str`, `default=None`): Set to `'local` or `'remote'` to enable pagination; by default pagination is disabled with the value set to `None`.\n",
"* **`row_content`** (`callable`): A function that receives the expanded row (`pandas.Series`) as input and should return a Panel object to render into the expanded region below the row.\n",
"* **`selection`** (`list`): The currently selected rows as a list of integer indexes.\n",
"* **`selectable`** (`boolean` or `str` or `int`, `default=True`): Defines the selection mode:\n",
" * `True`\n",
" Selects rows on click. To select multiple use Ctrl-select, to select a range use Shift-select\n",
" * `False`\n",
" Disables selection\n",
" * `'checkbox'`\n",
" Adds a column of checkboxes to toggle selections\n",
" * `'checkbox-single'`\n",
" Same as 'checkbox' but header does not allow select/deselect all\n",
" * `'toggle'`\n",
" Selection toggles when clicked\n",
" * `int`\n",
" The maximum number of selectable rows.\n",
"* **`selectable_rows`** (`callable`): A function that should return a list of integer indexes given a DataFrame indicating which rows may be selected.\n",
"* **`show_index`** (`boolean`, `default=True`): Whether to show the index column.\n",
"* **`sortable`** (`bool | dict[str, bool]`, `default=True`): Whether the table is sortable or whether individual columns are sortable. If specified as a bool applies globally otherwise sorting can be enabled/disabled per column.\n",
"* **`sorters`** (`list`): A list of sorter definitions mapping where each item should declare the column to sort on and the direction to sort, e.g. `[{'field': 'column_name', 'dir': 'asc'}, {'field': 'another_column', 'dir': 'desc'}]`.\n",
"* **`text_align`** (`dict` or `str`): A mapping from column name to alignment or a fixed column alignment, which should be one of `'left'`, `'center'`, `'right'`.\n",
"* **`theme`** (`str`, `default='simple'`): The CSS theme to apply (note that changing the theme will restyle all tables on the page), which should be one of `'default'`, `'site'`, `'simple'`, `'midnight'`, `'modern'`, `'bootstrap'`, `'bootstrap4'`, `'materialize'`, `'bulma'`, `'semantic-ui'`, or `'fast'`.\n",
"* **`theme_classes`** (`list[str]`): List of extra CSS classes to apply to the Tabulator element to customize the theme.\n",
"* **`title_formatters`** (`dict`): A dictionary mapping from column name to a *Tabulator* formatter specification.\n",
"* **`titles`** (`dict`): A mapping from column name to a title to override the name with.\n",
"* **`value`** (`pd.DataFrame`): The pandas DataFrame to display and edit\n",
"* **`widths`** (`dict`): A dictionary mapping from column name to column width in the rendered table.\n",
"\n",
"##### Display\n",
"\n",
"* **`disabled`** (`boolean`): Whether the cells are editable\n",
"\n",
"##### Properties\n",
"\n",
"* **`current_view`** (`DataFrame`): The current view of the table that is displayed, i.e. after sorting and filtering are applied. `current_view` isn't guaranteed to be in sync with the displayed current view when sorters are applied and values are edited, in which case `current_view` is sorted while the displayed table isn't.\n",
"* **`selected_dataframe`** (`DataFrame`): A DataFrame reflecting the currently selected rows.\n",
"\n",
"##### Callbacks\n",
"\n",
"* **`on_click`**: Allows registering callbacks which are given `CellClickEvent` objects containing the `column`, `row` and `value` of the clicked cell.\n",
"* **`on_edit`**: Allows registering callbacks which are given `TableEditEvent` objects containing the `column`, `row`, `value` and `old` value of the edited cell.\n",
"\n",
"In both these callbacks `row` is the index of the `value` DataFrame.\n",
"___"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The `Tabulator` widget renders a DataFrame using an interactive grid, which allows directly editing the contents of the DataFrame in place, with any changes being synced with Python. The `Tabulator` will usually determine the appropriate formatter appropriately based on the type of the data:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df = pd.DataFrame({\n",
" 'int': [1, 2, 3],\n",
" 'float': [3.14, 6.28, 9.42],\n",
" 'str': ['A', 'B', 'C'],\n",
" 'bool': [True, False, True],\n",
" 'date': [dt.date(2019, 1, 1), dt.date(2020, 1, 1), dt.date(2020, 1, 10)],\n",
" 'datetime': [dt.datetime(2019, 1, 1, 10), dt.datetime(2020, 1, 1, 12), dt.datetime(2020, 1, 10, 13)]\n",
"}, index=[1, 2, 3])\n",
"\n",
"df_widget = pn.widgets.Tabulator(df, buttons={'Print': \"\"})\n",
"df_widget"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Formatters\n",
"\n",
"By default the widget will pick Bokeh `CellFormatter` and `CellEditor` types appropriate to the dtype of the column. These may be overridden by explicit dictionaries mapping from the column name to the editor or formatter instance. For example below we create a `NumberFormatter` to customize the formatting of the numbers in the `float` column and a `BooleanFormatter` instance to display the values in the `bool` column with tick crosses:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from bokeh.models.widgets.tables import NumberFormatter, BooleanFormatter\n",
"\n",
"bokeh_formatters = {\n",
" 'float': NumberFormatter(format='0.00000'),\n",
" 'bool': BooleanFormatter(),\n",
"}\n",
"\n",
"pn.widgets.Tabulator(df, formatters=bokeh_formatters)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The list of valid Bokeh formatters includes:\n",
" \n",
"* [BooleanFormatter](https://docs.bokeh.org/en/latest/docs/reference/models/widgets/tables.html#bokeh.models.BooleanFormatter)\n",
"* [DateFormatter](https://docs.bokeh.org/en/latest/docs/reference/models/widgets/tables.html#bokeh.models.DateFormatter)\n",
"* [NumberFormatter](https://docs.bokeh.org/en/latest/docs/reference/models/widgets/tables.html#bokeh.models.NumberFormatter)\n",
"* [HTMLTemplateFormatter](https://docs.bokeh.org/en/latest/docs/reference/models/widgets/tables.html#bokeh.models.HTMLTemplateFormatter)\n",
"* [StringFormatter](https://docs.bokeh.org/en/latest/docs/reference/models/widgets/tables.html#bokeh.models.StringFormatter)\n",
"* [ScientificFormatter](https://docs.bokeh.org/en/latest/docs/reference/models/widgets/tables.html#bokeh.models.ScientificFormatter)\n",
"\n",
"However in addition to the formatters exposed by Bokeh it is also possible to provide valid formatters built into the *Tabulator* library. These may be defined either as a string or as a dictionary declaring the `type` and other arguments, which are passed to *Tabulator* as the `formatterParams`:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"tabulator_formatters = {\n",
" 'float': {'type': 'progress', 'max': 10},\n",
" 'bool': {'type': 'tickCross'}\n",
"}\n",
"\n",
"pn.widgets.Tabulator(df, formatters=tabulator_formatters)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The list of valid *Tabulator* formatters can be found in the [Tabulator documentation](https://tabulator.info/docs/5.5/format#format-builtin).\n",
"\n",
"Note that the equivalent specification may also be applied for column titles using the `title_formatters` parameter (but does not support Bokeh `CellFormatter` types)."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"## Editors/Editing\n",
"\n",
"Just like the formatters, the `Tabulator` will natively understand the Bokeh `Editor` types. However, in the background it will replace most of them with equivalent editors natively supported by the *Tabulator* library:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from bokeh.models.widgets.tables import CheckboxEditor, NumberEditor, SelectEditor\n",
"\n",
"bokeh_editors = {\n",
" 'float': NumberEditor(),\n",
" 'bool': CheckboxEditor(),\n",
" 'str': SelectEditor(options=['A', 'B', 'C', 'D']),\n",
"}\n",
"\n",
"pn.widgets.Tabulator(df[['float', 'bool', 'str']], editors=bokeh_editors)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Therefore it is often preferable to use one of the [*Tabulator* editors](https://tabulator.info/docs/5.5/edit#edit) directly. Setting the editor of a column to `None` makes that column non-editable. Note that in addition to the standard *Tabulator* editors the `Tabulator` widget also supports `'date'` and `'datetime'` editors:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"tabulator_editors = {\n",
" 'int': None,\n",
" 'float': {'type': 'number', 'max': 10, 'step': 0.1},\n",
" 'bool': {'type': 'tickCross', 'tristate': True, 'indeterminateValue': None},\n",
" 'str': {'type': 'list', 'valuesLookup': True},\n",
" 'date': 'date',\n",
" 'datetime': 'datetime'\n",
"}\n",
"\n",
"edit_table = pn.widgets.Tabulator(df, editors=tabulator_editors)\n",
"\n",
"edit_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"When editing a cell the data stored on the `Tabulator.value` is updated and you can listen to any changes using the usual `.param.watch(callback, 'value')` mechanism. However if you need to know precisely which cell was changed you may also attach an `on_edit` callback which will be passed a `TableEditEvent` containing the:\n",
"\n",
"- `column`: Name of the edited column\n",
"- `row`: Integer index of the edited row of the `value` DataFrame\n",
"- `old`: Old cell value\n",
"- `value`: New cell value"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"edit_table.on_edit(lambda e: print(e.column, e.row, e.old, e.value))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Column layouts\n",
"\n",
"By default the DataFrame widget will adjust the sizes of both the columns and the table based on the contents, reflecting the default value of the parameter: `layout=\"fit_data_table\"`. Alternative modes allow manually specifying the widths of the columns, giving each column equal widths, or adjusting just the size of the columns.\n",
"\n",
"### Manual column widths\n",
"\n",
"To manually adjust column widths provide explicit `widths` for each of the columns:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"custom_df = df.iloc[:3, :]\n",
"\n",
"pn.widgets.Tabulator(custom_df, widths={'index': 70, 'A': 50, 'B': 50, 'C': 70, 'D': 130})"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can also declare a single width for all columns this way:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(custom_df, widths=130)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"or even use percentage widths:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(custom_df, widths={'index': '5%', 'A': '15%', 'B': '15%', 'C': '25%', 'D': '40%'}, sizing_mode='stretch_width')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Autosize columns\n",
"\n",
"To automatically adjust the columns depending on their content set `layout='fit_data'`:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(custom_df, layout='fit_data', width=400)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To ensure that the table fits all the data but also stretches to fill all the available space, set `layout='fit_data_stretch'`:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(custom_df, layout='fit_data_stretch', width=400)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The `'fit_data_fill'` option on the other hand won't stretch the last column but still fill the space:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(custom_df, layout='fit_data_fill', width=400)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Perhaps the most useful of these options is `layout='fit_data_table'` (and therefore the default) since this will automatically size both the columns and the table:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(custom_df, layout='fit_data_table')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Equal size\n",
"\n",
"The simplest option is simply to allocate each column equal amount of size:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(custom_df, layout='fit_columns', width=650)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Alignment\n",
"\n",
"The content of a column or its header can be horizontally aligned with `text_align` and `header_align`. These two parameters accept either a string that globally defines the alignment or a dictionary that declares which particular columns are meant to be aligned and how."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(df.iloc[:, :2], header_align='center', text_align={'int': 'center', 'float': 'left'}, widths=150)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Styling\n",
"\n",
"The ability to style the contents of a table based on its content and other considerations is very important. Thankfully `pandas` provides a powerful [styling API](https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html), which can be used in conjunction with the `Tabulator` widget. Specifically the `Tabulator` widget exposes a `.style` attribute just like a `pandas.DataFrame` which lets the user apply custom styling using methods like `.apply` and `.applymap`. For a detailed guide to styling see the [Pandas documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html).\n",
"\n",
"Here we will demonstrate with a simple example, starting with a basic table:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"style_df = pd.DataFrame(np.random.randn(4, 5), columns=list('ABCDE'))\n",
"styled = pn.widgets.Tabulator(style_df)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Next we define two functions which apply styling cell-wise (`color_negative_red`) and column-wise (`highlight_max`), which we then apply to the `Tabulator` using the `.style` API and then display the `styled` table:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"def color_negative_red(val):\n",
" \"\"\"\n",
" Takes a scalar and returns a string with\n",
" the css property `'color: red'` for negative\n",
" strings, black otherwise.\n",
" \"\"\"\n",
" color = 'red' if val < 0 else 'black'\n",
" return 'color: %s' % color\n",
"\n",
"def highlight_max(s):\n",
" '''\n",
" highlight the maximum in a Series yellow.\n",
" '''\n",
" is_max = s == s.max()\n",
" return ['background-color: yellow' if v else '' for v in is_max]\n",
"\n",
"styled.style.applymap(color_negative_red).apply(highlight_max)\n",
"\n",
"styled"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Theming\n",
"\n",
"The Tabulator library ships with a number of themes, which are defined as CSS stylesheets. For that reason changing the theme on one table will affect all tables on the page and it will usually be preferable to see the theme once at the class level like this:\n",
"\n",
"```python\n",
"pn.widgets.Tabulator.theme = 'default'\n",
"```\n",
"\n",
"For a full list of themes see the [Tabulator documentation](http://tabulator.info/docs/4.9/theme), however the default themes include:\n",
"\n",
"- `'simple'`\n",
"- `'default'`\n",
"- `'midnight'`\n",
"- `'site'`\n",
"- `'modern'`\n",
"- `'bootstrap'`\n",
"- `'bootstrap4'`\n",
"- `'materialize'`\n",
"- `'semantic-ui'`\n",
"- `'bulma'`"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Additionally, you may provide additional theming classes [as described here](https://tabulator.info/docs/5.5/theme#framework)."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(df, theme='bootstrap5', theme_classes=['thead-dark', 'table-sm'])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Selection/Click\n",
"\n",
"The `selection` parameter controls which rows in the table are selected and can be set from Python and updated by selecting rows on the frontend:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"sel_df = pd.DataFrame(np.random.randn(3, 5), columns=list('ABCDE'))\n",
"\n",
"select_table = pn.widgets.Tabulator(sel_df, selection=[0, 2])\n",
"select_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Once initialized, the `selection` parameter will return the integer indexes of the selected rows, while the `selected_dataframe` property will return a new DataFrame containing just the selected rows:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"select_table.selection = [1]\n",
"\n",
"select_table.selected_dataframe"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The `selectable` parameter declares how the selections work. \n",
"\n",
"- `True`: Selects rows on click. To select multiple use Ctrl-select, to select a range use Shift-select\n",
"- `False`: Disables selection\n",
"- `'checkbox'`: Adds a column of checkboxes to toggle selections\n",
"- `'checkbox-single'`: Same as `'checkbox'` but disables (de)select-all in the header\n",
"- `'toggle'`: Selection toggles when clicked\n",
"- Any positive `int`: A number that sets the maximum number of selectable rows"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(sel_df, selection=[0, 2], selectable='checkbox')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Additionally we can also disable selection for specific rows by providing a `selectable_rows` function. The function must accept a `DataFrame` and return a list of integer indexes indicating which rows are selectable, e.g. here we disable selection for every second row:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"select_table = pn.widgets.Tabulator(sel_df, selectable_rows=lambda df: list(range(0, len(df), 2)))\n",
"select_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To trigger events based on an exact cell that was clicked you may also register an `on_click` callback which is called whenever a cell is clicked."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"def click(event):\n",
" print(f'Clicked cell in {event.column!r} column, row {event.row!r} with value {event.value!r}')\n",
"\n",
"select_table.on_click(click) \n",
"# Optionally we can also limit the callback to a specific column\n",
"# select_table.on_click(click, column='A') "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Freezing rows and columns\n",
"\n",
"Sometimes your table will be larger than can be displayed in a single viewport, in which case scroll bars will be enabled. In such cases, you might want to make sure that certain information is always visible. This is where the `frozen_columns` and `frozen_rows` options come in.\n",
"\n",
"#### Frozen columns\n",
"\n",
"When you have a large number of columns and can't fit them all on the screen you might still want to make sure that certain columns do not scroll out of view. The `frozen_columns` option makes this possible by specifying a list of columns that should be frozen, e.g. `frozen_columns=['index']` will freeze the index column:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(df, frozen_columns=['index'], width=400)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"By default, columns given in the list format are frozen to the left hand side of the table. If you want to customize where columns are frozen to on the table, you can specify this with a dictionary:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(df, frozen_columns={'index': 'left', 'float': 'right'}, width=400)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The 'index' column will be frozen on the left side of the table, and the 'float' on the right. Non-frozen columns will scroll between these two."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Frozen rows\n",
"\n",
"Another common scenario is when you have certain rows with special meaning, e.g. aggregates that summarize the information in the rest of the table. In this case you may want to freeze those rows so they do not scroll out of view. You can achieve this by setting a list of `frozen_rows` by integer index (which can be positive or negative, where negative values are relative to the end of the table):"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"date_df = df.set_index('date').iloc[:5, :2]\n",
"agg_df = pd.concat([date_df, date_df.median().to_frame('Median').T, date_df.mean().to_frame('Mean').T])\n",
"agg_df.index= agg_df.index.map(str)\n",
"\n",
"pn.widgets.Tabulator(agg_df, frozen_rows=[-2, -1], height=200)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Row contents\n",
"\n",
"A table can only display so much information without becoming difficult to scan. We may want to render additional information to a table row to provide additional context. To make this possible you can provide a `row_content` function which is given the table row as an argument (a `pandas.Series` object) and should return a panel object that will be rendered into an expanding region below the row. By default the contents are fetched dynamically whenever a row is expanded, however using the `embed_content` parameter we can embed all the content.\n",
"\n",
"Below we create a periodic table of elements where the Wikipedia page for each element will be rendered into the expanded region:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from bokeh.sampledata.periodic_table import elements\n",
"\n",
"periodic_df = elements[['atomic number', 'name', 'atomic mass', 'metal', 'year discovered']].set_index('atomic number')\n",
"\n",
"content_fn = lambda row: pn.pane.HTML(\n",
" f'',\n",
" sizing_mode='stretch_width'\n",
")\n",
"\n",
"periodic_table = pn.widgets.Tabulator(\n",
" periodic_df, height=350, layout='fit_columns', sizing_mode='stretch_width',\n",
" row_content=content_fn, embed_content=True\n",
")\n",
"\n",
"periodic_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The currently expanded rows can be accessed and set on the `expanded` parameter:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"periodic_table.expanded"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Grouping\n",
"\n",
"Another useful option is the ability to group specific rows together, which can be achieved using `groups` parameter. The `groups` parameter should be composed of a dictionary mapping from the group titles to the column names:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.widgets.Tabulator(date_df.iloc[:3], groups={'Group 1': ['A', 'B'], 'Group 2': ['C', 'D']})"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Groupby\n",
"\n",
"In addition to grouping columns we can also group rows by the values along one or more columns:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from bokeh.sampledata.autompg import autompg\n",
"\n",
"pn.widgets.Tabulator(autompg, groupby=['yr', 'origin'], height=240)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Hierarchical Multi-index\n",
"\n",
"The `Tabulator` widget can also render a hierarchical multi-index and aggregate over specific categories. If a DataFrame with a hierarchical multi-index is supplied and the `hierarchical` is enabled the widget will group data by the categories in the order they are defined in. Additionally for each group in the multi-index an aggregator may be provided which will aggregate over the values in that category.\n",
"\n",
"For example we may load population data for locations around the world broken down by sex and age-group. If we specify aggregators over the 'AgeGrp' and 'Sex' indexes we can see the aggregated values for each of those groups (note that we do not have to specify an aggregator for the outer index since we specify the aggregators over the subgroups in this case the 'Sex'):"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from bokeh.sampledata.population import data as population_data \n",
"\n",
"pop_df = population_data[population_data.Year == 2020].set_index(['Location', 'AgeGrp', 'Sex'])[['Value']]\n",
"\n",
"pn.widgets.Tabulator(value=pop_df, hierarchical=True, aggregators={'Sex': 'sum', 'AgeGrp': 'sum'}, height=200)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Pagination\n",
"\n",
"When working with large tables it is generally not advisible to display the whole table at once. In these scenarios we can enable either `'local'` or `'remote'` pagination, which will render only a single page of data at the same time. In the case of `'remote'` pagination only the currently viewed data is actually transferred from the backend server to the frontend and new data is fetched dynamically when we switch the page or filter and sort the data. Note that Panel will automatically enable `'local'` pagination for tables larger than 200 rows and enable `'remote'` pagination for tables larger than 10 000 rows. This protection may be overridden by explicitly setting the `pagination` parameter.\n",
"\n",
"The pagination setting may be enabled by setting `pagination='remote'` or `pagination='local'` and the size of each page can be set using the `page_size` option:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"large_df = pd.DataFrame({'A': np.random.rand(10000)})\n",
"pn.widgets.Tabulator(large_df, pagination='remote', page_size=3)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Contrary to the `'remote'` option, `'local'` pagination transfers all of the data but still allows to display it on multiple pages:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"medium_df = pd.DataFrame({'A': np.random.rand(1000)})\n",
"pn.widgets.Tabulator(medium_df, pagination='local', page_size=3)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Filtering\n",
"\n",
"A very common scenario is that you want to attach a number of filters to a table in order to view just a subset of the data. You can achieve this through callbacks or other reactive approaches but the `.add_filter` method makes it much easier.\n",
"\n",
"#### Constant and Widget filters\n",
"\n",
"The simplest approach to filtering is to select along a column with a constant or dynamic value. The `.add_filter` method allows passing in constant values, widgets and Param `Parameter`s. If a widget or `Parameter` is provided the table will watch the object for changes in the value and update the data in response. The filtering will depend on the type of the constant or dynamic value:\n",
"\n",
"- scalar: Filters by checking for equality\n",
"- `tuple`: A tuple will be interpreted as range, the *start* and *end* bounds being both included in the range. Setting one of the bounds to `None` create an open-ended bound.\n",
"- `list`/`set`: A list or set will be interpreted as a set of discrete scalars and the filter will check if the values in the column match any of the items in the list.\n",
"\n",
"As an example we will create a DataFrame with some data of mixed types:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"filter_table = pn.widgets.Tabulator(df)\n",
"filter_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now we will start adding filters one-by-one, e.g. to start with we add a filter for the `'A'` column, selecting a range from 0 to 3:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"filter_table.add_filter((1, 2), 'int')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Next we add dynamic widget based filter, a `RangeSlider` which allows us to further narrow down the data along the `'A'` column:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"slider = pn.widgets.RangeSlider(start=0, end=3, name='A Filter')\n",
"filter_table.add_filter(slider, 'int')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Lastly we will add a `MultiSelect` filter along the `'C'` column:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"select = pn.widgets.MultiSelect(options=list('ABC'), name='str Filter')\n",
"filter_table.add_filter(select, 'str')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now let's display the table alongside the widget based filters:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pn.Row(\n",
" pn.Column(slider, select),\n",
" filter_table\n",
")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"After filtering (and sorting) you can inspect the current view with the `current_view` property:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"select.value = ['A', 'B']\n",
"filter_table.current_view"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Function based filtering"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"For more complex filtering tasks you can supply a function that should accept the DataFrame to be filtered as the first argument and must return a filtered copy of the data. Let's start by loading some data."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import sqlite3\n",
"\n",
"from bokeh.sampledata.movies_data import movie_path\n",
"\n",
"con = sqlite3.Connection(movie_path)\n",
"movies_df = pd.read_sql('SELECT Title, Year, Genre, Director, Writer, Rating, imdbRating from omdb', con)\n",
"movies_df = movies_df[~movies_df.Director.isna()]\n",
"\n",
"movies_table = pn.widgets.Tabulator(movies_df, pagination='remote', page_size=4)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"By using the `pn.bind` function, which binds widget and `Parameter` values to a function, complex filtering can be achieved. E.g. here we will add a filter function that tests whether the string or regex is contained in the 'Director' column of a listing of thousands of movies:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"director_filter = pn.widgets.TextInput(name='Director filter', value='Chaplin')\n",
"\n",
"def contains_filter(df, pattern, column):\n",
" if not pattern:\n",
" return df\n",
" return df[df[column].str.contains(pattern)]\n",
" \n",
"movies_table.add_filter(pn.bind(contains_filter, pattern=director_filter, column='Director')) \n",
"\n",
"pn.Row(director_filter, movies_table)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Client-side filtering\n",
"\n",
"In addition to the Python API the `Tabulator` widget also offers a client-side filtering API, which can be exposed through `header_filters` or by manually setting filters in the rendered table. The API for declaring header filters is almost identical to the API for defining [Editors](#Editors). The `header_filters` can either be enabled by setting it to `True` or by manually supplying filter types for each column. The types of filters supports all the same options as the editors, in fact if you do not declare explicit `header_filters` the `Tabulator` widget will simply use the defined `editors` to determine the correct filter type:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"tabulator_editors = {\n",
" 'float': {'type': 'number', 'max': 10, 'step': 0.1},\n",
" 'bool': {'type': 'tickCross', 'tristate': True, 'indeterminateValue': None},\n",
" 'str': {'type': 'list', 'valuesLookup': True},\n",
"}\n",
"\n",
"header_filter_table = pn.widgets.Tabulator(\n",
" df[['float', 'bool', 'str']], height=140, width=400, layout='fit_columns',\n",
" editors=tabulator_editors, header_filters=True\n",
")\n",
"header_filter_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"When a filter is applied client-side the `filters` parameter is synced with Python. The definition of `filters` looks something like this:\n",
"\n",
"```\n",
"[{'field': 'Director', 'type': '=', 'value': 'Steven Spielberg'}]\n",
"```\n",
"\n",
"Try applying a filter and then inspect the `filters` parameter:"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"For all supported filtering types see the [*Tabulator* Filtering documentation](http://tabulator.info/docs/4.9/filter).\n",
"\n",
"If we want to change the filter type for the `header_filters` we can do so in the definition by supplying a dictionary indexed by the column names and then either providing a dictionary which may define the `'type'`, a comparison `'func'`, a `'placeholder'` and any additional keywords supported by the particular filter type."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"movie_filters = {\n",
" 'Title': {'type': 'input', 'func': 'like', 'placeholder': 'Enter title'},\n",
" 'Year': {'placeholder': 'Enter year'},\n",
" 'Genre': {'type': 'input', 'func': 'like', 'placeholder': 'Enter genre'},\n",
" 'Director': {'type': 'input', 'func': 'like', 'placeholder': 'Enter director'},\n",
" 'Writer': {'type': 'input', 'func': 'like', 'placeholder': 'Enter writer'},\n",
" 'Rating': {'type': 'list', 'func': 'in', 'valuesLookup': True, 'sort': 'asc', 'multiselect': True},\n",
" 'imdbRating': {'type': 'number', 'func': '>=', 'placeholder': 'Enter minimum rating'},\n",
"}\n",
"\n",
"filter_table = pn.widgets.Tabulator(\n",
" movies_df.iloc[:200], pagination='local', layout='fit_columns', page_size=4, sizing_mode='stretch_width',\n",
" header_filters=movie_filters\n",
")\n",
"filter_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Downloading\n",
"\n",
"The `Tabulator` widget also supports triggering a download of the data as a CSV or JSON file depending on the filename. The download can be triggered with the `.download()` method, which optionally accepts the filename as the first argument.\n",
"\n",
"To trigger the download client-side (i.e. without involving the server) you can use the `.download_menu` method which creates a `TextInput` and `Button` widget, which allow setting the filename and triggering the download respectively:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"download_df = pd.DataFrame(np.random.randn(4, 5), columns=list('ABCDE'))\n",
"\n",
"download_table = pn.widgets.Tabulator(download_df)\n",
"\n",
"filename, button = download_table.download_menu(\n",
" text_kwargs={'name': 'Enter filename', 'value': 'default.csv'},\n",
" button_kwargs={'name': 'Download table'}\n",
")\n",
"\n",
"pn.Row(\n",
" pn.Column(filename, button),\n",
" download_table\n",
")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Note that when `pagination='remote'` is enabled the download feature will only include the current page for technical reasons. If you want to support downloading all the data use the [`FileDownload` widget](./FileDownload.ipynb)."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Buttons\n",
"\n",
"If you want to trigger custom actions by clicking on a table cell you may declare a set of `buttons` that are rendered in columns after all the data columns. To respond to button clicks you can register a callback using the general `on_click` method:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"button_table = pn.widgets.Tabulator(df, buttons={\n",
" 'print': '',\n",
" 'check': ''\n",
"})\n",
"\n",
"string = pn.widgets.StaticText()\n",
"\n",
"button_table.on_click(\n",
" lambda e: string.param.update(value=f'Clicked {e.column!r} on row {e.row}')\n",
")\n",
"\n",
"pn.Row(button_table, string)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
" Please note, that in a server context you will have to include the *font awesome* css file to get the button icons rendered, i.e. use\n",
" \n",
" ```python\n",
" pn.extension(\"tabulator\", ..., css_files=[\"https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.2/css/all.min.css\"])\n",
" ```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Streaming\n",
"\n",
"When we are monitoring some source of data that updates over time, we may want to update the table with the newly arriving data. However, we do not want to transmit the entire dataset each time. To handle efficient transfer of just the latest data, we can use the `.stream` method on the `Tabulator` object:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"stream_df = pd.DataFrame(np.random.randn(5, 5), columns=list('ABCDE'))\n",
"\n",
"stream_table = pn.widgets.Tabulator(stream_df, layout='fit_columns', width=450, height=400)\n",
"stream_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"As example, we will schedule a periodic callback that streams new data every 1000ms (i.e. 1s) five times in a row:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"def stream_data(follow=True):\n",
" stream_df = pd.DataFrame(np.random.randn(5, 5), columns=list('ABCDE'))\n",
" stream_table.stream(stream_df, follow=follow)\n",
"\n",
"pn.state.add_periodic_callback(stream_data, period=1000, count=5);"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If you are viewing this example with a live Python kernel you will be able to watch the table update and scroll along. If we want to disable the scrolling behavior, we can set `follow=False`:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"stream_data(follow=False)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Patching\n",
"\n",
"In certain cases we don't want to update the table with new data but just patch existing data."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"patch_table = pn.widgets.Tabulator(df[['int', 'float', 'str', 'bool']].copy())\n",
"patch_table"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The easiest way to patch the data is by supplying a dictionary as the patch value. The dictionary should have the following structure:\n",
"\n",
"```python\n",
"{\n",
" column: [\n",
" (index: int or slice, value),\n",
" ...\n",
" ],\n",
" ...\n",
"}\n",
"```\n",
" \n",
"As an example, below we will patch the `'bool'` and `'int'` columns. On the `'bool'` column we will replace the 0th and 2nd row and on the `'int'` column we replace the first two rows:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"patch_table.patch({\n",
" 'bool': [\n",
" (0, False),\n",
" (2, False)\n",
" ],\n",
" 'int': [\n",
" (slice(0, 2), [3, 2])\n",
" ]\n",
"}, as_index=False)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Static Configuration\n",
"\n",
"Panel does not expose all options available from Tabulator, if a desired option is not natively supported, it can be set via the `configuration` argument. \n",
"This dictionary can be seen as a base dictionary which the tabulator object fills and passes to the Tabulator javascript-library.\n",
"\n",
"As an example, we can enable `clipboard` functionality and set the `rowHeight` options. `columnDefaults` takes a dictionary used to configure the columns specifically, in this example we disable header sorting with `headerSort`."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df = pd.DataFrame({\n",
" 'int': [1, 2, 3],\n",
" 'float': [3.14, 6.28, 9.42],\n",
" 'str': ['A', 'B', 'C'],\n",
" 'bool': [True, False, True],\n",
" 'date': [dt.date(2019, 1, 1), dt.date(2020, 1, 1), dt.date(2020, 1, 10)]\n",
"}, index=[1, 2, 3])\n",
"\n",
"pn.widgets.Tabulator(df, configuration={\n",
" 'clipboard': True,\n",
" 'rowHeight': 50,\n",
" 'columnDefaults': {\n",
" 'headerSort': False,\n",
" },\n",
"})"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"These and other available *Tabulator* options are listed at http://tabulator.info/docs/5.4/options. \n",
"\n",
"Obviously not all options will work though, especially any settable callbacks and options which are set by the internal Panel tabulator module (for example the `columns` option).\n",
"Additionally it should be noted that the configuration parameter is not responsive so it can only be set at instantiation time."
]
}
],
"metadata": {
"language_info": {
"name": "python",
"pygments_lexer": "ipython3"
}
},
"nbformat": 4,
"nbformat_minor": 4
}