一个类，基于对象，使用属性和/或方法生成数据透视表，可以使用Zope获取来获取这些表。
collective.pivottable的Python项目详细描述
导言
这个包帮助使用python对象作为源创建Pivot Tables。
由lucmult-Luciano Pacheco在Simples Consultoria开发
您不需要SQL，但可以使用从数据库检索的行。
您需要：
- 对象列表
- 映射对象属性（或方法）的dict
- 用作列名的属性（或方法）
注意：属性可以是：
- 属性
- 方法（可调用），不带参数
- 可以使用Zope获取，但它是可选的，可以在没有Zope的情况下安全使用；-）
让我们举个例子
定义您的类
>>> class Purchase(object): ... def __init__(self, cost=0.0, price=0.0, month='', ou=''): ... self.cost = cost ... self.price = price ... self.month = month ... self.ou = ou ... def gain(self): ... return (self.price - self.cost) / self.cost
代表你购买物品的类。
我们买点东西吧
>>> purchases = [Purchase(cost=5.0, price=7, month='jan', ou='NY'), ... Purchase(cost=5.0, price=7, month='jan', ou='NY'), ... Purchase(cost=14.66, price=4946.68, month='feb', ou='NY'), ... Purchase(cost=7.33, price=7184.90, month='mar', ou='NY'), ... Purchase(cost=7.33, price=7834.92, month='apr', ou='NY'), ... Purchase(cost=73.3, price=8692.67, month='may', ou='NY'), ... Purchase(cost=128.28, price=9552.14, month='jun', ou='NY'), ... Purchase(cost=58.64, price=8828.44, month='jul', ou='NY'), ... Purchase(cost=128.28, price=9652.73, month='aug', ou='NY'), ] >>> purchases += [Purchase(cost=14.66, price=463.61, month='jan', ou='RJ'), ... Purchase(cost=14.66, price=4946.68, month='feb', ou='RJ'), ... Purchase(cost=7.33, price=7184.90, month='mar', ou='RJ'), ... Purchase(cost=7.33, price=7834.92, month='apr', ou='RJ'), ... Purchase(cost=73.3, price=8692.67, month='may', ou='RJ'), ... Purchase(cost=128.28, price=9552.14, month='jun', ou='RJ'), ... Purchase(cost=58.64, price=8828.44, month='jul', ou='RJ'), ... Purchase(cost=128.28, price=9652.73, month='aug', ou='RJ'), ]
现在我们有一个对象列表；-）。
您可以使用回调函数格式化要在generated表中显示的值
>>> def formatter(value): ... if isinstance(value, float): ... return '%.2f' % value ... else: ... return '%s' % value
它有一个内置的示例显示为字符串
>>> from collective.pivottable import StringTable >>> tbl = StringTable()
定义属性以命名列
>>> tbl.attr_to_name_col = 'month'
定义属性映射以及如何聚合值
>>> tbl.attrs_to_fill_row = [{'attr': 'cost', 'label': 'Cost Total', 'callback': formatter, 'aggr_func': Sum},
... {'attr': 'price', 'label': "Sell's Price", 'callback': formatter , 'aggr_func': Sum},
... {'attr': 'gain', 'label': 'AVG Gain %', 'callback': formatter, 'aggr_func': Avg},
... {'attr': 'ou', 'label': 'OU', 'callback': formatter, 'aggr_func': GroupBy}]
将对象传递给tbl
>>> tbl.objects = purchases
将名称设置为第一列
>>> tbl.first_col_title = 'Purchases'
获取文本表格
>>> tbl.show() Purchases OU jan feb mar apr may jun jul aug Cost Total RJ 14.66 14.66 7.33 7.33 73.30 128.28 58.64 128.28 Sell's Price RJ 463.61 4946.68 7184.90 7834.92 8692.67 9552.14 8828.44 9652.73 AVG Gain % RJ 30.62 336.43 979.20 1067.88 117.59 73.46 149.55 74.25 Cost Total NY 5.00 14.66 7.33 7.33 73.30 128.28 58.64 128.28 Sell's Price NY 7 4946.68 7184.90 7834.92 8692.67 9552.14 8828.44 9652.73 AVG Gain % NY 0.40 336.43 979.20 1067.88 117.59 73.46 149.55 74.25
或获取行和列的列表（主要用途）
>>> for line in tbl.getAllRows(): ... print line ... ['Purchases', 'OU', 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug'] ['Cost Total', 'RJ', '14.66', '14.66', '7.33', '7.33', '73.30', '128.28', '58.64', '128.28'] ["Sell's Price", 'RJ', '463.61', '4946.68', '7184.90', '7834.92', '8692.67', '9552.14', '8828.44', '9652.73'] ['AVG Gain %', 'RJ', '30.62', '336.43', '979.20', '1067.88', '117.59', '73.46', '149.55', '74.25'] ['Cost Total', 'NY', '5.00', '14.66', '7.33', '7.33', '73.30', '128.28', '58.64', '128.28'] ["Sell's Price", 'NY', '7', '4946.68', '7184.90', '7834.92', '8692.67', '9552.14', '8828.44', '9652.73'] ['AVG Gain %', 'NY', '0.40', '336.43', '979.20', '1067.88', '117.59', '73.46', '149.55', '74.25'] []
“聚合函数”模块提供了一些聚合函数，您可以对其进行区分大小写
>>> from collective.pivottable.aggregate_functions import Avg, First, GroupBy, Last, Max, Min, Sum
创建聚合函数的基本api是
>>> class Sum(object): ... def __init__(self): ... self.values = [] ... def append(self, value): ... self.values.append(value) ... def __call__(self): ... return sum(self.values)
换句话说，追加和调用是可选的
#vim:ft=doctest
聚合
检查数据透视表
让我们创建类以添加到pivot表中
>>> class Purchase(object): ... def __init__(self, cost=0.0, price=0.0, month='', ou=''): ... self.cost = cost ... self.price = price ... self.month = month ... self.ou = ou ... def gain(self): ... return (self.price - self.cost) / self.cost ... def __repr__(self): ... return 'Purchase(cost=%f, price=%f, month=%s, ou=%s)' % (self.cost, ... self.price, self.month, self.ou)
让我们为纽约创建一些购买：
>>> purchases = [Purchase(cost=5, price=7, month='jan', ou='NY'), ... Purchase(cost=5, price=7, month='jan', ou='NY'), ... Purchase(cost=14, price=4900, month='feb', ou='NY'), ... Purchase(cost=7, price=7000, month='mar', ou='NY'), Purchase(cost=7, price=7834, month='apr', ou='NY'), ... Purchase(cost=73, price=8692, month='may', ou='NY'), Purchase(cost=128, price=9552, month='jun', ou='NY'), ... Purchase(cost=58, price=8828, month='jul', ou='NY'), Purchase(cost=128, price=9652, month='aug', ou='NY'), ]
让我们为RJ创建一些购买：
>>> purchases += [Purchase(cost=14, price=463, month='jan', ou='RJ'), Purchase(cost=14, price=4946, month='feb', ou='RJ'), ... Purchase(cost=7, price=7184, month='mar', ou='RJ'), Purchase(cost=7, price=7834, month='apr', ou='RJ'), ... Purchase(cost=73, price=8692, month='may', ou='RJ'), Purchase(cost=128, price=9552, month='jun', ou='RJ'), ... Purchase(cost=58, price=8828, month='jul', ou='RJ'), Purchase(cost=128, price=9652, month='aug', ou='RJ'), ]
生成简单透视表：
>>> from pivot_table import *
>>> fmt = PivotTable()
>>> fmt.attr_to_name_col = 'month'
>>> fmt.attrs_to_fill_row = [{'attr': 'cost', 'label': 'Cost Total', 'aggr_func': Sum},
... {'attr': 'price', 'label': "Sell's Price", 'aggr_func': Sum},
... {'attr': 'gain', 'label': 'AVG Gain %', 'aggr_func': Avg},
... {'attr': 'ou', 'label': 'OU', 'aggr_func': GroupBy}]
>>> fmt.objects = purchases
>>> fmt.first_col_title = 'Purchases'
检查标题
>>> fmt.getHeader() ['Purchases', 'OU', 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug']
检查行
>>> rows = fmt.getRows() >>> rows[0] ['Cost Total', 'RJ', 14, 14, 7, 7, 73, 128, 58, 128] >>> rows[1] ["Sell's Price", 'RJ', 463, 4946, 7184, 7834, 8692, 9552, 8828, 9652] >>> rows[2] ['AVG Gain %', 'RJ', 32.0, 352.0, 1025.0, 1118.0, 118.0, 73.0, 151.0, 74.0] >>> rows[3] ['Cost Total', 'NY', 10, 14, 7, 7, 73, 128, 58, 128] >>> rows[4] ["Sell's Price", 'NY', 14, 4900, 7000, 7834, 8692, 9552, 8828, 9652] >>> rows[5] ['AVG Gain %', 'NY', 0.0, 349.0, 999.0, 1118.0, 118.0, 73.0, 151.0, 74.0]
检查页脚
>>> fmt.getFooter() []
现在，新购买
纽约在1月和2月有采购
>>> purchases = [Purchase(cost=5, price=10, month='jan', ou='NY'), ... Purchase(cost=5, price=10, month='jan', ou='NY'), ... Purchase(cost=14, price=28, month='feb', ou='NY'), ... Purchase(cost=14, price=28, month='feb', ou='NY'), ... ]
RJ只有在2月份才有购买活动。
>>> purchases += [ ... Purchase(cost=14, price=28, month='feb', ou='RJ'), ... Purchase(cost=14, price=28, month='feb', ou='RJ'), ... ]
使用相同的参数来透视表
>>> fmt = PivotTable()
>>> fmt.attr_to_name_col = 'month'
>>> fmt.attrs_to_fill_row = [{'attr': 'cost', 'label': 'Cost Total', 'aggr_func': Sum},
... {'attr': 'price', 'label': "Sell's Price", 'aggr_func': Sum},
... {'attr': 'gain', 'label': 'AVG Gain %', 'aggr_func': Avg},
... {'attr': 'ou', 'label': 'OU', 'aggr_func': GroupBy}]
>>> fmt.objects = purchases
>>> fmt.first_col_title = 'Purchases'
RJ需要1月份的列为空（无）
>>> fmt.getHeader() ['Purchases', 'OU', 'jan', 'feb'] >>> rows = fmt.getRows() >>> rows[0] ['Cost Total', 'RJ', None, 28] >>> rows[1] ["Sell's Price", 'RJ', None, 56] >>> rows[2] ['AVG Gain %', 'RJ', None, 1.0] >>> rows[3] ['Cost Total', 'NY', 10, 28] >>> rows[4] ["Sell's Price", 'NY', 20, 56] >>> rows[5] ['AVG Gain %', 'NY', 1.0, 1.0]
变更日志
1.1.1-（2009-09-14）
- 修正了文本购买的错误-谢谢leandro lameiro:-）[lucmult]
1.1-（2009-09-07）
- 修正了一个错误，当一行在列中没有值时（比如第一列），我们使用的是下一列的值（第二列）也修复了被破坏的聚合。并添加测试o/[lucmult]
1.0-初始释放
- 初次发行
欢迎加入QQ群-->： 979659372
