兩個不同資料幀（dfs）中單詞之間的字串文字匹配并生成一個新的資料幀

我有兩個資料框 df1 和 df2

df1 =

120 行 X 5 列

df2 =

3000 行 X1 列

目標是形成df3

匹配字串文字并遍歷 "Student first name" 、 "Student last name" 、 "Student nick name" 列中的每個單元格以生成下表

Df3 =

3000 行 X 4 列

uj5u.com熱心網友回復：

您可以melt：merge

import re

df1_melt = df1.melt(['University', 'School'], value_name='Match')

regex = '|'.join(map(re.escape, df1_melt['Match']))

out = df2.join(
 df1_melt[['Match', 'University', 'School']]
    .merge(df2['Statement']
              .str.extract(f'({regex})', expand=False)
              .rename('Match'),
           how='right', on='Match'
          )
)

輸出：

                                                  Statement     Match University        School
0      Stuart had a headache last nigh which was due to the    Stuart        DDD          Arts
1  Rah basically found a new found friend which lead to the       Rah        DDD          Arts
2                    Gerome got a brand new watch which was       NaN        NaN           NaN
3          Adam was found chilling all through out his life      Adam        EEE  Life science
4                       Savannah is such a common name that  Savannah        BBB      Business

uj5u.com熱心網友回復：

天真的方法，回圈列以查找匹配項，然后回圈以合并匹配項：

import re


columns_to_match = ["Student first name", "last name", "nick name"]

dfs = []
for column in columns_to_match:
    search_strings = df1[column].unique().tolist()

    regex = "|".join(map(re.escape, search_strings))
    df2["Matching"] = df2["Statement"].str.extract(f"({regex})")
    dfs.append(df2.dropna())

matched_df = pd.concat(dfs).reset_index(drop=True)


dfs = []
for column in columns_to_match:
    final_df = df1.merge(matched_df, how="inner", left_on=column, right_on="Matching")
    dfs.append(final_df)

final_df = pd.concat(dfs).reset_index(drop=True).drop(columns=columns_to_match)

uj5u.com熱心網友回復：

我的回答做了以下假設：

• 上的索引df1作為學生 ID 并且是唯一的。
• 您只想填寫找到的第一個學生。像“John 和 Steve 是朋友”這樣的陳述句將分配給 John。

import re

assigned = pd.Series([False] * len(df2))
df3 = df2.copy()

# Loop through each student, taking their first, last and nick name
for idx, names in df1[["Student first name", "last name", "nick name"]].iterrows():
    # If all statements have been assigned, terminate the loop
    if assigned.all():
        break

    # Combine the student's first, last and nick name into a regex pattern
    pattern = f"({'|'.join(names.map(re.escape))})"

    # For each UNASSIGNED statement, Find the pattern. We only search unassigned
    # statements to lower the number of searches.
    match = df3.loc[~assigned, "Statement"].str.extract(pattern, expand=False)

    # Mark the statement as assigned
    cond = ~assigned & match.notna()
    assigned[cond] = True

    # Fill in the student's info
    df3.loc[cond, "Match"] = match[cond]
    df3.loc[cond, "University"] = df1.loc[idx, "University"]
    df3.loc[cond, "School"] = df1.loc[idx, "School"]

uj5u.com熱心網友回復：

您可以創建三個資料框（分別與所有三列合并）并將結果連接到一個資料框中，而不是遍歷每個單元格。

df2['Matching'] = df2['Statement'].str.split().str[0]

dfs = []
for col in ['Student first name', 'last name', 'nick name']:
    df_temp = pd.merge(df2, df1[[col, 'University', 'School']].rename(columns={col:'Matching'}), how='left')
    dfs.append(df_temp)

df3 = pd.concat(dfs).drop_duplicates()

標籤：Python熊猫细绳nltk匹配

上一篇：Julia中的re.escape()等價物？

下一篇：使用正則運算式檢查字串格式