select based on row combinations on different columns pandas-CodePudding

I have the following pandas data frame.

ID  col1 col2    value
1    4    New     20
2    4    OLD     30
3    5    OLD     60
4    5    New     50
5    3    New     70

I would like to select only rows which has the following rules. from col1 value 4 and 3 should be in New and 5 should be in Old in col2. Drop other rows other wise.

ID  col1 col2    value
1    4    New     20
3    5    Old     60
5    3    New     70

Can any one help with this in Python pandas?

CodePudding user response：

Use DataFrame.query with filter by in chained by & for bitwise AND and second condition chain by | for bitwise OR:

df1 = df.query("(col1 in [4,3] & col2 == 'New') | (col1 == 5 & col2 == 'OLD')")
print (df1)
   ID  col1 col2  value
0   1     4  New     20
2   3     5  OLD     60
4   5     3  New     70

Or use boolean indexing with Series.isin:

df1 = df[df['col1'].isin([3,4]) & df['col2'].eq('New') | 
         df['col1'].eq(5) & df['col2'].eq('OLD')]