cross-tabulation pointers

Discussion in 'Python' started by selwyn, Jun 16, 2004.

  1. selwyn

    selwyn Guest

    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
     
    selwyn, Jun 16, 2004
    #1
    1. Advertising

  2. selwyn

    John Hunter Guest

    >>>>> "selwyn" == selwyn <> 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
     
    John Hunter, Jun 16, 2004
    #2
    1. Advertising

  3. selwyn

    selwyn Guest

    many thanks!

    John Hunter wrote:
    >>>>>>"selwyn" == selwyn <> 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
    >
     
    selwyn, Jun 17, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    331
  2. Ajith Prasad

    Cross-tabulation module in Python

    Ajith Prasad, Oct 5, 2004, in forum: Python
    Replies:
    0
    Views:
    333
    Ajith Prasad
    Oct 5, 2004
  3. Robert Brewer

    RE: Cross-tabulation module in Python

    Robert Brewer, Oct 5, 2004, in forum: Python
    Replies:
    1
    Views:
    653
    Harry George
    Oct 6, 2004
  4. Robert Brewer

    RE: Cross-tabulation module in Python

    Robert Brewer, Oct 6, 2004, in forum: Python
    Replies:
    0
    Views:
    367
    Robert Brewer
    Oct 6, 2004
  5. chris smith

    Cross tabulation

    chris smith, Nov 25, 2005, in forum: Ruby
    Replies:
    0
    Views:
    95
    chris smith
    Nov 25, 2005
Loading...

Share This Page