<br><br><div><span class="gmail_quote">On 7/30/07, <b class="gmail_sendername">Geoffrey Zhu</b> <<a href="mailto:zyzhu2000@gmail.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">zyzhu2000@gmail.com
</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi Timothy,<br><br>On 7/30/07, Timothy Hochberg <<a href="mailto:tim.hochberg@ieee.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">tim.hochberg@ieee.org</a>> wrote:<br>><br>><br>> On 7/30/07, Geoffrey Zhu <
<a href="mailto:zyzhu2000@gmail.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">zyzhu2000@gmail.com
</a>> wrote:<br>> > Hi Everyone,<br>> ><br>> > I am wondering what is the best (and fast) way to build a pivot table<br>> > aside from the 'brute force way?'<br>><br>> What's the brute force way? It's easier to offer an improved suggestion if
<br>> we know what we're trying to beat.<br>><br>> > I want to transform an numpy array into a pivot table. For example, if<br>> > I have a numpy array like below:<br>> ><br>> > Region Date # of Units
<br>> > ---------- ---------- --------------<br>> > East 1/1 10<br>> > East 1/1 20<br>> > East 1/2 30<br>> > West 1/1 40
<br>> > West 1/2 50<br>> > West 1/2 60<br>> ><br>> > I want to transform this into the following table, where f() is a<br>> > given aggregate function:<br>
> ><br>> > Date<br>> > Region 1/1 1/2<br>> > ----------<br>> > East f(10,20) f(30)<br>> > West f(40) f(50,60)<br>> >
<br>> ><br>> > I can regroup them into 'sets' and do it the brute force way, but that<br>> > is kind of slow to execute. Does anyone know a better way?<br>><br>> I would use a python to dictionary to assemble lists of values. I would key
<br>> off (region/date) tuples. In outline:<br>><br>> map = {}<br>> dates = set()<br>> regions = set()<br>> for (region, date, units) in data:<br>> dates.add(date)<br>> regions.add(regions)
<br>> key = (region, date)<br>> if key not in map:<br>> map[key] = []<br>> map[key].append(data)<br>><br>> Once you have map, regions and dates, you can trivially make a table as<br>
> above. The details will depend on what format you want the table to have,<br>> but it should be easy to do.<br>></blockquote><div><br>[SNIP] <br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
The 'brute-force' way is basically what you suggested -- looping<br>through all the records and building a two-way hash-table of the data.<br><br>The problem of the brute-force' approach is that it is not taking
<br>advantage of facilities of numpy and can be slow in speed. If only<br>there is some built-in mechanism in numpy to handle this.</blockquote><div><br>I'm curious; have you tried this and found it slow, or is this a hunch based on the reputed slowness of Python? Algorithmically, you can't do much better: the dictionary and set operations are O(1), so the whole operation is O(N), and you won't do any better than that, order wise. What your left with is trying to reduce constant factors.
<br> <br>There are various ways one might go about reducing constant factors, but they depend on the details of the problem. For example, if the dates are dense and you are going to parse them anyway, you could replace the hash with table that you index into with the date as an integer. I'm not sure that you are going to do a lot better than the brute force algorithm in the generic force case though.
<br><br>-tim<br><br><br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">The other thing I am not sure is in your map object above, do I append
<br>the row number to the numpy array or do I append the row object (such<br>as data[r])?<br><br>Thanks,<br>Geoffrey<br>_______________________________________________<br>Numpy-discussion mailing list<br><a href="mailto:Numpy-discussion@scipy.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
Numpy-discussion@scipy.org</a><br><a href="http://projects.scipy.org/mailman/listinfo/numpy-discussion" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://projects.scipy.org/mailman/listinfo/numpy-discussion
</a><br></blockquote></div><br><br clear="all"><br>-- <br>
. __<br>. |-\<br>.<br>. <a href="mailto:tim.hochberg@ieee.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">tim.hochberg@ieee.org</a>