Apply to populate a dataframe based on fields in another dataframe

Viewed 52

I have a DataFrame called location_details that contains location names, latitudes and longitudes:

location_details:

Index       Latitude    Longitude
London      51.3245     -3.2341
Paris       52.4523     -3.5334
New York    50.2313     -3.3245 

Based on this dataframe, I want to create another dataframe with all locations in index, all locations in columns and the distance in km between any two locations as fields. It would look something like this:

new_dataframe:

Index       London    Paris    New York
London      0         1000     2000
Paris       1000      0        3000
New York    2000      3000     0 

I'm using this formula to calculate the distances:

import geopy.distance

coords_1 = (52.2296756, 21.0122287)
coords_2 = (52.406374, 16.9251681)

print (geopy.distance.distance(coords_1, coords_2).km)

What I've done, which is un-pythonic and not optimised in any way, is this:

  1. I've first created a list called location_name that contains all locations - e.g. location_name = ['London','Paris','New York']
  2. Using a double for loop I'm getting coordonates of all locations and adding them to new_dataframe using the formula above
for i in location_name:
    coords_i = (location_details.loc[i]['Latitude'],location_details.loc[i]['Longitude'])
    for j in location_name:
        coords_j = (location_details.loc[j]['Latitude'],location_details.loc[j]['Longitude'])
        new_dataframe.loc[i,j] = geopy.distance.distance(coords_i,coords_j).km

Is there a smarter way of doing this? I don't know how apply would be used in this circumstance.

EDIT:

Found a neat solution below, which compiled much, much quicker than my initial solution:

for i in all_locations:
    location_coordinates.append([name,(latitude,longitude)])
    location_name.append(name)

location_distances_list = [geopy.distance.distance(value_i[1],value_j[1]).km for i, value_i in enumerate(location_coordinates) for j, value_j in enumerate(location_coordinates)]

location_distances = pd.DataFrame(np.array(location_distances_list).reshape(2583,2583), 
                                 columns=location_name, location=station_name)
0 Answers
Related