Find a string from a column of one table in another table

[deanhystad]

Something like this maybe?

import pandas as pd
from string import ascii_letters as letters
from random import choice, choices, randint


def find_supplier(description):
    """Return word if word in description matches a supplier code, else None."""
    intersection = set(description.split()) & suppliers
    return list(intersection)[0] if intersection else None


# Make some random table thing that we can use to search for words in the description
# that match a supplier code.
product_table = pd.DataFrame(
    [
        {
            "Product": i,
            "Supplier Code": choice("ABCDE"),
            "Description": " ".join(choices(letters, k=randint(5, 10))),
        }
        for i in range(100, 120)
    ]
)

# Get set of suppliers.
suppliers = set(product_table["Supplier Code"].values)

# Make supplier table.  Supplier table contains rows from product_table
# where one of the words in the description matches a supplier code.
supplier_table = product_table[["Description"]]
supplier_table["Product"] = supplier_table["Description"].map(find_supplier)
supplier_table = supplier_table[~supplier_table["Product"].isna()][
    ["Product", "Description"]
]
print(supplier_table)

Output:
   Product          Description
1        E    T u V x Z E a k s
2        A  K H a K P G z m l A
3        E          Q L H E q J
5        B  N X x b i B q D F M
8        D      d U q K Y W I D
10       C    U V H C f F n N z
14       C            C o X u J
15       E          D F e E Q u
18       B          o f B P x O

This is easy to break up into individual supplier tables.

for supplier in suppliers:
    print(
        supplier,
        supplier_table[supplier_table["Product"] == supplier].reset_index(drop=True),
        sep="\n",
        end="\n\n",
    )

Output:
A
  Product        Description
0       A    d j A c S o F U
1       A          o A w I W
2       A          z s e j A
3       A  c P R w Z M A V b

D
  Product          Description
0       D  t u P r p R v G D O
1       D    j P w D h v o m w

C
  Product    Description
0       C  n j r C r R T

B
  Product        Description
0       B  H O P B A c r C n
1       B          B g Z r z
2       B    r o y g u l B A

E
  Product          Description
0       E            P E m t Z
1       E  S E Y m F a K h Z T