Substracting Values From Two Different Pandas Dataframes Based On Same Row Values

I have data of two dataframes First dataframe: Name price date Apple 25 23-9-2021 Orange 35 23-9-2021 Banana 15 23-9-2021 Gauva 10 23-9-2021 Grapes 5 23-

Solution 1:

1. merge both dataframes
2. Find the difference in price
3. concat

df3 = df2.merge(df1, how="left", on=["Name", "date"])
df3["price"] = df3["price_x"]-df3["price_y"]

output = pd.concat([df1, df3.drop(["price_x", "price_y"], axis=1)])

>>> output
     Name  price       date0   Apple     2523-9-20211  Orange     3523-9-20212  Banana     1523-9-20213   Gauva     1023-9-20214  Grapes      523-9-20215  Sapota     1522-9-20216  Papaya     3321-9-20210   Apple      523-9-20211  Orange     1023-9-20212  Banana     1523-9-20213   Gauva     1023-9-20214  Grapes     2023-9-2021

Solution 2:

You can use set_index instead of merge:

out = pd.concat([df1, df2.set_index(['Name', 'date'])['price']
                         .sub(df1.groupby(['Name', 'date'])['price'].sum())
                         .dropna().reset_index()], ignore_index=True)

Output:

>>>out

      Name  price       date
0    Apple   25.0  23-9-2021
1   Orange   35.0  23-9-2021
2   Banana   15.0  23-9-2021
3    Gauva   10.0  23-9-2021
4   Grapes    5.0  23-9-2021
5   Sapota   15.0  22-9-2021
6   Papaya   33.0  21-9-2021
7    Apple   20.0  23-9-2021
8   Orange   30.0  23-9-2021
9   Banana   10.0  23-9-2021
10   Gauva   10.0  23-9-2021
11  Grapes    5.0  23-9-2021
12   Apple   15.0  23-9-2021
13  Orange   25.0  23-9-2021
14  Banana   25.0  23-9-2021
15   Gauva   20.0  23-9-2021
16  Grapes   15.0  23-9-2021
17   Apple   20.0  23-9-2021
18  Banana   40.0  23-9-2021
19   Gauva   20.0  23-9-2021
20  Grapes   20.0  23-9-2021
21  Orange   10.0  23-9-2021