Bring Drag & Drop to Dash with Dashboard Engine. 💫 Learn how at our next webinar!

Html.table([]) number of ids depending of the data

Hello everyone,

I’m working on my first application with Dash. I already posted a message previously and I got some help, thank you very much again.

For now, the app is displaying a table filtered by an input and then calculates some scores in two html.table during the app.callback steps. It was quite easy to develop this since the scores will be always displayed the same way, whatever the input is (each input has the same number of different scores).

Here’s my current issue. I want to display a third html.table. But, for this one, the number of records to display in the html.table depends of the number of row called by the input. I don’t know if what I say is clear or not so I put two screenshots at the end of this post. Sorry, it’s in French. As you can see if I choose the pizzas I got 6 ingredients, so I need 6 ids in my third html.table with the associated html.Th (“Jambon cuit”, “Gruyère”, etc). But if I choose the gâteaux et pâtisseries, then I need only 5 (and again, the html.Th should be set automatically).

Like I said it’s a little bit different from the two other tables I’ve got for now since for those ones the number of scores to calculate is fixed. The input is only used to make each calculation. I put a third screenshot with one example.

Could you give me an advice on how to make what I need? If I understood well, the calculation and algorithms are done in the app.callback steps, so I’m not sure what I should do in the layout to get what I want.

I also put my code at the end of this post. Thank you very much if you can give me an advice to follow.

Alpy



# =============================================================================
# Initialization
# =============================================================================

# Packages
# --------

# Pandas
import pandas as pd

# Numpy
import numpy as np

# Functools
from functools import reduce

# Datetime timedelta
from datetime import timedelta

import os
import datetime
import sys
import random

# Plotly Dash
import dash
import dash_core_components as dcc
import dash_html_components as html
import dash_table
from dash.dependencies import Input, Output
from dash_table import DataTable
from dash.exceptions import PreventUpdate

# Plotly
import plotly.express as px


# =============================================================================
# Data importation
# =============================================================================

# AGRIBALYSE.xlsx
agribalyse = pd.ExcelFile (r'AGRIBALYSE.xlsx')

# Detail ingrédient sheet
tmp_ingredients = pd.read_excel(agribalyse, r'Detail ingredient')

# Detail étapes sheet
tmp_etapes = pd.read_excel(agribalyse, r'Detail etape')


# =============================================================================
# Data management
# =============================================================================


# Ingredients sheet
# -----------------

# Abribalyse database with fill-in of all missing rows for products
ingredients=tmp_ingredients.fillna(method='ffill')

# List of columns to display in the application
ingredients_tab_data_total=ingredients[["Nom Français", "Groupe d'aliment", "Sous-groupe d'aliment", "Ingredients"]]
ingredients_tab_data = ingredients_tab_data_total[ingredients_tab_data_total["Ingredients"] != "Total"]

# List of available products
available_produtcs = ingredients['Nom Français'].unique()


# Details sheet
# -------------

tmp_etapes_2 = pd.DataFrame(tmp_etapes.reset_index())
tmp_etapes_2.reset_index(inplace = True) 
etapes = tmp_etapes_2[["Nom du Produit en Français", "DQR", "Agriculture_EF", "Transformation_EF", "Emballage_EF", "Transport_EF", "Supermarché et distribution_EF", "Consommation_EF", "Total_EF"]]


# =============================================================================
# Dash application
# =============================================================================

# Style sheet
# -----------

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


# Application call
# ----------------

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

app.layout = html.Div([
        html.Div([
                html.H1('Agribalyse'),
                html.Div('''
                         This is a database about food and ecology. The table below shows the products and ingredients.
                         '''),
                html.H1(' '),
                html.Div('''
                         Please choose the ingredient you want to get the ecological impact.
                         ''')
        ]),
        html.Div([
                dcc.Dropdown(
                        id='select_column',
                        options=[{'label':i, 'value': i} for i in available_produtcs],
                        value='Pizza jambon fromage',
                        #value=[],
                        #multi=True
                )
        ]),
        dash_table.DataTable(
            id='data_table',
            columns=[{"name": i, "id": i} for i in ingredients_tab_data.columns],
            data=ingredients_tab_data.to_dict("rows")
        ),
        html.Div([
                html.H1(" "),
                html.H2('''
                         The ecological results are displayed below:
                         '''),
                html.H1(' ')
        ]),
        html.Div([
                html.Table([
                    html.Tr([html.Th(['Indicateur']),                                           html.Th(['Mesure']),     html.Th(['Unité'])]),
                    html.Tr([html.Td(['Score unique EF']),                                      html.Td(id='I01'),       html.Td(['mPt/kg de produit'])]),
                    html.Tr([html.Td(['Changement climatique']),                                html.Td(id='I02'),       html.Td(['kg CO2 eq/kg de produit'])]),
                    html.Tr([html.Td(["Appauvrissement de la couche d'ozone"]),                 html.Td(id='I03'),       html.Td(['E-06 kg CVC11 eq/kg de produit'])]),
                    html.Tr([html.Td(['Rayonnements ionisants']),                               html.Td(id='I04'),       html.Td(['kBq U-235 eq/kg de produit'])]),
                    html.Tr([html.Td(["Formation photochimique d'ozone"]),                      html.Td(id='I05'),       html.Td(['E-03 kg NMVOC eq/kg de produit'])]),
                    html.Tr([html.Td(["Particules"]),                                           html.Td(id='I06'),       html.Td(['E-06 disease inc./kg de produit'])]),
                    html.Tr([html.Td(["Acidification terrestre et eaux douces"]),               html.Td(id='I07'),       html.Td(['mol H+ eq/kg de produit'])]),
                    html.Tr([html.Td(["Eutrophisation terreste"]),                              html.Td(id='I08'),       html.Td(['mol N eq/kg de produit'])]),
                    html.Tr([html.Td(["Eutrophisation eaux douces"]),                           html.Td(id='I09'),       html.Td(['E-03 kg P eq/kg de produit'])]),
                    html.Tr([html.Td(["Eutrophisation marine"]),                                html.Td(id='I10'),       html.Td(['E-03 kg N eq/kg de produit'])]),
                    html.Tr([html.Td(["Utilisation du sol"]),                                   html.Td(id='I11'),       html.Td(['Pt/kg de produit'])]),
                    html.Tr([html.Td(["Écotoxicité pour écosystèmes aquatiques d'eau douce"]),  html.Td(id='I12'),       html.Td(['CTUe/kg de produit'])]),
                    html.Tr([html.Td(["Épuisement des ressources eau"]),                        html.Td(id='I13'),       html.Td(['m3 depriv./kg de produit'])]),
                    html.Tr([html.Td(["Épuisement des ressources énergétiques"]),               html.Td(id='I14'),       html.Td(['MJ/kg de produit'])]),
                    html.Tr([html.Td(["Épuisement des ressources minéraux"]),                   html.Td(id='I15'),       html.Td(['E-06 kg Sb eq/kg de produit'])])
                ],className="six columns"),
                html.Table([
                    html.Tr([html.Th(['Impact par étapes du cycle de vie']),                    html.Th(['%'])]),
                    html.Tr([html.Td(['Agriculture']),                                          html.Td(id='ICV01')]),
                    html.Tr([html.Td(['Transformation']),                                       html.Td(id='ICV02')]),
                    html.Tr([html.Td(['Emballage']),                                            html.Td(id='ICV03')]),
                    html.Tr([html.Td(['Transport']),                                            html.Td(id='ICV04')]),
                    html.Tr([html.Td(["Supermarché et distribution"]),                          html.Td(id='ICV05')]),
                    html.Tr([html.Td(["Consommation"]),                                         html.Td(id='ICV06')])
                ],className="six columns")
    ],className="row")
])

app.css.append_css({
    'external_url': 'https://codepen.io/chriddyp/pen/bWLwgP.css'
})                
           
# Display the table
@app.callback(
        Output('data_table', 'data'),
        Input('select_column', 'value')
)
  

def update_table(product):
    data=ingredients_tab_data[ingredients_tab_data["Nom Français"] == product]
    #data=ingredients_tab_data[ingredients_tab_data["Nom Français"].isin(product)]
    return data.to_dict("rows")

# Display the scores
@app.callback(
    Output('I01', 'children'),
    Output('I02', 'children'),
    Output('I03', 'children'),
    Output('I04', 'children'),
    Output('I05', 'children'),
    Output('I06', 'children'),
    Output('I07', 'children'),
    Output('I08', 'children'),
    Output('I09', 'children'),
    Output('I10', 'children'),
    Output('I11', 'children'),
    Output('I12', 'children'),
    Output('I13', 'children'),
    Output('I14', 'children'),
    Output('I15', 'children'),
    Input('select_column', 'value')
)

def callback_score(product):
    #  Summary of scores
    tmp_score_0 = ingredients[ingredients["Ingredients"] != "Total"]
    tmp_score_1 = tmp_score_0[tmp_score_0["Nom Français"]== product]
    I01 = "{:0.2f}".format(tmp_score_1["Score unique EF (mPt/kg de produit)"].sum())
    I02 = "{:0.2f}".format(tmp_score_1["Changement climatique (kg CO2 eq/kg de produit)"].sum())
    I03 = "{:0.2f}".format(tmp_score_1["Appauvrissement de la couche d'ozone (E-06 kg CVC11 eq/kg de produit)"].sum())
    I04 = "{:0.2f}".format(tmp_score_1["Rayonnements ionisants (kBq U-235 eq/kg de produit)"].sum())
    I05 = "{:0.2f}".format(tmp_score_1["Formation photochimique d'ozone (E-03 kg NMVOC eq/kg de produit)"].sum())
    I06 = "{:0.2f}".format(tmp_score_1["Particules (E-06 disease inc./kg de produit)"].sum())
    I07 = "{:0.2f}".format(tmp_score_1["Acidification terrestre et eaux douces (mol H+ eq/kg de produit)"].sum())
    I08 = "{:0.2f}".format(tmp_score_1["Eutrophisation terreste (mol N eq/kg de produit)"].sum())
    I09 = "{:0.2f}".format(tmp_score_1["Eutrophisation eaux douces (E-03 kg P eq/kg de produit)"].sum())
    I10 = "{:0.2f}".format(tmp_score_1["Eutrophisation marine (E-03 kg N eq/kg de produit)"].sum())
    I11 = "{:0.2f}".format(tmp_score_1["Utilisation du sol (Pt/kg de produit)"].sum())
    I12 = "{:0.2f}".format(tmp_score_1["Écotoxicité pour écosystèmes aquatiques d'eau douce (CTUe/kg de produit)"].sum())
    I13 = "{:0.2f}".format(tmp_score_1["Épuisement des ressources eau (m3 depriv./kg de produit)"].sum())
    I14 = "{:0.2f}".format(tmp_score_1["Épuisement des ressources énergétiques (MJ/kg de produit)"].sum())
    I15 = "{:0.2f}".format(tmp_score_1["Épuisement des ressources minéraux (E-06 kg Sb eq/kg de produit)"].sum())
    
    return I01, I02, I03, I04, I05, I06, I07, I08, I09, I10, I11, I12, I13, I14, I15

# Display the ecological impact
@app.callback(
    Output('ICV01', 'children'),
    Output('ICV02', 'children'),
    Output('ICV03', 'children'),
    Output('ICV04', 'children'),
    Output('ICV05', 'children'),
    Output('ICV06', 'children'),
    Input('select_column', 'value')
)

def callback_impact(product):
    tmp_impact_1 = etapes[etapes["Nom du Produit en Français"]==product]
    tmp_ICV01 = ((tmp_impact_1["Agriculture_EF"]/tmp_impact_1["Total_EF"])*100)
    tmp_ICV02 = ((tmp_impact_1["Transformation_EF"]/tmp_impact_1["Total_EF"])*100)
    tmp_ICV03 = ((tmp_impact_1["Emballage_EF"]/tmp_impact_1["Total_EF"])*100)
    tmp_ICV04 = ((tmp_impact_1["Transport_EF"]/tmp_impact_1["Total_EF"])*100)
    tmp_ICV05 = ((tmp_impact_1["Supermarché et distribution_EF"]/tmp_impact_1["Total_EF"])*100)
    tmp_ICV06 = ((tmp_impact_1["Consommation_EF"]/tmp_impact_1["Total_EF"])*100)
    
    ICV01 = round(tmp_ICV01, 1)
    ICV02 = round(tmp_ICV02, 1)
    ICV03 = round(tmp_ICV03, 1)
    ICV04 = round(tmp_ICV04, 1)
    ICV05 = round(tmp_ICV05, 1)
    ICV06 = round(tmp_ICV06, 1)
    
    return ICV01, ICV02, ICV03, ICV04, ICV05, ICV06

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

Hi Alpy,
I’m not sure if I understand your problem, but it seems to me that you are complicating yourself trying to bild each row of the table.
Just bild your desired table in Pandas and then send it to the html.Div ‘children’ where you want to show the table:

       html.Div(id='theTableWillbeHere'),



Output('theTableWillbeHere', 'children'),

     return [
                 dash_table.DataTable(
                           id='table',
                           columns=[{"name": i, "id": i} for i in df.columns],
                           data=df.to_dict('records'),
                            )
          ]

Sorry if I misunderstood your question.

1 Like

Hello @Eduardo and thank you very much for your answer.

This is working just fine, but that’s not exactly what I wanted. I’d like to get the nice visual of the html.table. Do you know if this is possible to make an automation (maybe with a definition) in a html.table to directly have the right number of row ids?

I got the table with a dash_table.DataTable. I’m not sure I used the same thing as you mentionned though. I used exactly the same code as for my first data table. I noted that I need to initialized my calculated column “percentage” before the app.layout (I refer to my initial database, but the calculation of my column “percentage” is performed during the app.callback). Again I’m not sure this is the best way to do this.

I’m not a pro with Python. I do some programming with some co-workers of mine and now we’d like to make our first steps with the applications. Sure we need more trainings. : ]

Now I’m not sure if you understanded my answer, I’m just saying that there is simple way to show a Pandas DataFrame into a Dash Table:
You just add a Div in your layou, in my example: html.Div(id='theTableWillbeHere'), and with the callback you send the Data Table to that Div id. in my example:
return [ dash_table.DataTable( id='table', columns=[{"name": i, "id": i} for i in df.columns], data=df.to_dict('records'), ) ]
Then what you need to accomplish is just bild your DataFrame as you want to be showed and once you have the DataFrame with all the columns and rows you need (bilded using pandas) drop it to the Dash data table.

Hello @Eduardo, thanks for you answer and sorry for answering only now. Maybe I didn’t understand. I was using this code you mentionned and it worked well. But I wanted to display my table not with dash_table, but using html.Table.

I found something though, with this post: Display tables in Dash

Now I only have this in the layout:

html.Div(id='display_table')

and this in the app.callback:

@app.callback(
        Output('display_table', 'children'), 
        [Input('selected_product', 'value')]
 )

def callback_display_table(product):
    ingredients_display = ingredients_data_display[ingredients_data_display["Nom Français"] == product]
    return generate_table(ingredients_display)

It’s working fine as you can see here:

Anyway, thank you very much for helping me. I’m learning a lot since I started a few weeks ago, thanks to the guide and this forum.

Alpy