使用用户定义的比较函数在两个数据帧之间查找

Question

我有两个熊猫数据帧（示例是说明性的）。df1['list\u of_keywords']和df2['list\u of_words']列分别包含单词列表。

df1 = pd.DataFrame(columns=('some_data','another_data','list_of_keywords'))
df2= pd.DataFrame(columns=('something','something_more','something_else','list_of_words'))

df1：

df2：

对于df1['list\u of_keywords']（源单元格）中的每个单元格，我想了解df2['list\u of_words']（目标单元格）中是否存在匹配的单元格。匹配单元格定义为包含源单元格中的所有单词的单元格（它可能包含其他不在源单元格中的单词）。我还想在df1中添加一列来记录匹配结果（如果匹配，则为True，如果不匹配，则为False）。在上面的示例中，对于df1的第一行，df1和df2之间存在匹配，但对于df1的第二行，不存在匹配。

这里有一个比较两个单词列表的函数，我想使用它：

def compare_names(wordlist1, wordlist2):
    shorter = wordlist1
    longer = wordlist2
    if len(wordlist2) < len(wordlist1):
        t = shorter
        shorter = longer
        longer = t
    result = all(item in longer for item in shorter)
    return result

df1有大约50k行，df2有500-600k行。“list_of_keywords”中的每一项大约有2-5个单词，“list_of_words”中的每一项大约有3-10个单词，所以内存不是问题。

问题：除了在两个数据帧之间显式循环外，有没有更快/更短/更具Pythonic风格的方法来解决这个问题？我使用的是Python v3.7和Pandas v1.2.4

Answer 1

你可以试试这个：

# Setup
import pandas as pd

df1 = pd.DataFrame(
    {
        "some_data": ["id0001", "id0002", "id0003"],
        "another_data": [12391, 3233, 3426],
        "list_of_keywords": [
            ["word1", "word2", "word3"],
            ["word7", "word8", "word7"],
            ["word1", "word2", "word4"],
        ],
    }
)
df2 = pd.DataFrame(
    {
        "something": ["id_abcd", "id_eeed", "id_dgef"],
        "something_more": ["ref34322", "ref5555", "ref2963"],
        "something_else": ["some comment", "some comment", "some comment"],
        "list_of_words": [
            ["word5", "word4", "word5", "word4", "word9"],
            ["word5", "word3", "word2", "word4", "word1"],
            ["word1", "word2", "word3", "word4", "word6"],
        ],
    }
)

# Data preparation
lists_of_keywords = df1["list_of_keywords"].values
lists_of_words = df2["list_of_words"].values

# Iterate to find a match
match = {"in_df2": []}
for list_of_keywords in lists_of_keywords:
    search = []
    for list_of_words in lists_of_words:
        if set(list_of_keywords).issubset(set(list_of_words)):
            search.append(True)
        else:
            search.append(False)
    if any(search):
        match["in_df2"].append("True")
    else:
        match["in_df2"].append("False")

df1["in_df2"] = pd.DataFrame(match)

print(df1)
# Outputs
  some_data  another_data       list_of_keywords in_df2
0    id0001         12391  [word1, word2, word3]   True
1    id0002          3233  [word7, word8, word7]  False
2    id0003          3426  [word1, word2, word4]   True