Shubham Srivastava Shubham Srivastava - 4 years ago 125
Python Question

making all possible combination in Python and also use of google API for csv/xlsx file

I have to write a script in python that will do following actions
I have a xlsx/csv file in which there are 300 cities listed in one column


  1. I have to make all pairs between them and also with help of google api I have to add their distance and travel time in the second column



my CSV file is looks like this:


=======
SOURCE
=======
Agra
Delhi
Jaipur


and expected output in csv/xlsx file be like this


=============================================
SOURCE | DESTINATION | DISTANCE | TIME_TRAVEL
=============================================
Agra | Delhi | 247 | 4
Agra | Jaipur | 238 | 4
Delhi | Agra | 247 | 4
Delhi | jaipur | 281 | 5
Jaipur | Agra | 238 | 4
Jaipur | Delhi | 281 | 5


and so on.. how to do this.?

NOTE: Distance and Travel Time are from google.

Answer Source

To make the pairs you can use itertools.permutations to get all possible pairs. Code for the same would be as :

import csv     # imports the csv module
import sys      # imports the sys module
import ast
import itertools    
source_list = []
destination_list = []
type_list = []list
f = open(sys.argv[1], 'rb')
g = open(sys.argv[2], 'wb')
 # opens the csv file
try:
    reader = csv.reader(f)
    my_list = list(reader) # creates the reader object
    for i in my_list:
        source_list.append(i[0])
    a = list(itertools.permutations(source_list, 2))
    for i in a:
        source_list.append(i[0])
        destination_list.append(i[1])
    mywriter=csv.writer(g)
    rows = zip(source_list,destination_list)
    mywriter.writerows(rows)
    g.close()

finally:
    f.close() 

Apart from that to get distance and time from the google this sample code may work for full debugging.

import csv     # imports the csv module
import sys      # imports the sys module
import urllib2,json
import ast  
api_google_key = ''
api_google_url = 'https://maps.googleapis.com/maps/api/distancematrix/json?origins='
source_list = []
destination_list = []
distance_list = []
duration_list = []
f = open(sys.argv[1], 'rb')
g = open(sys.argv[2], 'wb')
 # opens the csv file
try:
    reader = csv.reader(f)
    my_list = list(reader) # creates the reader object
    for i in my_list:
    if i:
            s = (i[0])
        src = s.replace(" ","")
            d = (i[1])
        dest = d.replace(" ","")
        source = ''.join(e for e in src if e.isalnum())
        destination = ''.join(e for e in dest if e.isalnum())
        print 'source status = '+str(source.isalnum())
        print 'dest status = '+str(destination.isalnum())
        source_list.append(source)
            destination_list.append(destination)
            request = api_google_url+source+'&destinations='+destination+'&key='+api_google_key
        print request
            dist = json.load(urllib2.urlopen(request))
        if dist['rows']:
                if 'duration' in dist['rows'][0]['elements'][0].keys():
                        duration_dict = dist['rows'][0]['elements'][0]['duration']['text']
                        distance_dict = dist['rows'][0]['elements'][0]['distance']['text']
                else:
                    duration_dict = 0
                    distance_dict = 0
        else:
                duration_dict = 0
                distance_dict = 0

            distance_list.append(distance_dict)
            duration_list.append(duration_dict)
    mywriter=csv.writer(g)
    rows = zip(source_list,destination_list,distance_list,duration_list)
    mywriter.writerows(rows)
    g.close()

finally:
    f.close() 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download