Compare rows of csv and work out percentage
Question: I'm relatively new to Python. I'm trying to find a way to create a script that
looks at a CSV file called "data_old" from a previous month, and compares it
with the data in a more recent month called "data_new", then finally outputs
that data into a new CSV "data_compare".

The files each month are consistently laid out and look like this (example)

> Month 1  
>  Company, StaffNumber, NeedToPass, Passed, %age meeting requirement  
>  xxxxxxxx, 100, 80, 30, 30%
>
> Month 3  
>  Company, StaffNumber, NeedToPass, Passed, %meeting requirement  
>  xxxxxxxx, 101, 81, 54, 60%

I'm trying to get the output file to compare the data from all rows and show
me "Percentage improved, instead of "Percentage meeting requirement". Nothing
I try seems to work.

As the numbers change all the time the only common data will be the company
name.

I need a simple, explanatory way with comments... as I'd like to understand
the logic so I can modify it and add functions.

Much appreciated.


Answer: Here ist a python code example which might does what you want. This script
asumes that the two input csv files have the same amount of lines. In the
function `test` the function `zip` i used, which stops if one list is at the
end. If your files have a different amount of lines you have to manually loop
over both. But I think it is a good starting point

    
    
    #!/usr/bin/env python
    # -*- coding: utf-8 -*-
    import csv
    
    
    def parse_csv(filename, sort_row=0, as_dict=False, delimiter=","):
        r = list()
        with open(filename, "rb") as f:
            # make csv reader object
            reader = csv.reader(f, delimiter=delimiter)
    
            if as_dict:
                # make dict if desired
                header = [h.strip() for h in reader.next()]
    
            for row in reader:
                if as_dict:
                    # make dict if desired
                    r.append(dict(zip(header, row)))
                else:
                    # strip each item in the row and append it to the return list
                    r.append([h.strip() for h in row])
    
        # sort the list by the first item (company name in this example)
        r.sort(key=lambda x: x[sort_row])
        return r
    
    
    def write_csv(filename, fieldnames, rows, delimiter=","):
        with open(filename, "w") as f:
            # make csv writer object
            writer = csv.writer(f, delimiter=delimiter)
            # write the first header line
            writer.writerow(fieldnames)
            for row in rows:
                # write each row
                writer.writerow(row)
    
    
    def test():
        data_old = parse_csv("m1.csv")
        data_new = parse_csv("m2.csv")
        #write_csv("data_compare.csv", data_old[:1][0], data_old[1:])
    
        result = list()
    
        # loop over the items (skipping the first header row)
        for o, n in zip(data_old[1:], data_new[1:]):
            # calculate the improvement (or whatever needs to be calculated)
            value = float(n[4].replace("%", "")) - float(o[4].replace("%", ""))
    
            # create the row
            result.append([o[0], "%s%%" % value, o[4], n[4]])
            #result.append(["%s%%" % value])
    
        header = ["Company", "Percentage improved", "old", "new"]
        #header = ["Company", "Percentage improved"]
        write_csv("data_compare.csv", header, result)
    
    
    if __name__ == '__main__':
        test()
    

