Matplotlib bar graph x axis won't plot string values

Question

My name is David and I work for an ambulance service in Florida.

I am using Python 2.7 and matplotlib. I am attempting to reach into my database of ambulance calls and count up the number of calls that happen on each weekday.

I will then use matplotlib to create a bar chart of this information to give the paramedics a visual graphic of how busy they are on each day.

HERE IS CODE THAT WORKS VERY WELL:

import pyodbc import matplotlib.pyplot as plt MySQLQuery = """ SELECT   DATEPART(WEEKDAY, IIU_tDispatch)AS [DayOfWeekOfCall] , COUNT(DATEPART(WeekDay, IIU_tDispatch)) AS [DispatchesOnThisWeekday] FROM AmbulanceIncidents GROUP BY DATEPART(WEEKDAY, IIU_tDispatch) ORDER BY DATEPART(WEEKDAY, IIU_tDispatch) """ cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=MyServer;DATABASE=MyDatabase;UID=MyUserID;PWD=MyPassword') cursor = cnxn.cursor() GraphCursor = cnxn.cursor() cursor.execute(MySQLQuery)  #generate a graph to display the data data = GraphCursor.fetchall() DayOfWeekOfCall, DispatchesOnThisWeekday = zip(*data) plt.bar(DayOfWeekOfCall, DispatchesOnThisWeekday) plt.grid() plt.title('Dispatches by Day of Week') plt.xlabel('Day of Week') plt.ylabel('Number of Dispatches') plt.show() 

The code shown above works very well. It returns a nice looking graph and I am happy. I just want to make one change.

Instead of the X axis showing the names of the days of the week, such as "Sunday", it shows the integer. In other words, Sunday is 1, Monday is 2, etc.

My fix for this is that I rewrite my sql query to use DATENAME() instead of DATEPART(). Shown below is my sql code to return the name of the week (as opposed to an integer).

SELECT   DATENAME(WEEKDAY, IIU_tDispatch)AS [DayOfWeekOfCall] , COUNT(DATENAME(WeekDay, IIU_tDispatch)) AS [DispatchesOnThisWeekday] FROM AmbulanceIncidents GROUP BY DATENAME(WEEKDAY, IIU_tDispatch) ORDER BY DATENAME(WEEKDAY, IIU_tDispatch) 

Everything else in my python code stays the same. However this will not work and I cannot understand the error messages.

Here are the error messages:

Traceback (most recent call last):   File "C:\Documents and Settings\kulpandm\workspace\FiscalYearEndReport\CallVolumeByDayOfWeek.py", line 59, in   <module>     plt.bar(DayOfWeekOfCall, DispatchesOnThisWeekday)   File "C:\Python27\lib\site-packages\matplotlib\pyplot.py", line 2080, in bar     ret = ax.bar(left, height, width, bottom, **kwargs)   File "C:\Python27\lib\site-packages\matplotlib\axes.py", line 4740, in bar     self.add_patch(r)   File "C:\Python27\lib\site-packages\matplotlib\axes.py", line 1471, in add_patch     self._update_patch_limits(p)   File "C:\Python27\lib\site-packages\matplotlib\axes.py", line 1489, in _update_patch_limits     xys = patch.get_patch_transform().transform(vertices)   File "C:\Python27\lib\site-packages\matplotlib\patches.py", line 547, in get_patch_transform     self._update_patch_transform()   File "C:\Python27\lib\site-packages\matplotlib\patches.py", line 543, in _update_patch_transform     bbox = transforms.Bbox.from_bounds(x, y, width, height)   File "C:\Python27\lib\site-packages\matplotlib\transforms.py", line 745, in from_bounds     return Bbox.from_extents(x0, y0, x0 + width, y0 + height) TypeError: coercing to Unicode: need string or buffer, float found 

I cannot figure this out.

To sum up, when I output my data with the x axis as integers representing days of week and y axis showing a count of the number of ambulance incidents, Matplotlib will produce a nice graph. But when my data output is the x axis is a string (Sunday, Monday, etc). then Matplotlib will not work.

I have done several hours of research on Google and reading the matplotlib documentation. Please help me with this. I am hoping to use Matplotlib as my reports engine.

Answer 1

Your question has nothing to do with an SQL query, it is simply a means to end. What you are really asking is how to change the text labels on a bar chart in pylab. The docs for the bar chart are useful for customizing, but to simply change the labels here is a minimal working example (MWE):

import pylab as plt  DayOfWeekOfCall = [1,2,3] DispatchesOnThisWeekday = [77, 32, 42]  LABELS = ["Monday", "Tuesday", "Wednesday"]  plt.bar(DayOfWeekOfCall, DispatchesOnThisWeekday, align='center') plt.xticks(DayOfWeekOfCall, LABELS) plt.show() 

Answer 2

Don't change your SQL code just to alter the illustration. Instead, make a small addition to your Python code.

I believe you can do something like this answer. Set the tick labels to be the days of the week.

It may be as simple as adding the following line:

plt.xticks((1, 2, ..., 7), ('Sunday', 'Monday', ..., 'Saturday')) 

Documentation: pyplot.xticks

EDIT: Example in response to comment using a fictional table IncidentTypes that maps integer keys to names of incident types.

cursor.execute('select incident_type_id, count(*), incident_type      from Incidents join IncidentTypes using (incident_type_id)      group by incident_type_id') results = cursor.fetchall() tickpositions = [int(r[0]) for r in results] numincidents = [int(r[1]) for r in results] ticklabels = [r[2] for r in results]  plt.bar(tickpositions, numincidents) plt.xticks(tickpositions, ticklabels)