Compare column of 2 similar dataframes with un equal rows and replace the value of a column-CodePudding

I have 2 similar dataframes with unequal rows.

DataFrame 1
------------
    stockname  quantity  purchase_price purchase_date   weight
0       BSHSL        34        102.0000    2020-09-01  0.06172
1     SBICARD         8        804.3410    2020-09-01  0.11863
2   SANGINITA       110         71.2500    2020-09-01  0.13902
3   TOUCHWOOD       218         50.1479    2020-09-01  0.19395
4      SOLARA        12        917.7250    2020-09-01  0.20000
5    SUMICHEM        41        269.8520    2020-09-01  0.20000
6  LIQUIDBEES        14        999.9900    2020-09-01  0.08668

DataFrame2
-----------
   stockname  quantity  purchase_price purchase_date   weight
5  LIQUIDBEES        12          1000.0    2020-06-26  0.25399

I am trying to deduct the quantity of Dataframe1 with the quantity of Dataframe2 where Dataframe1.stockname == Dataframe2.stockname. Basically I want to replace the quantity with the difference in Dataframe1

Kindly suggest.

Thanks

CodePudding user response：

here is one way to do it. merge the two DF and then subtract the quantity from DF2

df['qty'] = df.merge(df2[['stockname', 'quantity']], on='stockname', how='left')['quantity_y'].fillna(0) 
df['quantity'] = df['quantity'] - df['qty']
df.drop(columns=['qty'])

using assign , calculating the different from df and df1 quantity and updating it in place, result is same

df=df.assign(quantity= df['quantity'] - 
          df.merge(df2[['stockname', 'quantity']], on='stockname', how='left')['quantity_y'].fillna(0) )

    stockname   quantity    purchase_price  purchase_date   weight
0   BSHSL          34.0     102.0000        2020-09-01     0.06172
1   SBICARD         8.0     804.3410        2020-09-01     0.11863
2   SANGINITA     110.0     71.2500         2020-09-01     0.13902
3   TOUCHWOOD     218.0     50.1479         2020-09-01     0.19395
4   SOLARA         12.0     917.7250        2020-09-01     0.20000
5   SUMICHEM       41.0     269.8520        2020-09-01     0.20000
6   LIQUIDBEES      2.0     999.9900        2020-09-01     0.08668