python - 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 SettingskulpandmworkspaceFiscalYearEndReportCallVolumeByDayOfWeek.py", line 59, in 

<module>
    plt.bar(DayOfWeekOfCall, DispatchesOnThisWeekday)
  File "C:Python27libsite-packagesmatplotlibpyplot.py", line 2080, in bar
    ret = ax.bar(left, height, width, bottom, **kwargs)
  File "C:Python27libsite-packagesmatplotlibaxes.py", line 4740, in bar
    self.add_patch(r)
  File "C:Python27libsite-packagesmatplotlibaxes.py", line 1471, in add_patch
    self._update_patch_limits(p)
  File "C:Python27libsite-packagesmatplotlibaxes.py", line 1489, in _update_patch_limits
    xys = patch.get_patch_transform().transform(vertices)
  File "C:Python27libsite-packagesmatplotlibpatches.py", line 547, in get_patch_transform
    self._update_patch_transform()
  File "C:Python27libsite-packagesmatplotlibpatches.py", line 543, in _update_patch_transform
    bbox = transforms.Bbox.from_bounds(x, y, width, height)
  File "C:Python27libsite-packagesmatplotlibransforms.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()