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