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

X axis to show correct time

Hello all,
m currently making an applicaton to list the downtime of machines that are not working.I’m getting the result where the x is showed as numeric values.My project manager would like that the numeric values be changed to day,week and month under bar of the exact date ie.When I unout the start date from 01.08.2021 to 09.08.2021 that under every bar is showed the time like 06.08.2021 now it only shows like 1,2,3 per bar.Do you have any info how I can make it work?

this is the picture how it looks atm.

this is my code atm:

@app.callback(
    Output("bar_chart7", "figure"),
    #[
    #Output("table1", "data"),
    ##Output("Bar_chart_link", "children"),
    #Output("bar7", "children")
    #]
    [Input("downtime_data", "data")],
    state=[State("start_date_picker", "date"),
    State("start_time_picker", "value"),
    State("end_date_picker", "date"),
    State("end_time_picker", "value"),
    State("database", "value"),
    State("language", "value")]
)
def create_bar_chart(n_clicks, start_date_picker, start_time_picker, end_date_picker, end_time_picker, database, language):
    if n_clicks:
        start_time = datetime.datetime.strptime(start_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(start_time_picker[0:2]))
        end_time = datetime.datetime.strptime(end_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(end_time_picker[0:2]))
        parameters = [start_time, end_time]
    # if not n_clicks:
    #     db_query_string = """"SELECT NotificationLog.ID as 'ID',
    #                     DAY(Timestamp) as 'DAY',
    #                     MONTH(Timestamp) as 'MONTH',
    #                     NotificationLog.Duration,
    #                     DATEPART(WEEK, NotificationLog.Timestamp)-1 as 'Week Number'
    #                     FROM NotificationLog
    #                         BETWEEN ? AND ?"""
        query_string = mssql_queries.build_db_query_app7()
        records = mssql_conn.execute_query(query_string, parameters, str(database))


        if records:
            dataframe = pd.DataFrame.from_records(records)
            dataframe.columns = ["ID", "DAY", "MONTH", "Duration", "Week Number"]
            # dataframe = dataframe.groupby('Week Number')['Duration'].sum().reset_index()
            dataframe = dataframe.groupby('DAY')['Duration'].sum().reset_index()
            dataframe['Duration'] = dataframe['Duration'].apply(lambda x: x/60)#pretvoriti u sekunde
            #dataframe['Duration'] = dataframe['Duration'].astype('datetime64[s]').dt.strftime("%H:%M:%S")#pretvoriti u minute
            # trace1 = go.Bar(name="Down_Time_In_Min", x=dataframe["Duration"], y=dataframe["Down Time In Min"], marker=dict(color='rgb(34,163,192)'))
            print("--dataframe--")
            print(dataframe)
            print("---")
            # layout = go.Layout(
            #     font=go.Font(
            #         color='rgb(128,128,128)',
            #         family='Balto, sans-serif',
            #         size=12
            #     ),
            #     height=800,
            #     paper_bgcolor='rgb(240,240,240',
            #     plot_bgcolor='rgb(240,240,240)',
            # )


            # mask = dataframe.applymap(type) != bool
            # d = {True: "1", False: "0"}
            # dataframe = dataframe.where(mask, dataframe.replace(d))
            # print("----")
            # print(dataframe)
            #layout = go.Layout(
            #title='Consumer Financial Protection Bureau: Consumer Complaints',
            #titlefont=dict(
            #    color='rgb(128,128,128)',
            #    family='Balto, sans-serif',
            #    size=12
            #),
            #font=go.Font(
            #    color='rgb(128,128,128)',
            #    family='Balto, sans-serif',
            #    size=12
            #),
            #width=1770,
            #height=800,
            #paper_bgcolor='rgb(240, 240, 240)',
            #plot_bgcolor='rgb(240, 240, 240)',
            #
            #)
            # fig = px.bar(dataframe, x=dataframe['Week Number'], y=dataframe['Duration'])
            fig = px.bar(dataframe, x=dataframe['DAY'], y=dataframe['Duration'])
            #fig.show()
            return  go.Figure(fig)
            #return dbc.Table.from_dataframe(dataframe), dataframe.to_json(date_format="iso", orient="split"), None

Hi,

You will need to first build a datetime object or anything that Plotly can identify as a date. You can probably format it directly from the Timestamp in your DB as “YYYY-MM-DD”. If you have a column in this format, regardless of being datetime or string, Plotly will switch automatically to a time series axis.

When you get there, then it is simple to adjust the axis:

fig = px.bar(dataframe, x="your_date_column", y="Duration") 
fig.update_xaxes(
    dtick="D1", # sets minimal interval to day
    tickformat="%d.%m.%Y", # the date format you want 
)

More information here.

Please let me know if this helps! :smiley:

Hi,
thank you very much for the reply, I understand how it work in theory but not practically as I’m new to this atm.This is my db for my app.
def build_db_query_app7():

db_query_string = """SELECT NotificationLog.ID as 'ID',

                    DAY(Timestamp) as 'DAY',

                    MONTH(Timestamp) as 'MONTH',

                    NotificationLog.Duration,

                    DATEPART(WEEK, NotificationLog.Timestamp)-1 as 'Week Number'

                    FROM NotificationLog

                    WHERE NotificationLog.Timestamp

                        BETWEEN ? AND ?"""

return db_query_string

Do you have inputs for what I need to do with it so that I make a fuctioning datetime object ?

I imagine that if you use DATE(Timestamp) as 'DATE' in the query, then dataframe["DATE"] will be understood as a date by Plotly. If not, you can convert using e.g. pd.to_datetime().

I added a new query and now it works and looks like

but when i put
fig = px.bar(dataframe, x=dataframe[‘DATE’], y=dataframe[‘Duration’])

my graph doesn’t load at all and I get the following errors:
Callback error updating link7.children
Traceback (most recent call last):
File “C:\Users\denis.akvic\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3361, in get_loc
return self._engine.get_loc(casted_key)
File “pandas_libs\index.pyx”, line 76, in pandas._libs.index.IndexEngine.get_loc

File “pandas_libs\index.pyx”, line 108, in pandas._libs.index.IndexEngine.get_loc

File “pandas_libs\hashtable_class_helper.pxi”, line 5198, in pandas._libs.hashtable.PyObjectHashTable.get_item

File “pandas_libs\hashtable_class_helper.pxi”, line 5206, in pandas._libs.hashtable.PyObjectHashTable.get_item

KeyError: ‘DATE’

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “C:\Users\denis.akvic\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3363, in get_loc
raise KeyError(key) from err
KeyError: ‘DATE’

KeyError: ‘DATE’

Traceback (most recent call last)

  • File “C:\Users\denis.akvic\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3361 , in get_loc

return self._engine.get_loc(casted_key)

  • File “pandas_libs\index.pyx”, line 76 , in pandas._libs.index.IndexEngine.get_loc

  • File “pandas_libs\index.pyx”, line 108 , in pandas._libs.index.IndexEngine.get_loc

  • File “pandas_libs\hashtable_class_helper.pxi”, line 5198 , in pandas._libs.hashtable.PyObjectHashTable.get_item

  • File “pandas_libs\hashtable_class_helper.pxi”, line 5206 , in pandas._libs.hashtable.PyObjectHashTable.get_item

  • The above exception was the direct cause of the following exception:

  • File “C:\Users\denis.akvic\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3363 , in get_loc

raise KeyError(key) from err

KeyError: ‘DATE’

This error usually means that dataframe does not have a column named “DATE”, so maybe you are discarding the new column…?

Please also be mindful that the format you showed in the picture has also time, so you might need to use a different SQL function (not sure which engine you are using) or convert to a date before grouping by it.

Hi,

I added in my code the DATE value:

dataframe = pd.DataFrame.from_records(records)
dataframe.columns = [“ID”, “DATE”, “DAY”, “MONTH”, “Duration”, “Week Number”]
dataframe = dataframe.groupby(‘DATE’)[‘Duration’].sum().reset_index()
dataframe[‘Duration’] = dataframe[‘Duration’].apply(lambda x: x/60)
fig = px.bar(dataframe, x=dataframe[‘DATE’], y=dataframe[‘Duration’])
return go.Figure(fig)

Now I’m getting the axis dates that look like this:

but there no bars showing now and I’m getting an error:
:rescue_worker_helmet:Callback error updating bar_chart2.figure
Callback error updating bar_chart2.figure
Callback error updating bar_chart2.figure

The above exception was the direct cause of the following exception:
File "C:\Users\denis.akvic\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py", line 3363, in get_loc

raise KeyError(key) from err

KeyError: 'DATE'

This is the Copy/Paste friendly version of the traceback.

Traceback (most recent call last):
File “C:\Users\denis.akvic\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3361, in get_loc
return self._engine.get_loc(casted_key)
File “pandas_libs\index.pyx”, line 76, in pandas._libs.index.IndexEngine.get_loc

File “pandas_libs\index.pyx”, line 108, in pandas._libs.index.IndexEngine.get_loc

File “pandas_libs\hashtable_class_helper.pxi”, line 5198, in pandas._libs.hashtable.PyObjectHashTable.get_item

File “pandas_libs\hashtable_class_helper.pxi”, line 5206, in pandas._libs.hashtable.PyObjectHashTable.get_item

KeyError: ‘DATE’

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “C:\Users\denis.akvic\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3363, in get_loc
raise KeyError(key) from err
KeyError: ‘DATE’

Do you have any suggestions?

I don’t have any suggestions. It still looks like the problem is on the Pandas DataFrame, so could you check which one of the lines in your code (not pandas) is raising the error that you shared?

Besides, just a small tip: with plotly.express you can pass the column names directly to the plotting function:

fig = px.bar(dataframe, x="DATE", y="Duration")

I changed some stuff in my code,mainly I deleted stuff from my database and added:
This is how it was

"""SELECT NotificationLog.ID as 'ID',
                        DAY(Timestamp) as 'DAY',
                        MONTH(Timestamp) as 'MONTH',
                        NotificationLog.Duration,
                        DATEPART(WEEK, NotificationLog.Timestamp)-1 as 'Week Number'
                        FROM NotificationLog
                        WHERE NotificationLog.Timestamp
                            BETWEEN ? AND ?"""

INTO


def build_db_query_app7():

    db_query_string = """SELECT NotificationLog.ID as 'ID',

                        NotificationLog.Timestamp,

                        NotificationLog.Duration,

                        DATEPART(WEEK, NotificationLog.Timestamp)-1 as 'Week Number'

                        FROM NotificationLog

                        WHERE NotificationLog.Timestamp

                            BETWEEN ? AND ?"""

I used the Timestamp from the database and worked on my bar chart:

@app.callback(

    Output("bar_chart7", "figure"),

    [Input("downtime_data", "data")],

    state=[State("start_date_picker", "date"),

    State("start_time_picker", "value"),

    State("end_date_picker", "date"),

    State("end_time_picker", "value"),

    State("database", "value"),

    State("language", "value")]

)

def create_bar_chart(n_clicks, start_date_picker, start_time_picker, end_date_picker, end_time_picker, database, language):

    if n_clicks:

        start_time = datetime.datetime.strptime(start_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(start_time_picker[0:2]))

        end_time = datetime.datetime.strptime(end_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(end_time_picker[0:2]))

        parameters = [start_time, end_time]



        query_string = mssql_queries.build_db_query_app7()

        records = mssql_conn.execute_query(query_string, parameters, str(database))

        if records:

            dataframe = pd.DataFrame.from_records(records)

            dataframe.columns = ["ID", "Timestamp", "Duration", "Week Number"]

            dataframe = dataframe.groupby('Timestamp')['Duration'].sum().reset_index()

            dataframe['Duration'] = dataframe['Duration'].apply(lambda x: x/60)#make into  seconds


            print("--dataframe--")

            print(dataframe)

            print("---")           


            fig = px.bar(dataframe, x=dataframe['Timestamp'], y=dataframe['Duration'])

            fig.update_xaxes(

                dtick="D1",

                tickformat="%d-%m-%Y",

            )

       

            return  go.Figure(fig)

          

Now I’m getting the correct time stamp from the start date and the end date I pick, but I’m not getting any visual graphs.Do you have input for me?
Maybe the start_date_picker", “date, start_date_picker”, “value,end_date_picker”, "date how to something with the dataframe columsn?

I don’t know… There’s not in my eyes that looks wrong, so this should have worked.

Can you share what is the output of print(dataframe) in the callback?

Seems it doesn’t even load the graphs for the first daily bar chart only the second weekly bar chart.
This is what I get:

PS C:\Users\denis.akvic\Desktop\BDE\flask> & C:/Users/denis.akvic/AppData/Local/Programs/Python/Python39
/python.exe c:/Users/denis.akvic/Desktop/BDE/flask/index.py

  • Serving Flask app ‘app’ (lazy loading)
  • Serving Flask app ‘app’ (lazy loading)
  • Environment: production
    WARNING: This is a development server. Do not use it in a production deployment.
    Use a production WSGI server instead.
  • Debug mode: on

SELECT NotificationLog.ID as ‘ID’,
NotificationLog.Timestamp,
NotificationLog.Duration,
DATEPART(WEEK, NotificationLog.Timestamp)-1 as ‘Week Number’
FROM NotificationLog
WHERE NotificationLog.Timestamp
BETWEEN ? AND ?
c:\Users\denis.akvic\Desktop\BDE\flask\apps\app7.py:472: FutureWarning: Use of **kwargs is deprecated, use engine_kwargs instead.
writer = pd.ExcelWriter(xlsx_io, engine=‘xlsxwriter’, options={“remove_timezone” : True})

SELECT NotificationLog.ID as ‘ID’,
NotificationLog.Timestamp,
NotificationLog.Duration,
DATEPART(WEEK, NotificationLog.Timestamp)-1 as ‘Week Number’
WHERE NotificationLog.Timestamp
BETWEEN ? AND ?
BETWEEN ? AND ?

SELECT NotificationLog.ID as ‘ID’,
NotificationLog.Timestamp,
NotificationLog.Duration,
DATEPART(WEEK, NotificationLog.Timestamp)-1 as ‘Week Number’
FROM NotificationLog
WHERE NotificationLog.Timestamp
BETWEEN ? AND ?

--dataframe--
--dataframe--
   Week Number      Duration
0           31   6831.783333
1           32  11168.133333
                   Timestamp  Duration
0    2021-08-01 06:02:11.517  0.000000
1    2021-08-01 06:02:12.230  0.000000
2    2021-08-01 06:03:33.760  0.566667
3    2021-08-01 06:03:34.210  0.566667
4    2021-08-01 06:03:45.960  0.000000
...                      ...       ...
4470 2021-08-09 05:58:52.763  0.000000
4471 2021-08-09 05:58:55.580  0.000000
4472 2021-08-09 05:58:58.060  0.000000
4473 2021-08-09 05:59:01.650  0.000000
4474 2021-08-09 05:59:38.103  0.000000

[4475 rows x 2 columns]

I made it work by adding a new dataframe to the def create_bar_chart:

dataframe['Timestamp'] = pd.to_datetime(dataframe['Timestamp']).dt.date

dataframe = dataframe.groupby("Timestamp")['Duration'].sum().reset_index()

print("--dataframe--")

print(dataframe)