Yet Another Tabular Data Question

Discussion in 'Python' started by patrick.waldo@gmail.com, Nov 29, 2007.

  1. Guest

    Hi all,

    Fairly new Python guy here. I am having a lot of trouble trying to
    figure this out. I have some data on some regulations in Excel and I
    need to basically add up the total regulations for each country--a
    statistical analysis thing that I'll copy to another Excel file.
    Writing with pyExcelerator has been easier than reading with xlrd for
    me...So that's what I did first, but now I'd like to learn how to
    crunch some data.

    The input looks like this:

    Country Module
    Topic # of Docs
    Argentina Food and Consumer Products Cosmetics 1
    Argentina Food and Consumer Products Cosmetics 8
    Argentina Food and Consumer Products Food Additives 1
    Argentina Food and Consumer Products Food Additives 1
    Australia Food and Consumer Products Drinking Water 7
    Australia Food and Consumer Products Food Additives 3
    Australia Food and Consumer Products Food Additives 1
    etc...

    So I need to add up all the docs for Argentina, Australia, etc...and
    add up the total amount for each Topic for each country so, Argentina
    has 9 Cosmetics laws and 2 Food Additives Laws, etc...

    So, here is the reduced code that can't add anything...Any thoughts
    would be really helpful.

    import xlrd
    import pyExcelerator
    from pyExcelerator import *

    #Open Excel files for reading and writing
    path_file = "c:\\1\\data.xls"
    book = xlrd.open_workbook(path_file)
    Counts = book.sheet_by_index(1)
    wb=pyExcelerator.Workbook()
    matrix = wb.add_sheet("matrix")

    #Get all Excel data
    n=1
    data = []
    while n<Counts.nrows:
    data.append(Counts.row_values(n, start_colx=0, end_colx=None))
    n=n+1

    COUNTRY, MODULE, TOPIC,DOCS = range(4)
    COUNTRY_TOT = []
    n=0
    while n<len(data):
    x=n
    while data[n][COUNTRY]==data[n+1][COUNTRY]:
    n=n+1
    print sum(data[x:n][FT_DOCS])

    wb.save('c:\\1\\matrix.xls')
     
    , Nov 29, 2007
    #1
    1. Advertising

  2. Guest

    On Nov 29, 5:46 pm, wrote:
    > Hi all,
    >
    > Fairly new Python guy here. I am having a lot of trouble trying to
    > figure this out. I have some data on some regulations in Excel and I
    > need to basically add up the total regulations for each country--a
    > statistical analysis thing that I'll copy to another Excel file.
    > Writing with pyExcelerator has been easier than reading with xlrd for
    > me...So that's what I did first, but now I'd like to learn how to
    > crunch some data.
    >
    > The input looks like this:
    >
    > Country Module
    > Topic # of Docs
    > Argentina Food and Consumer Products Cosmetics 1
    > Argentina Food and Consumer Products Cosmetics 8
    > Argentina Food and Consumer Products Food Additives 1
    > Argentina Food and Consumer Products Food Additives 1
    > Australia Food and Consumer Products Drinking Water 7
    > Australia Food and Consumer Products Food Additives 3
    > Australia Food and Consumer Products Food Additives 1
    > etc...
    >
    > So I need to add up all the docs for Argentina, Australia, etc...and
    > add up the total amount for each Topic for each country so, Argentina
    > has 9 Cosmetics laws and 2 Food Additives Laws, etc...
    >
    > So, here is the reduced code that can't add anything...Any thoughts
    > would be really helpful.
    >
    > import xlrd
    > import pyExcelerator
    > from pyExcelerator import *
    >
    > #Open Excel files for reading and writing
    > path_file = "c:\\1\\data.xls"
    > book = xlrd.open_workbook(path_file)
    > Counts = book.sheet_by_index(1)
    > wb=pyExcelerator.Workbook()
    > matrix = wb.add_sheet("matrix")
    >
    > #Get all Excel data
    > n=1
    > data = []
    > while n<Counts.nrows:
    > data.append(Counts.row_values(n, start_colx=0, end_colx=None))
    > n=n+1
    >
    > COUNTRY, MODULE, TOPIC,DOCS = range(4)
    > COUNTRY_TOT = []
    > n=0
    > while n<len(data):
    > x=n
    > while data[n][COUNTRY]==data[n+1][COUNTRY]:
    > n=n+1
    > print sum(data[x:n][FT_DOCS])
    >
    > wb.save('c:\\1\\matrix.xls')


    Considering the topic of the usenet group, I know this is heresy but
    I'd suggest using the Pivot Table feature in Excel. The whole thing
    will be done if 5 clicks and no code. Simple is better than complex.
     
    , Nov 30, 2007
    #2
    1. Advertising

  3. wrote:
    > On Nov 29, 5:46 pm, wrote:
    >> Hi all,
    >>
    >> Fairly new Python guy here. I am having a lot of trouble trying to
    >> figure this out. I have some data on some regulations in Excel and I
    >> need to basically add up the total regulations for each country--a
    >> statistical analysis thing that I'll copy to another Excel file.
    >> Writing with pyExcelerator has been easier than reading with xlrd for
    >> me...So that's what I did first, but now I'd like to learn how to
    >> crunch some data.
    >>
    >> The input looks like this:
    >>
    >> Country Module
    >> Topic # of Docs
    >> Argentina Food and Consumer Products Cosmetics 1
    >> Argentina Food and Consumer Products Cosmetics 8
    >> Argentina Food and Consumer Products Food Additives 1
    >> Argentina Food and Consumer Products Food Additives 1
    >> Australia Food and Consumer Products Drinking Water 7
    >> Australia Food and Consumer Products Food Additives 3
    >> Australia Food and Consumer Products Food Additives 1
    >> etc...
    >>
    >> So I need to add up all the docs for Argentina, Australia, etc...and
    >> add up the total amount for each Topic for each country so, Argentina
    >> has 9 Cosmetics laws and 2 Food Additives Laws, etc...
    >>
    >> So, here is the reduced code that can't add anything...Any thoughts
    >> would be really helpful.
    >>
    >> import xlrd
    >> import pyExcelerator
    >> from pyExcelerator import *
    >>
    >> #Open Excel files for reading and writing
    >> path_file = "c:\\1\\data.xls"
    >> book = xlrd.open_workbook(path_file)
    >> Counts = book.sheet_by_index(1)
    >> wb=pyExcelerator.Workbook()
    >> matrix = wb.add_sheet("matrix")
    >>
    >> #Get all Excel data
    >> n=1
    >> data = []
    >> while n<Counts.nrows:
    >> data.append(Counts.row_values(n, start_colx=0, end_colx=None))
    >> n=n+1
    >>
    >> COUNTRY, MODULE, TOPIC,DOCS = range(4)
    >> COUNTRY_TOT = []
    >> n=0
    >> while n<len(data):
    >> x=n
    >> while data[n][COUNTRY]==data[n+1][COUNTRY]:
    >> n=n+1
    >> print sum(data[x:n][FT_DOCS])
    >>
    >> wb.save('c:\\1\\matrix.xls')

    >


    Check itertools.groupby() and operator.itemgetter()
     
    Ricardo Aráoz, Nov 30, 2007
    #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. t
    Replies:
    3
    Views:
    583
    Pier Paolo
    Feb 16, 2006
  2. Luigi Donatello Asero

    Layout versus tabular data.

    Luigi Donatello Asero, Oct 14, 2005, in forum: HTML
    Replies:
    8
    Views:
    488
    Luigi Donatello Asero
    Oct 14, 2005
  3. Berehem
    Replies:
    4
    Views:
    561
    Lawrence Kirby
    Apr 28, 2005
  4. hyena
    Replies:
    3
    Views:
    1,178
    hyena
    Aug 29, 2007
  5. James Broome

    Tabular Data Control question

    James Broome, Aug 20, 2003, in forum: Javascript
    Replies:
    1
    Views:
    94
    James Broome
    Aug 20, 2003
Loading...

Share This Page