How to create an OD matrix from a pandas Data Frame only with specific columns

Question

I have this data frame as in the picture below. I need to create an Origin-Destination matrix wherein the Row axis I will have the date as a column and the values from the "From municipality code", On the Columns axis I will have the values of the "To municipality code" and as the values to fill the matrix will be the values of the column "count". How do you get a matrix from the pandas data frame?

result_final.head()
ODMatrix= pd.DataFrame(0, index=list(range(0,202708)), columns = list(range(0,202708))
                ).add(df.pivot_table(values='count', index="from_municipality_code",
               columns='to_municipality_code', aggfunc=len),fill_value=0).astype('int')

I tried to convert the pandas data frame into numpy array but it did not work.

result_final[['date', 'from_municipality_code','to_municipality_code','count','Lng_x','Lat_x','Lng_y','Lat_y',]].to_numpy()

This is the final matrix I want if this helps to visualize:

Answer 1

You can use the pivot_table method. Here is a working example:

import pandas as pd
import numpy as np

# Some example data
df = pd.DataFrame({"from": np.random.randint(0, 10, (1000,)), "to": np.random.randint(0, 10, (1000,))})
# Remove examples where from == to
df = df.loc[df["from"] != df["to"]].copy()

# The key operation
matrix = (
    df.assign(count=1)
    .pivot_table(index="from", columns="to", values="count", aggfunc="count")
    .fillna(0)
    .astype(int)
)

print(matrix)                                                                                                                                                                                               

to     0   1   2   3   4   5   6   7   8   9
from                                        
0      0  10  14   7   9  14  18   6  11   8
1     11   0  12   7   4  12   9  11   6  13
2      6  14   0  12  13   8   5  15  11  10
3     10   9  12   0  14  10   8  14   9  11
4     10  14  14  11   0   8   4  10  11   4
5     15  10  10  18   8   0  15  15   8  12
6      9   7  10  13  10   8   0  11  12  10
7      9  12   4   6   9   9   8   0   8  12
8      8   8  11  12  15  10  11   4   0   6
9     10  13  11  16  14  18  11   9   4   0