Substraction between two dataframe's column

user_13795295 0
Viewed 41

I have different dataset total product data and selling data. I need to find out the Remaining products from product data comparing selling data. So, for that, I have done some general preprocessing and make both dataframe ready to use. But can't get it how to compare them.

DataFrame 1:

    Item            Qty
0   BUDS2           1.0
1   C100            4.0
2   CK1             5.0
3   DM10            10.0
4   DM7             2.0
5   DM9             9.0
6   HM12            6.0
7   HM13            4.0
8   HOCOX25(CTYPE)  1.0
9   HOCOX30USB      1.0
10  RM510           8.0
11  RM512           8.0
12  RM569           1.0
13  RM711           2.0
14  T2C             1.0

and

DataFrame 2 : 

    Item Name      Quantity
0   BUDS2          2.0
1   C100           5.0
2   C101CABLE      1.0
3   CK1            8.0
4   DM10           12.0
5   DM7            5.0
6   DM9            10.0
7   G20CTYPE       1.0
8   G20NORMAL      1.0
9   HM12           9.0
10  HM13           8.0
11  HM9            3.0
12  HOCOX25CTYPE   3.0
13  HOCOX30USB     3.0
14  M45            1.0
15  REMAXRC080M    2.0
16  RM510          11.0
17  RM512          10.0
18  RM569          2.0
19  RM711          3.0
20  T2C            1.0
21  Y1             3.0
22  ZIRCON         1.0

I want to see the available quantity for each item. And I want to get an output like dataframe 2 but the Quantity column values will be changed after doing the subtraction operation. How can I do that ??

Expected Output: 

    Item Name      Quantity
0   BUDS2          1.0
1   C100           1.0
2   C101CABLE      1.0
3   CK1            3.0
4   DM10           2.0
5   DM7            3.0
6   DM9            1.0
7   G20CTYPE       1.0
8   G20NORMAL      1.0
9   HM12           3.0
10  HM13           4.0
11  HM9            3.0
12  HOCOX25CTYPE   2.0
13  HOCOX30USB     2.0
14  M45            1.0
15  REMAXRC080M    2.0
16  RM510          3.0
17  RM512          2.0
18  RM569          1.0
19  RM711          1.0
20  T2C            0.0
21  Y1             3.0
22  ZIRCON         1.0
pandas numpy python
1 Answers

This can help by merging two dataframe:

df_new = df_2.merge(df_1,'left',left_on='Item Name',right_on='Item').fillna(0)
df_new.Quantity = df_new.Quantity - df_new.Qty
df_new = df_new.drop(['Item','Qty'],axis=1)

df_new output:

       Item Name  Quantity
0          BUDS2       1.0
1           C100       1.0
2      C101CABLE       1.0
3            CK1       3.0
4           DM10       2.0
5            DM7       3.0
6            DM9       1.0
7       G20CTYPE       1.0
8      G20NORMAL       1.0
9           HM12       3.0
10          HM13       4.0
11           HM9       3.0
12  HOCOX25CTYPE       3.0
13    HOCOX30USB       2.0
14           M45       1.0
15   REMAXRC080M       2.0
16         RM510       3.0
17         RM512       2.0
18         RM569       1.0
19         RM711       1.0
20           T2C       0.0
21            Y1       3.0
22        ZIRCON       1.0
user_49759810
Related