shorten csv file based on rules python
Question: I am stuck writing the following program.

I have a csv file

    
    
    "SNo","Column1","Column2"
    "A1","X","Y"
    "A2","A","B"
    "A1","X","Z"
    "A3","M","N"
    "A1","D","E"
    

I want to shorten this csv to follow these rules

    
    
    a.) If the SNo occurs more than once in the file, 
        combine all column1 and column2 entries of that serial number
    b.) If same column1 entries and column2 entries occur more than once, 
        then do not combine them twice.
    

Therefore the output of the above should be

    
    
    "SNo","Column1","Column2"
    "A1","X,D","Y,Z,E"
    "A2","A","B"
    "A3","M","N"
    

So far I am reading the csv file, iterating the rows. checking if SNo of next
row is same as the previous row. Whats the best way to combine.

    
    
    import csv
    temp = "A1"
    col1=""
    col2=""
    col3=""
    with open("C:\\file\\file1.csv","rb") as f:
        reader = csv.reader(f)
        for row in reader:
            if row[0] == temp:
                continue
            col1 = col1+row[1]
            col2=col2+row[2]
            col3=col3+row[3]
            temp = row[0]
            print row[0]+";"+col1+";"+col2+";"+col3
        col1=""
        col2=""
        col3=""
    

Please let me know a good way to do this.

Thanks


Answer: The simplest approach is to maintain a dictionary with keys as serial numbers
and sets to contain the columns. Then you could do something like the
following:

    
    
    my_dict = {}
    
    for row in reader:
        if not row[0] in my_dict.keys():
            my_dict[row[0]] = [set(), set()]
    
        my_dict[row[0]][0].add(row[1])
        my_dict[row[0]][1].add(row[2])
    

Writing the file out (to a file opened as `file_out`) would be as simple as
iterating through the dictionary using a join command:

    
    
    for k in my_dict.keys(): 
        file_out.write("{0},\"{1}\",\"{2}\"\n".format(
            k,
            ','.join([x for x in my_dict[k][0]]),
            ','.join([x for x in my_dict[k][1]])
        ))
    

