[Numpy-discussion] How to implement a 'pivot table?'

[Bruce Southey]

Hi,
The hard part is knowing what aggregate function that you want. So a
hard way, even after cheating, to take the data provided is given
below. (The Numpy Example List was very useful especially on the where
function)!

I tried to be a little generic so you can replace the sum by any
suitable function and probably the array type as well. Of course it is
not complete because you still need to know the levels of the 'rows'
and 'columns' and also is not efficient as it has loops.

Bruce

from numpy import *
A=array([[1,1,10],
         [1,1,20],
         [1,2,30],
         [2,1,40],
         [2,2,50],
         [2,2,60] ])
C = zeros((2,2))

for i in range(2):
      crit1 = (A[:,0]==1+i)
      subA=A[crit1,1:]
      for j in range(2):
            crit2 = (subA[:,0]==1+j)
            subB=subA[crit2,1:]
            C[i,j]=subB.sum()


print C

On 7/30/07, Geoffrey Zhu <zyzhu2000 at gmail.com> wrote:
> Hi Everyone,
>
> I am wondering what is the best (and fast) way to build a pivot table
> aside from the 'brute force way?'
>
> I want to transform an numpy array into a pivot table. For example, if
> I have a numpy array like below:
>
> Region     Date          # of Units
> ----------    ----------        --------------
> East        1/1             10
> East        1/1             20
> East        1/2             30
> West       1/1             40
> West       1/2             50
> West       1/2             60
>
> I want  to transform this into the following table, where f() is a
> given aggregate function:
>
>            Date
> Region           1/1          1/2
> ----------
> East         f(10,20)         f(30)
> West        f(40)             f(50,60)
>
>
> I can regroup them into 'sets' and do it the brute force way, but that
> is kind of slow to execute. Does anyone know a better way?
>
>
> Thanks,
> Geoffrey
> _______________________________________________
> Numpy-discussion mailing list
> Numpy-discussion at scipy.org
> http://projects.scipy.org/mailman/listinfo/numpy-discussion
>