How can I graph a candlestick chart with a DataFrame in Python?

Question

I am not able to figure out how to graph a candlestick OHLC chart with python. Ever since matplotlib.finance was deprecated I've had this issue... Thanks for your help!

The DataFrame "quotes" is an excel (can't paste here), but has the following columns:

Index(['Date', 'Open', 'High', 'Low', 'Close'], dtype='object')

I also have a default index. The 'Date' column is a pandas._libs.tslibs.timestamps.Timestamp

When I run the code I get the following error:

File "", line 30, in candlestick_ohlc(ax, zip(mdates.date2num(quotes.index.to_pydatetime()), AttributeError: 'RangeIndex' object has no attribute 'to_pydatetime'

Here is my code:

import datetime

import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import pandas as pd
from matplotlib.dates import MONDAY, DateFormatter, DayLocator, 
WeekdayLocator

from mpl_finance import candlestick_ohlc

date1 = "2004-2-1"
date2 = "2004-4-12"


mondays = WeekdayLocator(MONDAY)
alldays = DayLocator()
weekFormatter = DateFormatter('%b %d')
dayFormatter = DateFormatter('%d')


fig, ax = plt.subplots()
fig.subplots_adjust(bottom=0.2)
ax.xaxis.set_major_locator(mondays)
ax.xaxis.set_minor_locator(alldays)
ax.xaxis.set_major_formatter(weekFormatter)

candlestick_ohlc(ax, zip(mdates.date2num(quotes.index.to_pydatetime()), 
quotes['Open'], quotes['High'],
quotes['Low'], quotes['Close']),
width=0.6)

ax.xaxis_date()
ax.autoscale_view()
plt.setp(plt.gca().get_xticklabels(), rotation=45, 
horizontalalignment='right')

plt.show()

I don't think this has anything to do with mpl_finance, but rather what the error tells you, namely that your dataframe index does not have the method to_pydatetime. This has to do with how you create the dataframe and which type of index you use. But this remains unknown here. — ImportanceOfBeingErnest

Roland W Roland W · Accepted Answer · 2019-01-26T00:52:57

If you don't specify an index while building your DataFrame, it will default to a RangeIndex that just numbers your rows consecutively. This RangeIndex is obviously not convertible to a date -- hence the error. The read_excel function takes index_col as a parameter to specify which column to use as an index. You might also have to provide parse_dates=True.

How can I graph a candlestick chart with a DataFrame in Python?

1 Answers