Creating a "distance" dataframe from a pivot table

Alright, so I have a relationship table (think person has-an item) which I have successfully pivoted into a boolean matrix. I want to go the next step and find a matrix for the 'distance' between any two given users.

I know if I wanted to do a one off distance I can essentially do the following:

df = ...
df["val"] = 1

pivot = df.pivot(index="person", columns="hasa", values="val").fillna(0)

# compute one difference
(pivot["Alice"] - pivot["Carol"]).abs().sum()

I have no idea how to go from here to a full dataframe though.

Initial table

Pivot Table

Goal Table

You can compute the "pivot" directly using crosstab, then the distance with scipy.spatial.distance.cdist using the default "euclidean" metric, you only need to square it:

from scipy.spatial.distance import cdist

pivot = pd.crosstab(df['person'], df['hasa'])

out = pd.DataFrame(cdist(pivot, pivot)**2,
                   index=pivot.index,
                   columns=pivot.index,
                  )

Alternatively, you can also use corr with a custom function for the second step:

out = pivot.T.corr(lambda a,b: sum(a!=b))

Or manually with [tag:numpy] broadcasting (memory expensive for a large input):

a = pivot.to_numpy()
out = (a[..., None] != a.T).sum(axis=1)

Output:

person  Alice  Bob  Carol
person                   
Alice     0.0  1.0    2.0
Bob       1.0  0.0    3.0
Carol     2.0  3.0    0.0

Let's start simple. You can calculate the distances between all rows in your pivot table by creating another DataFrame that will store these distances. The main logic here is to iterate through each row of the pivot table and calculate the distance to every other row.

# Create initial DataFrame
df = pd.DataFrame({
    'person': ['Alice', 'Bob', 'Carol', 'Bob'],
    'hasa': ['Apple', 'Banana', 'Carrot', 'Apple']
})

# Pivot DataFrame
pivot_df = pd.pivot_table(df, index='person', columns='hasa', aggfunc=len, fill_value=0)

# Create empty DataFrame for distances
distance_df = pd.DataFrame(index=pivot_df.index, columns=pivot_df.index)

# Fill DataFrame with distances
for person1 in pivot_df.index:
    for person2 in pivot_df.index:
        distance_df.loc[person1, person2] = (pivot_df.loc[person1] - pivot_df.loc[person2]).abs().sum()

Now, distance_df should have the right values. Note that the resulting DataFrame is symmetrical along the main diagonal (because the distance from Alice to Bob is the same as the distance from Bob to Alice). The main diagonal is filled with zeros (since the distance from a person to themself is always zero).