Merge distance matrix results and original indices with Python Pandas

Question

I have a panda df with list of bus stops and their geolocations:

    stop_id   stop_lat   stop_lon  
0   1         32.183939  34.917812  
1   2         31.870034  34.819541  
2   3         31.984553  34.782828  
3   4         31.888550  34.790904  
4   6         31.956576  34.898125  

stop_id isn't necessarily incremental.

Using sklearn.metrics.pairwise.manhattan_distances I calculate distances and get a symmetric distance matrix:

array([[0.      , 1.412176, 2.33437 , 3.422297, 5.24705 ],
       [1.412176, 0.      , 1.151232, 2.047153, 4.165126],
       [2.33437 , 1.151232, 0.      , 1.104079, 3.143274],
       [3.422297, 2.047153, 1.104079, 0.      , 2.175247],
       [5.24705 , 4.165126, 3.143274, 2.175247, 0.      ]])

But I can't manage to easily connect between the two now. I want to have a df that contains a tuple for each pair of stops and their distance, something like:

stop_id_1 stop_id_2 distance
1         2         3.33

I tried working with the lower triangle, convert to vector and all sorts but I feel I just over-complicate things with no success.

Answer 1

Hope this helps!

d= '''    stop_id   stop_lat   stop_lon  
0   1         32.183939  34.917812  
1   2         31.870034  34.819541  
2   3         31.984553  34.782828  
3   4         31.888550  34.790904  
4   6         31.956576  34.898125 '''

df = pd.read_csv(pd.compat.StringIO(d), sep='\s+') 

from sklearn.metrics.pairwise import manhattan_distances
distance_df = pd.DataFrame(manhattan_distances(df))

distance_df.index = df.stop_id.values
distance_df.columns = df.stop_id.values
print(distance_df)

output:

          1         2         3         4         6
1  0.000000  1.412176  2.334370  3.422297  5.247050
2  1.412176  0.000000  1.151232  2.047153  4.165126
3  2.334370  1.151232  0.000000  1.104079  3.143274
4  3.422297  2.047153  1.104079  0.000000  2.175247
6  5.247050  4.165126  3.143274  2.175247  0.000000

Now, to create the long format of the same df, use the following.

long_frmt_dist=distance_df.unstack().reset_index()
long_frmt_dist.columns = ['stop_id_1', 'stop_id_2', 'distance']
print(long_frmt_dist.head())

output:

   stop_id_1  stop_id_2  distance
0          1          1  0.000000
1          1          2  1.412176
2          1          3  2.334370
3          1          4  3.422297
4          1          6  5.247050

Answer 2

df_dist = pd.DataFrame.from_dict(dist_matrix)
pd.merge(df, df_dist, how='left', left_index=True, right_index=True)

example