Announcing Dash Bio 1.0.0 🎉 : a one-stop-shop for bioinformatics and drug development visualizations.

Pandas merge command and pivot tables are not working

hi…i created this dashboard and it was working really well. but yesterday i tried adding two pareto charts to it and for that i need to add two more pandas dataframe and merge them. the whole pandas commands are running really well individually, but when i’m running it on the dash it’s not working. sometime it’s not able to find a column name ‘RunStatus’ for sorting. pd.merge is also not working. even it’s showing errors for the pivot tables created in it.
Later i wanna run the same command with pyodbc i hope it’ll work with it too fetching live data. pls help me.

import dash
import dash_core_components as dcc
import dash_html_components as html
import pandas as pd
import pyodbc
import dash_table
import plotly.express as px  
import plotly.graph_objects as go
from dash.dependencies import Input, Output
from plotly.subplots import make_subplots
import numpy as np
import flask


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

                        ]
server = flask.Flask(__name__)
app = dash.Dash(__name__, external_stylesheets=external_stylesheets, server=server)

app.css.config.serve_locally = True
app.scripts.config.serve_locally = True

df = pd.read_csv('C:\\Users\\ie1\\IIOT.csv', skiprows=range(1, 280233))
df.fillna(0, inplace=True)
df['Date_Time'] = df['shiftDate'].astype(str) + " " + df['shiftTime'].astype(str).str[:9]
df['MachineRunStatus'] = np.where(df['RunStatus'] == 1, "Running", "Idle")
df.sort_values(by='Date_Time', ascending=True, inplace=True)
df.reset_index(inplace=True, drop=True)
df['Production_Order'] = np.where(
    np.array(df['RunStatus'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] == 0,
    np.where(np.array(df['RunStatus'])[df.index] == 1, df['ProductionOrder'], np.nan), np.nan)
df['PO_Dummy'] = np.where(df['ProductionOrder'] == 0, np.nan, df['ProductionOrder'])
df['PO_Dummy'].ffill(inplace=True)
df.loc[df.last_valid_index(), 'Production_Order'] = df.loc[df.last_valid_index(), 'ProductionOrder']
df['Production_Order'].bfill(inplace=True)
df['UniquePORun'] = np.where(
    np.array(df['POLength'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] <= 0,
    np.where(np.array(df['POLength'])[df.index] > 0, np.where(
        np.array(df['RunStatus'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] !=
        np.array(df['RunStatus'])[df.index], df['ProductionOrder'], np.nan), np.nan),
    np.nan)  # np.where(np.array(df['Production_Order'])[np.where(df.index==df.last_valid_index(),df.index,df.index+1)] != np.array(df['PO_Dummy'])[df.index] , np.nan,  df['ProductionOrder'])
df['UniquePORun'] = np.where(
    np.array(df['Production_Order'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] ==
    np.array(df['Production_Order'])[df.index], df['UniquePORun'], df['Production_Order'])

# df['UniquePORun']=np.where(df.index>=df.last_valid_index()-4,df['UniquePORun'],np.where(pd.isnull(np.array(df['UniquePORun'])[df.index])==False,np.where(np.array(df['POLength'])[np.where(df.index+3>=df.last_valid_index(),df.index,df.index+4)]>=np.array(df['POLength'])[df.index]-10,np.nan,df['UniquePORun']),df['UniquePORun']))
df['count'] = 1
df['RunNo'] = np.where(pd.isnull(df['UniquePORun']) == False,
                       df.groupby(['UniquePORun', 'RunCategory'])['count'].cumsum(), np.nan)
df['RunNo'] = np.where(df['RunNo'] == 0.0, np.nan, df['RunNo'])
df['TB'] = np.where(pd.isnull(df['UniquePORun']) == False, df['RunCategory'], df['UniquePORun'])
df['TB'].bfill(inplace=True)
df['RunNo'].bfill(inplace=True)
df['Run_Category'] = np.where(df['TB'] == 1, "Trial ", np.where(df['TB'] == 2, "Bulk ", np.nan))
df['Run_Category'] = df['Run_Category'] + df['RunNo'].astype(str)
df.drop(columns=['TB', 'UniquePORun', 'PO_Dummy', 'RunNo'], inplace=True)


df_Stoppage1 = pd.DataFrame(
    list(zip([0, 1, 2, 3, 4], ['Not Selected', 'Line in Fabric', 'Dust', 'Screen Chokeup', 'End of Fabric'])),
    columns=['slowSpeedCode', 'Slow_Speed_Reason'])
df_Stoppage0 = pd.DataFrame(
    list(zip([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20], ['Not Selected',
                                                                                          'Machine Cleaning',
                                                                                          'Fabric Changeover',
                                                                                          'Trolley/Batch Non-Availability',
                                                                                          'Mechanical Breakdown',
                                                                                          'Electrical Breakdown',
                                                                                          'Preventive Maintenance',
                                                                                          'Low Steam Pressure',
                                                                                          'Low Air Pressure',
                                                                                          'No Water',
                                                                                          'Man Power Shortage',
                                                                                          'No Program', 'Design Change',
                                                                                          'Screen Washing',
                                                                                          'No Color Preparation',
                                                                                          'Design Not Ready',
                                                                                          'Corrective Maintenance',
                                                                                          'Low Temp Due To Thermopack',
                                                                                          'End Ring Not Fit',
                                                                                          'Blanket Coating',
                                                                                          'Gum Circulation Pump Not Working']))
    , columns=['Stoppage', 'Stoppage_Name'])

df=pd.merge(df,df_Stoppage0,on="Stoppage",how="outer")
df=pd.merge(df,df_Stoppage1,on="slowSpeedCode",how="outer")


app.layout = html.Div([
    dcc.Interval(id='refresh', interval=2000),
    html.H1("Ichinose-1 IIOT Dashboard",
            style={"text-align": "centre", 'display': 'flex', 'justify-content': 'center'}),
    html.Br([]),
    html.Div([
        html.Div([
            dcc.Dropdown(id="Date",
                         options=[
                             {"label": i, "value": i} for i in df['shiftDate'].unique()
                         ],
                         multi=False,
                         style={'width': '100%'},
                         placeholder="Select a Date"
                         )], className="three columns"),
        html.Div([
            dcc.Dropdown(id="Shift",
                         options=[
                             {"label": i, "value": i} for i in df['shift'].unique()
                         ],
                         multi=False,
                         # value='A',
                         placeholder="Select a Shift",
                         style={'width': '100%'}
                         )], className="three columns"),
        html.Div([
            dcc.Dropdown(id="select_PO",
                         multi=False,
                         placeholder="Select a P.O.",
                         style={'width': '100%'}
                         ), ], className="three columns"),
        html.Div([
            dcc.Dropdown(id="select_RunCategory",
                         multi=False,
                         placeholder="Select Bulk or Trial?",
                         style={'width': '100%'}
                         ), ], className="three columns"),
    ], className="row"),

    html.Div(id="output_container", children=[]),
    html.Br([]),
    html.Br([]),
    html.Div([
        dcc.RadioItems(
            id="Running_Idle_Selector",
            options=[
                {'label': 'Machine Running', 'value': 'Running'},
                {'label': 'Machine Idle', 'value': 'Idle'},
                {'label': 'Both', 'value': 'Both'}
            ],
            value='Both',
            labelStyle={'display': 'inline-block'},
            style={
                "display": "block",
                "position": "relative",
                "padding-left": "35px",
                "cursor": "pointer",
                "font-size": "22px",
                "width": "100%",
                "height": "2em",
                "margin": "10px 0"
            },
        )
    ], style={'border': '1px solid black'}),

    html.Div([
        html.Div([
            dcc.Tabs([
                dcc.Tab(label="Speed_Length_Graph", children=[
                    dcc.Graph(id='Speed_Length_Graph', figure={}),
                ]),
                dcc.Tab(label="Air_Water_Graph", children=[
                    dcc.Graph(id='Air_Water_Graph', figure={}),
                ]),
                dcc.Tab(label="Thermic-Oil_Energy-Consumption_Graph", children=[
                    dcc.Graph(id='Thermic_Energy_Graph', figure={}),
                ])
            ])
        ], style={'width': '78%', 'border-right': '1px solid black'}),
        html.Div([
            dcc.Graph(id='piechart'),
        ], style={'width': '22%'}),
    ], style={'align-items': 'center', 'border': '1px solid black', 'display': 'flex', 'justify-content': 'center'}),
    html.Br([]),
    html.Div([
        html.Div([
            dcc.Graph(id='Air_Graph', figure={}), ], className="three columns", style={'border': '1px solid black'}),
        html.Div([
            dcc.Graph(id='Water_Graph', figure={}), ], className="three columns", style={'border': '1px solid black'}),
        html.Div([
            dcc.Graph(id='Thermic_Graph', figure={}), ], className="three columns",
            style={'border': '1px solid black'}),
        html.Div([
            dcc.Graph(id='Energy_Graph', figure={}), ], className="three columns", style={'border': '1px solid black'})
    ], className="row"),
    html.Br([]),

    html.Div([
        html.Div([
            dcc.Tabs([
                dcc.Tab(label="Pareto of Stoppage", children=[
                    dcc.Graph(id="Pareto_Stoppage", figure={})
                ]),
                dcc.Tab(label="Pareto of Slow Speed", children=[
                    dcc.Graph(id="Pareto_Slow_Speed", figure={})
                ])
            ])
        ])
    ]),

    html.Div([
        dash_table.DataTable(
            id='datatable',
            editable=False,
            row_deletable=False,
            page_action="native",
            page_current=0,
            page_size=6,
            style_data_conditional=[
                {
                    'if': {'row_index': 'odd'},
                    'backgroundColor': 'rgb(248, 248, 248)'
                }
            ],
            style_header={
                'backgroundColor': 'rgb(230, 230, 230)',
                'fontWeight': 'bold'
            },
            style_cell={'textAlign': 'center', 'font_size': '20px', 'font_family': 'noto sans'}
        ),
    ], className='row'),
    html.Br([]),
    html.Br([]),
    html.Br([]),
    html.Br([]),
    html.Br([]),

])


@app.callback([Output(component_id="select_PO", component_property="options"),
               ],
              [Input(component_id="Date", component_property="value"),
               Input(component_id="Shift", component_property="value")
               ])
def update_dropdown(Date_value, Shift_value):
    dff = df.copy()
    if Date_value is not None:
        dff = dff[dff['shiftDate'] == Date_value]
        if Shift_value is not None:
            dff = dff[dff['shift'] == Shift_value]
            return_PO = dff['Production_Order'].unique()
        else:
            return_PO = dff['Production_Order'].unique()
    else:
        if Shift_value is not None:
            dff = dff[dff['shift'] == Shift_value]
            return_PO = dff['Production_Order'].unique()
        else:
            return_PO = dff['Production_Order'].unique()
    return [[{"label": i, "value": i} for i in return_PO]]


@app.callback(
    [
        Output(component_id="select_RunCategory", component_property="options")],
    [
        Input(component_id="Date", component_property="value"),
        Input(component_id="Shift", component_property="value"),
        Input(component_id="select_PO", component_property="value")
    ]
)
def update_run_category(Date_value, Shift_value, PO_Value):
    dff = df.copy()
    if Date_value is not None:
        dff = dff[dff['shiftDate'] == Date_value]
        if Shift_value is not None:
            dff = dff[dff['shift'] == Shift_value]
            if PO_Value is not None:
                dff = dff[dff['Production_Order'] == PO_Value]
                return_run_category = dff['Run_Category'].unique()
            else:
                return_run_category = ["Please select a P.O."]
        else:
            if PO_Value is not None:
                dff = dff[dff['Production_Order'] == PO_Value]
                return_run_category = dff['Run_Category'].unique()
            else:
                return_run_category = ["Please select a P.O."]
    else:
        if Shift_value is not None:
            dff = dff[dff['shift'] == Shift_value]
            if PO_Value is not None:
                dff = dff[dff['Production_Order'] == PO_Value]
                return_run_category = dff['Run_Category'].unique()
            else:
                return_run_category = ["Please select a P.O."]
        else:
            if PO_Value is not None:
                dff = dff[dff['Production_Order'] == PO_Value]
                return_run_category = dff['Run_Category'].unique()
            else:
                return_run_category = ["Please select a P.O."]
    return [[{"label": i, "value": i} for i in return_run_category]]


@app.callback(
    [Output(component_id="datatable", component_property="data"),
     Output(component_id="datatable", component_property="columns"),
     Output(component_id="output_container", component_property="children"),
     Output(component_id="Speed_Length_Graph", component_property="figure"),
     Output(component_id="Air_Water_Graph", component_property="figure"),
     Output(component_id="Thermic_Energy_Graph", component_property="figure"),
     Output(component_id="piechart", component_property="figure"),
     Output(component_id="Air_Graph", component_property="figure"),
     Output(component_id="Water_Graph", component_property="figure"),
     Output(component_id="Thermic_Graph", component_property="figure"),
     Output(component_id="Energy_Graph", component_property="figure"),
     Output(component_id="Pareto_Stoppage", component_property="figure"),
     Output(component_id="Pareto_Slow_Speed", component_property="figure")],
    [Input(component_id="Date", component_property="value"),
     Input(component_id="Shift", component_property="value"),
     Input(component_id="select_PO", component_property="value"),
     Input(component_id="select_RunCategory", component_property="value"),
     Input(component_id="Running_Idle_Selector", component_property="value")
     ])
def update_graph(Date_value, Shift_value, PO_Number, Run_Category, Running_Idle_Selector):
    if PO_Number is None:
        container = "Please Select a PO No."
    else:
        container = "The Selected PO No. is {}".format(PO_Number)

    df_graph = df.copy()
    df_graph = df_graph.sort_values(by='Date_Time')

    if Date_value is not None:
        df_graph = df_graph[df_graph['shiftDate'] == Date_value]
        if PO_Number is not None:
            if Shift_value is not None:
                df_graph = df_graph[df_graph['shift'] == Shift_value]
                if Run_Category is not None:
                    df_graph = df_graph[df_graph['Production_Order'] == PO_Number]
                    df_graph = df_graph[df_graph['Run_Category'] == Run_Category]
                    df_graph2 = df_graph
                else:
                    df_graph = df_graph[df_graph['Production_Order'] == PO_Number]
                    df_graph2 = df_graph
            else:
                if Run_Category is not None:
                    df_graph = df_graph[df_graph['Production_Order'] == PO_Number]
                    df_graph = df_graph[df_graph['Run_Category'] == Run_Category]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
                else:
                    df_graph = df_graph[df_graph['Production_Order'] == PO_Number]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
        else:
            if Shift_value is not None:
                df_graph = df_graph[df_graph['shift'] == Shift_value]
                if Run_Category is not None:
                    df_graph = df_graph[df_graph['Run_Category'] == Run_Category]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
                else:
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]

            else:
                if Run_Category is not None:
                    df_graph = df_graph[df_graph['Run_Category'] == Run_Category]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
                else:
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
    else:
        if PO_Number is not None:
            if Shift_value is not None:
                df_graph = df_graph[df_graph['shift'] == Shift_value]
                if Run_Category is not None:
                    df_graph = df_graph[df_graph['Production_Order'] == PO_Number]
                    df_graph = df_graph[df_graph['Run_Category'] == Run_Category]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
                else:
                    df_graph = df_graph[df_graph['Production_Order'] == PO_Number]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]

            else:
                if Run_Category is not None:
                    df_graph = df_graph[df_graph['Production_Order'] == PO_Number]
                    df_graph = df_graph[df_graph['Run_Category'] == Run_Category]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
                else:
                    df_graph = df_graph[df_graph['Production_Order'] == PO_Number]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
        else:
            if Shift_value is not None:
                df_graph = df_graph[df_graph['shift'] == Shift_value]
                if Run_Category is not None:
                    df_graph = df_graph[df_graph['Run_Category'] == Run_Category]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
                else:
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]

            else:
                if Run_Category is not None:
                    df_graph = df_graph[df_graph['Run_Category'] == Run_Category]
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]
                else:
                     # change this for live data
                    df_graph2 = df_graph
                    # df_graph = df_graph[df_graph['RunStatus'] == 1]

    df_Stoppage = df_graph2[['RunStatus', 'Stoppage_Name', 'Slow_Speed_Reason']]
    df_Stoppage = df_Stoppage[df_Stoppage['RunStatus'] == 0]
    dfp = df_Stoppage.pivot_table(values=('RunStatus'), index=('Stoppage_Name'), aggfunc='count')
    dfp.sort_values(by='RunStatus', ascending=False, inplace=True)
    dfp["cumpercentage"] = dfp["RunStatus"].cumsum() / dfp["RunStatus"].sum() * 100
    dfp.reset_index(inplace=True)
    dfpp = df_Stoppage.pivot_table(values=('RunStatus'), index=('Slow_Speed_Reason'), aggfunc='count')
    dfpp.sort_values(by='RunStatus', ascending=False, inplace=True)
    dfpp["cumpercentage"] = dfpp["RunStatus"].cumsum() / dfpp["RunStatus"].sum() * 100
    dfp.reset_index(inplace=True)



    Stoppage_Pareto = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    Stoppage_Pareto.add_trace(
        go.Scatter(
            x=dfp['Stoppage_Name'],
            y=dfp['cumpercentage'],
            name="Cummulative Percentage",
        ), secondary_y=True, )

    Stoppage_Pareto.add_trace(
        go.Bar(
            x=dfp['Stoppage_Name'],
            y=dfp['RunStatus'],
            name="Count of Occurence",
        ), secondary_y=False, )

    Stoppage_Pareto.update_layout(
        title_text='Pareto Chart of Stoppage',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-1),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category"),
    )

    Stoppage_Pareto.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    Stoppage_Pareto.update_yaxes(title_text="<b>Count of Occurence</b>", secondary_y=False,
                                 ticks='outside', showline=True, linewidth=2, linecolor='black', )

    Stoppage_Pareto.update_yaxes(title_text="<b>Cummulative Percentage</b>", secondary_y=True,
                                 ticks='outside', showline=True, linewidth=2, linecolor='black', )

    ########

    Slow_Speed_Pareto = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    Slow_Speed_Pareto.add_trace(
        go.Scatter(
            x=dfpp['Slow_Speed_Reason'],
            y=dfpp['cumpercentage'],
            name="Cummulative Percentage",
        ), secondary_y=True, )

    Slow_Speed_Pareto.add_trace(
        go.Bar(
            x=dfpp['Slow_Speed_Reason'],
            y=dfpp['RunStatus'],
            name="Count of Occurence",
        ), secondary_y=False, )

    Slow_Speed_Pareto.update_layout(
        title_text='Pareto Chart of Slow Speed',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-1),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category"),
    )

    Slow_Speed_Pareto.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    Slow_Speed_Pareto.update_yaxes(title_text="<b>Count of Occurence</b>", secondary_y=False,
                                   ticks='outside', showline=True, linewidth=2, linecolor='black', )

    Slow_Speed_Pareto.update_yaxes(title_text="<b>Cummulative Percentage</b>", secondary_y=True,
                                   ticks='outside', showline=True, linewidth=2, linecolor='black', )

    if Running_Idle_Selector == "Running":
        df_graph = df_graph[df_graph['RunStatus'] == 1]
    elif Running_Idle_Selector == "Idle":
        df_graph = df_graph[df_graph['RunStatus'] == 0]

    pie_chart = px.pie(
        data_frame=df_graph2,
        names='MachineRunStatus',
        values='count',
        color='MachineRunStatus',
        color_discrete_map={"Running": "green", "Idle": "red"},
        hole=0.3,

    )
    pie_chart.update_traces(textposition='outside', textinfo='percent+label',
                            marker=dict(line=dict(color='#000000', width=2)),
                            pull=[0, 0.2], opacity=0.9, rotation=180, hoverinfo="none")

    pie_chart.update_layout(
        title_text='Utilisation',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3),
        plot_bgcolor='white', margin=dict(t=10, b=0, l=0, r=0)
    )

    df_table = df_graph[['Date_Time', 'shift', 'Production_Order', 'FabricSpeed', 'POLength']]

    fig = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    fig.add_trace(
        go.Scattergl(x=df_graph['Date_Time'].astype(str), y=df_graph['FabricSpeed'], name='Fabric Speed',
                     hovertext=list(df_graph['MachineRunStatus'])), secondary_y=False,
    )

    fig.add_trace(
        go.Scattergl(x=df_graph['Date_Time'].astype(str), y=df_graph['POLength'], name='Fabric Length',
                     hovertext=list(df_graph['Run_Category'])), secondary_y=True,
    )
    # hovertemplate = "Time: %{x},<br>Length: %{y}</br> Run_Category: ${df_graph['Run_Category']}"

    fig.update_layout(
        title_text='Speed & Length of P.O. w.r.t. Date-Time',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-1),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category")
    )

    fig.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig.update_yaxes(title_text="<b>Fabric Speed</b>", secondary_y=False,
                     ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig.update_yaxes(title_text="<b>Fabric Length</b>", secondary_y=True,
                     ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig2 = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    fig2.add_trace(
        go.Scattergl(x=df_graph['Date_Time'].astype(str), y=df_graph['AirFlow'], name='Air Flow',
                     hovertext=list(df_graph['MachineRunStatus'])), secondary_y=False,
    )

    fig2.add_trace(
        go.Scattergl(x=df_graph['Date_Time'].astype(str), y=df_graph['WaterFlow'], name='Water Flow',
                     hovertext=list(df_graph['Run_Category'])), secondary_y=True,
    )

    fig2.update_layout(
        title_text='Air & Water Flow of P.O. w.r.t. Date-Time',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category")
    )

    fig2.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig2.update_yaxes(title_text="<b>Air Flow</b>", secondary_y=False,
                      ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig2.update_yaxes(title_text="<b>Water Flow</b>", secondary_y=True,
                      ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig3 = make_subplots(
        specs=[[{"secondary_y": True}]]
    )

    fig3.add_trace(
        go.Scattergl(x=df_graph['Date_Time'].astype(str), y=df_graph['ThermicFlow'], name='Thermic Oil Flow',
                     hovertext=list(df_graph['MachineRunStatus'])), secondary_y=False,
    )

    fig3.add_trace(
        go.Scattergl(x=df_graph['Date_Time'].astype(str), y=df_graph['EnergyKW'], name='Energy Consumption',
                     hovertext=list(df_graph['Run_Category'])), secondary_y=True,
    )

    fig3.update_layout(
        title_text='Thermic Oil Flow & Energy Consumption of P.O. w.r.t. Date-Time',
        legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3),
        hovermode="x unified", plot_bgcolor='white', xaxis=dict(type="category")
    )

    fig3.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig3.update_yaxes(title_text="<b>Thermic Oil Flow</b>", secondary_y=False,
                      ticks='outside', showline=True, linewidth=2, linecolor='black')

    fig3.update_yaxes(title_text="<b>Energy Consumption</b>", secondary_y=True,
                      ticks='outside', showline=True, linewidth=2, linecolor='black')

    air_totaliser = go.Figure(data=[
        go.Bar(name='While Machine Running', x=['Air Consumption'],
               y=[df_graph2[df_graph2['RunStatus'] == 1]['AirFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>'),
        go.Bar(name='While Machine Not Running', x=['Air Consumption'],
               y=[df_graph2[df_graph2['RunStatus'] == 0]['AirFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>')
    ])


    air_totaliser.update_layout(barmode='stack',
                                legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3)
                                , plot_bgcolor='white')

    air_totaliser.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    air_totaliser.update_yaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    water_totaliser = go.Figure(data=[
        go.Bar(name='While Machine Running', x=['Water Consumption'],
               y=[df_graph2[df_graph2['RunStatus'] == 1]['WaterFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>'),
        go.Bar(name='While Machine Not Running', x=['Water Consumption'],
               y=[df_graph2[df_graph2['RunStatus'] == 0]['WaterFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>')
    ])
    # Change the bar mode
    water_totaliser.update_layout(barmode='stack',
                                  legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3)
                                  , plot_bgcolor='white')

    water_totaliser.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')
    water_totaliser.update_yaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    thermic_totaliser = go.Figure(data=[
        go.Bar(name='While Machine Running', x=['Thermic Consumption'],
               y=[df_graph2[df_graph2['RunStatus'] == 1]['ThermicFlow'].sum()],
               hovertemplate='%{y:.2f}<extra></extra>'),
        go.Bar(name='While Machine Not Running', x=['Thermic Consumption'],
               y=[df_graph2[df_graph2['RunStatus'] == 0]['ThermicFlow'].sum()], hovertemplate='%{y:.2f}<extra></extra>')
    ])
    thermic_totaliser.update_layout(barmode='stack',
                                    legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3)
                                    , plot_bgcolor='white')

    thermic_totaliser.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')
    thermic_totaliser.update_yaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    energy_totaliser = go.Figure(data=[
        go.Bar(name='While Machine Running', x=['Energy Consumption'],
               y=[df_graph2[df_graph2['RunStatus'] == 1]['EnergyKW'].sum()], hovertemplate='%{y:.2f}<extra></extra>'),
        go.Bar(name='While Machine Not Running', x=['Energy Consumption'],
               y=[df_graph2[df_graph2['RunStatus'] == 0]['EnergyKW'].sum()], hovertemplate='%{y:.2f}<extra></extra>')
    ])
    energy_totaliser.update_layout(barmode='stack',
                                   legend=dict(orientation="h", xanchor="center", yanchor="top", x=0.5, y=-0.3)
                                   , plot_bgcolor='white')

    energy_totaliser.update_xaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')
    energy_totaliser.update_yaxes(ticks='outside', showline=True, linewidth=2, linecolor='black')

    return df_table.to_dict('records'), [{"name": i, "id": i} for i in
                                         df_table.columns], container, fig, fig2, fig3, pie_chart, air_totaliser, water_totaliser, thermic_totaliser, energy_totaliser, Stoppage_Pareto, Slow_Speed_Pareto


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

I’m using latest version of dash on pycharm on Windows 10 operating system.
I’m sorry i can’t disclose the base data file of this code due to my company’s policy. but pls do help me.

hi @bsbalkar

I didn’t test this… but did you intend to reset the index on dfpp in the last line? If the Slow_Speed_Pareto subplot doesn’t show up then this might be the problem.

Also, df_Stoppage is still filtered for RunStatus ==0 when you do the dfpp pivot table. I don’t know your data, but I’m guessing the slow speed items would have a RunStatus of 1?

    df_Stoppage = df_graph2[['RunStatus', 'Stoppage_Name', 'Slow_Speed_Reason']]
    df_Stoppage = df_Stoppage[df_Stoppage['RunStatus'] == 0]
    dfp = df_Stoppage.pivot_table(values=('RunStatus'), index=('Stoppage_Name'), aggfunc='count')
    dfp.sort_values(by='RunStatus', ascending=False, inplace=True)
    dfp["cumpercentage"] = dfp["RunStatus"].cumsum() / dfp["RunStatus"].sum() * 100
    dfp.reset_index(inplace=True)
    dfpp = df_Stoppage.pivot_table(values=('RunStatus'), index=('Slow_Speed_Reason'), aggfunc='count')
    dfpp.sort_values(by='RunStatus', ascending=False, inplace=True)
    dfpp["cumpercentage"] = dfpp["RunStatus"].cumsum() / dfpp["RunStatus"].sum() * 100
    dfp.reset_index(inplace=True)

hi @AnnMarieW Thanks for your reply but as i mentioned that each and every pandas commands are working perfectly if i run them individually without dash. but when i’m running dash the everything else works well but the merge and pivot_table command of pandas is not running. the selected columns resulting from the merge and pivot_table commands are showing me “ValueError”.

hmm. I would expect key errors from what I pointed out, not value errors. Would it be possible to provide some test/dummy data that reproduces the problem?

hi @AnnMarieW. yes you’re right it was a key error. sorry for my mistake. Yes i would like to share data with you. pls tell me how can i share data with you in this forum.

Hi @bsbalkar

Since you have tested this step by step outside Dash and you know it works, would you like to try making some changes to the combined code in Dash first?

    ### starting at your second pivot

     #added Line (yes, it's repeated)
    df_Stoppage = df_graph2[['RunStatus', 'Stoppage_Name', 'Slow_Speed_Reason']]   

    # added line
    #Change this line to select the correct value to select slow speed.  I'm guessing it's 1, but I don't know
    df_Stoppage = df_Stoppage[df_Stoppage['RunStatus'] == 1]  

    dfpp = df_Stoppage.pivot_table(values=('RunStatus'), index=('Slow_Speed_Reason'), aggfunc='count')
    dfpp.sort_values(by='RunStatus', ascending=False, inplace=True)
    dfpp["cumpercentage"] = dfpp["RunStatus"].cumsum() / dfpp["RunStatus"].sum() * 100

    #fixed the typo  (dfpp instead of  dfp)
    dfpp.reset_index(inplace=True)                                                       

If you are still getting errors, you could include a link to some data or just manually create the df with a few rows of random data based on you csv file

hi @AnnMarieW Thanks for your reply.
below is the link for the data used for the above code

also i had corrected what you said.

    df_Stoppage = df_graph2[['RunStatus', 'Stoppage_Name', 'Slow_Speed_Reason']]
    df_Stoppage = df_Stoppage[df_Stoppage['RunStatus'] == 0]
    dfp = df_Stoppage.pivot_table(values=('RunStatus'), index=('Stoppage_Name'), aggfunc='count')
    dfp.sort_values(by='RunStatus', ascending=False, inplace=True)
    dfp["cumpercentage"] = dfp["RunStatus"].cumsum() / dfp["RunStatus"].sum() * 100
    dfp.reset_index(inplace=True)

    #i'm using second table for the second pivot_table
    df_Stoppage2 = df_graph2[['RunStatus', 'Stoppage_Name', 'Slow_Speed_Reason']]
    df_Stoppage2 = df_Stoppage2[df_Stoppage2['RunStatus'] == 1]
    dfpp = df_Stoppage2.pivot_table(values=('RunStatus'), index=('Slow_Speed_Reason'), aggfunc='count')
    dfpp.sort_values(by='RunStatus', ascending=False, inplace=True)
    dfpp["cumpercentage"] = dfpp["RunStatus"].cumsum() / dfpp["RunStatus"].sum() * 100
    dfpp.reset_index(inplace=True)

but the problem is still occurring.
this is the current error- “options[81].label in Dropdown with ID “select_PO” is required but it was not provided.”

the below code is the part of the above code which is used to for data cleaning as per our requirement before running the dash app. the last two lines contain the merge command which is not working if i’m using it while running the dash app. i think when the merge command will work correctly, the pivot tables will also start working as the rows in the pivot table are linked with the merge command.

df = pd.read_csv('C:\\Users\\ie1\\IIOT.csv', skiprows=range(1, 280233))
df.fillna(0, inplace=True)
df['Date_Time'] = df['shiftDate'].astype(str) + " " + df['shiftTime'].astype(str).str[:9]
df['MachineRunStatus'] = np.where(df['RunStatus'] == 1, "Running", "Idle")
df.sort_values(by='Date_Time', ascending=True, inplace=True)
df.reset_index(inplace=True, drop=True)
df['Production_Order'] = np.where(
    np.array(df['RunStatus'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] == 0,
    np.where(np.array(df['RunStatus'])[df.index] == 1, df['ProductionOrder'], np.nan), np.nan)
df['PO_Dummy'] = np.where(df['ProductionOrder'] == 0, np.nan, df['ProductionOrder'])
df['PO_Dummy'].ffill(inplace=True)
df.loc[df.last_valid_index(), 'Production_Order'] = df.loc[df.last_valid_index(), 'ProductionOrder']
df['Production_Order'].bfill(inplace=True)
df['UniquePORun'] = np.where(
    np.array(df['POLength'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] <= 0,
    np.where(np.array(df['POLength'])[df.index] > 0, np.where(
        np.array(df['RunStatus'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] !=
        np.array(df['RunStatus'])[df.index], df['ProductionOrder'], np.nan), np.nan),
    np.nan)  # np.where(np.array(df['Production_Order'])[np.where(df.index==df.last_valid_index(),df.index,df.index+1)] != np.array(df['PO_Dummy'])[df.index] , np.nan,  df['ProductionOrder'])
df['UniquePORun'] = np.where(
    np.array(df['Production_Order'])[np.where(df.index == df.last_valid_index(), df.index, df.index + 1)] ==
    np.array(df['Production_Order'])[df.index], df['UniquePORun'], df['Production_Order'])

# df['UniquePORun']=np.where(df.index>=df.last_valid_index()-4,df['UniquePORun'],np.where(pd.isnull(np.array(df['UniquePORun'])[df.index])==False,np.where(np.array(df['POLength'])[np.where(df.index+3>=df.last_valid_index(),df.index,df.index+4)]>=np.array(df['POLength'])[df.index]-10,np.nan,df['UniquePORun']),df['UniquePORun']))
df['count'] = 1
df['RunNo'] = np.where(pd.isnull(df['UniquePORun']) == False,
                       df.groupby(['UniquePORun', 'RunCategory'])['count'].cumsum(), np.nan)
df['RunNo'] = np.where(df['RunNo'] == 0.0, np.nan, df['RunNo'])
df['TB'] = np.where(pd.isnull(df['UniquePORun']) == False, df['RunCategory'], df['UniquePORun'])
df['TB'].bfill(inplace=True)
df['RunNo'].bfill(inplace=True)
df['Run_Category'] = np.where(df['TB'] == 1, "Trial ", np.where(df['TB'] == 2, "Bulk ", np.nan))
df['Run_Category'] = df['Run_Category'] + df['RunNo'].astype(str)
df.drop(columns=['TB', 'UniquePORun', 'PO_Dummy', 'RunNo'], inplace=True)


df_Stoppage1 = pd.DataFrame(
    list(zip([0, 1, 2, 3, 4], ['Not Selected', 'Line in Fabric', 'Dust', 'Screen Chokeup', 'End of Fabric'])),
    columns=['slowSpeedCode', 'Slow_Speed_Reason'])
df_Stoppage0 = pd.DataFrame(
    list(zip([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20], ['Not Selected',
                                                                                          'Machine Cleaning',
                                                                                          'Fabric Changeover',
                                                                                          'Trolley/Batch Non-Availability',
                                                                                          'Mechanical Breakdown',
                                                                                          'Electrical Breakdown',
                                                                                          'Preventive Maintenance',
                                                                                          'Low Steam Pressure',
                                                                                          'Low Air Pressure',
                                                                                          'No Water',
                                                                                          'Man Power Shortage',
                                                                                          'No Program', 'Design Change',
                                                                                          'Screen Washing',
                                                                                          'No Color Preparation',
                                                                                          'Design Not Ready',
                                                                                          'Corrective Maintenance',
                                                                                          'Low Temp Due To Thermopack',
                                                                                          'End Ring Not Fit',
                                                                                          'Blanket Coating',
                                                                                          'Gum Circulation Pump Not Working']))
    , columns=['Stoppage', 'Stoppage_Name'])

df=pd.merge(df,df_Stoppage0,on="Stoppage",how="outer")
df=pd.merge(df,df_Stoppage1,on="slowSpeedCode",how="outer")

Just a quick update here to say we found the problem… The NaNs from the outer join in the merge were causing the errors in other parts of the dashboard. Fixing this along with the typo as previously suggested got the app working again.

1 Like

Yes there are two options either to use the df.fillna() again or chose the type of merge correctly i.e. inner join, outer join, left join or right join.
Thanks @AnnMarieW for your help too.