Plotly chart is a mess of lines after index converted to pandas datetime

Question

My plotly chart is just a mess of zig-zagging lines (see chart here). This only happens after I use df['Date'] = pd.to_datetime(df.index) to convert the index to the datetime format.

Full code:

#IMPORTS
import yfinance as yf
import time
import pandas as pd
import datetime
import numpy as np
import xlsxwriter
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

# SETTING UP DF
df = ((pd.read_csv('Book1.csv')).set_index('Date'))[:-1]
df['SMA30'] = df.Total.rolling(30).sum()
df['SMA365'] = df.Total.rolling(365).sum()
df['Monthly Avg'] = df.SMA30.mean()
df['Date'] = pd.to_datetime(df.index)

# PLOTTING FIGURE
fig = go.Figure()
fig.update_layout(title = 'EQ Footfall')
fig.add_trace(go.Scatter(x=df['Date'], y=df.Total, name = 'Footfall Daily'))
fig.add_trace(go.Scatter(x=df.index, y=df.SMA30, name = 'SMA30'))
fig.add_trace(go.Scatter(x=df.index, y=df.SMA365, name = 'SMA365'))
fig.update_xaxes(rangeslider_visible=True)
fig.update_xaxes(tickangle=-45)

Answer 1

• the order of the dates in the dataframe index is important
• have simulated dates in none sequential order in format YYYYMMDD
• without this line df = df.reindex(df.sort_index().index), plot generated is drawing lines between x & y co-ordinates where x is not sequential
• when date is a string it's a categorical so does behave differently, than a continuous variable

import yfinance as yf
import time
import pandas as pd
import datetime
import numpy as np
import xlsxwriter
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

# SETTING UP DF
# df = ((pd.read_csv('Book1.csv')).set_index('Date'))[:-1]
df = pd.DataFrame({"Date":pd.Series(pd.date_range("1-jan-2018", periods=int(365*2.5))).dt.strftime("%Y%m%d"), 
                   "Total":np.random.randint(1,5, int(365*2.5))}).set_index("Date")
# simulate dates in none sequential order
np.random.shuffle(df.index.values)
# reindex with sequential dates,  NB dates or format YYYYMMDD are sortable in this way
df = df.reindex(df.sort_index().index)
df['SMA30'] = df.Total.rolling(30).sum()
df['SMA365'] = df.Total.rolling(365).sum()
df['Monthly Avg'] = df.SMA30.mean()
df['Date'] = pd.to_datetime(df.index)
# df["Date"] = df.index

# PLOTTING FIGURE
fig = go.Figure()
fig.update_layout(title = 'EQ Footfall')
fig.add_trace(go.Scatter(x=df['Date'], y=df.Total, name = 'Footfall Daily'))
fig.add_trace(go.Scatter(x=df.index, y=df.SMA30, name = 'SMA30'))
fig.add_trace(go.Scatter(x=df.index, y=df.SMA365, name = 'SMA365'))
fig.update_xaxes(rangeslider_visible=True)
fig.update_xaxes(tickangle=-45)

Answer 2

Another solution I have found to this is amending the date structure in the csv file to yyyy-mm-dd. This seems to mainly be an issue with how plotly reads dates. Hope this helps.

Answer 3

Suggestion from @Oddaspa also worked for me:

Sorting the index would help. df.sort_index()