cross-tabulation pointers

selwyn selwyn at aotearoa.is.home.nz
Wed Jun 16 11:57:07 CEST 2004


hi there,

I would like some pointers on a pythonesque way of cross-tabulating an 
SQL result set.

i.e. from the result set below:
dept	| gender
-------------
hr	| m
hr	| f
sales	| m
sales	| m

should result in this (formatting aside):

dept	| M  |  F
------------------
hr	| 1  |  1
sales	| 2  |  0


I have come across a couple of server-side solutions such as the 
following from http://www.devshed.com/c/a/MySQL/MySQL-wizardry/1/

mysql> SELECT location, SUM(IF(gender='M',1,0)) AS M,
SUM(IF(gender='F',1,0)) AS F, COUNT(*) AS total
GROUP by location;

However, I am using SQLite and there is no IF function available. 
Moreover I am hoping that someone may point me towards an undoubtedly 
more pleasant python solution ;-)

thanks heaps,
Selwyn





More information about the Python-list mailing list