我有一家餐厅的销售详情如下。
+----------+------------+---------+----------+
| Location | Units Sold | Revenue | Footfall |
+----------+------------+---------+----------+
| Loc - 01 | 100 | 1,150 | 85 |
+----------+------------+---------+----------+
我想从下表餐厅数据中找到与上面最相关的餐厅
+----------+------------+---------+----------+
| Location | Units Sold | Revenue | Footfall |
+----------+------------+---------+----------+
| Loc - 02 | 100 | 1,250 | 60 |
| Loc - 03 | 90 | 990 | 90 |
| Loc - 04 | 120 | 1,200 | 98 |
| Loc - 05 | 115 | 1,035 | 87 |
| Loc - 06 | 89 | 1,157 | 74 |
| Loc - 07 | 110 | 1,265 | 80 |
+----------+------------+---------+----------+
请指导我如何使用 python 或 pandas 完成此操作。 注意:- 相关性是指在售出单位、收入 和客流量方面最匹配/相似的餐厅。
请您参考如下方法:
如果你的相关性应该被描述为最小欧氏距离，解决方案是:
#convert columns to numeric
df1['Revenue'] = df1['Revenue'].str.replace(',','').astype(int)
df2['Revenue'] = df2['Revenue'].str.replace(',','').astype(int)
#distance of all columns subtracted by first row of first DataFrame
dist = np.sqrt((df2['Units Sold']-df1.loc[0, 'Units Sold'])**2 +
(df2['Revenue']- df1.loc[0, 'Revenue'])**2 +
(df2['Footfall']- df1.loc[0, 'Footfall'])**2)
print (dist)
0 103.077641
1 160.390149
2 55.398556
3 115.991379
4 17.058722
5 115.542200
dtype: float64
#get index of minimal value and select row of second df
print (df2.loc[[dist.idxmin()]])
Location Units Sold Revenue Footfall
4 Loc - 06 89 1157 74
