How to calculate set product quantity from subproduct quantity?-CodePudding

I have 2 tables: The first have product code set, subproduct code and the corresponding quantity. The second is containing quantity in stock of each subproduct code:

import pandas as pd
data1 = [
['MHA601', 'A1: 1 | A2: 4'],
['MHA602', 'A1: 1 | A2: 6'],
['MHB502', 'B1: 1 | B2: 1 | B3: 1']]
df1 = pd.DataFrame(data1, columns=['Product_code_set','Subproduct_code'])
data2 = [
['A1', 2],
['A2', 8],
['B1', 1],
['B2', 0],
['B3', 1]]
df2 = pd.DataFrame(data2, columns=['Subproduct_code','Quantity'])

# How do i create a table containing the quantity of products set:
data3 = [
['MHA601', 2],
['MHA602', 1],
['MHB502', 0]]
result = pd.DataFrame(data3 , columns=['Product_code_set','Quantity'])

Thank you very much.

CodePudding user response：

You can extract the codes/values, divide the reference quantity and get the min integer value per group:

extract = df1['Subproduct_code'].str.extractall('(?P<code>[\w] ): (?P<qty>\d )')

s = df2.set_index('Subproduct_code')['Quantity']

df1['quantity'] = (extract['code'].map(s)
 .div(extract['qty'].astype(int))
 .astype(int)
 .groupby(level=0).min()
)

output:

  Product_code_set        Subproduct_code  quantity
0           MHA601          A1: 1 | A2: 4         2
1           MHA602          A1: 1 | A2: 6         1
2           MHB502  B1: 1 | B2: 1 | B3: 1         0

intermediates:

# extract
        code qty
  match         
0 0       A1   1
  1       A2   4
1 0       A1   1
  1       A2   6
2 0       B1   1
  1       B2   1
  2       B3   1

# s
Subproduct_code
A1    2
A2    8
B1    1
B2    0
B3    1
Name: Quantity, dtype: int64

# (extract['code'].map(s)
#  .div(extract['qty'].astype(int))
# )
   match
0  0        2.000000
   1        2.000000
1  0        2.000000
   1        1.333333
2  0        1.000000
   1        0.000000
   2        1.000000
dtype: float64