Create a distance matrix from individual distances

Question

I have a list of distance increase between every two adjacent stations in a railroad in the right order. What I need to do is to create a matrix for the distances between every two stations. This is this list.

    +-------------------------+-------------------------+---------------+
    |    Departure Station    |     Arrival Station     | distance in m |
    +-------------------------+-------------------------+---------------+
    |                         | San Francisco           |           0.0 |
    | San Francisco           | 22nd Street             |   2521.949349 |
    | 22nd Street             | Bayshore                |     5875.8986 |
    | Bayshore                | South San Francisco     |   6690.161279 |
    | South San Francisco     | San Bruno               |   2964.853585 |
    | San Bruno               | Millbrae Transit Center |   4154.792069 |
    | Millbrae Transit Center | Broadway                |   2549.171972 |
    | Broadway                | Burlingame              |   1762.653178 |
    | Burlingame              | San Mateo               |   2307.847611 |
    | San Mateo               | Hayward Park            |   2148.992125 |
    | Hayward Park            | Hillsdale               |   2597.932334 |
    | Hillsdale               | Belmont                 |       2092.15 |
    | Belmont                 | San Carlos              |   1990.239598 |
    | San Carlos              | Redwood City            |   3492.618122 |
    | Redwood City            | Atherton                |   3847.644532 |
    | Atherton                | Menlo Park              |    1752.92218 |
    | Menlo Park              | Palo Alto               |   2011.382315 |
    | Palo Alto               | Stanford                |   1582.663905 |
    | Stanford                | California Ave.         |       965.606 |
    | California Ave.         | San Antonio             |   3939.685111 |
    | San Antonio             | Mountain View           |   3108.414275 |
    | Mountain View           | Sunnyvale               |    4312.51742 |
    | Sunnyvale               | Lawrence                |   3189.943773 |
    | Lawrence                | Santa Clara             |   5889.680131 |
    | Santa Clara             | College Park            |    2252.43061 |
    | College Park            | San Jose Diridon        |   1872.857195 |
    | San Jose Diridon        | Tamien                  |   2887.967478 |
    | Tamien                  | Capitol                 |    4999.21158 |
    | Capitol                 | Blossom Hill            |   5304.202424 |
    | Blossom Hill            | Morgan Hill             |   19050.76536 |
    | Morgan Hill             | San Martin              |     5917.5495 |
    | San Martin              | Gilroy                  |   10061.59472 |
    | Gilroy                  | Gilroy                  |           0.0 |
    +-------------------------+-------------------------+---------------+

My idea was to make a list of distances and a dictionary of stations and their indexes to make a matrix where the values will be generated by looking at the dictionary of stations and defining the range of indexes in which we need to summarize the distances. I worked a lot on making this matrix this way but could not obtain the results.

import pandas as pd
file = open('/Users/miss_evgenia/Downloads/Caltrain Metrics - Sheet4.csv')
dist = pd.read_csv(file)
distances = list(dist['distance in m'])
#%%
names = list(dist['Departure Station'])
names.pop(0)
names= dict(zip(names, range(len(names))))
#%%
def sumRange(L,a,b):
    sum = 0
    for i in range(a,b+1,1):
        sum += L[i]
    return sum

This is the dictionary and list I have.

{'San Francisco': 0, '22nd Street': 1, 'Bayshore': 2, 'South San Francisco': 3, 'San Bruno': 4, 'Millbrae Transit Center': 5, 'Broadway': 6, 'Burlingame': 7, 'San Mateo': 8, 'Hayward Park': 9, 'Hillsdale': 10, 'Belmont': 11, 'San Carlos': 12, 'Redwood City': 13, 'Atherton': 14, 'Menlo Park': 15, 'Palo Alto': 16, 'Stanford': 17, 'California Ave.': 18, 'San Antonio': 19, 'Mountain View': 20, 'Sunnyvale': 21, 'Lawrence': 22, 'Santa Clara': 23, 'College Park': 24, 'San Jose Diridon': 25, 'Tamien': 26, 'Capitol': 27, 'Blossom Hill': 28, 'Morgan Hill': 29, 'San Martin': 30, 'Gilroy': 31}

[0.0, 2521.949349, 5875.8986, 6690.161279, 2964.8535850000003, 4154.792069, 2549.171972, 1762.653178, 2307.847611, 2148.992125, 2597.932334, 2092.15, 1990.2395980000001, 3492.618122, 3847.6445320000003, 1752.92218, 2011.3823149999998, 1582.663905, 965.6060000000001, 3939.685111, 3108.414275, 4312.51742, 3189.943773, 5889.680131, 2252.4306100000003, 1872.8571949999998, 2887.967478, 4999.21158, 5304.202424, 19050.765359999998, 5917.5495, 10061.594720000001, 0.0]

Help, please! Thank you.

Answer 1

You can compute the "positions" of the stations as the cumsum of distances and then use scipy.spatial.distance.pdist for computing the distances:

from scipy.spatial.distance import pdist, squareform

positions = data['distance in m'].cumsum()
matrix = squareform(pdist(positions.to_numpy()[:, None], 'euclidean'))

Answer 2

In addition to a_guest you might also try the following to get the result back as a pandas dataframe with labels

def transform_dataframe():
    with open("test_data.csv", "r") as input_data:
        station_distances = pd.read_csv(input_data)
        # to stop gilroy appearing twice
        station_distances.drop(station_distances.tail(1).index,inplace=True)
    cumulative_distances = station_distances['distance in m'].cumsum()

    distance_matrix = cumulative_distances.values - cumulative_distances.values[:, None]
    distance_matrix = pd.DataFrame(distance_matrix, index=station_distances["Arrival Station"], columns=station_distances["Arrival Station"])
    return distance_matrix