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

Question

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

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.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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()

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

Sign up