Pivot Table/Groupby/Sum question

[patrick.waldo at gmail.com]

Hi all,

I tried reading http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/334695
on the same subject, but it didn't work for me.  I'm trying to learn
how to make pivot tables from some excel sheets and I am trying to
abstract this into a simple sort of example.  Essentially I want to
take input data like this:

Name  Time of day  Amount
Bob   Morn            240
Bob   Aft               300
Joe   Morn             70
Joe   Aft                80
Jil   Morn              100
Jil   Aft                 150

And output it as:

Name  Total    Morning  Afternoon
Bob    540      240        300
Joe     150      70          80
Jil       250     100        150
Total   940      410        530

The writing the output part is the easy part.  However, I have a
couple problems.  1) Grouping by name seems to work perfectly, but
working by time does not.  ie

I will get:
Bob
	240
	300
Joe
	70
	80
Jil
	100
	150
which is great but...
Morn
	240
Aft
	300
Morn
	70
Aft
	80
Morn
	100
Aft
	150
And not
Morn
        240
        70
        100
Aft
       300
       80
       150

2) I can't figure out how to sum these values because of the
iteration.  I always get an error like: TypeError: iteration over non-
sequence

Here's the code:

from itertools import groupby

data = [['Bob', 'Morn', 240],['Bob', 'Aft', 300],['Joe', 'Morn', 70],
['Joe', 'Aft', 80],\
            ['Jil', 'Morn', 100],['Jil', 'Aft', 150]]

NAME, TIME, AMOUNT=range(3)
for k, g in groupby(data, key=lambda r: r[NAME]):
    print k
    for record in g:
        print "\t", record[AMOUNT]
for k, g in groupby(data, key=lambda r: r[TIME]):
    print k
    for record in g:
        print "\t", record[AMOUNT]

Thanks for any comments