for Loop for multiplying a value of each row with a specific column from another table based on cond-CodePudding

I have table A

NAME	STAGE	Quan	Products	A	B
XYD1234	PROD	4643	A, C, T, Y	1	0
FGY4567	TEST	4739	A, U, p, Y	0	1
YUD4568	QUE	45632	I, Y, O, P	0	1
OKE4556	STACK	97474	A, C, P, Y	1	0

Table B:

STAGE	A	B
PROD	10	5
TEST	20	10
QUE	30	15
STACK	49	25

This is the output I want.
If the stage of each row matches the stage in table B, then multiply Column A of Table A with column A of table B.

NAME	STAGE	Quan	Products	A	B	A_STAGE	B_STAGE
XYD1234	PROD	4643	A, C, T, Y	1	0	10	0
FGY4567	TEST	4739	A, U, p, Y	0	1	0	10
YUD4568	QUE	45632	I, Y, O, P	0	1	0	15
OKE4556	STACK	97474	A, C, P, Y	1	0	49	0

If this the code I have but it is not working as it should be.

for i in range(0, len(A)):
  A.loc['Stage'] == B['Stage']
  A['A_Stage'] = A['A'] * B['A']

CodePudding user response：

import pandas as pd
df1 = pd.DataFrame({'NAME':['XYD1234','FGY4567','YUD4568','OKE4556'],
                    'STAGE':['PROD','TEST','QUE','STACK'], 
                    'Quan':[4643,4739,45632,97474], 
                    'Products':['A, C, T, Y','A, U, p, Y','I, Y, O, P','A, C, P, Y'], 
                    'A':[1, 0, 0, 1], 
                    'B':[0, 1, 1, 0]})
df2 = pd.DataFrame({'STAGE':['PROD','TEST','QUE','STACK'],
                    'A':[10, 20, 30, 49], 
                    'B':[5, 10, 15, 25]})
df2.columns = ['STAGE', 'A_STAGE', 'B_STAGE']
df1 = df1.merge(df2, on='STAGE')
df1.loc[:,'A_STAGE'] *= df1.loc[:,'A']
df1.loc[:,'B_STAGE'] *= df1.loc[:,'B']