gpt4 book ai didi

python - 如何匹配 2 个数据框中的电子邮件地址

转载 作者:行者123 更新时间:2023-12-01 07:43:41 26 4
gpt4 key购买 nike

我有两个数据框,都包含“电子邮件”列。理想情况下,电子邮件地址应该一一匹配,但由于拼写错误或其他原因,很多人无法在其他数据框中找到匹配项。如何忽略两列中的大小写,删除特殊符号,然后合并电子邮件地址?

我的数据框如下所示:

df1 = pd.DataFrame({'URL': ['/','/','/instr-analytics'],
'Email': ['apple@gmail.com','bananA@gmail.com','peaR@gmail.com']})

df2 = pd.DataFrame({'URL': ['/s','/d','/qinstr-analytics'],
'Email': ['Apple@gmail.com','banana@gmail.com','peaR@gmail.com']})

在这种情况下如何匹配电子邮件地址?

最佳答案

我的解决方案取决于两个 DataFrame 的大小

  1. 电子邮件改为小写
  2. 检查电子邮件是否有效
  3. 进行交叉连接
  4. 计算Levenshtein distance

代码:

import pandas as pd
import re

# email validation pattern
pattern = '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$'

def distance(a, b):
"Calculates the Levenshtein distance between a and b."
n, m = len(a), len(b)
if n > m:
# Make sure n <= m, to use O(min(n, m)) space
a, b = b, a
n, m = m, n

current_row = range(n + 1) # Keep current and previous row, not entire matrix
for i in range(1, m + 1):
previous_row, current_row = current_row, [i] + [0] * n
for j in range(1, n + 1):
add, delete, change = previous_row[j] + 1, current_row[j - 1] + 1, previous_row[j - 1]
if a[j - 1] != b[i - 1]:
change += 1
current_row[j] = min(add, delete, change)

return current_row[n]

def prepare_df(df):
df["Email_lower"] = df["Email"].apply(lambda x: x.lower())
df["is_valid"] = df["Email_lower"].apply(lambda x: 0 if re.match(pattern, x) is None else 1)
# drop all invalid emails
df = df[df["is_valid"] == 1]
df["key"] = 0
return df

df1 = pd.DataFrame({'URL': ['/','/','/instr-analytics'],
'Email': ['apple@gmail.com','bananA@gmail.com','peaR@gmail.com']})

df2 = pd.DataFrame({'URL': ['/s','/d','/qinstr-analytics'],
'Email': ['Apple@gmail.com','banana@gmail.com','peaR@gmail.com']})

prepared_df1 = prepare_df(df1)
prepared_df2 = prepare_df(df2)

cross_merge = prepared_df1.merge(prepared_df2, on="key", how="outer")
cross_merge["dist"] = cross_merge.apply(lambda row: distance(row["Email_lower_x"], row["Email_lower_y"]), axis=1)

cross_merge[cross_merge["dist"] < 1]

这不适用于大型 DataFrame,但您可以优化解决方案

关于python - 如何匹配 2 个数据框中的电子邮件地址,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56568685/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com