Sort and add elements of a list based on a common attribute
Question: I have a csv file that has the data:

    
    
    California  C1      A         1
    .           .       .         .
    .           .       .         .
    .           .       .         .
    .           .       .         .
    

so it looks like this when viewed in python:

    
    
    ['California','C1','A',1] 
    ['Hawaii','H1','B',2]
    ['California','C1','A',3]
    ['California','C2','A',4]
    ['Hawaii','H1','A',5]
    ['Hawaii','H1','A',6]
    ['California','C1','B',7]
    ['Hawaii','H2','B',8]
    ['California','C1',B',9]
    ['Hawaii','H2','A',10]
    

I wanted to have the output as top 1 of each list, as follows:

    
    
    ['California','C1',B',16]
    ['California','C2','A',4]
    ['Hawaii','H1','A',11]
    ['Hawaii','H2','A',10]
    

basically. I wanted to sum the last part of the list based on the first 3
attributes of the list then return the top 1 given the three attributes. My
code are as follows:

    
    
    import collections
    
    def top_1(list):
        ranking = collections.Counter(list)
        return [elem for elem, _ in sorted(counts.most_common(),key=lambda x:(‐x[1], x[0]))
        [:1]]
    csvReader =csv.reader(open('data.csv','rb'), delimiter=',', quotechar='"')
        data = []
        for i in range(int(line[3]):
           data.append([line[0], line[1], line[2]))
        print top_1(data)
    

but it does not give me the output that I am expecting.


Answer: The following approach should give you the desired output:

    
    
    from collections import Counter    
    from itertools import groupby, islice
    import csv
    
    counts = Counter()
    
    with open('data.csv', 'rb') as f_input:
        csv_input = csv.reader(f_input)
    
        for row in csv_input:
            counts.update({tuple(row[:3]) : int(row[3])})
    
    output = []        
    for k, g in groupby(sorted(counts.iteritems(), key=lambda x:(x[0][0], -x[1])), lambda x:x[0][0]):
        output.extend([list(e[0]) + [e[1]] for e in islice(g, 0, 2)])
    
    print output 
    

This will display:

    
    
    [['California', 'C1', 'B', 16], ['California', 'C2', 'A', 4], ['Hawaii', 'H1', 'A', 11], ['Hawaii', 'H2', 'A', 10]]
    

