[FIXED] Datetime to matplotlib dates conversion leads to incorrect candlestick chart

Issue

I'm trying to plot candlestick chart with data as follows:

               openDateTime open    high    low close
1763    2023-11-04 20:45:00 34735.1 34758.6 34735.1 34751.9
1764    2023-11-04 21:00:00 34751.8 34764.1 34749.4 34761.5
1765    2023-11-04 21:15:00 34761.6 34894.0 34761.5 34858.4
1766    2023-11-04 21:30:00 34858.4 34880.9 34812.0 34825.9
1767    2023-11-04 21:45:00 34826.0 34826.0 34795.4 34801.0


Data columns (total 5 columns):
 #   Column           Non-Null Count  Dtype         
---  ------           --------------  -----         
 0   openDateTime     5 non-null      datetime64[ns]
 1   open             5 non-null      float64       
 2   high             5 non-null      float64       
 3   low              5 non-null      float64       
 4   close            5 non-null      float64       
dtypes: datetime64[ns](1), float64(4)

However I'm getting incorrect results when trying to convert openDateTime to matplotlib datetime using mpl_dates.date2num.

My full code below with incorrect chart:

import matplotlib.pyplot as plt
from mplfinance.original_flavor import candlestick_ohlc
import pandas as pd
import matplotlib.dates as mpdates
 
df['openDateTime'] = df['openDateTime'].apply(mpl_dates.date2num) #<-- this is where I think incorrect conversion occurs but this is due to  df['openDateTime'] being in incorrect format for .apply(mpl_dates.date2num) ???
# creating Subplots
fig, ax = plt.subplots()
 
# plotting the data
candlestick_ohlc(ax, df.values, width = 0.6,
                 colorup = 'green', colordown = 'red', 
                 alpha = 0.8)
 
# allow grid
ax.grid(True)
 
# Setting labels 
ax.set_xlabel('openDateTime')
ax.set_ylabel('open')
 
date_format = mpdates.DateFormatter('%d-%m-%Y %H:%m:%S')
ax.xaxis.set_major_formatter(date_format)
fig.autofmt_xdate()
 
fig.tight_layout()
 
# show the plot
plt.show()

After converting openDateTime, df looks like this:

openDateTime    open    high    low close
1764    19665.875000    34751.8 34764.1 34749.4 34761.5
1765    19665.885417    34761.6 34894.0 34761.5 34858.4
1766    19665.895833    34858.4 34880.9 34812.0 34825.9
1767    19665.906250    34826.0 34826.0 34795.0 34798.3
1768    19665.916667    34798.3 34930.0 34798.3 34894.0

Data columns (total 5 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   openDateTime  5 non-null      float64
 1   open          5 non-null      float64
 2   high          5 non-null      float64
 3   low           5 non-null      float64
 4   close         5 non-null      float64

Alternatively I can also have unix time stamp in ms for openDateTime if this helps in anyway with conversion:

         openTimeUnixMs openDateTime    open    high    low close
1764    1699131600000   2023-11-04 21:00:00 34751.8 34764.1 34749.4 34761.5
1765    1699132500000   2023-11-04 21:15:00 34761.6 34894.0 34761.5 34858.4
1766    1699133400000   2023-11-04 21:30:00 34858.4 34880.9 34812.0 34825.9
1767    1699134300000   2023-11-04 21:45:00 34826.0 34826.0 34795.0 34798.3
1768    1699135200000   2023-11-04 22:00:00 34798.3 34930.0 34798.3 34894.0  

Solution

As I understand it, the problem with your figure seems to be that each bar is too wide. candlestick_ohlc's width parameter controls the rectangle width as a fraction of a day. Since the frequency of your data is 15min, adjust the width accordingly to make the base from 1D to 15min (which is 1/96th of a day).

import io
import pandas as pd
from mplfinance.original_flavor import candlestick_ohlc
import matplotlib.pyplot as plt
import matplotlib.dates as mpl_dates

x = """
               openDateTime    open    high     low   close
1763    2023-11-04 20:45:00 34735.1 34758.6 34735.1 34751.9
1764    2023-11-04 21:00:00 34751.8 34764.1 34749.4 34761.5
1765    2023-11-04 21:15:00 34761.6 34894.0 34761.5 34858.4
1766    2023-11-04 21:30:00 34858.4 34880.9 34812.0 34825.9
1767    2023-11-04 21:45:00 34826.0 34826.0 34795.4 34801.0
"""

df = pd.read_fwf(io.StringIO(x), index_col=[0])
df['openDateTime'] = pd.to_datetime(df['openDateTime'])
df['openDateTime'] = df['openDateTime'].apply(mpl_dates.date2num)

fig, ax = plt.subplots(figsize=(6,4))
_ = candlestick_ohlc(
    ax, df.values, width=1/96*0.6,     # <--- set base width to be 15min
    colorup='green', colordown='red', alpha=0.8)
ax.grid(True)
ax.set(xlabel='openDateTime', ylabel='open')
date_format = mpl_dates.DateFormatter('%d-%m-%Y %H:%m:%S')
ax.xaxis.set_major_formatter(date_format)
fig.autofmt_xdate()
fig.show()

Answered By - cottontail