database/googleDistanceMatrix/clean_data.py

import re
import pandas as pd
import csv

def extract_before_parenthesis(s):
    match = re.search(r'^(.*?)\([^)]*\)', s)
    return match.group(1) if match else s

if __name__ == '__main__':
    data = pd.read_csv('/home/xj/toolAugEnv/code/toolConstraint/database/googleDistanceMatrix/distance.csv')
    data = data.to_dict(orient = 'split')
    fieldnames = ['origin', 'destination', 'cost', 'duration', 'distance']
    with open('/home/xj/toolAugEnv/code/toolConstraint/database/googleDistanceMatrix/distance2.csv', 'w', newline='') as csvfile:
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
        writer.writeheader()
        for row in data['data']:
            writer.writerow({'origin': extract_before_parenthesis(row[0]), 'destination': extract_before_parenthesis(row[1]), 'cost': row[2], 'duration': row[3], 'distance': row[4]})