Yet Another Tabular Data Question

[Ricardo Aráoz]

carllemp at gmail.com wrote:
> On Nov 29, 5:46 pm, patrick.wa... at gmail.com 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()