A class to generate Pivot Tables based on Objects, using your attributes and/or methods, that can use Zope Acquisition to get those.
Project description
Introduction
This package helps creates Pivot Tables using your Python objects as source.
Developed by lucmult - Luciano Pacheco at Simples Consultoria.
You don’t need SQL, but can use row retrieved from your database.
You need :
A list of your objects
A dict mapping your object’s attributes (or methods)
An attribute (or method) to use as column name
NOTE: An attribute can be :
an attribute
a method (callable), without args
can use Zope Acquisition, but it’s optional, can safely used without Zope ;-)
Let’s show a example.
Define your class
>>> 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
A class representing your purchases.
Let’s do some purchases
>>> 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'), ]
Now we have a list of objects ;-).
You can use a callback function to format values to display in your genereated table
>>> def formatter(value): ... if isinstance(value, float): ... return '%.2f' % value ... else: ... return '%s' % value
It have a built-in example to display as string
>>> from collective.pivottable import StringTable >>> tbl = StringTable()
Define an attrbute to name cols
>>> tbl.attr_to_name_col = 'month'
Define the attrs mapping and how aggregate the values
>>> 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}]
Pass your objects to tbl
>>> tbl.objects = purchases
Set a name to first col
>>> tbl.first_col_title = 'Purchases'
Get your text table
>>> 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
Or get a list of rows and cols (main use)
>>> 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'] []
The module aggregate_functions provides some aggregates functions, that you can case
>>> from collective.pivottable.aggregate_functions import Avg, First, GroupBy, Last, Max, Min, Sum
The Base API to create a aggregate_function is
>>> class Sum(object): ... def __init__(self): ... self.values = [] ... def append(self, value): ... self.values.append(value) ... def __call__(self): ... return sum(self.values)
In other words, a append and a __call__, the __init__ is optional.
# vim:ft=doctest
Aggregating
Checking Pivot Table
Let’s create our class to add in pivot table
>>> 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)
Let’s create some purchases, for NY:
>>> 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'), ]
Let’s create some purchases, for 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'), ]
Generating a simple Pivot Table:
>>> 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'
Checking the titles
>>> fmt.getHeader() ['Purchases', 'OU', 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug']
Checking the rows
>>> 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]
Checking the footer
>>> fmt.getFooter() []
Now, new purchases
NY has purchases in jan. and feb.
>>> 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 has purchases only in feb.
>>> purchases += [ ... Purchase(cost=14, price=28, month='feb', ou='RJ'), ... Purchase(cost=14, price=28, month='feb', ou='RJ'), ... ]
Using the same params to Pivot Table
>>> 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 need the col jan. to be empty (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]
Changelog
1.1.1 - (2009-09-14)
fixes typos on text purchases - Thanks Leandro Lameiro :-) [lucmult]
1.1 - (2009-09-07)
fixes a bug, when a row doesn’t has value in a column (like fist col), and we were using value from the next col (second col). Fixes, too, the aggregation that was broken. And add tests o/ [lucmult]
1.0 - Initial Release
Initial release