Calculate pairwise similarity/distance between rows with conditional values in pandas

Question

I'm trying to compute distance between between values in rows that share a category. For user_id 1 parameter 1, the distance between 1 and 7 Par 2 distance between 10, 20.

    df1 = pd.DataFrame({"user_id":[1,2,1,2], "Par1":[1, 3, 7,9], "Par2":[10, 15, 20, 22]})

       Par1  Par2  user_id
    0     1    10        1
    1     3    15        2
    2     7    20        1
    3     9    22        2

I am able to sum up the values:

   df1.groupby([ "user_id"], as_index=False).sum()

and my question is, is there a relatively easy way to compute pairwise distances in lieu of the sum()?

desired output

            Par1                  Par2          user_id
    0     similarity[1,7]    similarity[10,20]       1
    1     similarity[3,9]    similarity[15,22]      2

What do you mean by distance? The difference between indexes or the values? — Alex, Nov 22 '16 at 17:40
The trivial distance between two numbers in ℕ is called subtraction. But what about when you have 3 numbers in one group? — Zeugma, Nov 22 '16 at 19:56
pairwise with 3 numbers would return 3 numbers 1>3, 1>2, 2>3, ignoring similarity to self which is going to be either 1 or 0. — lrn2code, Nov 22 '16 at 20:25

Alex · Accepted Answer · 2016-11-22T20:04:30.937

This looks to work for your simple example and should be extensible.

def distance_metric(x1, x2):
    return x2 - x1 # replace this with whatever you want

df_dist = pd.DataFrame()
df_dist['user_id'] = df.user_id.unique()

for col in (set(df.columns) - set(['user_id'])):
   vals = [df[df.user_id == i][col].values for i in df.user_id.unique()]
   vals = [distance_metric(val[0], val[1]) for val in vals]
   df_dist[col] = vals

Calculate pairwise similarity/distance between rows with conditional values in pandas

1 Answers1