Data normalization with Python
Question: This is a sample of a csv file that will eventually be loaded to a MySQL
database. The issue is that the data is not normalized, as there are multiple
values in the `routes` column.

    
    
    stop_id,on_street,cross_street,routes,boardings
    49,HARRISON,PAULINA,"126, 755",1.6
    50,ASHLAND,CONGRESS,"9,126",14.8
    51,ASHLAND,VAN BUREN,"9,126",100.9
    52,JACKSON,1900 W.(MALCOLM X COLL.),126,82.8
    

I would like to extract the `routes` column into a new csv file with `stop_id`
and `route` as the column headers and there be only 1 route per row. I've
already tried to import the un-normalized csv into a MySQL database but was
unable to pragmatically normalize it. Any help doing this in Python before
importing to the database would be greatly appreciated.


Answer: This will create one row per route. You can fiddle with the inner for loop if
you want all routes in one row.

    
    
    import csv
    import re
    
    sample = """stop_id,on_street,cross_street,routes,boardings
    49,HARRISON,PAULINA,"126, 755",1.6
    50,ASHLAND,CONGRESS,"9,126",14.8
    51,ASHLAND,VAN BUREN,"9,126",100.9
    52,JACKSON,1900 W.(MALCOLM X COLL.),126,82.8"""
    
    open('sample.csv','w').write(sample)
    
    with open('sample.csv') as sample, open('output.csv','w') as output:
        reader = csv.reader(sample)
        writer = csv.writer(output)
        # discard input header
        next(reader)
        # write output header
        writer.writerow(['stop_id', 'route'])
        # process rows
        for row in reader:
            if row:
                for route in re.split(r', *', row[3].replace('"', '')):
                    writer.writerow([row[0], route])
    
    
    print open('output.csv').read()
    

