DataTable - Julia Callbacks

Backend Paging

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)
index
continent
country
gdpPercap
lifeExp
pop
1
Asia
Afghanistan
974.5803384
43.828
31889923
2
Europe
Albania
5937.029525999999
76.423
3600523
3
Africa
Algeria
6223.367465
72.301
33333216
4
Africa
Angola
4797.231267
42.731
12420476
5
Americas
Argentina
12779.37964
75.32
40301927
1

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

Backend Paging and Page Numbers

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)
index
continent
country
gdpPercap
lifeExp
pop
1
Asia
Afghanistan
974.5803384
43.828
31889923
2
Europe
Albania
5937.029525999999
76.423
3600523
3
Africa
Algeria
6223.367465
72.301
33333216
4
Africa
Angola
4797.231267
42.731
12420476
5
Americas
Argentina
12779.37964
75.32
40301927
1
/
20

Page count:

Backend Paging with Sorting

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)
index
continent
country
gdpPercap
lifeExp
pop
1
Asia
Afghanistan
974.5803384
43.828
31889923
2
Europe
Albania
5937.029525999999
76.423
3600523
3
Africa
Algeria
6223.367465
72.301
33333216
4
Africa
Angola
4797.231267
42.731
12420476
5
Americas
Argentina
12779.37964
75.32
40301927
1

Backend Paging with Multi Column Sorting

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, dff)
                elseif operator == "le"
                    dff = DataFrames.filter(r -> 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)
continent
country
gdpPercap
lifeExp
pop
Asia
Afghanistan
974.5803384
43.828
31889923
Europe
Albania
5937.029525999999
76.423
3600523
Africa
Algeria
6223.367465
72.301
33333216
Africa
Angola
4797.231267
42.731
12420476
Americas
Argentina
12779.37964
75.32
40301927
1

Backend Paging with Filtering

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:

  • Enter eq Asia in the "continent" column
  • Enter > 5000 in the "gdpPercap" column
  • Enter < 80 in the lifeExp column

Note that unlike the front-end filtering, our backend filtering expression language doesn't require or support num() 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, dff)
                elseif operator == "le"
                    dff = DataFrames.filter(r -> 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)
continent
country
gdpPercap
lifeExp
pop
Asia
Afghanistan
974.5803384
43.828
31889923
Europe
Albania
5937.029525999999
76.423
3600523
Africa
Algeria
6223.367465
72.301
33333216
Africa
Angola
4797.231267
42.731
12420476
Americas
Argentina
12779.37964
75.32
40301927
1

Backend Paging with Filtering and Multi-Column Sorting

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)
continent
country
gdpPercap
lifeExp
pop
Asia
Afghanistan
974.5803384
43.828
31889923
Europe
Albania
5937.029525999999
76.423
3600523
Africa
Algeria
6223.367465
72.301
33333216
Africa
Angola
4797.231267
42.731
12420476
Americas
Argentina
12779.37964
75.32
40301927
1

Connecting Backend Paging with a Graph

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, dff)
                elseif operator == "le"
                    dff = DataFrames.filter(r -> 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)
continent
country
gdpPercap
lifeExp
pop
Asia
Afghanistan
974.5803384
43.828
31889923
Europe
Albania
5937.029525999999
76.423
3600523
Africa
Algeria
6223.367465
72.301
33333216
Africa
Angola
4797.231267
42.731
12420476
Americas
Argentina
12779.37964
75.32
40301927
Oceania
Australia
34435.367439999995
81.235
20434176
Europe
Austria
36126.4927
79.829
8199783
Asia
Bahrain
29796.04834
75.635
708573
Asia
Bangladesh
1391.253792
64.062
150448339
Europe
Belgium
33692.60508
79.441
10392226
Africa
Benin
1441.284873
56.728
8078314
Americas
Bolivia
3822.137084
65.554
9119152
Europe
Bosnia and Herzegovina
7446.298803
74.852
4552198
Africa
Botswana
12569.85177
50.728
1639131
Americas
Brazil
9065.800825
72.39
190010647
Europe
Bulgaria
10680.79282
73.005
7322858
Africa
Burkina Faso
1217.032994
52.295
14326203
Africa
Burundi
430.0706916
49.58
8390505
Asia
Cambodia
1713.778686
59.723
14131858
Africa
Cameroon
2042.09524
50.43
17696293
1
AfghanistanAlgeriaArgentinaAustriaBangladeshBeninBosnia and HerzegovinaBrazilBurkina FasoCambodia050M100M150M200M
AfghanistanAlbaniaAlgeriaAngolaArgentinaAustraliaAustriaBahrainBangladeshBelgiumBeninBoliviaBosnia and HerzegovinaBotswanaBrazilBulgariaBurkina FasoBurundiCambodiaCameroon020406080
AfghanistanAlbaniaAlgeriaAngolaArgentinaAustraliaAustriaBahrainBangladeshBelgiumBeninBoliviaBosnia and HerzegovinaBotswanaBrazilBulgariaBurkina FasoBurundiCambodiaCameroon010k20k30k