Thursday, July 8, 2021

Comparing two dimensionally different dataframes based on one mutual column

Comparing two dimensionally different dataframes based on one mutual column
In [1]:
import pandas as pd
In [2]:
def compareDF(df1,df2,ColumnName, diff=''):
    
    leftList=[]
    rightList=[]
    for i in list(df1):
        leftList.append(str(i)+'_l')
    for i in list(df2):
        rightList.append(str(i)+'_r')
    
    
    df1.columns=leftList
    df1=df1.rename(columns={str(ColumnName)+"_l": ColumnName})
    df2.columns=rightList
    df2=df2.rename(columns={str(ColumnName)+"_r": ColumnName})
    df = pd.merge(df1,df2, on=ColumnName, suffixes=('', '') ,how='outer')
    df=df.fillna(0)
    df.sort_values(ColumnName, inplace=True)
    dropList=[]
    for index, row in df.iterrows():
        total=0
        for i in list(df):
            if i==ColumnName:
                continue
            else:
                total=row[i]+total
        if total==0:
            dropList.append(index)
    df=df.drop(dropList)
    df=df.reset_index(drop=True)
    
    #if diff=='x':
        
    
    return df
In [3]:
def formatX(c):  # Optional
    c=str(c)
    c=c.replace(',', '')
    c=c.replace('$','')
    return float(str(c))
In [4]:
def cleanDF(df, columnName): 
    df=df.fillna(0)
    for i in list(df):
        try:
            if i != columnName:
                df[i] = df.apply(lambda x: formatX(x[i]),axis=1)
        except Exception as e:
            continue
    return df
In [5]:
def getDiff(df, columnName):
    
    leftList=[]
    rightList=[]
    
    for i in list(df):
        if str(i)[-2:]=='_l':
            leftList.append(str(i)[:-2])
        elif str(i)[-2:]=='_r':
            rightList.append(str(i)[:-2])
    
    for i in leftList:
        if i not in rightList:
            df[str(i+'_r')]=0
    
    for i in rightList:
        if i not in leftList:
            df[str(i+'_l')]=0

    for i in list(df):
        try:
            if i != columnName and str(i)[-2:]!='_r':
                if str(i)[:-2]+'_r' in list(df):
                    df[str(i)[:-2]]=df[str(i)[:-2]+'_r']-df[i]
                else:
                    print("not there",str(i)[:-2]+'_r', list(df))
            elif '_r' in str(i):
                pass
                #df=df.drop([i], axis=1)
        except Exception as e:
            #print("Error:", str(e))
            continue
    
    for i in list(df):
        if str(i)[-2:]=='_l' or str(i)[-2:]=='_r':
            df=df.drop([i], axis=1)
        
    return df
    
    
    
            
In [6]:
df1 = pd.DataFrame(columns=["id","A", "B"], data=[[1,5,3], [2,3,5]])
df2 = pd.DataFrame(columns=["id","A", "B"], data=[[1,2,3], [2,3,6]])
In [7]:
df1=cleanDF(df1, 'id')
df2=cleanDF(df2, 'id')
result=compareDF(df1,df2,'id')
In [8]:
result.head()
Out[8]:
id A_l B_l A_r B_r
0 1 5.0 3.0 2.0 3.0
1 2 3.0 5.0 3.0 6.0
In [10]:
# Right - Left
result2=getDiff(result, 'id')
not there _r ['id', 'A_l', 'B_l', 'A_r', 'B_r', 'A', 'B']
not there _r ['id', 'A_l', 'B_l', 'A_r', 'B_r', 'A', 'B']
In [11]:
result2.head()
Out[11]:
id A B
0 1 -3.0 0.0
1 2 0.0 1.0
In [18]:
df1 = pd.DataFrame(columns=["id","A", "B"], data=[[1,5,3], [2,3,5]])
df2 = pd.DataFrame(columns=["id","A", "B", "C"], data=[[1,2,3,4], [2,3,6,2], [12,13,16,1]])
In [19]:
df1=cleanDF(df1, 'id')
df2=cleanDF(df2, 'id')
result=compareDF(df2,df1,'id')
In [20]:
result.head()
Out[20]:
id A_l B_l C_l A_r B_r
0 1 2.0 3.0 4.0 5.0 3.0
1 2 3.0 6.0 2.0 3.0 5.0
2 12 13.0 16.0 1.0 0.0 0.0
In [21]:
result2=getDiff(result, 'id')
In [22]:
result2.head()
Out[22]:
id A B C
0 1 3.0 0.0 -4.0
1 2 0.0 -1.0 -2.0
2 12 -13.0 -16.0 -1.0
In [ ]:
 
In [ ]: