python - 在合并之前清理数据的更好方法是什么？

Question

我有两个不同的数据框需要合并，并且在合并发生之前需要清理合并列(“标题”)。示例数据示例如下所示；

data1 = pd.DataFrame({'id': ['a12bcde0','b20bcde9'], 'title': ['a.b. company','company_b']})

data2 = pd.DataFrame({'serial_number': ['01a2b345','10ab2030','40ab4060'],'title':['ab company','company_b (123)','company_f']})

正如预期的那样，第一个标题的合并不会成功。我一直在使用 replace() 方法，但它很快就变得难以管理，因为由于拼写、区分大小写等问题，我有数百个标题需要纠正。

关于如何最好地清理和合并数据还有其他建议吗？

完整示例:

import pandas as pd
import numpy as np

data1 = pd.DataFrame({'id': ['a12bcde0','b20bcde9'], 'title': ['a.b. company','company_b']})

data2 = pd.DataFrame({'serial_number': ['01a2b345','10ab2030','40ab4060'],'title':['ab company','company_b (123)','company_f']}) 

data2['title'].replace(regex=True,inplace=True,to_replace=r"\s\(.*\)",value=r'')

replacements = {
   'title': {
        r'a.b. company *.*': 'ab company'
    }
}
data1.replace(replacements, regex=True, inplace=True)

pd.merge(data1, data2, on='title') 

Answer 1

首先，这个问题没有完美的解决方案，但我建议做两件事:

• 事先进行任何您可以做的简单清理，包括删除任何您不期望的字符。
• 应用一些模糊匹配逻辑

您会发现这并不完美，因为即使这个示例也不能 100% 工作。

<小时/>

首先，让我们先让您的示例稍微复杂一点，引入一个常规拼写错误(coampany_b 而不是 company_b，这是不会被下面的简单清洁)

data1 = pd.DataFrame({'id': ['a12bcde0','b20bcde9', 'csdfsjkbku'], 'title': ['a.b. company','company_b', 'coampany_b']})
data2 = pd.DataFrame({'serial_number': ['01a2b345','10ab2030','40ab4060'],'title':['ab company','company_b (123)','company_f']})

<小时/>

然后假设您只期望 [a-z] 个字符，如 @Maarten Fabré 提到的那样。因此，让我们将所有内容都小写并删除其他内容。

data1['cleaned_title'] = data1['title'].str.lower().replace(regex=True,inplace=False,to_replace=r"[^a-z]", value=r'')
data2['cleaned_title'] = data2['title'].str.lower().replace(regex=True,inplace=False,to_replace=r"[^a-z]", value=r'')

<小时/>

现在，让我们使用 difflib's get_close_matches (了解更多和其他选项 here )

import difflib
data1['closestmatch'] = data1.cleaned_title.apply(lambda x: difflib.get_close_matches(x, data2.cleaned_title)[0])
data2['closestmatch'] = data1.cleaned_title.apply(lambda x: difflib.get_close_matches(x, data2.cleaned_title)[0])

这是结果数据1，看起来不错!

    id          title           cleaned_title   closestmatch
0   a12bcde0    a.b. company    abcompany       abcompany
1   b20bcde9    company_b       companyb        companyb
2   csdfsjkbku  coampany_b      coampanyb       companyb

现在，这是 data2，看起来有点不太好...我们要求它找到最接近的匹配项，因此它找到了company_f 的一个，而您显然不想要它。

    serial_number   title           cleaned_title   closestmatch
0   01a2b345        ab company      abcompany       abcompany
1   10ab2030        company_b (123) companyb        companyb
2   40ab4060        company_f       companyf        companyb

理想的情况是，如果您旁边有一份干净的公司名称列表，在这种情况下，您应该根据该列表找到最接近的匹配项。如果不这样做，您就必须发挥创意或手动清理偶然情况。

最后，您现在可以对“closestmatch”执行常规合并。