cross-tabulation pointers

[selwyn]

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

 

[John Hunter]

selwyn> hi there, I would like some pointers on a pythonesque way
selwyn> of cross-tabulating an SQL result set.

Supposing your results are a row of dicts

results = (
{'dept' : 'hr', 'gender' : 'm'},
{'dept' : 'hr', 'gender' : 'f'},
{'dept' : 'sales', 'gender' : 'm'},
{'dept' : 'sales', 'gender' : 'm'},
)

count = {}
for row in results:
dept = row['dept']
if row['gender']=='m': ind = 0
else: ind = 1
count.setdefault(dept, [0,0])[ind] += 1

print count

 

[selwyn]

many thanks!

selwyn> hi there, I would like some pointers on a pythonesque way
selwyn> of cross-tabulating an SQL result set.

Supposing your results are a row of dicts

results = (
{'dept' : 'hr', 'gender' : 'm'},
{'dept' : 'hr', 'gender' : 'f'},
{'dept' : 'sales', 'gender' : 'm'},
{'dept' : 'sales', 'gender' : 'm'},
)

count = {}
for row in results:
dept = row['dept']
if row['gender']=='m': ind = 0
else: ind = 1
count.setdefault(dept, [0,0])[ind] += 1

print count