一个用于基于对象生成Pivot表的类，使用您的属性和/或方法，可以使用Zope Acquisition来获取这些。
项目描述
简介
本软件包使用Python对象作为源来帮助创建Pivot表。
由lucmult - Luciano Pacheco在Simples Consultoria开发。
您不需要SQL，但可以使用从数据库检索的行。
您需要
您的对象列表
一个映射您的对象属性（或方法）的字典
用作列名的属性（或方法）
注意：一个属性可以是
一个属性
一个没有参数的方法（可调用对象）
可以使用Zope Acquisition，但这是可选的，可以安全地在没有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'), ]
现在我们有一个对象列表 ;-).
您可以使用回调函数来格式化要在生成的表中显示的值
>>> 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'] []
模块aggregate_functions提供了一些聚合函数，您可以根据需要进行选择
>>> 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)
换句话说，一个append和一个__call__，__init__是可选的。
# vim:ft=doctest
聚合
检查Pivot表
让我们创建一个类，将其添加到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'), ]
让我们为里约热内卢创建一些采购
>>> 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'), ]
生成一个简单的Pivot表
>>> 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'), ... ]
里约只在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'
里约需要1月列（jan.）为空（None）
>>> 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)
修复了一个错误，当一个行在某列没有值（例如第一列）时，我们会使用下一列（第二列）的值。同时修复了损坏的聚合功能。还添加了测试 [lucmult]
1.0 - 初次发布
初次发布
