Find difference between two integer columns but by specific ID column

user_11315276 0
Viewed 52

I have the following two dataframes.

last_request_df:

name    fruit_id  sold
apple   123       1 
melon   456       12
banana  12        23

current_request_df:

name    fruit_id  sold
apple   123       5 
melon   456       19
banana  12        43
orange  55        3 
mango   66        0

The output should be based on matching the fruit_id column from both last_request_df and current_request_df and figuring out the difference in the sold column:

difference_df:

name    fruit_id  sold
apple   123       4 
melon   456       7
banana  12        20
orange  55        3 
mango   66        0

I've tried the following but I'm afraid this is not matching by the fruid_id column.

difference_df['sold_diff'] = current_request_df['sold'] - last_request_df['sold']

Is there a preferred method to capture the difference_df based on the data I've provided?

pandas dataframe python-3.x
1 Answers
 #Reset index to name for both dfs

 difference_df=current_request_df.set_index('name')
 last_request_df=last_request_df.set_index('name')
     

#Find the difference using sub. To do this ensure the two dfs have same index by reindexing 

difference_df['sold']=difference_df['sold'].sub(last_request_df.reindex(index=difference_df.index).fillna(0)['sold'])
    

  

         fruit_id  sold
name                  
apple        123   4.0
melon        456   7.0
banana        12  20.0
orange        55   3.0
mango         66   0.0
user_89869750
Related