Skip to main content

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

>>> purschases = [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'), ]

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

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:

>>> purschases = [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:

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

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

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

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

collective.pivottable-1.1dev-r96945.tar.gz (8.1 kB view details)

Uploaded Source

Built Distribution

File details

Details for the file collective.pivottable-1.1dev-r96945.tar.gz.

File metadata

File hashes

Hashes for collective.pivottable-1.1dev-r96945.tar.gz
Algorithm Hash digest
SHA256 10dbe4ee5123cbe5629891f583b6139a91e538a4c494eb7994a1549bb1a8fe11
MD5 180f87cc5e508c3f0f84239a3d00eaed
BLAKE2b-256 70e904589aee06ba56669a6e7a5321a7c786bfb15c6c4f501e96fa1c64420f6b

See more details on using hashes here.

File details

Details for the file collective.pivottable-1.1dev_r96945-py2.4.egg.

File metadata

File hashes

Hashes for collective.pivottable-1.1dev_r96945-py2.4.egg
Algorithm Hash digest
SHA256 38543f4707db1b7aec6baaae558226c8048916adca1209e442f7aca57ef9866f
MD5 8f42df95f2e57aebb0a480cb04865603
BLAKE2b-256 90e1719d1f13e214c09d7fa9f5134bdadb3dc378e57187cb74dcef1b04a5e5ae

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page