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 2 2082
>>>>> "selwyn" == selwyn <se****@aotearoa.is.home.nz> writes:
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
many thanks!
John Hunter wrote: >>"selwyn" == selwyn <se****@aotearoa.is.home.nz> writes:
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Web Science |
last post by:
Site and Features: http://www.eigensearch.com
Search engine, eigenMethod, eigenvector, mathematical, manifolds, science, technical, search tools, eigenmath, Jacobian, quantum, mechanics,...
|
by: * ProteanThread * |
last post by:
but depends upon the clique:
...
|
by: rollasoc |
last post by:
Hi,
Doing a bit of system testing on a Windows 98 laptop. (.Net 1.1 app).
Did a bit of testing. Loaded a previously saved file. A gray box
appeared with the text and buttons all white...
|
by: Web Science |
last post by:
Site and Features: http://www.eigensearch.com
Search engine, eigenMethod, eigenvector, mathematical, manifolds, science, technical, search tools, eigenmath, Jacobian, quantum, mechanics,...
|
by: David Peach |
last post by:
Hello, hope somebody here can help me... I have a query that lists
defects recorded in a user defined date range. That query is then used
as the source for a Cross Tab query that cross-tabs count...
|
by: Jeff Rodriguez |
last post by:
Here's what I want do:
Have a main daemon which starts up several threads in a Boss-Queue structure.
From those threads, I want them all to sit and watch a queue. Once an entry
goes into the...
|
by: Web Science |
last post by:
Site and Features: http://www.eigensearch.com
Search engine, eigenMethod, eigenvector, mathematical, manifolds, science, technical, search tools, eigenmath, Jacobian, quantum, mechanics,...
|
by: Rob Woodworth |
last post by:
Hi,
I'm having serious problems getting my report to work. I need to
generate a timesheet report which will contain info for one employee between
certain dates (one week's worth of dates). I...
|
by: Bart Van der Donck |
last post by:
Hello,
I'm presenting my new library 'AJAX Cross Domain' - a javascript
extension that allows to perform cross-domain AJAX requests.
http://www.ajax-cross-domain.com/
Any comments or...
|
by: ampo |
last post by:
Hello.
Can anyone help with cross-domain problem?
I have HTML page from server1 that send xmlHTTPRequest to server2.
How can I do it?
Thanks.
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |