Python Dataframe calculate Distance via intermediate point

user_13739692 0
Viewed 40

I have a python dataframe with distances listed as follows

dict = {'from' : ['A','A','A','B','B','D','D','D'],
         'to' : ['B','C','D','C','E','B','C','E'],
        'distances': [4,3,1,1,3,4,2,9]}
df = pd.DataFrame.from_dict(dict)

I want enumerate all of the Distances for:

From point1 == > point2

where point1==> point2 = From point1 ==> B + From B==> point2 and is included in the a

how can i do this efficiently using python - i assume some kind of pd.merge?

I would then like to reformate the dataframe into the following

columns = ['From','To','Distance','Distance via B']
reshape dataframe distance dictionary python
1 Answers

If you're looking for routes of length 3, here's a solution. Note that in some cases, the direct route (e.g. A to B) is shorter than the route A-B-C: 

three_route = pd.merge(df, df, left_on="to", right_on="from")
three_route["distance"] = three_route.distances_x + three_route.distances_y
three_route = three_route[["from_x", "to_x", "to_y", "distance"]]. \
      rename(columns = {"from_x":"from", "to_x": "via", "to_y": "to"})

The result is: 

  from via to  distance
0    A   B  C         5
1    A   B  E         7
2    D   B  C         5
3    D   B  E         7
4    A   D  B         5
5    A   D  C         3
6    A   D  E        10
user_11055600
Related