[Python] Find the value that is different when a row is marked "left_only" or "right_only"...

There are two dataframes: current and base. current is an incremental update to base.

Whenever there is an update made to current, we want to run checks to see if it adheres to rules. If it does, current becomes base.

Updates to column Issr is not expected except empty value. So, there are two checks:

• New entry must not add any value to Issr column ('' is accepted)
• Existing row's value for column Issr shouldn't be updated (if updated to empty, it is fine). Update to any other column is ok.

base

SNo Rank Ctry Cat Issr Ref
1 10 A Book Y 100
2 14 B Laptop C 101
3 15 C Pen J 102
4 50 D Pen 103

current

SNo Rank Ctry Cat Issr Ref
1 10 A Book1 Y 100
2 (updated) 14 B Laptop 101
3 15 C Pen J 102
4 (updated) 50 D Pen U 103
5 (new entry) 24 K Pencil W 101
6 (new entry) 24 RT Pencil 201

Above current fails checks because of multiple issues:

• Row# 4 in base: Existing row's column Issr got updated to a non-empty values (while existing Row# 2 update is fine as it made the value empty)
• Row# 5 is a new entry in current with a non-empty value. This is violation. Row#6 is also new entry but ok since the value for Issr is empty.

import pandas as pd

base = {
'Rank': [10,14,15,50],
'Ctry': ['A', 'B', 'C', 'D'],
'Cat': ['Book', 'Laptop', 'Pen', 'Pen'],
'Issr': ['Y', 'C', 'J', ''],
'Ref': ['100', '101', '102', '103']
}

current = {
'Rank': [10,14,15,50, 24, 24],
'Ctry': ['A', 'B', 'C', 'D', 'K', 'RT'],
'Cat': ['Book', 'Laptop', 'Pen', 'Pen', 'Pencil', 'Pencil'],
'Issr': ['Y', '', 'J', 'U', 'W', ''],
'Ref': ['100', '101', '102', '103', '101', '201']
}

base_df = pd.DataFrame(base)
current_df = pd.DataFrame(current)


merged_df = pd.merge(current_df, base_df, how="outer", indicator=True)

print(merged_df)

Rank Ctry Cat Issr Ref _merge
10 A Book Y 100 both
14 B Laptop 101 left_only
15 C Pen J 102 both
50 D Pen U 103 left_only <--- how to know this got marked `left_only` as value of `issr` col is different
24 K Pencil W 101 left_only <--- Invalid - new entry has no-empty value for `Issr` col
24 RT Pencil 201 left_only
14 B Laptop C 101 right_only
50 D Pen 103 right_only




I can get left_only (indicating update/new rows in current df) but how to know because of which column/columns the row got marked as left_only?

If I get to know that pandas marked left_only as it saw a diff in Issr column, I can just check its value (empty or not), and pass/fail the job.

How to get this column info?

Continue reading...