I am tasked with matching the products from a table to the Work Center they are supposed to be sent to. To do this, I have 2 tables: one contains the details about what product goes to what Work Center, and one containing the informations about the product to be made.

The product names are usually like this str (group) int( size) and may or may not be followed by other strings (the motor to make), or a ‘/T’ which doesn’t affect the work center associated

The first table about the products and work centers:

Here, this shows that, for example:
In the first row, if product name starts with R and which has any other character after that, and if it has a size of 37 and anything else attached to it, should go to the Work Center -> CELL21

Now, take a look at the orders table instead:

It shows that, the first product is called:

R37 DRN90L2

This is exactly the case I just described above, it’s an R , and size is 37 both of which conditions match the Work Center to be CELL21.

Therefore, the final dataset should be as follows (these are all examples you can verify with the excerpt of table I sent above):

Now, what I am trying to do, is to match through the use of regex each of the rows for the dataset containing the details of the orders, and when I obtain a group to cross-check it with the table of the products and work centers to return the work centers into which it should go:

import pandas as pd
import re

df_products = pd.read_excel('orders_table.xlsx')
df_rules = pd.read_excel('products_work_centers.xlsx')

def match_workcenter(prodotto, df_rules):
    # Extract group and size from the product code using regex
    match = re.match(r"([A-Z]+)(d+)", prodotto)
    if match:
        group, size = match.groups()
        size = size + '*'  
        for _, row in df_rules.iterrows():
            if group == row['Type_group'].replace('*', '') and size == row['Type_size']:
                return row['Workcenter']
    return None

df_products['New_Workcenter'] = df_products['Prodotto'].apply(lambda x: match_workcenter(x, df_rules))

df_products

But the result is not satisfactory, as for some of these cells it works and some others it doesn’t:

Can I get some advice into how to modify this to work?