Matplotlib 条形图 x 轴无法绘制字符串值

Question

我正在使用Python 2.7和matplotlib。我想从我的救护车呼叫数据库中提取数据，统计每个工作日的呼叫次数。

然后，我会用matplotlib制作一个柱状图，给急救人员提供一个关于他们每天忙碌程度的直观图形。

下面是我写的代码，它运行得很好：

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()

上面的代码效果很好，生成了一个漂亮的图表，我对此很满意。我只想做一个小改动。

我希望X轴显示的是星期几的名称，比如“星期天”，而不是数字。换句话说，星期天是1，星期一是2，依此类推。

为了解决这个问题，我把我的SQL查询改成使用DATENAME()，而不是DATEPART()。下面是我的SQL代码，用来返回星期的名称（而不是数字）。

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)

我在Python代码中的其他部分保持不变。不过这样做后，程序却不工作，我也看不懂错误信息。

这是错误信息：

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

总的来说，当我输出的数据中，X轴是表示星期几的整数，Y轴显示救护车事件的数量时，Matplotlib会生成一个漂亮的图表。但当我的数据输出中，X轴是字符串（比如星期天、星期一等）时，Matplotlib就无法正常工作。

我该如何解决这个问题呢？

Answer 1

最终解决问题的完整答案： 非常感谢你，Steve。你帮了我大忙。我在大学学的是地理，不是编程，所以这对我来说挺难的。 这是我最终能用的代码。

 import pyodbc
    import matplotlib.pyplot as plt
    MySQLQuery = """
    SELECT 
      DATEPART(WEEKDAY, IIU_tDispatch)AS [IntegerOfDayOfWeek]
    , COUNT(DATENAME(WeekDay, IIU_tDispatch)) AS [DispatchesOnThisWeekday]
    , DATENAME(WEEKDAY, IIU_tDispatch)AS [DayOfWeekOfCall]
    FROM IIncidentUnitSummary
    INNER JOIN PUnit ON IIU_kUnit = PUN_Unit_PK
    WHERE PUN_UnitAgency = 'LC'
    AND IIU_tDispatch BETWEEN 'October 1, 2010' AND 'October 1, 2011'
    AND PUN_UnitID LIKE 'M__'
    GROUP BY DATEPART(WEEKDAY, IIU_tDispatch), DATENAME(WEEKDAY, IIU_tDispatch)
    ORDER BY DATEPART(WEEKDAY, IIU_tDispatch)
    """
    cnxn = pyodbc.connect("a bunch of stuff I don't want to share")
    cursor = cnxn.cursor()
    GraphCursor = cnxn.cursor()
    cursor.execute(MySQLQuery)

    results = cursor.fetchall()
    IntegerDayOfWeek, DispatchesOnThisWeekday, DayOfWeekOfCall = zip(*results)
    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)
    #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()

    cursor.close()
    cnxn.close()

我其实不太明白“results=cursor.fetchall()”和后面四行创建数组的代码之间的关系。我很高兴你能理解，因为我看了还是没搞明白。 非常感谢你。这对我帮助很大。 David

Answer 2

不要为了改变图示而去修改你的SQL代码。相反，可以在你的Python代码中做一个小的添加。

我觉得你可以参考这个回答。把刻度标签设置为星期几。

这可能只需要添加以下这一行代码：

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

文档：pyplot.xticks

编辑：根据评论的要求，给出一个例子，使用一个虚构的表格IncidentTypes，它将整数键映射到事件类型的名称。

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)

Answer 3

你的问题其实和SQL查询没有关系，这只是一个手段。你真正想问的是如何在pylab中更改柱状图的文本标签。关于柱状图的文档可以帮助你进行自定义，但如果你只是想更改标签，这里有一个简单的示例（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()