matplotlib: how to prevent x-axis labels from overlapping

I think you're confused on a few points about how matplotlib handles dates.

You're not actually plotting dates, at the moment. You're plotting things on the x-axis with [0,1,2,...] and then manually labeling every point with a string representation of the date.

Matplotlib will automatically position ticks. However, you're over-riding matplotlib's tick positioning functionality (Using xticks is basically saying: "I want ticks in exactly these positions".)

At the moment, you'll get ticks at [10, 20, 30, ...] if matplotlib automatically positions them. However, these will correspond to the values that you used to plot them, not the dates (which you didn't use when plotting).

You probably want to actually plot things using dates.

Currently, you're doing something like this:

import datetime as dtimport matplotlib.dates as mdatesimport numpy as npimport matplotlib.pyplot as plt# Generate a series of dates (these are in matplotlib's internal date format)dates = mdates.drange(dt.datetime(2010, 01, 01), dt.datetime(2012,11,01),                       dt.timedelta(weeks=3))# Create some data for the y-axiscounts = np.sin(np.linspace(0, np.pi, dates.size))# Set up the axes and figurefig, ax = plt.subplots()# Make a bar plot, ignoring the date valuesax.bar(np.arange(counts.size), counts, align='center', width=1.0)# Force matplotlib to place a tick at every bar and label them with the datedatelabels = mdates.num2date(dates) # Go back to a sequence of datetimes...ax.set(xticks=np.arange(dates.size), xticklabels=datelabels) #Same as plt.xticks# Make space for and rotate the x-axis tick labelsfig.autofmt_xdate()plt.show()

Instead, try something like this:

import datetime as dtimport matplotlib.dates as mdatesimport numpy as npimport matplotlib.pyplot as plt# Generate a series of dates (these are in matplotlib's internal date format)dates = mdates.drange(dt.datetime(2010, 01, 01), dt.datetime(2012,11,01),                       dt.timedelta(weeks=3))# Create some data for the y-axiscounts = np.sin(np.linspace(0, np.pi, dates.size))# Set up the axes and figurefig, ax = plt.subplots()# By default, the bars will have a width of 0.8 (days, in this case) We want# them quite a bit wider, so we'll make them them the minimum spacing between# the dates. (To use the exact code below, you'll need to convert your sequence# of datetimes into matplotlib's float-based date format.  # Use "dates = mdates.date2num(dates)" to convert them.)width = np.diff(dates).min()# Make a bar plot. Note that I'm using "dates" directly instead of plotting# "counts" against x-values of [0,1,2...]ax.bar(dates, counts, align='center', width=width)# Tell matplotlib to interpret the x-axis values as datesax.xaxis_date()# Make space for and rotate the x-axis tick labelsfig.autofmt_xdate()plt.show()

As for your question on how to show only every 4th tick (for example) on the xaxis, you can do this:

import matplotlib.ticker as mtickermyLocator = mticker.MultipleLocator(4)ax.xaxis.set_major_locator(myLocator)

• The issue in the OP is the dates are formatted as string type. matplotlib plots every value as a tick label with the tick location being a 0 indexed number based on the number of values.
• The resolution to this issue is to convert all values to the correct type, datetime in this case.
  • Once the axes have the correct type, there are additional matplotlib methods, which can be used to further customize the tick spacing.
• The answers to What is plotted when string data is passed to the matplotlib API? explain in more detail what happens when string values are passed to matplotlib.
• As of 2014-09-30, pandas has a read_sql function, which has a parse_dates parameter. You definitely want to use that instead.

Original Answer

Here's how you should convert your date string into real datetime objects:

import numpy as npimport matplotlib.pyplot as pltimport matplotlib.dates as mdatesdata_tuples = [    ('4084036', '1', '2006-12-22 22:46:35'),    ('4084938', '1', '2006-12-23 16:19:13'),    ('4084938', '2', '2006-12-23 16:20:23'),    ('4084939', '1', '2006-12-23 16:29:14'),    ('4084954', '1', '2006-12-23 16:28:28'),    ('4250653', '1', '2007-02-12 21:58:53'),    ('4250657', '1', '2007-03-12 21:58:53')]datatypes = [('col1', 'i4'), ('col2', 'i4'), ('date', 'S20')]data = np.array(data_tuples, dtype=datatypes)col1 = data['col1']# convert the dates to a datetime typedates = mdates.num2date(mdates.datestr2num(data['date']))fig, ax1 = plt.subplots()ax1.bar(dates, col1)fig.autofmt_xdate()

Getting a simple list of tuples out of your database cursor should be as simple as...

data_tuples = []for row in cursor:    data_tuples.append(row)

However, I posted a version of a function that I use to take db cursors directly to record arrays or pandas dataframes here: How to convert SQL Query result to PANDAS Data Structure?

Hopefully that helps too.