pandas - 使用 Pandas 计算两列中匹配记录的百分比

Question

我需要熊猫代码来计算匹配记录的百分比。假设我有两列 Hotel_name 和 Property_name 并且总记录是 100 和 30 记录从这两个列匹配，那么 % 匹配记录应该是 30%。

标签： pandasdataframeif-statementstring-matching

Answer 1

如果在两列上都设置了索引，则可以进行外连接，然后在某些必需列上计算空值。

例子：

x = [['hotel1','property1'],['hotel2','property2'],['hotel3','property3'],['hotel9','property9']]
y = [['hotel1','property1'],['hotel2','property2'],['hotel3','property3'],['hotel4','property4'],['hotel5','property5']]

df1 = pd.DataFrame(x, columns=['hotel_name','property_name'])
df2 = pd.DataFrame(y, columns=['hotel_name','property_name'])
df1['flag'] = 1
df2['flag'] = 1

df1.set_index(['hotel_name','property_name'], inplace=True)
df2.set_index(['hotel_name','property_name'], inplace=True)
ans = df1.join(df2, how='outer', lsuffix='x', rsuffix='y',sort=True)

print("Percent Match in X is: ", 100*(1-len(ans[ans.flagx.isna()]) / len(ans)))
print("Percent Match in Y is: ", 100*(1-len(ans[ans.flagy.isna()]) / len(ans)))
print(ans)

结果：

Percent Match in X is:  66.66666666666667
Percent Match in Y is:  83.33333333333334
                          flagx  flagy
hotel_name property_name              
hotel1     property1        1.0    1.0
hotel2     property2        1.0    1.0
hotel3     property3        1.0    1.0
hotel4     property4        NaN    1.0
hotel5     property5        NaN    1.0
hotel9     property9        1.0    NaN