How to pull a date index out of a pandas dataframe to use as x-axis in matplotlib

Question

I am trying to plot data in a pandas dataframe, using the index, which is a date and time, as the x-axis, and the rest of the data in the dataframe as the actual data. Here's what I am trying now:

from matplotlib.finance import candlestick2

bars[['open','high','low','close']].head()


tickdatetime         open        high        low         close          

2012-09-20 09:00:00  1447.50     1447.50     1447.00     1447.00

2012-09-20 09:01:00  1447.00     1447.25     1447.00     1447.25

2012-09-20 09:02:00  1447.25     1447.75     1447.25     1447.50

2012-09-20 09:03:00  1447.75     1447.75     1447.25     1447.50

2012-09-20 09:04:00  1447.25     1447.50     1447.25     1447.50

fig,ax = plt.subplots()
ax.plot_date(bars.ix.to_pydatetime(), s, 'v-')
fig,ax = plt.subplots()
ax.plot_date(bars.ix.to_pydatetime(), s, 'v-')
ax = fig.add_axes([0.1, 0.2, 0.85, 0.7])
ax.autoscale_view()
linecol, rectcol =  candlestick2(ax,bars['open'],bars['close'],bars['high'],bars['low'],width=.5,colorup='g',colordown''r',alpha=1) 
z  = rectcol.get_zorder()
linecol.set_zorder(0.9*z)

but I get this error:

AttributeError                            Traceback (most recent call last)
<ipython-input-57-d62385067ceb> in <module>()
1 fig,ax = plt.subplots()
----> 2 ax.plot_date(bars.ix.to_pydatetime(), s, 'v-')
3 
4 #ax = fig.add_axes([0.1, 0.2, 0.85, 0.7])
5 ax.autoscale_view()

AttributeError: '_NDFrameIndexer' object has no attribute 'to_pydatetime'

---

I understand that bars.plot() is a nice interface to handle this automatically but I want to be able to do stuff like use candlestick2, more subplots, etc.

I think the root of my problem is just trying to get the index values out of the dataframe, and converting the index values to datetimes, but I haven't been able to do that yet.

Any ideas are appreciated!

Answer 1

As Chang She noted, bars.index is what you want, not bars.ix. bars.index returns an Index object that's essentially a Series with your indexes. This is what you want. bars.ix returns an _NDFrameIndexer, something that seems very poorly documented, but is some sort of view of the entire DataFrame (eg, try bars.ix[2]).

Replacing bars.ix with bars.index in your code will remove the error (if you also remove the typo that makes an unmatched '), and make date ticks on the first two subplots. However, it's worth noting that it won't make date ticks on your candlestick plot: your code doesn't do that, though with bars.index it could certainly be done without too much difficulty.

If you want to put date ticks on your candlestick2 plot, however, that's going to be a bit harder. The problem here is that candlestick2, as you can see by what you input into it, doesn't use x-axis values at all. It plots the candlesticks at 0,1,2... and so on. If you try to set your x axis with your dates, whether as the ticks or the limits, then everything's going to be messed up, as your plot is going to be somewhere completely different on the x axis.

One easy way to solve this, which doesn't involve using candlestick instead of candlestick2, is to let your x axis stay as just integer indexes of the data, and instead set your tick labels based on your dates. So, for example:

fig = figure() 
ax = fig.add_subplot(111) 
candlestick2(ax,bars['open'],bars['close'],bars['high'],bars['low'],width=.5,colorup='g',colordown='r',alpha=1)
ax.set_xticks(arange(0,len(bars))) 
ax.set_xticklabels(bars.index,rotation=70)

This plots your date, (a) makes sure that your ticks are at integer locations, and then sets the labels of those ticks based on the dates. I've also rotated them so that you can actually see them. ax.set_xticklabels takes strings, so you can modify the date formatting however you'd like.