Calculate similarity between rows of a dataframe (count values in common)

Viewed 1079

I want to calculate similarity between the rows of my dataframe. I have some columns with informations about some people. One row is one person. It looks like that :

 print(df)
        id  name      firstname  email                town    age
    0    1  martin    pierre     truc@machin.com      Paris   na
    1    2  dupond    sarah      bidule@machin.com    London  32
    2    3  dupond    sarah      bidule@machin.com    Berlin  32
    3    4  dupond    john       na                   Madrid  45
    4    5  smith     na         something@thing.com  Paris   28

I want to count for each row the number of values in common with the other rows divided by the number of columns if at least 3 columns are completed. For example, between the row with the index 1 and the row with the index 2, there are 4 variables in common. So, my similarity will be 4/5 (id doesn't count) = 80% of similarity. My result has to be a similarity matrix, because after that I want to find the rows with a similarity higher than 0.6 to build a new dataframe. It could be something like that :

 print(similarity)
        0    1    2    3    4
    0   1    0    0    0    0.2
    1   0.2  1    0.8  0.2  0
    2   0    0.8  1    0.2  0
    3   0    0.2  0.2  1    0
    4   0.2  0    0    0    1

Because the results are duplicated, half of that would be enough :

 print(similarity)
        0    1    2    3    4
    0        0    0    0    0.2
    1             0.8  0.2  0
    2                  0.2  0
    3                       0
    4 

I'm looking for a function that will automate that but I couldn't find. Does something like that exist? Thanks for reading, any advice or idea will be welcomed.

1 Answers

You can use scipy.spatial.distance.pdist with a custom distance function

from scipy.spatial.distance import pdist, squareform
pd.DataFrame(1 - squareform(pdist(df.set_index('id'), lambda u,v: (u != v).mean())))

Out:

     0    1    2    3    4
0  1.0  0.0  0.0  0.0  0.2
1  0.0  1.0  0.8  0.2  0.0
2  0.0  0.8  1.0  0.2  0.0
3  0.0  0.2  0.2  1.0  0.0
4  0.2  0.0  0.0  0.0  1.0
Related