Lookup a value in a matrix based on two variables of another dataframe using Python

I am a still beginner in data analysis, especially in my new study field analysis. Currently, I have a Distance Matrix [7,000 row x 7,000 col] with a total of 49 M cells. Also, I have another Count Dataframe that has some indices from that matrix, not all of them.

Distance Matrix

Count Dataframe

So I need to look up and get the relevant distance value from the Distance matrix based on each i and j values of the Count Dataframe to calculate the total distance, such as the following table using Python.

Total Distance Dataframe
Counts Dataframe

you can first create a function to look up the distance value from the Distance Matrix based on the Origin and Destination values from the Counts DataFrame.

Then, apply this function to the Counts DataFrame to get the Distance values.

Finally, calculate the Total_Dist column by multiplying the Counts and Dist columns.

# Convert data to DataFrames
distance_df = pd.DataFrame(distance_data).set_index('Zone')
counts_df = pd.DataFrame(counts_data)

# Function to look up distance value based on Origin and Destination
def get_distance(origin, destination):
    return distance_df.loc[origin, destination]

# Apply the function to the Counts DataFrame to get Dist column
counts_df['Dist'] = counts_df.apply(lambda row: get_distance(row['Origin'], row['Destination']), axis=1)

# Calculate the Total_Dist column
counts_df['Total_Dist'] = counts_df['Counts'] * counts_df['Dist']

# Display the final Total Distance DataFrame
print(counts_df)

OUTPUT :

   Origin  Destination  Counts  Dist  Total_Dist
0   10001        10002       5     3          15
1   10001        10003      10    50         500
2   10002        10001     100     3         300
3   10002        10003      10     9          90

You can set index of distance df from the column "Zone" (if not already) and then use .loc to locate the right elements:

# set index of df_dist (if not already):
df_dist.set_index('Zone', inplace=True)

df_count['Dist'] = df_count.apply(lambda x: df_dist.loc[x['Origin'], x['Destination']], axis=1)
df_count['Total Dist'] = df_count['Dist'] * df_count['Counts']
print(df_count)

Prints:

   Origin  Destination  Counts  Dist  Total Dist
0   10001        10002       5     3          15
1   10001        10003      10    50         500
2   10002        10001     100     3         300
3   10002        10003      10     9          90

If your only requirement is to use python then i would recommend using Pandas for large scale work.

I would do it something like this

import pandas as pd
#Declaration of given Matrix : Distance_Matrix 
Distance_Matrix = {
    'Zone': [10001, 10002, 10003, 10004],
    '10001': [0, 3, 50, 40],
    '10002': [3, 0, 9, 25],
    '10003': [50, 9, 0, 1],
    '10004': [40, 25, 1, 0],
}
#Declaration of given Matrix : Count_Dataframe 
Count_Dataframe = {
    'Origin': [10001, 10001, 10002, 10002],
    'Destination': [10002, 10003, 10001, 10003],
    'Counts': [5, 10, 100, 10],
}

#Convert Matrix into a Pandas DataFrame
dm = pd.DataFrame(Distance_Matrix)
cm = pd.DataFrame(Count_Dataframe)

#function to for Selecting value of "Distance_Matrix" 
def get_distance_value(row):
    # origin_zone = row taken from "Zone"
    origin_zone = row['Origin']
    
    # destination_zone = column
    destination_zone = row['Destination']
    
    # Return Value located in Distance_Matrix at position [origin_zone,destination_zone]
    return dm.loc[dm['Zone'] == origin_zone, str(destination_zone)].values[0]

#Create new Table, as its the same base as Count_Dataframe create a copy 
Total_Distance_Dataframe_Counts_Dataframe= cm.copy()
#create new column "Distance" by applying created function to select Value from "Distance_Matrix "
Total_Distance_Dataframe_Counts_Dataframe['Distance'] = Total_Distance_Dataframe_Counts_Dataframe.apply(get_distance_value, axis=1)
#create last column that is product of "Counts" * "Distance"
Total_Distance_Dataframe_Counts_Dataframe['Total_Dist'] =  Total_Distance_Dataframe_Counts_Dataframe['Counts'] * Total_Distance_Dataframe_Counts_Dataframe['Distance']

#print new table
print(Total_Distance_Dataframe_Counts_Dataframe)

Simple Explanation:
DataFrames are, in this case, your Tables, I see them as a fancy Dictionary because just like in a Dict you can edit values by inserting the key in form of the column name. In a form of a DataFrame you get a complete Column back that you can then work with.
Using .loc[] on a pandas DataFrame can be seen as a SELECT statement in SQL statements. combining the selection and .loc[] you can search for values in your Table or edit them.