Welcome, guest | Sign In | My Account | Store | Cart

Notice! PyPM is being replaced with the ActiveState Platform, which enhances PyPM’s build and deploy capabilities. Create your free Platform account to download ActivePython or customize Python with the packages you require and get automatic updates.

Download
ActivePython
INSTALL>
pypm install collective.pivottable

How to install collective.pivottable

  1. Download and install ActivePython
  2. Open Command Prompt
  3. Type pypm install collective.pivottable
 Python 2.7Python 3.2Python 3.3
Windows (32-bit)
1.1.1dev-r97462 Available View build log
Windows (64-bit)
1.1.1dev-r97462 Available View build log
Mac OS X (10.5+)
1.1.1dev-r97462 Available View build log
Linux (32-bit)
1.1.1dev-r97462 Available View build log
Linux (64-bit)
1.1.1dev-r97462 Available View build log
 
License
GPL
Dependencies
Lastest release
version 1.1.1dev-r97462 on Jan 5th, 2011

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

System Message: ERROR/3 (<string>, line 27)

Inconsistent literal block quoting.

... 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'),

System Message: ERROR/3 (<string>, line 40)

Inconsistent literal block quoting.

... 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):

System Message: ERROR/3 (<string>, line 64)

Inconsistent literal block quoting.

... 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},

System Message: ERROR/3 (<string>, line 81)

Inconsistent literal block quoting.

... {'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()

System Message: ERROR/3 (<string>, line 96)

Inconsistent literal block quoting.

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

System Message: ERROR/3 (<string>, line 107)

Inconsistent literal block quoting.

... 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):

System Message: ERROR/3 (<string>, line 125)

Inconsistent literal block quoting.

... 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):

System Message: ERROR/3 (<string>, line 150)

Inconsistent literal block quoting.

... 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'),

System Message: ERROR/3 (<string>, line 165)

Inconsistent literal block quoting.

... 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'),

System Message: ERROR/3 (<string>, line 174)

Inconsistent literal block quoting.

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

System Message: ERROR/3 (<string>, line 196)

Inconsistent literal block quoting.

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

System Message: ERROR/3 (<string>, line 219)

Inconsistent literal block quoting.

[]

Now, new purchases

NY has purchases in jan. and feb.

>>> purchases = [Purchase(cost=5, price=10, month='jan', ou='NY'),

System Message: ERROR/3 (<string>, line 227)

Inconsistent literal block quoting.

... 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 += [

System Message: ERROR/3 (<string>, line 235)

Inconsistent literal block quoting.

... 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},

System Message: ERROR/3 (<string>, line 244)

Inconsistent literal block quoting.

... {'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()

System Message: ERROR/3 (<string>, line 253)

Inconsistent literal block quoting.

['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

Subscribe to package updates

Last updated Jan 5th, 2011

Download Stats

Last month:1

What does the lock icon mean?

Builds marked with a lock icon are only available via PyPM to users with a current ActivePython Business Edition subscription.

Need custom builds or support?

ActivePython Enterprise Edition guarantees priority access to technical support, indemnification, expert consulting and quality-assured language builds.

Plan on re-distributing ActivePython?

Get re-distribution rights and eliminate legal risks with ActivePython OEM Edition.