Easy data reconciliation in Python

In this post, I would like to share all the common methods in python for data reconciliation. Consider scenarios like below: 1. Finding symmetric differences, 2. Combining data with overlaps. All the code below are for illustration purposes only, but the fundamentals can be scaled to more complex datasets.

import pandas as pd

import numpy as np

# turn jupyter notebook warnings off

import warnings

warnings.filterwarnings("ignore")

Working example one: Two dataframes with reconciliation column key containing non-duplicated elements

df1 = pd.DataFrame({'tradeid':range(5),'profit':range(1000,2000,200)})

df2 = pd.DataFrame({'tradeid':range(2,7,1), 'stock':   ['APL','MST','JNJ','TSL','BAB']})

display(df1)

display(df2)

Solution one: Since ‘tradeid’ is always unique, drop_duplicates() is the best way to spot the “exclusive-or” differences between two dataframes

pd.concat([df1,df2],axis=0).drop_duplicates(subset='tradeid',keep=False)

Solution two: Since ‘tradeid’ is always unique, we can also use a set function to find our differences

diff = set(df1.tradeid).symmetric_difference(set(df2.tradeid)) 

Then use .isin() method to select the rows from a list. Also python set is not searchable by hash values; thus converting to list

pd.concat([df1.loc[df1['tradeid'].isin(list(diff)),:],df2.loc[df2['tradeid'].isin(list(diff)),:]])

Solution three: Similar to solution two, this time we are finding symmetric differences using np.array

diff = np.setxor1d(np.array(df1.tradeid), np.array(df2.tradeid)) 

pd.concat([df1.loc[df1['tradeid'].isin(list(diff)),:],df2.loc[df2['tradeid'].isin(list(diff)),:]])

Working example two: Two dataframes with reconciliation column key containing duplicated elements

df1 = pd.DataFrame({'tradeid':[0,2,2,3,4],'profit':range(1000,2000,200)})

df2 = pd.DataFrame({'tradeid':[2,3,4,5,5], 'stock':['APL','MST','JNJ','TSL','BAB']})

display(df1)

display(df2)

Solution: Use the pandas merge function with the indicator flag

df3 = df1.merge(df2, on='tradeid',how='outer',indicator=True)

display(df3)

df3 = df3.loc[df3["_merge"] != 'both', :]

# cleanup to make df3 our final result

del df3["_merge"]

df3

Working example three: Finding symmetric differences for 2 lists containing duplicates

list1 = [0,1,1,2,3]

list2 = [0,1,3,4,4] 

Solution is embedded using functional programming

list3 = list(filter(lambda x:x not in list2, list1))

list4 = list(filter(lambda x:x not in list1, list2))

list3 + list4 

Out: [2, 4, 4]

p.s. list comprehension does not work here somehow [list1.remove(x) for x in list1 if x in list2]

Working example four: Combining data with overlaps

d1 = pd.DataFrame({'tradeid':[0,1,2],'profit':[1000,np.nan,2000],'stock':['APL','JNJ',np.nan]})

d2 = pd.DataFrame({'tradeid':[3,1,2],'profit':[1000,1500,np.nan],'stock':['APL',np.nan,np.nan]})

display(d1)

display(d2) 

Subsequent dataset will combine and overwrite the original

oned2.combine_first(d1)