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

np.random.seed(7)
pn.extension('tabulator')

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)],
    'datetime': [dt.datetime(2019, 1, 1, 10), dt.datetime(2020, 1, 1, 12), dt.datetime(2020, 1, 10, 13)]
}, index=[1, 2, 3])

df_widget = pn.widgets.Tabulator(df, buttons={'Print': "<i class='fa fa-print'></i>"})
df_widget

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

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

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

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

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

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

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

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

tabulator_editors = {
    'int': None,
    'float': {'type': 'number', 'max': 10, 'step': 0.1},
    'bool': {'type': 'tickCross', 'tristate': True, 'indeterminateValue': None},
    'str': {'type': 'list', 'valuesLookup': True},
    'date': 'date',
    'datetime': 'datetime'
}

edit_table = pn.widgets.Tabulator(df, editors=tabulator_editors)

edit_table

edit_table.on_edit(lambda e: print(e.column, e.row, e.old, e.value))

options = {
    "A": ["A.1", "A.2", "A.3", "A.4", "A.5"],
    "B": {
        "1": ["B1.1", "B1.2", "B1.3"],
        "2": ["B2.1", "B2.2", "B2.3"],
        "3": ["B3.1", "B3.2", "B3.3"],
    },
}
tabulator_editors = {
    "0": {"type": "list", "values": ["A", "B"]},
    "1": {"type": "list", "values": [1, 2, 3]},
    "Nested Selection": {"type": "nested", "options": options, "lookup_order": ["0", "1"]},
}

nested_df = pd.DataFrame({"0": ["A", "B", "A"], "1": [1, 2, 3], "Nested Selection": [None, None, None]})
nested_table = pn.widgets.Tabulator(nested_df, editors=tabulator_editors, show_index=False)
nested_table

def clear_nested_column(event):
    if event.column in ["0", "1"]:
        nested_table.patch({"2": [(event.row, None)]})

nested_table.on_edit(clear_nested_column)

custom_df = df.iloc[:3, :]

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

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

pn.widgets.Tabulator(custom_df, widths={'index': '5%', 'A': '15%', 'B': '15%', 'C': '25%', 'D': '40%'}, sizing_mode='stretch_width')

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

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

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

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

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

pn.widgets.Tabulator(df.iloc[:, :2], header_align='center', text_align={'int': 'center', 'float': 'left'}, widths=150)

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

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.map(color_negative_red).apply(highlight_max)

styled

gradient_table = pn.widgets.Tabulator(style_df)
gradient_table.style.text_gradient(cmap="RdYlGn", subset=["B", "C"])
gradient_table.style.background_gradient(cmap="RdYlGn", subset=["D", "E"])
gradient_table

pn.widgets.Tabulator(df, theme='bootstrap5', theme_classes=['thead-dark', 'table-sm'])

pn.widgets.Tabulator(df, theme='bootstrap5', stylesheets=[":host .tabulator {font-size: 10px;}"])

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

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

select_table.selection = [1]

select_table.selected_dataframe

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

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

def click(event):
    print(f'Clicked cell in {event.column!r} column, row {event.row!r} with value {event.value!r}')

select_table.on_click(click) 
# Optionally we can also limit the callback to a specific column
# select_table.on_click(click, column='A') 

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

pn.widgets.Tabulator(df, frozen_columns={'index': 'left', 'float': 'right'}, width=400)

date_df = df.set_index('date').iloc[:5, :2]
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], height=200)

from bokeh.sampledata.periodic_table import elements

periodic_df = elements[['atomic number', 'name', 'atomic mass', 'metal', 'year discovered']].set_index('atomic number')

content_fn = lambda row: pn.pane.HTML(
    f'<iframe src="https://en.wikipedia.org/wiki/{row["name"]}?printable=yes" width="100%" height="200px"></iframe>',
    sizing_mode='stretch_width'
)

periodic_table = pn.widgets.Tabulator(
    periodic_df, height=350, layout='fit_columns', sizing_mode='stretch_width',
    row_content=content_fn, embed_content=True
)

periodic_table

periodic_table.expanded

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

from bokeh.sampledata.autompg import autompg

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

from bokeh.sampledata.autompg import autompg_clean as autompg_df

autompg_df = autompg_df.set_index(["origin", "yr", "mfr"])
autompg_df.head(3)

pn.widgets.Tabulator(value=autompg_df, hierarchical=True, aggregators={"origin": "mean", "yr": "mean"}, height=200)

nested_aggs = {"origin": {"mpg": "mean", "hp": "max"}, "yr": {"mpg": "mean", "hp": "max"}}
pn.widgets.Tabulator(value=autompg_df[["mpg", "hp"]], hierarchical=True, aggregators=nested_aggs, height=200)

large_df = pd.DataFrame({'A': np.random.rand(10000)})
pn.widgets.Tabulator(large_df, pagination='remote', page_size=3)

medium_df = pd.DataFrame({'A': np.random.rand(1000)})
pn.widgets.Tabulator(medium_df, pagination='local', page_size=3)

filter_table = pn.widgets.Tabulator(df)
filter_table

filter_table.add_filter((1, 2), 'int')

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

select = pn.widgets.MultiSelect(options=list('ABC'), name='str Filter')
filter_table.add_filter(select, 'str')

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

select.value = ['A', 'B']
filter_table.current_view

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, Rating, imdbRating from omdb', con)
movies_df = movies_df[~movies_df.Director.isna()]

movies_table = pn.widgets.Tabulator(movies_df, pagination='remote', page_size=4)

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)

tabulator_editors = {
    'float': {'type': 'number', 'max': 10, 'step': 0.1},
    'bool': {'type': 'tickCross', 'tristate': True, 'indeterminateValue': None},
    'str': {'type': 'list', 'valuesLookup': True},
}

header_filter_table = pn.widgets.Tabulator(
    df[['float', 'bool', 'str']], height=140, width=400, layout='fit_columns',
    editors=tabulator_editors, header_filters=True
)
header_filter_table

movie_filters = {
    'Title': {'type': 'input', 'func': 'like', 'placeholder': 'Enter title'},
    'Year': {'placeholder': 'Enter year'},
    'Genre': {'type': 'input', 'func': 'like', 'placeholder': 'Enter genre'},
    'Director': {'type': 'input', 'func': 'like', 'placeholder': 'Enter director'},
    'Writer': {'type': 'input', 'func': 'like', 'placeholder': 'Enter writer'},
    'Rating': {'type': 'list', 'func': 'in', 'valuesLookup': True, 'sort': 'asc', 'multiselect': True},
    'imdbRating': {'type': 'number', 'func': '>=', 'placeholder': 'Enter minimum rating'},
}

filter_table = pn.widgets.Tabulator(
    movies_df.iloc[:200], pagination='local', layout='fit_columns', page_size=4, sizing_mode='stretch_width',
    header_filters=movie_filters
)
filter_table

download_df = pd.DataFrame(np.random.randn(4, 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
)

button_table = pn.widgets.Tabulator(df, buttons={
    'print': '<i class="fa fa-print"></i>',
    'check': '<i class="fa fa-check"></i>'
})

string = pn.widgets.StaticText()

button_table.on_click(
    lambda e: string.param.update(value=f'Clicked {e.column!r} on row {e.row}')
)

pn.Row(button_table, string)

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

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

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

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

stream_data(follow=False)

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

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

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

pn.widgets.Tabulator(df, configuration={
    'clipboard': True,
    'rowHeight': 50,
    'columnDefaults': {
        'headerSort': False,
    },
})