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
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
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
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