CODEWITHSUNDEEP
pythonpandasdataframesubtraction
Byungjun Lee
Byungjun Lee

Reputation: 137

Subtracting from one column based on values in another column

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

Upvotes: 3

Views: 2641

Answers (3)

DJK
DJK

Reputation: 9264

You could also merge and then do subtraction

df = df1.merge(df2,how='left',on='product_name',suffixes=('','R'))
df.amount = df.amount - df.amountR.fillna(0)

  product_name  amount  price  amountR
0            a     0.0      1      1.0
1            b     0.0      2      2.0
2            c     3.0      3      NaN
3            d     4.0      4      NaN

Upvotes: 0

cs95
cs95

Reputation: 402423

Option 1
Taking advantage of your data using df.isin:

mask = a.product_name.isin(b.product_name)
a.loc[mask, 'amount'] = 0

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

Option 2
set_index + reindex + subtract. Slightly more robust, doesn't set to 0:

b = b.set_index('product_name').reindex(a.product_name).fillna(0)
a.amount -= b.amount.values

a
  product_name  amount  price
0            a     0.0      1
1            b     0.0      2
2            c     3.0      3
3            d     4.0      4

Upvotes: 5

Phurich.P
Phurich.P

Reputation: 1416

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

Upvotes: 1

Related Questions