Python Forum
Select rows based on a criteria
Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Select rows based on a criteria
#6
Pandas does not support "conditional join". It can be done by using ordinary (or even cartesian) join first and then applying your 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, as
t1.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.
Reply


Messages In This Thread
Select rows based on a criteria - by klllmmm - May-04-2017, 02:01 AM
RE: Select rows based on a criteria - by Larz60+ - May-04-2017, 04:04 AM
RE: Select rows based on a criteria - by klllmmm - May-07-2017, 07:58 AM
RE: Select rows based on a criteria - by snippsat - May-07-2017, 01:01 PM
RE: Select rows based on a criteria - by klllmmm - May-08-2017, 07:56 PM
RE: Select rows based on a criteria - by zivoni - May-11-2017, 10:34 AM
RE: Select rows based on a criteria - by klllmmm - May-11-2017, 01:46 PM
RE: Select rows based on a criteria - by zivoni - May-11-2017, 06:10 PM
RE: Select rows based on a criteria - by klllmmm - May-11-2017, 06:58 PM

Possibly Related Threads…
Thread Author Replies Views Last Post
  Merging rows and adding columns based on matching index pythonnewbie78 3 748 Dec-24-2023, 11:51 AM
Last Post: Pedroski55
  Pandas Dataframe Filtering based on rows mvdlm 0 1,396 Apr-02-2022, 06:39 PM
Last Post: mvdlm
  Extracting rows based on condition on one column Robotguy 2 2,170 Aug-07-2020, 02:27 AM
Last Post: Robotguy
  Dropping Rows From A Data Frame Based On A Variable JoeDainton123 1 2,186 Aug-03-2020, 02:05 AM
Last Post: scidam
  Counting Criteria in Pandas Question Koenig 1 2,137 Sep-30-2019, 05:16 AM
Last Post: perfringo
  select data based on indice Staph 4 2,458 Jul-15-2019, 02:05 AM
Last Post: scidam
  Removing rows at random based on the value of a specific column Mr_Keystrokes 4 5,530 Aug-24-2018, 11:15 AM
Last Post: Mr_Keystrokes
  Updating df rows based on 2 conditions stretch 1 3,108 May-02-2018, 09:15 AM
Last Post: volcano63
  drop duplicate values based on a criteria klllmmm 1 5,581 Apr-28-2017, 10:17 AM
Last Post: zivoni
  Insert values into a column in the same table based on a criteria klllmmm 3 4,176 Apr-13-2017, 10:10 AM
Last Post: zivoni

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020