(Sep-05-2023, 03:03 PM)visedwings049 Wrote: I am using Python 3.9

1. I have created a product table that splits a description out into multiple columns.

2. I have created a supplier table that has the supplier product. Many times a supplier product code is in the description of a product.

3. I want to populate the product.supplier code column with any string that is contained in the supplier.product column. In this example we would have found the code widget in column 4 in the supplier table and returned the word widget in the supplier code table.

4. So i want to do this on a loop for column 0 then move on to column 1 and so on.

There will never be two examples of a supplier code in the same string, so i am not worried about overwriting a first instance with a second.

I have tried the str.contains function but this just returns true or false.

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

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

(Sep-05-2023, 04:21 PM)visedwings049 Wrote:

(Sep-05-2023, 03:03 PM)visedwings049 Wrote: I am using Python 3.9

1. I have created a product table that splits a description out into multiple columns.

2. I have created a supplier table that has the supplier product. Many times a supplier product code is in the description of a product.

3. I want to populate the product.supplier code column with any string that is contained in the supplier.product column. In this example we would have found the code widget in column 4 in the supplier table and returned the word widget in the supplier code table.

4. So i want to do this on a loop for column 0 then move on to column 1 and so on.

There will never be two examples of a supplier code in the same string, so i am not worried about overwriting a first instance with a second.

I have tried the str.contains function but this just returns true or false.

I apologize as this is my first post but they are both being read as pandas dataframes from CSV.

(Sep-05-2023, 06:57 PM)deanhystad Wrote: 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

supplier_table = product_table[["Description"]]

supplier_table = product_table[["Description"]].copy()