Subtracting from one column based on values in another column

Question

df_1

product_name  amount   price
     a           1        1
     b           2        2
     c           3        3
     d           4        4

df_2
product_name  amount
     a           1    
     b           2 

Here is Sample of two data frame in pandas.

I want to make it subtracted amount with product_names like a product will be amount 0 and b product also amount 0... with product_name column value.

thx for your time

Answer 1

Let's say you have

df_1

product_name | amount | price
     a           1        1
     b           2        2
     c           3        3
     d           4        4

df_2

product_name | amount
     a           1    
     b           2 

You can use set_index to set product_name as index for both df_1 and df_2, then use subtract() with fill_value = 0 to maintain any values that are not present in both dataframes.

df_1.set_index('product_name').subtract(df_2.set_index('product_name'), fill_value=0).reset_index()

The code above yields

product_name  | amount | price
     a           0.0      1.0
     b           0.0      2.0
     c           3.0      3.0
     d           4.0      4.0

The question is quite similar to subtracting two dataframes