Find row wise difference of 2 dataframes with multiple indexes in python

Viewed 52

I am designing one file comparator tool using streamlit, where user can upload 2 files [type csv] then the user can select the comparison keys [index], and comparison factors [column names for which comparison can be done].

Issue 1:

I need to get the missing/extra elements from both files. I have used pandas merge with an outer join but looks like in outer join result both dataframe columns are appearing with -x, -y, if I want only the left file or right file entries what should be done. Here is my function for getting the missing elements:

def missing_elements(df1,df2,key):
    result_df = df1.merge(df2,on=key,indicator=True,how='outer')
    return result_df

missed_df = missing_element(prod_df,uat_df,cmp_keys) #i had set indexes for the selected cmp_keys
prod_miss = missed_df.loc[lambda v: v['_merge']=='left_only']
uat_miss = missed_df.loc[lambda v: v['_merge']=='right_only']

But in the result, we are getting both the dataframe columns but we only need the prod only or uat only data

Issue 2:

Example of 2 dataframes:

prod_Df

agreement_id deal_ref cash_in cash_out reference
12345 20001-A 430.66 450.75 Let-out
12356 20001-B 511.40 500.60 Let-in
12366 20001-C 255.36 350.14 NA
12376 20001-D 155.20 155.20 NA

uat_Df

agreement_id deal_ref cash_in cash_out reference
12345 20001-A 430.66 450.75 Let-out
12356 20001-B 510.40 501.60 Let-out
12366 20001-C 275.56 350.14 NA
12386 20001-D 155.20 155.20 NA

I want the results in this way:

prod extra entries:

agreement_id deal_ref cash_in cash_out reference
12376 20001-D 155.20 155.20 NA

uat extra entries:

agreement_id deal_ref cash_in cash_out reference
12386 20001-D 155.20 155.20 NA

Differences:

                      prod    uat      difference  prod    uat         difference
agreement_id deal_ref cash_in cash_in  cash_in     cash_out  cash_out  cash_out
12345        20001-A  430.66  430.66    0.0        450.75    450.75    0.00
12356        20001-B  511.40  510.40    1.40       500.60    501.60    1.00
12366        20001-C  255.36  257.56    2.20       350.14    350.14    0.00

Here is my code after concat, but I am not getting how we can get the difference between the two columns:

def calculate_diff(df1,df2):
    df_diff = pd.concat([df1,df2],axis='columns',keys=['prod','uat'])
    final_df = df_diff.swaplevel(axis='columns')[df1.columns[1:]]
    return final_df    
0 Answers
Related