Set column names from callback Plotly Dash

Question

I'm trying to create a Data table which shows so aggregate result from the core table (df). For that I am using Pandas, Dash and Plotly as follows:

import pandas as pd
import plotly.graph_objects as go
import dash
import dash_core_components as dcc
import dash_html_components as html
from dash_html_components import Div
from dash_table import DataTable
from dash.dependencies import Input, Output

df = pd.DataFrame([['1', '2021-01-31', 'category_1', 20],
                   ['1', '2021-01-31', 'category_3', 12],
                   ['1', '2021-02-28', 'category_1', 35],
                   ['1', '2021-02-28', 'category_2', 17],
                   ['1', '2021-02-28', 'category_3', 35],
                   ['1','2021-03-31', 'category_1', 12],
                   ['1','2021-03-31', 'category_2', 58],
                   ['1','2021-03-31', 'category_3', 23],
                   ['2', '2021-01-31', 'category_1', 29],
                   ['2', '2021-01-31', 'category_2', 66],
                   ['2', '2021-01-31', 'category_3', 22],
                   ['2', '2021-02-28', 'category_1', 53],
                   ['2', '2021-02-28', 'category_2', 71],
                   ['2', '2021-02-28', 'category_3', 32],
                   ['2','2021-03-31', 'category_1', 19],
                   ['2','2021-03-31', 'category_2', 2],
                   ['2','2021-03-31', 'category_3', 99],
                   ['3', '2021-02-28', 'category_1', 53],
                   ['3', '2021-02-28', 'category_2', 71],
                   ['3','2021-03-31', 'category_1', 19],
                   ['3','2021-03-31', 'category_2', 2],
                   ['3','2021-03-31', 'category_3', 99],
                   ['3','2021-03-31', 'category_4', 39]],
                   columns=['Account', 'Date', 'category', 'Amount'])


external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']

app = dash.Dash(__name__, external_stylesheets=external_stylesheets)

app.layout = html.Div([
                        dcc.Input(id="account", type="string", placeholder="Enter Account"),
                        DataTable(id='figure_1',
                        style_cell_conditional=[{'if': {'column_id': c}, 'textAlign': 'center'} for c in [0, 1, 2, 3, 4]],
                        style_as_list_view=True,
                        fill_width=True,
                        style_cell={'font-size': '12px'},
                        style_header={'display': 'none'},
                        style_table={'height': '395px', 'overflowY': 'auto'})])
                                       

@app.callback(dash.dependencies.Output('figure_1',  'data'),
              [dash.dependencies.Input('account', 'value')])

def update_figura_2(account):
    df_query = df[df['Account'] == account].copy()
    df_query = df_query.groupby(['Account', 'Date', 'category']).agg({'Amount': 'sum'}).reset_index().pivot(values='Amount', columns='Date')
    df_query.index =  df.loc[df['Account'] == account, 'category'].copy()
    df_query.fillna(0, inplace=True)
    return df_query.to_dict(orient='records')


if __name__ == '__main__':
    app.run_server(debug=False)

So the output i'd like to get:

However, when I run the code I get an empty Dataframe:

I suspect it has to be something with the callback, but I don't know what!

Should I define the columns parameter in the DataTable? If so, how could I get the columns of the callback?

Answer 1

I found a workaround where you don't have to build a table through it's html components manually as Derek's answer.

import pandas as pd
import plotly.graph_objects as go
import dash
import dash_core_components as dcc
import dash_html_components as html
from dash_html_components import Div
from dash_table import DataTable
from dash.dependencies import Input, Output

df = pd.DataFrame([['1', '2021-01-31', 'category_1', 20],
                   ['1', '2021-01-31', 'category_3', 12],
                   ['1', '2021-02-28', 'category_1', 35],
                   ['1', '2021-02-28', 'category_2', 17],
                   ['1', '2021-02-28', 'category_3', 35],
                   ['1','2021-03-31', 'category_1', 12],
                   ['1','2021-03-31', 'category_2', 58],
                   ['1','2021-03-31', 'category_3', 23],
                   ['2', '2021-01-31', 'category_1', 29],
                   ['2', '2021-01-31', 'category_2', 66],
                   ['2', '2021-01-31', 'category_3', 22],
                   ['2', '2021-02-28', 'category_1', 53],
                   ['2', '2021-02-28', 'category_2', 71],
                   ['2', '2021-02-28', 'category_3', 32],
                   ['2','2021-03-31', 'category_1', 19],
                   ['2','2021-03-31', 'category_2', 2],
                   ['2','2021-03-31', 'category_3', 99],
                   ['3', '2021-02-28', 'category_1', 53],
                   ['3', '2021-02-28', 'category_2', 71],
                   ['3','2021-03-31', 'category_1', 19],
                   ['3','2021-03-31', 'category_2', 2],
                   ['3','2021-03-31', 'category_3', 99],
                   ['3','2021-03-31', 'category_4', 39]],
                   columns=['Account', 'Date', 'category', 'Amount'])


external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']

app = dash.Dash(__name__, external_stylesheets=external_stylesheets)

app.layout = html.Div([
                        dcc.Input(id="account", type="string", placeholder="Enter Account"),
                        html.Div(id='figure_1')
])

@app.callback(Output('figure_1', 'children'),
             [Input('account', 'value')])
def update_figura_2(account):
    df_query = df[df['Account'] == account].copy()
    df_query = df_query.groupby(['Account', 'Date', 'category']).agg({'Amount': 'sum'}).reset_index().pivot(values='Amount', columns='Date')
    df_query.index =  df.loc[df['Account'] == account, 'category'].copy()
    df_query.fillna(0, inplace=True)
    return [DataTable(columns=[{"name": i, "id": i} for i in df_query.columns],
                      data=df_query.to_dict('records'),                     
                      style_as_list_view=True,
                      fill_width=True,
                      style_cell={'font-size': '12px'},
                      style_header={'display': 'none'},
                      style_table={'height': '395px', 'overflowY': 'auto'})]


if __name__ == '__main__':
    app.run_server(debug=False)

The logic in this is to create the DataTable figure inside the callback and returning it as a figure's child, so the returned figure will be filtered by account with the columns/values you needed.

Answer 2

If you look at the documentation for the DataTable object, there are arguments for the columns and data but I could not successfully modify both of these attributes using a callback (if I figure this out, I will edit my answer).

I tried using the following callback to modify both the columns and data of the DataTable object:

@app.callback([Output('datatable', 'columns'), Output('datatable', 'data')],
    [Input('account', 'value')])
def update_table(account):
...
return df_query

... but I wasn't able to get this to work: even though no errors are thrown, the table won't display. My guess is that columns cannot be modified once the DataTable object is created, and this is why the callback fails to work as intended.

Instead, we can build the table from scratch using a previous answer I wrote as a template, wrapping the entire table in a html.Div container.

Since the DataFrame df_query resulting from each user input are indexed by category, we need to be careful about how we construct our table using html.Tr and html.Th objects from the dash_html_components library. We can iterate through each row of the DataFrame, adding the index value, followed by the remaining values of the row.

import pandas as pd
import plotly.graph_objects as go
import dash
import dash_core_components as dcc
import dash_html_components as html
from dash_html_components import Div
import dash_table_experiments as dt
from dash_table import DataTable
from dash.dependencies import Input, Output

df = pd.DataFrame([['1', '2021-01-31', 'category_1', 20],
                   ['1', '2021-01-31', 'category_3', 12],
                   ['1', '2021-02-28', 'category_1', 35],
                   ['1', '2021-02-28', 'category_2', 17],
                   ['1', '2021-02-28', 'category_3', 35],
                   ['1','2021-03-31', 'category_1', 12],
                   ['1','2021-03-31', 'category_2', 58],
                   ['1','2021-03-31', 'category_3', 23],
                   ['2', '2021-01-31', 'category_1', 29],
                   ['2', '2021-01-31', 'category_2', 66],
                   ['2', '2021-01-31', 'category_3', 22],
                   ['2', '2021-02-28', 'category_1', 53],
                   ['2', '2021-02-28', 'category_2', 71],
                   ['2', '2021-02-28', 'category_3', 32],
                   ['2','2021-03-31', 'category_1', 19],
                   ['2','2021-03-31', 'category_2', 2],
                   ['2','2021-03-31', 'category_3', 99],
                   ['3', '2021-02-28', 'category_1', 53],
                   ['3', '2021-02-28', 'category_2', 71],
                   ['3','2021-03-31', 'category_1', 19],
                   ['3','2021-03-31', 'category_2', 2],
                   ['3','2021-03-31', 'category_3', 99],
                   ['3','2021-03-31', 'category_4', 39]],
                   columns=['Account', 'Date', 'category', 'Amount'])

## construct a table with variable number of columns
def generate_table(df, max_rows=100):
    return html.Table(
        # Header
        [html.Tr([html.Th(df.columns.name)] + [html.Th(col) for col in df.columns])] +

        # Body
        [html.Tr([html.Th(df.index.name)])] + 
        [html.Tr([html.Td(df.index[i])] + [ 
            html.Td(df.iloc[i][col]) for col in df.columns
        ]) for i in range(min(len(df), max_rows))]
    )

external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']

app = dash.Dash(__name__, external_stylesheets=external_stylesheets)

app.layout = html.Div(
    children=[
    dcc.Input(id="account", type="text", placeholder="Enter Account"),
    html.Div(id='table-container')
    ])                   

@app.callback(Output('table-container', 'children'),
    [Input('account', 'value')])
def update_table(account):
    df_query = df[df['Account'] == account].copy()
    df_query = df_query.groupby(['Account', 'Date', 'category']).agg({'Amount': 'sum'}).reset_index().pivot(values='Amount', columns='Date')
    df_query.index =  df.loc[df['Account'] == account, 'category'].copy()
    df_query.fillna(0, inplace=True)
    # print(df_query.to_dict(orient='records'))
    return generate_table(df_query)

if __name__ == '__main__':
    app.run_server(debug=True)

To make the table look a bit nicer, you can use some css to alternate row colors. In the same directory that you are running your dash app python file from, create a folder called assets and create a new file called style.css with the following contents:

tr:nth-child(even) {background: #CCC}
tr:nth-child(odd) {background: #FFF}

Here is the result: