使用字符串包含和多列合并2個dfs

Question 1

我有兩個要合并的DF。但我需要根據字符串包含并使用多個列來合并它們

df_1

    IN          Start_Time          Description                                                                     Per_Extr
0   IN7305517   2022-07-24 00:06:59 ABEND JOB PP_BRAI_VAR_CARTAO_IND_IBI_D and JOB_STREAM_NAME P26_BRAI_RS2...      FROM : 2022/01/08 TO : 2022/12/09
1   IN7305465   2022-07-24 00:09:49 ABEND JOB PP_AAAR_4898_POUP_MOV_TDCH_D and JOB_STREAM_NAME P26_AAAR_006_TSA...  FROM : 2022/01/08 TO : 2022/12/09
2   IN7305466   2022-07-24 00:10:16 ABEND JOB PP_AAAR_4898_POUPMOV_D and JOB_STREAM_NAME P26_AAAR_006_TSA...        FROM : 2022/01/08 TO : 2022/12/09
3   IN7305493   2022-07-24 00:20:27 ABEND JOB PP_BGDTPRODHBACMS102020_01_M and JOB_STREAM_NAME P26_BGDTDCHF_PUM...  FROM : 2022/01/08 TO : 2022/12/09

df_2

    JOB_STREAM_NAME     JOB_NAME
NaN P26_BRAI_RS2        PP_BRAI_VAR_CARTAO_IND_IBI_D
NaN P26_BRAI_VAR_TOD    PP_BRAI_VAR_CARTAO_IND_IBI_D
NaN P26_AAAR_006_TSA    PP_AAAR_4898_POUP_MOV_TDCH_D
NaN P26_AAAR_006_TSA    PP_AAAR_4898_POUPMOV_D
NaN P26_BGDTDCHF_PUM    PP_BGDTPRODHBACMS102020_01_M

描述列中有JOB_NAME和JOB_STREAM_NAME

我的目標是這樣一個df:merged_df

    IN          JOB_STREAM_NAME     JOB_NAME                        Start_Time          Description                                                                     Per_Extr
0   IN7305517   P26_BRAI_RS2        PP_BRAI_VAR_CARTAO_IND_IBI_D    2022-07-24 00:06:59 ABEND JOB PP_BRAI_VAR_CARTAO_IND_IBI_D and JOB_STREAM_NAME P26_BRAI_RS2...      FROM : 2022/01/08 TO : 2022/12/09
1   NaN         P26_BRAI_VAR_TOD    PP_BRAI_VAR_CARTAO_IND_IBI_D    NaN                 NaN                                                                             NaN
2   IN7305465   P26_AAAR_006_TSA    PP_AAAR_4898_POUP_MOV_TDCH_D    2022-07-24 00:10:16 ABEND JOB PP_AAAR_4898_POUPMOV_D and JOB_STREAM_NAME P26_AAAR_006_TSA...        FROM : 2022/01/08 TO : 2022/12/09
3   IN7305466   P26_AAAR_006_TSA    PP_AAAR_4898_POUPMOV_D          2022-07-24 00:10:16 ABEND JOB PP_AAAR_4898_POUPMOV_D and JOB_STREAM_NAME P26_AAAR_006_TSA...        FROM : 2022/01/08 TO : 2022/12/09
4   IN7305493   P26_AAAR_006_TSA    PP_AAAR_4898_POUPMOV_D          2022-07-24 00:20:27 ABEND JOB PP_BGDTPRODHBACMS102020_01_M and JOB_STREAM_NAME P26_BGDTDCHF_PUM...  FROM : 2022/01/08 TO : 2022/12/09

請注意，作業PP_BRAI_VAR_CARTAO_IND_IBI_D位于2 JOB_STREAM_NAME中，其中一個作業沒有in，這就是為什么在merged_df中JOB_STREAM_NAME=P26_BRAI_VAR_TOD中的作業沒有in（NaN）的原因

我被指示對一個列執行此操作，但對多個列執行相同的操作。

在一篇專欄文章中，我使用了這種方法：

jobs_list= "|".join(map(str, df_2['JOB_NAME']))
new_df.insert(0, 'merge_key', df_1['Description'].str.extract("("+jobs_list+")", expand=False))
df_merged = new_df.merge(df_1, how='right', left_on='merge_key', right_on='JOB_NAME').drop('merge_key', axis=1)

你們能幫我嗎？

Question 2

您需要一個鍵來合并這兩者，所以我們提取這些鍵并使用它們進行合并。

# extract the keys from the description and create addl columns
# you can always drop these afterwards

df[['JOB_NAME', 'JOB_STREAM_NAME' ]]=df['Description'].str.extract(r'JOB\s\b(\w+)\b.*?JOB_STREAM_NAME\s\b(\w+)\b' )

#merge on steam_name and job_name, since columns names are common, these won't be repeated
df3=df2.merge(df, on=['JOB_STREAM_NAME','JOB_NAME'], how='left')
df3

# drop the addl columns
df=df.drop(columns=['JOB_STREAM_NAME','JOB_NAME'])

    JOB_STREAM_NAME     JOB_NAME    IN  Start_Time  Description     Per_Extr
0   P26_BRAI_RS2    PP_BRAI_VAR_CARTAO_IND_IBI_D    IN7305517   2022-07-24 00:06:59     ABEND JOB PP_BRAI_VAR_CARTAO_IND_IBI_D and JOB...   FROM : 2022/01/08 TO : 2022/12/09
1   P26_BRAI_VAR_TOD    PP_BRAI_VAR_CARTAO_IND_IBI_D    NaN     NaN     NaN     NaN
2   P26_AAAR_006_TSA    PP_AAAR_4898_POUP_MOV_TDCH_D    IN7305465   2022-07-24 00:09:49     ABEND JOB PP_AAAR_4898_POUP_MOV_TDCH_D and JOB...   FROM : 2022/01/08 TO : 2022/12/09
3   P26_AAAR_006_TSA    PP_AAAR_4898_POUPMOV_D  IN7305466   2022-07-24 00:10:16     ABEND JOB PP_AAAR_4898_POUPMOV_D and JOB_STREA...   FROM : 2022/01/08 TO : 2022/12/09
4   P26_BGDTDCHF_PUM    PP_BGDTPRODHBACMS102020_01_M    IN7305493   2022-07-24 00:20:27     ABEND JOB PP_BGDTPRODHBACMS102020_01_M and JOB...   FROM : 2022/01/08 TO : 2022/12/09

(r'JOB\s  : match the literal JOB followed by \s (whitespace)
\b : word boundary
(\w+)\b : capture one or more letters followed by word boundary (that will be your jobid)
.*? : match one or letters (non greedy)
JOB_STREAM_NAME\s\b : match the literal followed by whitespace, followed by word boundary
(\w+)\b : capture one or more word characters followed by word boundary

' )

Answer 1

您需要一個鍵來合并這兩者，所以我們提取這些鍵并使用它們進行合并。

# extract the keys from the description and create addl columns
# you can always drop these afterwards

df[['JOB_NAME', 'JOB_STREAM_NAME' ]]=df['Description'].str.extract(r'JOB\s\b(\w+)\b.*?JOB_STREAM_NAME\s\b(\w+)\b' )

#merge on steam_name and job_name, since columns names are common, these won't be repeated
df3=df2.merge(df, on=['JOB_STREAM_NAME','JOB_NAME'], how='left')
df3

# drop the addl columns
df=df.drop(columns=['JOB_STREAM_NAME','JOB_NAME'])

    JOB_STREAM_NAME     JOB_NAME    IN  Start_Time  Description     Per_Extr
0   P26_BRAI_RS2    PP_BRAI_VAR_CARTAO_IND_IBI_D    IN7305517   2022-07-24 00:06:59     ABEND JOB PP_BRAI_VAR_CARTAO_IND_IBI_D and JOB...   FROM : 2022/01/08 TO : 2022/12/09
1   P26_BRAI_VAR_TOD    PP_BRAI_VAR_CARTAO_IND_IBI_D    NaN     NaN     NaN     NaN
2   P26_AAAR_006_TSA    PP_AAAR_4898_POUP_MOV_TDCH_D    IN7305465   2022-07-24 00:09:49     ABEND JOB PP_AAAR_4898_POUP_MOV_TDCH_D and JOB...   FROM : 2022/01/08 TO : 2022/12/09
3   P26_AAAR_006_TSA    PP_AAAR_4898_POUPMOV_D  IN7305466   2022-07-24 00:10:16     ABEND JOB PP_AAAR_4898_POUPMOV_D and JOB_STREA...   FROM : 2022/01/08 TO : 2022/12/09
4   P26_BGDTDCHF_PUM    PP_BGDTPRODHBACMS102020_01_M    IN7305493   2022-07-24 00:20:27     ABEND JOB PP_BGDTPRODHBACMS102020_01_M and JOB...   FROM : 2022/01/08 TO : 2022/12/09

(r'JOB\s  : match the literal JOB followed by \s (whitespace)
\b : word boundary
(\w+)\b : capture one or more letters followed by word boundary (that will be your jobid)
.*? : match one or letters (non greedy)
JOB_STREAM_NAME\s\b : match the literal followed by whitespace, followed by word boundary
(\w+)\b : capture one or more word characters followed by word boundary

' )

使用字符串包含和多列合并2個dfs

熱門問答

js 已知A,B,C三個點的坐標,求B的角度？

為什么Git存儲庫看不到子文件夾？

我如何設計一個循環，接受用戶輸入的高爾夫球員的名字和他們的得分，然后顯示列表？

每次調用QSortFilterProxyModel:：invalidateFilter時，都會重置Qt-rootIndex

如何用forloop比較多個輸入復選框值？

對象在申請編號時可能“未定義”

使用MFMailComposeViewController在iPhone上發送郵件時，如何設置郵件的主題、收件人和正文內容

Windows 10服務器的最佳安全設置是什么

Ext.get(element).setStyle('display', 'none'); 設置后，如何使得刷新或resize container時不被顯示回來

pandas 如何讀取csv之后，如何計算數據量

在近一億數據下,數據存在hive,使用Impala根據條件查其中的百條數據,大概多久

docker_lnmp封裝mosquitto

使用字符串包含和多列合并2個dfs

熱門問答

js 已知A,B,C三個點的坐標,求B的角度？

為什么Git存儲庫看不到子文件夾？

我如何設計一個循環，接受用戶輸入的高爾夫球員的名字和他們的得分，然后顯示列表？

每次調用QSortFilterProxyModel:：invalidateFilter時，都會重置Qt-rootIndex

如何用forloop比較多個輸入復選框值？

對象在申請編號時可能“未定義”

使用MFMailComposeViewController在iPhone上發送郵件時，如何設置郵件的主題、收件人和正文內容

Windows 10服務器的最佳安全設置是什么

Ext.get(element).setStyle('display', 'none'); 設置后， 如何使得刷新或resize container時不被顯示回來

pandas 如何讀取csv之后，如何計算數據量

在近一億數據下,數據存在hive,使用Impala根據條件查其中的百條數據,大概多久

docker_lnmp封裝mosquitto

Ext.get(element).setStyle('display', 'none'); 設置后，如何使得刷新或resize container時不被顯示回來