Best way to iterate through dataframe to call google distance API

user_7053074 0
Viewed 290

I'd like to know what is the best solution to get distances from the google maps distance API for my dataframe composed of coordinates (origin & destination) which is around 75k rows. 

    #Origin                     #Destination

1   (40.7127837, -74.0059413)   (34.0522342, -118.2436849)
2   (41.8781136, -87.6297982)   (29.7604267, -95.3698028)
3   (39.9525839, -75.1652215)   (40.7127837, -74.0059413)
4   (41.8781136, -87.6297982)   (34.0522342, -118.2436849)
5   (29.7604267, -95.3698028)   (39.9525839, -75.1652215)

So far my code iterates through the dataframe and calls the API copying the distance value into the new "distance" column. 

df['distance'] = ""

for index, row in df.iterrows():
    result = gmaps.distance_matrix(row['origin'], row['destination'], mode='driving')
    status = result['rows'][0]['elements'][0]['status']
    if status == "OK":                               # Handle "no result" exception
        KM = int(result['rows'][0]['elements'][0]['distance']['value'] / 1000)
        df['distance'].iloc[index] = KM
    else:
        df['distance'].iloc[index] = 0

df.to_csv('distance.csv')

I get the desired result but from what I've read iterating through dataframe is rather inefficient and should be avoided. It took 20 secondes for 240 rows, so it would take 1h30 to do all dataframe. Note that once done, no need to re-run anymore, only new few new rows a month (~500).

What would we the best solution here ?

Edit: if anybody has experience with the google distance API and its limitations any tips/best practices is welcomed.

pandas api python database
1 Answers

I tried to understand about any limitations about concurrent calls here but I couldn't find anything. Few suggestions

Avoid loops

About your code I'd rather skip for loops and use apply first

def get_gmaps_distance(row):
    result = gmaps.distance_matrix(row['origin'], row['destination'], mode='driving')
    status = result['rows'][0]['elements'][0]['status']
    if status == "OK":
        KM = int(result['rows'][0]['elements'][0]['distance']['value'] / 1000)
    else:
        KM = 0
    return KM

df["distance"] = df.apply(get_gmaps_distance, axis=1)

Split your dataframe and use multiprocessing

import multiprocessing as mp

def parallelize(fun, vec, cores=mp.cpu_count()):
    with mp.Pool(cores) as p:
        res = p.map(fun, vec)
    return res

# split your dataframe in many chunks as the number of cores

df = np.array_split(df, mp.cpu_count())

# this use your functions for every chunck 
def parallel_distance(x):
    x["distance"] = x.apply(get_gmaps_distance, axis=1)
    return x

df = parallelize(parallel_distance, df)

df = pd.concat(df, ignore_index=True, sort=False)

Do not calculate twice the same distance (save $$$)

In case you have duplicates row you should drop some of them

grp = df.drop_duplicates(["origin", "destination"]).reset_index(drop=True)

Here I didn't overwrite df as it possibly contain more information you need and you can merge the results to it.

grp["distance"] = grp.apply(get_gmaps_distance, axis=1)

df = pd.merge(df, grp, how="left")

Reduce decimals

You should ask you this question: do I really need to be accurate to the 7th decimal? As 1 degree of latitude is ~111km the 7th decimal place gives you a precision up to ~1cm. You get the idea from this when-less-is-more where reducing decimals they improved the model.

Conclusion

If you can eventually use all the suggested methods you could get some interesting improvements. I'd like you to comment them here as I don't have a personal API key to try by myself.

user_48193760
Related