Matplotlib dates in datetime format

Question

I am plotting a candlestick chart (ohlc+volume) and unfortunately I can't figure out how to display the date in datetime format. Several approaches did not work.

import matplotlib.pyplot as plt
from matplotlib.finance import candlestick_ohlc
from matplotlib import style
import pandas as pd

style.use('classic')

def graph(candlestick_list):
    df = pd.DataFrame(candlestick_list)
    ohlc = df[['date', 'open', 'high', 'low', 'close']]

    ax1 = plt.subplot2grid((6,1), (0,0), rowspan=5, colspan=1)
    ax2 = plt.subplot2grid((6,1), (5,0), rowspan=5, colspan=1, sharex=ax1)
    candlestick_ohlc(ax1, ohlc.values, width=0.5, colorup='g', colordown='r')
    ax2.bar(df['date'], df['volume'])
    plt.show()

Unfortunately the following approaches didn't work for me:

df['date'] = pd.to_datetime(df['date'], unit='s')

TypeError: unsupported operand type(s) for -: 'Timestamp' and 'float'

or this one:

import matplotlib.dates as mdates
ax1.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))

ValueError: year 4095909 is out of range

Does anybody have an idea? Many thanks in advance!

PS: the following list can be used to test the code

candlestick_list = [{'date': 1496332800, 'high': 215.3209, 'low': 200.3, 'open': 211.18, 'close': 206.47999999, 'volume': 2432435.9201853, 'quoteVolume': 11813.26856836, 'weightedAverage': 205.90710404}, {'date': 1496340000, 'high': 212.0001, 'low': 205.75400533, 'open': 206.47999992, 'close': 207.85, 'volume': 1301024.6514137, 'quoteVolume': 6250.47612412, 'weightedAverage': 208.14808753}, {'date': 1496347200, 'high': 212.44999999, 'low': 206.9813717, 'open': 208.49981874, 'close': 209.99811003, 'volume': 1062083.1773221, 'quoteVolume': 5056.26107738, 'weightedAverage': 210.05307302}, {'date': 1496354400, 'high': 213.20999999, 'low': 209.61231001, 'open': 210, 'close': 211.44, 'volume': 705968.23009208, 'quoteVolume': 3335.35026592, 'weightedAverage': 211.66239639}, {'date': 1496361600, 'high': 219, 'low': 210.81410968, 'open': 211.43999994, 'close': 215.41715998, 'volume': 972040.25543603, 'quoteVolume': 4500.001815, 'weightedAverage': 216.00885852}]

Solution

Try converting the dates into Matplotlib format. Often, Matplotlib will recognize and auto-convert dates from DataFrames, but there are many odd cases. Generally when things don't work, do your own conversion manually.

import matplotlib.pyplot as plt
from matplotlib.finance import candlestick_ohlc
from matplotlib import style
import pandas as pd
import matplotlib.dates as mdates

def graph(candlestick_list):
    df = pd.DataFrame(candlestick_list)
    df['date'] = pd.to_datetime(df['date'], unit='s')
    df['mdate'] = [mdates.date2num(d) for d in df['date']]

    ohlc = df[['mdate', 'open', 'high', 'low', 'close']]

    ax1 = plt.subplot2grid((6,1), (0,0), rowspan=5, colspan=1)
    ax2 = plt.subplot2grid((6,1), (5,0), rowspan=5, colspan=1, sharex=ax1)
    candlestick_ohlc(ax1, ohlc.values, width=0.05, colorup='g', colordown='r')
    ax2.bar(df['mdate'], df['volume'])
    ax2.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
    plt.xticks(rotation=60)
    plt.show()

Documentation for date2num(). Note I added a separate mdate column. Useful for debugging, and if you need to also use regular Pandas dates for other purposes. Here in this limited, within-a-function context, probably overkill to keep several date representations around. I also thinned the width of your OHLC bars, and rotated the date ticks so I could see them better. YMMV on such styling tweaks.

Finally, if the use of a list comprehension offends your sensibilties because "it's not vectorized," you can do the conversion to matplotlib style dates in one go with:

df['mdate'] = mdates.date2num(df.date.dt.to_pydatetime())

Comments

[sunwarr10r]

Thank you. It goes on, when I create a new dataframe df['date_ts'] = pd.to_datetime(df['date'], unit='s') and then do the mdates conversion, cause else candlestick_ohlc() can't use mdates. But now a new error appears: ValueError: DateFormatter found a value of x=0, which is an illegal date. This usually occurs because you have not informed the axis that it is plotting dates, e.g., with ax.xaxis_date() - I did ax1.xaxis_date(), but it still doesn't work

[Jonathan Eunice]

If you provide a dataset on which it doesn't work, I'll have a look.

[Ra Rot]

I just stumbled across this post and wanted to add, that matplotlib.finance is not existing anymore. One should use mpl_finance instead now. (from mpl_finance import candlestick_ohlc)