How to divide the multirow value with matching row vale in python?-CodePudding

My first dataframe as below:

d = {'Name' : pd.Series(['A', 'A', 'A','B', 'B', 'B', 'C', 'D', 'D']), 
 'ID' : pd.Series(['10', '11', '12','13', \
                     '10', '12','14','10','12']),
 'Value' : pd.Series([[100,200,300,200,400,600,20,50,100],[100,150,200,100,200,300,200,50,100],[100,200,300,200,400,400,20,40,50],[10,250,200,100,200,400,210,150,100],\
            [100,100,100,200,200,300,200,60,50],[100,200,200,200,100,200,200,410,50],[50,100,240,200,200,450,200,410,50] ,[100,100,200,100,200,100,120,140,150] ,[100,100,200,100,200,100,100,140,150]       ]),
'YES' : pd.Series(['Y','N','Y','N','Y','Y','N','N','N'])}
df = pd.DataFrame(d)

My second dataframe as below:

d = {'ID' : pd.Series(['10', '11', '12','13', '14']), 
 'Value' : pd.Series([[10,20,30,20,40,60,2,5,10],[10,15,20,10,20,30,20,5,10],[10,20,30,20,40,40,2,4,5],[10,25,20,10,20,40,21,15,10],[15,10,20,10,10,10,12,14,15]     ]),
'YES' : pd.Series(['Y','N','Y','N','Y'])}
df1 = pd.DataFrame(d)

Now I want to divide the Each row in the df from matching df1 id.

means Df id = 11 value with Df1 id = 11 value

Expected example First row output:

Name ID     Value                                      YES        Result 
A   10  [100, 200, 300, 200, 400, 600, 20, 50, 100]     Y   [10,10,10,10,10,10,10,10,10]
B   13  [10, 250, 200, 100, 200, 400, 210, 150, 100]    N   [1,10,10,10,10,10,10,15,10]

CodePudding user response：

You unfortunately need a double loop (pandas doesn't like lists as items):

s = df['ID'].map(df1.set_index('ID')['Value'])
df['Result'] = [[round(a/b, 2) for a,b in zip(l1,l2)]
                for l1, l2 in zip(df['Value'], s)]

NB. I rounded the values to 2 digits for clarity, this is optional.

output:

  Name  ID                                          Value YES                                                    Result
0    A  10    [100, 200, 300, 200, 400, 600, 20, 50, 100]   Y    [10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0]
1    A  11   [100, 150, 200, 100, 200, 300, 200, 50, 100]   N    [10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0]
2    A  12     [100, 200, 300, 200, 400, 400, 20, 40, 50]   Y    [10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0]
3    B  13   [10, 250, 200, 100, 200, 400, 210, 150, 100]   N     [1.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0]
4    B  10    [100, 100, 100, 200, 200, 300, 200, 60, 50]   Y       [10.0, 5.0, 3.33, 10.0, 5.0, 5.0, 100.0, 12.0, 5.0]
5    B  12   [100, 200, 200, 200, 100, 200, 200, 410, 50]   Y    [10.0, 10.0, 6.67, 10.0, 2.5, 5.0, 100.0, 102.5, 10.0]
6    C  14    [50, 100, 240, 200, 200, 450, 200, 410, 50]   N  [3.33, 10.0, 12.0, 20.0, 20.0, 45.0, 16.67, 29.29, 3.33]
7    D  10  [100, 100, 200, 100, 200, 100, 120, 140, 150]   N       [10.0, 5.0, 6.67, 5.0, 5.0, 1.67, 60.0, 28.0, 15.0]
8    D  12  [100, 100, 200, 100, 200, 100, 100, 140, 150]   N        [10.0, 5.0, 6.67, 5.0, 5.0, 2.5, 50.0, 35.0, 30.0]