How to divide the multirow value with matching ID row value in python?

user_2707200 0
Viewed 42

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']),
 'Jan' : pd.Series([100,200,300,200,400,600,20,50,100]),
 'Feb' : pd.Series ([100,150,200,100,200,300,200,50,100]),
 'Mar' : pd.Series([100,200,300,200,400,400,20,40,50]),
 'Apr' : pd.Series([10,250,200,100,200,400,210,105,100]),
 'May' : pd.Series([100,100,100,200,200,300,200,60,50]),
 'Jun' : pd.Series([100,200,200,200,100,200,200,410,50]),
 'Jul' : pd.Series([50,100,240,200,200,450,200,410,50]),
 'Aug' : pd.Series([100,100,200,100,200,100,120,140,150]),
 'Sep' :  pd.Series([100,100,200,100,200,100,100,140,150]),
'YES' : pd.Series(['Y','N','Y','N','Y','Y','N','N','N'])}
newdf = pd.DataFrame(d)

my second dataframe as below:

d = {'ID' : pd.Series(['10', '11', '12','13', '14','17','20','21','50']), 
 'Jan' : pd.Series([10,20,30,20,40,60,2,5,10]),
 'Feb' : pd.Series([10,15,20,10,20,30,20,5,10]),
 'Mar' : pd.Series([10,20,30,20,40,40,2,4,5]),
 'Apr' : pd.Series([15,10,20,10,10,10,12,14,15]),   
 'May' : pd.Series([10,25,20,10,20,40,21,15,10]),
 'Jun' : pd.Series([15,10,20,10,10,10,12,14,15]),
 'Jul' : pd.Series([2,2,2,0,2,2,3,2,2]),
 'Aug' : pd.Series( [2,2,2,20,2,1,2,2,2]),
 'Sep' : pd.Series( [3,0,2,2,2,1,2,5,2]),   
 'YES' : pd.Series(['Y','N','Y','N','Y','','','NS'])}
newdf1 = pd.DataFrame(d)

The second dataframe has extra id's I need result divide the first dataframe Jan to Sep using second df Jan to Sep based on matching id value

Example expected result: ( 5th index of first dataframe as below)

Name    ID  Jan     Feb     Mar     Apr     May     Jun     Jul     Aug     Sep     YES      RJan   RFeb    RMar    RApr    RMay    RJun    RJul    RAug    RSep
B      12   600     300     400     400     300     200     450     100     100     Y       20.       15.    13.33  20.    15.      10.     225.      50.      50.

Also is there any way we can capture output column like Name,ID, Jan...Sep,Yes, DJan..Dsep(dividing value from newdf1),RJan...Rsep?

D means dividing R means Result

pandas python-3.x python
1 Answers

You can use a merge to align the dataframe:

df2 = newdf[['ID']].merge(newdf1, on='ID').drop(columns=['ID', 'YES'])

out = newdf.join(newdf[df2.drop(columns='sep').columns].div(df2.drop(columns='sep')).add_prefix('R')).join(df2['sep'])

Output;

  Name  ID  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  ...  YES       RJan  \
0    A  10  100  100  100   10  100  100   50  100  ...    Y  10.000000   
1    A  11  200  150  200  250  100  200  100  100  ...    N  20.000000   
2    A  12  300  200  300  200  100  200  240  200  ...    Y  30.000000   
3    B  13  200  100  200  100  200  200  200  100  ...    N  10.000000   
4    B  10  400  200  400  200  200  100  200  200  ...    Y  13.333333   
5    B  12  600  300  400  400  300  200  450  100  ...    Y  20.000000   
6    C  14   20  200   20  210  200  200  200  120  ...    N   0.666667   
7    D  10   50   50   40  105   60  410  410  140  ...    N   2.500000   
8    D  12  100  100   50  100   50   50   50  150  ...    N   2.500000   

        RFeb       RMar       RApr  RMay       RJun   RJul   RAug  sep  
0  10.000000  10.000000   0.666667  10.0   6.666667   25.0   50.0    3  
1  15.000000  20.000000  16.666667  10.0  13.333333   50.0   50.0    3  
2  20.000000  30.000000  13.333333  10.0  13.333333  120.0  100.0    3  
3   6.666667  10.000000  10.000000   8.0  20.000000  100.0   50.0    0  
4  10.000000  13.333333  10.000000  10.0   5.000000  100.0  100.0    2  
5  15.000000  13.333333  20.000000  15.0  10.000000  225.0   50.0    2  
6  10.000000   0.666667  10.500000  10.0  10.000000  100.0   60.0    2  
7   5.000000   2.000000  10.500000   6.0  41.000000    inf    7.0    2  
8   5.000000   1.250000  10.000000   2.5   5.000000   25.0   75.0    2  

[9 rows x 21 columns]
user_163434640
Related