How to compare 2 data frames in python and highlight the differences?-CodePudding

I am trying to compare 2 files one is in xls and other is in csv format.

File1.xlsx (not actual data)

  Title Flag  Price  total    ...more columns
0     A    Y     12    300
1     B    N     15    700
2     C    N     18   1000
..
..
more rows

File2.csv (not actual data)

  Title Flag  Price  total    ...more columns
0     E    Y      7    234
1     B    N     16    600
2     A    Y     12    300
3     C    N     17   1000
..
..
more rows

I used Pandas and moved those files to data frame. There is no unique columns(to make id) in the files and there are 700K records to compare. I need to compare File 1 with File 2 and show the differences. I have tried few things but I am not getting the outliers as expected.

If I use merge function as below, I am getting output with the values only for File 1.

diff_df = df1.merge(df2, how = 'outer' ,indicator=True).query('_merge == "left_only"').drop(columns='_merge')

output I am getting

  Title Attention_Needed  Price  total
1     B                N     15    700
2     C                N     18   1000

This output is not showing the correct diff as record with Title 'E' is missing

I also tried using panda merge

diff_df = pd.merge(df1, df2, how='outer', indicator='Exist')

& output for above was

  Title Flag  Price  total       Exist
0     A    Y     12    300        both
1     B    N     15    700   left_only
2     C    N     18   1000   left_only
3     E    Y      7    234  right_only
4     B    N     16    600  right_only
5     C    N     17   1000  right_only

Problem with above output is it is showing records from both the data frames and it will be very difficult if there are 1000 of records in each data frame.

Output I am looking for (for differences) by adding extra column("Comments") and give message as matching, exact difference, new etc. or on the similar lines

  Title Flag  Price  total       Comments
0     A    Y     12    300        matching
1     B    N     15    700   Price, total different
2     C    N     18   1000   Price different
3     E    Y      7    234    New record

If above output can not be possible, then please suggest if there is any other way to solve this.

PS: This is my first question here, so please let me know if you need more details here.

CodePudding user response：

Rows in DF1 Which Are Not Available in DF2

df = df1.merge(df2, how = 'outer' ,indicator=True).loc[lambda x : x['_merge']=='left_only']

Rows in DF2 Which Are Not Available in DF1

df = df1.merge(df2, how = 'outer' ,indicator=True).loc[lambda x : x['_merge']=='right_only']

If you're differentiating by row not column

pd.concat([df1,df2]).drop_duplicates(keep=False)

If each df has the same columns and each column should be compared individually

for col in data.columns:
  set(df1.col).symmetric_difference(df2.col)
  # WARNING: this way of getting column diffs likely won't keep row order
  # new row order will be [unique_elements_from_df1_REVERSED] concat [unique_elements_from_df2_REVERSED]

CodePudding user response：

lets assume df1 (left) is our "source of truth" for what's considered an original record.

after running

diff_df = df1.merge(df2, how = 'outer' ,indicator=True).query('_merge == "left_only"').drop(columns='_merge')

take the output and split it into 2 df's.

df1 = diff_df[diff_df["Exist"] in ["both", "left_only"]]
df2 = diff_df[diff_df["Exist"] == "right_only"]

Right now, if you drop the "exist" row from df1, you'll have records where the comment would be "matching".

Let's assume you add the 'comments' column to df1

you could say that everything in df2 is a new record, but that would disregard the "price/total different".

If you really want the difference comment, now is a tricky bit where the 'how' really depends on what order columns matter most (title > flag > ...) and how much they matter (weighting system)

After you have a wighting system determined, you need a 'scoring' method that will compare two rows in order to see how similar they are based on the column ranking you determine.

# distributes weight so first is heaviest, last is lightest, total weight = 100
# if i was good i'd do this with numpy not manually
def getWeights(l):
    weights = [0 for col in l]
    total = 100
    while total > 0:
        for i, e in enumerate(l):
            for j in range(i 1):
                weights[j]  = 1
                total -= 1
    return weights


def scoreRows(row1, row2):
    s = 0
    for i, colName in enumerate(colRank):
        if row1[colName] == row2[colName]:
            s  = weights[i]


colRank = ['title', 'flag'] 
weights = getWeights(colRank)

Let's say only these 2 matter and the rest are considered 'modifications' to an original row

That is to say, if a row in df2 doesn't have a matching title OR flag for ANY row in df1, that row is a new record

What makes a row a new record is completely up to you.

Another way of thinking about it is that you need to determine what makes some row in df2 'differ' from some row in df1 and not a different row in df1

if you have 2 rows in df1 row1: [1, 2, 3, 4] row2: [1, 6, 3, 7]

and you want to compare this row against that df [1, 6, 5, 4]

this row has the same first element as both, the same second element as row2, and the same 4th element of row1.

so which row does it differ from?

if this is a question you aren't sure how to answer, consider cutting losses and just keep df1 as "good" records and df2 as "new" records

if you're sticking with the 'differs' comment, our next step is to filter out truly new records from records that have slight differences by building a score table

# to recap
# df1 has "both" and "left_only" records ("matching" comment)
# df2 has "right_only" records (new records and differing records)
rowScores = [] 
# list of lists
# each inner list index correlates to the index for df2
# inner lists are 

# made up of tuples
# each tuple first element is the actual row from df1 that is matched
# second element is the score for matching (out of 100)

for i, row1 in df2.itterrows(): 
    thisRowsScores = []
    #df2 first because they are what we are scoring
    for j, row2 in df1.iterrows():
        s = scoreRows(row1, row2)
        if s>0: # only save rows and scores that matter
            thisRowsScores.append((row2, s))
    # at this point, you can either leave the scoring as a table and have comments refer how different differences relate back to some row
    # or you can just keep the best score like i'll be doing

    #sort by score
    sortedRowScores = thisRowsScores.sort(key=lambda x: x[1], reverse=True)
    rowScores.append(sortedRowScores[0])
    # appends empty list if no good matches found in df1
    # alternatively, remove 'reversed' from above and index at -1

The reason we save the row itself is so that it can be indexed by df1 in order to add a "differ" comments

At this point, lets just say that df1 already has the comments "matching" added to it

Now that each row in df2 has a score and reference to the row it matched best in df1, we can edit the comment to that row in df1 to list the columns with different values.

But at this point, I feel as though that df now needs a reference back to df2 so that the record and values those difference refer to are actually gettable.