With backend paging, we can load data into our table progressively.
Instead of loading all of the data at once, we’ll only load data
as the user requests it when they click on the “previous” and “next”
buttons.
Since backend paging integrates directly with your Dash callbacks, you can
load your data from any Python data source.
using Dash
using CSV, DataFrames
df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
df[!," index"] =1:nrow(df)
app = dash()
PAGE_SIZE = 5
app.layout = dash_datatable(
id="datatable-paging",
columns=[
Dict("name" => i, "id" => i) for i in sort(names(df))
],
page_current=0,
page_size=PAGE_SIZE,
page_action="custom"
)
callback!(app,
Output("datatable-paging", "data"),
Input("datatable-paging", "page_current"),
Input("datatable-paging", "page_size")
) do page_current, page_size
return Dict.(pairs.(eachrow(
df[(page_current*page_size+1):((page_current+ 1)*page_size), :]
)))
end
run_server(app, "0.0.0.0", debug=true)
With backend paging, we can have front-end sorting and filtering
but it will only filter and sort the data that exists on the page.
This should be avoided. Your users will expect
that sorting and filtering is happening on the entire dataset and,
with large pages, might not be aware that this is only occurring
on the current page.
Instead, we recommend implementing sorting and filtering on the
backend as well. That is, on the entire underlying dataset.
Note for returning users - changed property names:
- Sorted fields are now in sort_by
, not sorting_settings
- The filter string is now in filter
, not filtering_settings
The pagination menu includes the number of the current page and
the total page count. With native (i.e., frontend) pagination, the
page count is calculated by the table. However, when using backend
pagination, the data are served to the table through a callback;
this makes it impossible for the table to calculate the total page
count. As a consequence, the last-page navigation button is
disabled (although all of the other buttons, as well as the direct
navigation, are still functional).
To get around this, supply a value to the page_count
parameter
of the table. This will serve as the “last page”, which will
re-enable the last-page navigation button and be displayed in the
pagination menu. Please note that you will not be able to use the
pagination menu to navigate to a page that comes after the last
page specified by page_count
!
using Dash
using CSV, DataFrames
df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
df[!," index"] =1:nrow(df)
app = dash()
PAGE_SIZE = 5
app.layout = html_div([
dash_datatable(
id="datatable-paging-page-count",
columns=[
Dict("name" => i, "id" => i) for i in sort(names(df))
],
page_current=0,
page_size=PAGE_SIZE,
page_action="custom"
),
html_br(),
dcc_checklist(
id="datatable-use-page-count",
options=[
Dict("label"=> "Use page_count", "value"=> "True")
],
value=["True"]
),
"Page count:",
dcc_input(
id="datatable-page-count",
type="number",
min=1,
max=29,
value=20
)
])
callback!(app,
Output("datatable-paging-page-count", "data"),
Input("datatable-paging-page-count", "page_current"),
Input("datatable-paging-page-count", "page_size")
) do page_current, page_size
return Dict.(pairs.(eachrow(
df[(page_current*page_size+1):((page_current+ 1)*page_size), :]
)))
end
callback!(app,
Output("datatable-paging-page-count", "page_count"),
Input("datatable-use-page-count", "value"),
Input("datatable-page-count", "value")
) do use_page_count, page_count_value
if length(use_page_count) == 0 || page_count_value isa Nothing
return nothing
end
return page_count_value
end
run_server(app, "0.0.0.0", debug=true)
using Dash
using CSV, DataFrames
df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
df[!," index"] =1:nrow(df)
app = dash()
PAGE_SIZE = 5
app.layout = dash_datatable(
id="table-paging-and-sorting",
columns=[
Dict("name" => i, "id" => i) for i in sort(names(df))
],
page_current=0,
page_size=PAGE_SIZE,
page_action="custom",
sort_action="custom",
sort_mode="single",
sort_by=[]
)
callback!(app,
Output("table-paging-and-sorting", "data"),
Input("table-paging-and-sorting", "page_current"),
Input("table-paging-and-sorting", "page_size"),
Input("table-paging-and-sorting", "sort_by")
) do page_current, page_size, sort_by
if isempty(sort_by)
dff = df
else
if sort_by[1].direction == "asc"
dir = true
else
dir = false
end
dff = sort(df, [sort_by[1].column_id], rev=dir)
end
return Dict.(pairs.(eachrow(
dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
)))
end
run_server(app, "0.0.0.0", debug=true)
Multi-column sort allows you to sort by multiple columns.
This is useful when you have categorical columns with repeated
values and you’re interested in seeing the sorted values for
each category.
In this example, try sorting by continent and then any other column.
using Dash
using CSV, DataFrames
df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
df[!," index"] =1:nrow(df)
app = dash()
PAGE_SIZE = 5
app.layout = dash_datatable(
id="table-sorting-filtering",
columns=[
Dict("name" => i, "id" => i, "deletable" => true) for i in sort(names(df))
],
page_current=0,
page_size=PAGE_SIZE,
page_action="custom",
filter_action="custom",
filter_query="",
sort_action="custom",
sort_mode="multi",
sort_by=[]
)
operators = [["ge ", ">="],
["le ", "<="],
["lt ", "<"],
["gt ", ">"],
["ne ", "!="],
["eq ", "=="],
["contains "],
["datestartswith "]]
function split_filter_part(filter_part)
for operator_type in operators
for operator in operator_type
if occursin(operator,filter_part)
name_part, value_part = split(filter_part,operator,limit=2)
name = name_part[
findfirst(isequal('{'),
name_part)+1:findlast(isequal('}'), name_part)-1
]
value_part = strip(value_part)
v0 = value_part[1]
if (v0 == value_part[end] && v0 in ("'", '"', '`'))
value = replace(value_part[2: end], string("\\",v0) => v0)
else
try
value = Float64(value_part)
catch
value = value_part
end
end
return name, strip(operator_type[1]), value
end
end
end
end
callback!(app,
Output("table-sorting-filtering", "data"),
Input("table-sorting-filtering", "page_current"),
Input("table-sorting-filtering", "page_size"),
Input("table-sorting-filtering", "sort_by"),
Input("table-sorting-filtering", "filter_query")
) do page_current, page_size, sort_by, filter
filtering_expressions = split(filter," && ")
dff = df
for filter_part in filtering_expressions
if filter_part != ""
col_name, operator, filter_value = split_filter_part(filter_part)
if operator in ("eq", "ne", "lt", "le", "gt", "ge")
if operator == "eq"
dff = DataFrames.filter(r -> r[col_name]==filter_value, dff)
elseif operator == "ne"
dff = DataFrames.filter(r -> r[col_name]!=filter_value, dff)
elseif operator == "lt"
dff = DataFrames.filter(r -> r[col_name]<filter_value,> r[col_name]<=filter_value, dff)
elseif operator == "gt"
dff = DataFrames.filter(r -> r[col_name]>filter_value, dff)
else
dff = DataFrames.filter(r -> r[col_name]>=filter_value, dff)
end
elseif operator == "contains"
dff = DataFrames.filter(r -> occursin(filter_value,string(r[col_name])), dff)
else
dff = DataFrames.filter(r -> startswith(r[col_name],filter_value), dff)
end
end
end
if !isempty(sort_by)
dir = [(i.direction == "asc" ? true : false) for i in sort_by]
cid = [i.column_id for i in sort_by]
dff = sort(dff, cid, rev=dir)
end
if nrow(dff) > ((page_current+ 1)*page_size)
return Dict.(pairs.(eachrow(
dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
)))
else
return Dict.(pairs.(eachrow(dff)))
end
end
run_server(app, "0.0.0.0", debug=true)
DataTable’s front-end filtering has its own filtering expression
language.
Currently, backend filtering must parse the same filtering language.
If you write an expression that is not “valid” under the filtering
language, then it will not be passed to the backend.
This limitation will be removed in the future to allow you to
write your own expression query language.
In this example, we’ve written a Pandas backend for the filtering
language. It supports eq
, <
, and >
. For example, try:
eq Asia
in the “continent” column> 5000
in the “gdpPercap” column< 80
in the lifeExp
columnNote that unlike the front-end filtering, our backend filtering
expression language doesn’t require or supportnum()
or wrapping
items in double quotes ("
).
We will improve this syntax in the future,
follow dash-table#169
for more.
using Dash
using CSV, DataFrames
df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
df[!," index"] =1:nrow(df)
app = dash()
PAGE_SIZE = 5
app.layout = dash_datatable(
id="table-filtering",
columns=[
Dict("name" => i, "id" => i) for i in sort(names(df))
],
page_current=0,
page_size=PAGE_SIZE,
page_action="custom",
filter_action="custom",
filter_query=""
)
operators = [["ge ", ">="],
["le ", "<="],
["lt ", "<"],
["gt ", ">"],
["ne ", "!="],
["eq ", "=="],
["contains "],
["datestartswith "]]
function split_filter_part(filter_part)
for operator_type in operators
for operator in operator_type
if occursin(operator,filter_part)
name_part, value_part = split(filter_part,operator,limit=2)
name = name_part[
findfirst(isequal('{'),
name_part)+1:findlast(isequal('}'), name_part)-1
]
value_part = strip(value_part)
v0 = value_part[1]
if (v0 == value_part[end] && v0 in ("'", '"', '`'))
value = replace(value_part[2: end], string("\\",v0) => v0)
else
try
value = Float64(value_part)
catch
value = value_part
end
end
return name, strip(operator_type[1]), value
end
end
end
end
callback!(app,
Output("table-filtering", "data"),
Input("table-filtering", "page_current"),
Input("table-filtering", "page_size"),
Input("table-filtering", "filter_query")
) do page_current,page_size, filter
filtering_expressions = split(filter," && ")
dff = df
for filter_part in filtering_expressions
if filter_part != ""
col_name, operator, filter_value = split_filter_part(filter_part)
if operator in ("eq", "ne", "lt", "le", "gt", "ge")
if operator == "eq"
dff = DataFrames.filter(r -> r[col_name]==filter_value, dff)
elseif operator == "ne"
dff = DataFrames.filter(r -> r[col_name]!=filter_value, dff)
elseif operator == "lt"
dff = DataFrames.filter(r -> r[col_name]<filter_value,> r[col_name]<=filter_value, dff)
elseif operator == "gt"
dff = DataFrames.filter(r -> r[col_name]>filter_value, dff)
else
dff = DataFrames.filter(r -> r[col_name]>=filter_value, dff)
end
elseif operator == "contains"
dff = DataFrames.filter(r -> occursin(filter_value,string(r[col_name])), dff)
else
dff = DataFrames.filter(r -> startswith(r[col_name],filter_value), dff)
end
end
end
if nrow(dff) > ((page_current+ 1)*page_size)
return Dict.(pairs.(eachrow(
dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
)))
else
return Dict.(pairs.(eachrow(dff)))
end
end
run_server(app, "0.0.0.0", debug=true)
using Dash
using CSV, DataFrames
df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
df[!," index"] =1:nrow(df)
app = dash()
PAGE_SIZE = 5
app.layout = dash_datatable(
id="table-multicol-sorting",
columns=[
Dict("name" => i, "id" => i) for i in sort(names(df))
],
page_current=0,
page_size=PAGE_SIZE,
page_action="custom",
sort_action="custom",
sort_mode="multi",
sort_by=[]
)
callback!(app,
Output("table-multicol-sorting", "data"),
Input("table-multicol-sorting", "page_current"),
Input("table-multicol-sorting", "page_size"),
Input("table-multicol-sorting", "sort_by")
) do page_current, page_size, sort_by
if isempty(sort_by)
dff = df
else
dir = [(i.direction == "asc" ? true : false) for i in sort_by]
cid = [i.column_id for i in sort_by]
dff = sort(df, cid, rev=dir)
end
return Dict.(pairs.(eachrow(
dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
)))
end
run_server(app, "0.0.0.0", debug=true)
This final example ties it all together: the graph component
displays the current page of the data
.
using Dash
using DashBootstrapComponents
using CSV, DataFrames
df = CSV.read(download("https://raw.githubusercontent.com/plotly/datasets/master/gapminder2007.csv"), DataFrame)
df[!," index"] =1:nrow(df)
app = dash(external_stylesheets=[dbc_themes.BOOTSTRAP])
PAGE_SIZE = 20
app.layout = dbc_row([
dbc_col([
dash_datatable(
id="table-paging-with-graph",
columns=[
Dict("name" => i, "id" => i) for i in sort(names(df))
],
page_current=0,
page_size=PAGE_SIZE,
page_action="custom",
filter_action="custom",
filter_query="",
sort_action="custom",
sort_mode="multi",
sort_by=[]
)
], md=8)
dbc_col(
id="table-paging-with-graph-container", md=3
)
])
operators = [["ge ", ">="],
["le ", "<="],
["lt ", "<"],
["gt ", ">"],
["ne ", "!="],
["eq ", "=="],
["contains "],
["datestartswith "]]
function split_filter_part(filter_part)
for operator_type in operators
for operator in operator_type
if occursin(operator,filter_part)
name_part, value_part = split(filter_part,operator,limit=2)
name = name_part[
findfirst(isequal('{'),
name_part)+1:findlast(isequal('}'), name_part)-1
]
value_part = strip(value_part)
v0 = value_part[1]
if (v0 == value_part[end] && v0 in ("'", '"', '`'))
value = replace(value_part[2: end], string("\\",v0) => v0)
else
try
value = Float64(value_part)
catch
value = value_part
end
end
return name, strip(operator_type[1]), value
end
end
end
end
callback!(app,
Output("table-paging-with-graph", "data"),
Input("table-paging-with-graph", "page_current"),
Input("table-paging-with-graph", "page_size"),
Input("table-paging-with-graph", "sort_by"),
Input("table-paging-with-graph", "filter_query")
) do page_current, page_size, sort_by, filter
filtering_expressions = split(filter," && ")
dff = df
for filter_part in filtering_expressions
if filter_part != ""
col_name, operator, filter_value = split_filter_part(filter_part)
if operator in ("eq", "ne", "lt", "le", "gt", "ge")
if operator == "eq"
dff = DataFrames.filter(r -> r[col_name]==filter_value, dff)
elseif operator == "ne"
dff = DataFrames.filter(r -> r[col_name]!=filter_value, dff)
elseif operator == "lt"
dff = DataFrames.filter(r -> r[col_name]<filter_value,> r[col_name]<=filter_value, dff)
elseif operator == "gt"
dff = DataFrames.filter(r -> r[col_name]>filter_value, dff)
else
dff = DataFrames.filter(r -> r[col_name]>=filter_value, dff)
end
elseif operator == "contains"
dff = DataFrames.filter(r -> occursin(filter_value,string(r[col_name])), dff)
else
dff = DataFrames.filter(r -> startswith(r[col_name],filter_value), dff)
end
end
end
if !isempty(sort_by)
dir = [(i.direction == "asc" ? true : false) for i in sort_by]
cid = [i.column_id for i in sort_by]
dff = sort(dff, cid, rev=dir)
end
if nrow(dff) > ((page_current+ 1)*page_size)
return Dict.(pairs.(eachrow(
dff[(page_current*page_size+1):((page_current+ 1)*page_size), :]
)))
else
return Dict.(pairs.(eachrow(dff)))
end
end
callback!(app,
Output("table-paging-with-graph-container", "children"),
Input("table-paging-with-graph", "data")
) do rows
if rows isa Nothing
throw(PreventUpdate())
end
dff = DataFrame(rows)
return html_div(
[ dcc_graph(
id=column,
figure=Dict(
"data" => [
Dict(
"x" => dff[!,"country"],
"y" => (column in names(dff)) ? dff[!, column] : [],
"type" => "bar",
"marker" => Dict("color" => "#0074D9"),
)
],
"layout" => Dict(
"xaxis" => Dict("automargin" => true),
"yaxis" => Dict("automargin" => true),
"height" => 250,
"margin" => Dict("t" => 10, "l" => 10, "r" => 10),
),
),
)
for column in ["pop", "lifeExp", "gdpPercap"]
]
)
end
run_server(app, "0.0.0.0", debug=true)