May-11-2017, 10:34 AM
Pandas does not support "conditional join". It can be done by using ordinary (or even cartesian) join first and then applying your conditions.
BTW, this is much simplified compared to your original task. With original vague conditions like "If there are many rows with CrAccount 9000 & DrAccount 1500, select the similar amount" or "go to next row, but ignore if the row already selected as reversal entry" it would likely need to iterate over your original dataframe, and searching/removing "candidate" row in copy of dataframe while checking all conditions.
Output:In [2]: df = pd.merge(table, table, left_on='DrCode', right_on='CrCode')
In [3]: df.query("Amount_y <= Amount_x and (Ref_x == 'Deduct' or Ref_x=='Transfer' and Ref_y=='Add')")
dataframe.query
is handy construct to apply boolean condition to a dataframe. I have ignored last condition of your sql query, ast1.Ref = 'Transfer' and t2.Ref = 'ADD' and t1.Ref != 'Transfer'is always false, likely there was some typo?
BTW, this is much simplified compared to your original task. With original vague conditions like "If there are many rows with CrAccount 9000 & DrAccount 1500, select the similar amount" or "go to next row, but ignore if the row already selected as reversal entry" it would likely need to iterate over your original dataframe, and searching/removing "candidate" row in copy of dataframe while checking all conditions.