patrick.waldo
12/28/2007 1:01:00 PM
Wow, I did not realize it would be this complicated! I'm fairly new
to Python and somehow I thought I could find a simpler solution. I'll
have to mull over this to fully understand how it works for a bit.
Thanks a lot!
On Dec 28, 4:03 am, John Machin <sjmac...@lexicon.net> wrote:
> On Dec 28, 11:48 am, John Machin <sjmac...@lexicon.net> wrote:
>
> > On Dec 28, 10:05 am, patrick.wa...@gmail.com wrote:
>
> > > If you have any ideas about how to solve this pivot table issue, which
> > > seems to be scant on Google, I'd much appreciate it. I know I can do
> > > this in Excel easily with the automated wizard, but I want to know how
> > > to do it myself and format it to my needs.
>
> > Watch this space.
>
> Tested as much as you see:
>
> 8<---
> class SimplePivotTable(object):
>
> def __init__(
> self,
> row_order=None, col_order=None, # see example
> missing=0, # what to return for an empty cell. Alternatives:
> '', 0.0, None, 'NULL'
> ):
> self.row_order = row_order
> self.col_order = col_order
> self.missing = missing
> self.cell_dict = {}
> self.row_total = {}
> self.col_total = {}
> self.grand_total = 0
> self.headings_OK = False
>
> def add_item(self, row_key, col_key, value):
> self.grand_total += value
> try:
> self.col_total[col_key] += value
> except KeyError:
> self.col_total[col_key] = value
> try:
> self.cell_dict[row_key][col_key] += value
> self.row_total[row_key] += value
> except KeyError:
> try:
> self.cell_dict[row_key][col_key] = value
> self.row_total[row_key] += value
> except KeyError:
> self.cell_dict[row_key] = {col_key: value}
> self.row_total[row_key] = value
>
> def _process_headings(self):
> if self.headings_OK:
> return
> self.row_headings = self.row_order or
> list(sorted(self.row_total.keys()))
> self.col_headings = self.col_order or
> list(sorted(self.col_total.keys()))
> self.headings_OK = True
>
> def get_col_headings(self):
> self._process_headings()
> return self.col_headings
>
> def generate_row_info(self):
> self._process_headings()
> for row_key in self.row_headings:
> row_dict = self.cell_dict[row_key]
> row_vals = [row_dict.get(col_key, self.missing) for
> col_key in self.col_headings]
> yield row_key, self.row_total[row_key], row_vals
>
> def get_col_totals(self):
> self._process_headings()
> row_dict = self.col_total
> row_vals = [row_dict.get(col_key, self.missing) for col_key in
> self.col_headings]
> return self.grand_total, row_vals
>
> if __name__ == "__main__":
>
> data = [
> ['Bob', 'Morn', 240],
> ['Bob', 'Aft', 300],
> ['Joe', 'Morn', 70],
> ['Joe', 'Aft', 80],
> ['Jil', 'Morn', 100],
> ['Jil', 'Aft', 150],
> ['Bob', 'Aft', 40],
> ['Bob', 'Aft', 5],
> ['Dozy', 'Aft', 1], # Dozy doesn't show up till lunch-time
> ]
> NAME, TIME, AMOUNT = range(3)
>
> print
> ptab = SimplePivotTable(
> col_order=['Morn', 'Aft'],
> missing='uh-oh',
> )
> for s in data:
> ptab.add_item(row_key=s[NAME], col_key=s[TIME],
> value=s[AMOUNT])
> print ptab.get_col_headings()
> for x in ptab.generate_row_info():
> print x
> print 'Tots', ptab.get_col_totals()
> 8<---