Column route represents the point for each location

So I have two tables like this, my question is how to calculate the sum of the load difference between the two tables if the route has to match with every location [1,2,3,4,5]

for example:

df1['Route'] = [1,5] and df2['Route'] = [2,3,4] then the result is [[1,5],[2,3,4]] with sum of load difference = 368.93 (-10325.19) = -9956,26

df1

df2

CodePudding user response：

Answer

import numpy as np

prime_map = {k:v for k,v in zip("12345", [2,3,5,7,11])}

def road_key_to_number(x):
    return np.prod(list(prime_map[k] for k in x.split(",")))


df1["key"] = df1["Route"].apply(road_key_to_number)
df2["key"] = df2["Route"].apply(road_key_to_number)
df2["pair_key"] = np.prod(list(prime_map.values())) / df2["key"]
data = df1.merge(df2, left_on = "key", right_on="pair_key")
data["diff"] = (data["Load Difference_x"]   data["Load Difference_y"])

Output

    Route_x  Route_y      diff
0       1,5    2,3,4  -9956.26
1     1,3,4      2,5 -10483.45
2       1,4    2,3,5 -11011.47
3     3,4,5      1,2 -10665.67
4       1,3    2,4,5 -11379.29
5         1  2,3,4,5 -11582.74
6       4,5    1,2,3 -10518.09
7       3,5    1,2,4 -11013.46
8       3,4    1,2,5 -11987.37
9         5  1,2,3,4 -10931.53
10        4  1,2,3,5 -12633.20
11        3  1,2,4,5 -13280.93
12    1,3,5      2,4  -9990.28
13    1,4,5      2,3 -10011.72
14  1,3,4,5        2  -9992.20
15        2  1,3,4,5  -9992.20
16      2,3    1,4,5 -10011.72
17      2,4    1,3,5  -9990.28
18    2,3,4      1,5  -9956.26
19      2,5    1,3,4 -10483.45
20      1,2    3,4,5 -10665.67
21    2,3,5      1,4 -11011.47
22    2,4,5      1,3 -11379.29
23    1,2,3      4,5 -10518.09
24    1,2,4      3,5 -11013.46
25  2,3,4,5        1 -11582.74
26  1,2,3,4        5 -10931.53
27    1,2,5      3,4 -11987.37
28  1,2,3,5        4 -12633.20
29  1,2,4,5        3 -13280.93

Explanation

• The key idea is to match two df.Route to calculate the diff between them.
• Here prime_map is using a method called Arithmeticization, it has been used in Metamathematics to prove Gödel's incompleteness theorems
  • Each node in the route has a unique prime number, so, we can use a unique product from them to represent a road, and it is order-independent, which means 1,2,4 equal 4,2,1
  • You also could use another method like intersection or union to represent it.

Reference

• Metamathematics - Wikipedia
• Gödel's incompleteness theorems