Tabulator

Download this notebook from GitHub (right-click to download).

import datetime as dt
import numpy as np
import pandas as pd
import panel as pn

pn.extension('tabulator')

The Tabulator widget allows displaying and editing a pandas DataFrame. The Tabulator is a largely backward compatible replacement for the DataFrame widget and will eventually replace it. It is built on the Tabulator library, which provides for a wide range of features.

For more information about listening to widget events and laying out widgets refer to the widgets user guide. Alternatively you can learn how to build GUIs by declaring parameters independently of any specific widgets in the param user guide. To express interactivity entirely using Javascript without the need for a Python server take a look at the links user guide.

Parameters:

For layout and styling related parameters see the customization user guide.

Core

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

  • configuration (dict): A dictionary mapping used to specify tabulator options not explicitly exposed by panel.

  • editors (dict): A dictionary mapping from column name to a bokeh CellEditor instance or tabulator editor specification.

  • formatters (dict): A dictionary mapping from column name to a bokeh CellFormatter instance or tabulator formatter specification.

  • groupby (list): Groups rows in the table by one or more columns.

  • hierarchical (boolean, default=False): Whether to render multi-indexes as hierarchical index (note hierarchical must be enabled during instantiation and cannot be modified later)

  • hidden_columns (list): List of columns to hide.

  • layout (str): Describes the column layout mode with one of the following options 'fit_columns', 'fit_data', 'fit_data_stretch', 'fit_data_fill', 'fit_data_table'.

  • frozen_columns (list): List of columns to freeze, preventing them from scrolling out of frame. Column can be specified by name or index.

  • frozen_rows: (list): List of rows to freeze, preventing them from scrolling out of frame. Rows can be specified by positive or negative index.

  • page (int): Current page if pagination is enabled.

  • page_size (int): Number of rows on each page.

  • pagination (str, default=None): Set to ‘local’ or ‘remote’ to enable pagination; by default pagination is disabled with the value set to None.

  • selection (list): The currently selected rows.

  • selectable (boolean or str): Whether to allow selection of rows. Can be True, False, 'checkbox', 'checkbox-single' or 'toggle'.

  • selectable_rows (callable): A function that should return a list of integer indexes given a DataFrame indicating which rows may be selected.

  • show_index (boolean): Whether to show the index column.

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

  • theme (str): The CSS theme to apply (note that changing the theme will restyle all tables on the page).

  • titles (dict): A mapping from column name to a title to override the name with.

  • value (pd.DataFrame): The pandas DataFrame to display and edit

  • widths (dict): A dictionary mapping from column name to column width in the rendered table.

Display

  • disabled (boolean): Whether the widget is editable

  • name (str): The title of the widget

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:

df = pd.DataFrame({
    'int': [1, 2, 3],
    'float': [3.14, 6.28, 9.42],
    'str': ['A', 'B', 'C'],
    'bool': [True, False, True],
    'date': [dt.date(2019, 1, 1), dt.date(2020, 1, 1), dt.date(2020, 1, 10)]
}, index=[1, 2, 3])

df_widget = pn.widgets.Tabulator(df)
df_widget

Formatters

By default the widget will pick bokeh CellFormatter and CellEditor types appropriate to the dtype of the column. These may be overriden by explicit dictionaries mapping from the column name to the editor or formatter instance. For example below we create a SelectEditor instance to pick from four options in the str column and a NumberFormatter to customize the formatting of the float values:

from bokeh.models.widgets.tables import NumberFormatter, BooleanFormatter

bokeh_formatters = {
    'float': NumberFormatter(format='0.00000'),
    'bool': BooleanFormatter(),
}

pn.widgets.Tabulator(df, formatters=bokeh_formatters)

The list of valid Bokeh formatters includes:

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:

tabulator_formatters = {
    'float': {'type': 'progress', 'max': 10},
    'bool': {'type': 'tickCross'}
}

pn.widgets.Tabulator(df, formatters=tabulator_formatters)

The list of valid Tabulator formatters can be found in the Tabulator documentation.

Editors

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:

from bokeh.models.widgets.tables import CheckboxEditor, NumberEditor, SelectEditor, DateEditor, TimeEditor

bokeh_editors = {
    'float': NumberEditor(),
    'bool': CheckboxEditor(),
    'str': SelectEditor(options=['A', 'B', 'C', 'D']),
}

pn.widgets.Tabulator(df[['float', 'bool', 'str']], editors=bokeh_editors)

Therefore it is often preferable to use one of the Tabulator editors directly:

from bokeh.models.widgets.tables import CheckboxEditor, NumberEditor, SelectEditor

bokeh_editors = {
    'float': {'type': 'number', 'max': 10, 'step': 0.1},
    'bool': {'type': 'tickCross', 'tristate': True, 'indeterminateValue': None},
    'str': {'type': 'autocomplete', 'values': True}
}

pn.widgets.Tabulator(df[['float', 'bool', 'str']], editors=bokeh_editors)

Column layouts

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.

Manual column widths

To manually adjust column widths provide explicit widths for each of the columns:

custom_df = pd._testing.makeMixedDataFrame()

pn.widgets.Tabulator(custom_df, widths={'index': 70, 'A': 50, 'B': 50, 'C': 70, 'D': 130})

You can also declare a single width for all columns this way:

pn.widgets.Tabulator(custom_df, widths=130)

Autosize columns

To automatically adjust the columns dependending on their content set layout='fit_data':

pn.widgets.Tabulator(custom_df, layout='fit_data', width=400)

To ensure that the table fits all the data but also stretches to fill all the available space, set layout='fit_data_stretch':

pn.widgets.Tabulator(custom_df, layout='fit_data_stretch', width=400)

The 'fit_data_fill' option on the other hand won’t stretch the last column but still fill the space:

pn.widgets.Tabulator(custom_df, layout='fit_data_fill', width=400)

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:

pn.widgets.Tabulator(custom_df, layout='fit_data_table')

Equal size

The simplest option is simply to allocate each column equal amount of size:

pn.widgets.Tabulator(custom_df, layout='fit_columns', width=650)

Styling

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

Here we will demonstrate with a simple example, starting with a basic table:

style_df = pd.DataFrame(np.random.randn(10, 5), columns=list('ABCDE'))
styled = pn.widgets.Tabulator(style_df, page_size=5)

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:

def color_negative_red(val):
    """
    Takes a scalar and returns a string with
    the css property `'color: red'` for negative
    strings, black otherwise.
    """
    color = 'red' if val < 0 else 'black'
    return 'color: %s' % color

def highlight_max(s):
    '''
    highlight the maximum in a Series yellow.
    '''
    is_max = s == s.max()
    return ['background-color: yellow' if v else '' for v in is_max]

styled.style.applymap(color_negative_red).apply(highlight_max)

styled

Theming

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:

pn.widgets.Tabulator.theme = 'default'

For a full list of themes see the Tabulator documentation, however the default themes include:

  • 'simple'

  • 'default'

  • 'midnight'

  • 'site'

  • 'modern'

  • 'bootstrap'

  • 'bootstrap4'

  • 'materialize'

  • 'semantic-ui'

  • 'bulma'

Selection

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:

sel_df = pd.DataFrame(np.random.randn(10, 5), columns=list('ABCDE'))

select_table = pn.widgets.Tabulator(sel_df, selection=[0, 3, 7])
select_table

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:

select_table.selection = [1, 4, 9]

select_table.selected_dataframe
A B C D E
1 -0.714669 0.831580 -0.843149 -1.190616 -0.644210
4 0.591138 1.691430 0.597582 -2.243417 0.956677
9 -0.084394 0.377717 0.368277 -0.840544 1.252494

The selectable parameter declares how the selections work.

  • True: Selects rows on click. To select multiple use Ctrl-select, to select a range use Shift-select

  • False: Disables selection

  • 'checkbox': Adds a column of checkboxes to toggle selections

  • 'checkbox-single': Same as 'checkbox' but disables (de)select-all in the header

  • 'toggle': Selection toggles when clicked

pn.widgets.Tabulator(sel_df, selection=[0, 3, 7], selectable='checkbox')

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:

pn.widgets.Tabulator(sel_df, selectable_rows=lambda df: list(range(0, len(df), 2)))

Freezing rows and columns

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.

Frozen columns

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:

wide_df = pd._testing.makeCustomDataframe(10, 10, r_idx_names=['index'])

pn.widgets.Tabulator(wide_df, frozen_columns=['index'], width=400)

Frozen rows

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):

date_df = pd._testing.makeTimeDataFrame().iloc[:10]
agg_df = pd.concat([date_df, date_df.median().to_frame('Median').T, date_df.mean().to_frame('Mean').T])
agg_df.index= agg_df.index.map(str)

pn.widgets.Tabulator(agg_df, frozen_rows=[-2, -1], width=400)

Grouping

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:

pn.widgets.Tabulator(date_df, groups={'Group 1': ['A', 'B'], 'Group 2': ['C', 'D']})

Groupby

In addition to grouping columns we can also group rows by the values along one or more columns:

from bokeh.sampledata.autompg import autompg

pn.widgets.Tabulator(autompg, groupby=['yr', 'origin'], height=240)

Hierarchical Multi-index

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.

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’):

from bokeh.sampledata.population import data as population_data 

pop_df = population_data[population_data.Year == 2020].set_index(['Location', 'AgeGrp', 'Sex'])[['Value']]

pn.widgets.Tabulator(value=pop_df, hierarchical=True, aggregators={'Sex': 'sum', 'AgeGrp': 'sum'}, height=400)

Filtering

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.

Constant and Widget filters

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 parameters. 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:

  • scalar: Filters by checking for equality

  • tuple: A tuple will be interpreted as range.

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

As an example we will create a DataFrame with some data of mixed types:

mixed_df = pd._testing.makeMixedDataFrame()
filter_table = pn.widgets.Tabulator(mixed_df)
filter_table

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:

filter_table.add_filter((0, 3), 'A')

Next we add dynamic widget based filter, a RangeSlider which allows us to further narrow down the data along the 'A' column:

slider = pn.widgets.RangeSlider(start=0, end=3, name='A Filter')
filter_table.add_filter(slider, 'A')

Lastly we will add a MultiSelect filter along the 'C' column:

select = pn.widgets.MultiSelect(options=['foo1', 'foo2', 'foo3', 'foo4', 'foo5'], name='C Filter')
filter_table.add_filter(select, 'C')

Now let’s display the table alongside the widget based filters:

pn.Row(
    pn.Column(slider, select),
    filter_table
)

Function based filtering

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.

import sqlite3

from bokeh.sampledata.movies_data import movie_path

con = sqlite3.Connection(movie_path)

movies_df = pd.read_sql('SELECT Title, Year, Genre, Director, Writer, imdbRating from omdb', con)
movies_df = movies_df[~movies_df.Director.isna()]

movies_table = pn.widgets.Tabulator(movies_df, pagination='remote', layout='fit_columns', width=800)

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 uses tests whether the string or regex is contained in the ‘Director’ column of a listing of thousands of movies:

director_filter = pn.widgets.TextInput(name='Director filter', value='Chaplin')

def contains_filter(df, pattern, column):
    if not pattern:
        return df
    return df[df[column].str.contains(pattern)]
    
movies_table.add_filter(pn.bind(contains_filter, pattern=director_filter, column='Director'))    

pn.Row(director_filter, movies_table)

Downloading

The Tabulator also supports triggering a download of the data as a CSV or JSON file dependending on the filename. The download can be triggered with the .download() method, which optionally accepts the filename as the first argument.

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:

download_df = pd.DataFrame(np.random.randn(10, 5), columns=list('ABCDE'))

download_table = pn.widgets.Tabulator(download_df)

filename, button = download_table.download_menu(
    text_kwargs={'name': 'Enter filename', 'value': 'default.csv'},
    button_kwargs={'name': 'Download table'}
)

pn.Row(
    pn.Column(filename, button),
    download_table
)

Streaming

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:

stream_df = pd.DataFrame(np.random.randn(10, 5), columns=list('ABCDE'))

stream_table = pn.widgets.Tabulator(stream_df, layout='fit_columns', width=450)
stream_table

As example, we will schedule a periodic callback that streams new data every 1000ms (i.e. 1s) five times in a row:

def stream_data(follow=True):
    stream_df = pd.DataFrame(np.random.randn(10, 5), columns=list('ABCDE'))
    stream_table.stream(stream_df, follow=follow)

pn.state.add_periodic_callback(stream_data, period=1000, count=5)

PeriodicCallback(callback=<function stream_data at 0x7f98e52533b0>, count=5, name='PeriodicCallback03066', period=1000, running=True, timeout=None)

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:

stream_data(follow=False)

Patching

In certain cases we don’t want to update the table with new data but just patch existing data.

patch_table = pn.widgets.Tabulator(df[['int', 'float', 'str', 'bool']])
patch_table

The easiest way to patch the data is by supplying a dictionary as the patch value. The dictionary should have the following structure:

{
    column: [
        (index: int or slice, value),
        ...
    ],
    ...
}

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:

patch_table.patch({
    'bool': [
        (0, False),
        (2, False)
    ],
    'int': [
        (slice(0, 2), [3, 2])
    ]
})

Static Configuration

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.
This dictionary can be seen as a base dictionary which the tabulator object fills and passes to the Tabulator javascript-library.

As an example, we can turn off sorting and resizing of columns by disabling the headerSort and resizableColumn options.

df = pd.DataFrame({
    'int': [1, 2, 3],
    'float': [3.14, 6.28, 9.42],
    'str': ['A', 'B', 'C'],
    'bool': [True, False, True],
    'date': [dt.date(2019, 1, 1), dt.date(2020, 1, 1), dt.date(2020, 1, 10)]
}, index=[1, 2, 3])

df_widget = pn.widgets.Tabulator(df, configuration={"headerSort": False, "resizableColumns": False})
df_widget.servable()

These and other available tabulator options are listed at http://tabulator.info/docs/4.9/options.
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). Additionally it should be noted that the configuration parameter is not responsive so it can only be set at instantiation time.

This web page was generated from a Jupyter notebook and not all interactivity will work on this website. Right click to download and run locally for full Python-backed interactivity.

Download this notebook from GitHub (right-click to download).

Statictext Terminal