Python根据部分匹配使用新数据添加新行

Question

表1

|Location|Type|Supplier|     ID    |Serial|
|   MAB  |Ant |  A     |    A123   |456/56|
|   MEB  |Ant |  B     |    A123   |456/56|

表2

|Location   |Type|Supplier|     ID      |Serial|#####|
|  MAB+MEB  |Ant |  A/B   | A123        |456/56|123-4|
|  MAB+MEB  |Ant |  A/B   | A123/B123   |456/56|432-1|
|  MAB+MEB  |Ant |  A/B   | A123/B123   |456/56|432-1|

表3

|Location|Type|Supplier|     ID    |Serial|#####|
|   MAB  |Ant |  A     | A123      |456/56|123-4|
|   MAB  |Ant |  A     | A123      |456/56|432-1|
|   MAB  |Ant |  A     | A123      |456/56|432-1|
|   MEB  |Ant |  B     | A123      |456/56|123-4|
|   MEB  |Ant |  B     | A123      |456/56|432-1|
|   MEB  |Ant |  B     | A123      |456/56|432-1|

如上所示，如果表1列“位置”、“供应商”、“ID”、“序列”单元格内容包含在表2的相同列单元格中，则生成表3

*请注意，表1用作核心模板，如果表2中包含相关列单元格，则我们只是复制表1中的行，并将“#####”列添加到每一行中

请告知我们如何制作表3

我的逻辑：对于表1中的a、b、c、d，如果表2中包含a、b、c、d，则按列将表2中的“Subconto Part#”追加到表1中，将所有“Subconto Part#####################”按、

其中a、b、c、d是感兴趣的列，表1和表2之间的链接

Answer 1

以下是我的建议，首先从表2中提取值，然后将转换后的数据帧与表1中感兴趣的变量合并：

首先，我重复你的例子：

import pandas as pd
import re
# reproducing table 1
df1 = pd.DataFrame({"Location": ["MAB", "MEB"],
                    "Type" : ["Ant", "Ant"],
                    "Supplier":["A","B"],
                     "ID": ["A123","A123"],
                    "Serial": ["456/56","456/56"]})
# then table 2
df = pd.DataFrame({"Location": ["MAB+MEB", "MAB+MEB", "MAB+MEB"],
                   "Type": ["Ant", "Ant", "Ant"],
                   "Supplier": ["A/B", "A/B","A/B"],
                   "ID": ["A123", "A123/B123", "A123/B123"],
                   "Serial":['456/56','456/56','456/56'],
                   "values_rand":[1,2,3]})

# First I split the column I am interested in based on regexp you can tweak according
# to what you want:
r = re.compile(r"[a-zA-Z0-9]+")
df['Supplier'], df["ID"], df["Location"] = df['Supplier'].str.findall(r),\
                                           df['ID'].str.findall(r), \
                                           df['Location'].str.findall(r)
table2 = pd.merge(df['Supplier'].explode().reset_index(), 
                  df["ID"].explode().reset_index(),on="index", how="outer")
table2 = pd.merge(table2, df["Location"].explode().reset_index(), 
                  on="index", how="outer")
table2 = pd.merge(table2, df.loc[:,["Type","Serial",
                                    "values_rand"]].reset_index(), on="index",how="left")
result = (pd.merge(table2,df1, on=['Location' , 'Supplier' , 'ID' , 'Serial',"Type"])
         .drop(columns="index"))

结果是

  Supplier    ID Location Type  Serial  values_rand
0        A  A123      MAB  Ant  456/56            1
1        A  A123      MAB  Ant  456/56            2
2        A  A123      MAB  Ant  456/56            3
3        B  A123      MEB  Ant  456/56            1
4        B  A123      MEB  Ant  456/56            2
5        B  A123      MEB  Ant  456/56            3

希望能有帮助