如何根据一行得到数据帧中行的最佳最近值？

Question

我的数据集

Food Name          Protein  Calorie Carbohydrate    Calcium
Butter, salted     0.85     717       0.06          24.0
Butter, whipped    0.49     718       2.87          23.0
Butter oil         0.28     876       0.00          4.0
Cheese, blue       21.40    353       2.34          528.0
Cheese, brick      23.24    371       2.79          674.0

还有一场争吵。。。你知道吗

Protein Calorie Carbohydrate    Calcium
56      2200    130             8

这里哪一行与这一行最匹配？你知道吗

Answer 1

我相信你需要：

#get diffrence of matched columns, convert to absolute
df3 = df1[df2.columns].sub(df2.iloc[0]).abs()

#compare by minimal values, count them by sum
s = df3.eq(df3.min()).sum(axis=1)

#filter rows with maximal count
df = df1[s.eq(s.max())]
print (df)
    Food Name  Protein  Calorie  Carbohydrate  Calcium
2  Butter oil     0.28      876           0.0      4.0

Answer 2

我想，你可以用Cosine similarity表示给定向量和给定矩阵之间的距离。找到abs不是一个好方法，因为数据不是标准化的。你知道吗

    import numpy as np
    from numpy import dot
    from numpy.linalg import norm

    a = np.array([[0.85,717,0.06,24.0],[0.49,718,2.87,23.0],[0.28,876,0.00,4.0],[21.40,353,2.34,528.0],[23.24,371,2.79,674.0]])
    b = np.array([56,2200,130,8])
    cos_sim = dot(a, b)/(norm(a)*norm(b))

    selected_row = np.argmin(cos_sim) + 1 
    print ("Distances:")
    print (cos_sim)
    print ("Selected_row: " + str(selected_row))