Dropping rows from pandas dataframe based on value in column(s)

Question

Suppose I have a dataframe which has Column 'A' and Column 'B' How do I drop rows where Column 'A' and 'B' are equal , but not in same row. I only wanto to drop rows where column 'B' is equal to column 'A'

For example Column 'B' from Rows 4, 8 & 9 is equal to Rows 2,3&5 Column 'A'. I want to drop Rows 4, 8 & 9

    Column A         Column B                                                 
1        10               62 
2        10               72
3        20               75
4        20               10
5        30               35
6        30               45               
7        40               55    
8        40               20
9        40               30

Drop Rows 4, 8 & 9 since Column B from rows is equal to column A from row 2,3&5

Expected output

    Column A         Column B                                                 
1        10               62 
2        10               72
3        20               75

5        30               35
6        30               45               
7        40               55

Rows 4, 8 & 9 needs to be deleted

Adding additional details: Column A and B will never be equal in same row. Multiple rows in Column B may have matching values in Column A. To illustrate I have expanded the dataframe Sorry if my originial row numbers are not matching. To summarize the requirement.

Multiple rows will have column B matching with Column A and expectation is to delete all rows where column B is matching with Column A in any row.

To reiterate Column A and Column B will not be equal in same row

Is the drop recursive? I.e. If there is a row 5 valued (50, 30), then what is the time it should be processed ? If it is processed after row 3 is dropped, then Column A does not have a value 30 so it will be retained. In contrast, if all rows were dropped at once, then row 5 will be marked and dropped. — Bill Huang
– Bill Huang, Commented Oct 4, 2020 at 19:34
Yes. There is a possibility that multiple rows will have the condition mentioned — MLNLPEnhusiast
– MLNLPEnhusiast, Commented Oct 4, 2020 at 19:35
@BillHuang you are right. Unique values in column A could be lost. I don't know if this is desired. That would make the problem interesting. — Michael Szczesny
– Michael Szczesny, Commented Oct 4, 2020 at 19:37
Could you provide a larger sample with multiple rows to be dropped? I have posted an answer but deleted it, because I am little bit confused about exactly what you are looking for. If it recursive that makes the solution much more difficult and it would make more sense to loop. — David Erickson
– David Erickson, Commented Oct 4, 2020 at 19:50
@MLNLPEnhusiast is there anything about the solution by Michael that is incorrect? I don't see anything that could be incorrect about his answer if you change it to df[~(df['Column B'].isin(df['Column A'])] ??? I think we were all overthinking this a little bit yesterday? :) ??? — David Erickson
– David Erickson, Commented Oct 5, 2020 at 21:50

Trenton McKinney · Accepted Answer · 2020-10-05 21:51:29Z

This solution is assuming that unique values in column A should be dropped, too, when the condition is met in column B.

I added a fifth row to test for the condition that equal values in the same row should not be dropped

   Column A  Column B
1        10        62
2        20        75
3        30        45
4        45        55
5        65        65

Check for all values in column B if they are in column A with isin but exclude rows with equal values.

df[~(df['Column B'].isin(df['Column A']) & (df['Column B'] != df['Column A']))]

Out:

   Column A  Column B
1        10        62
2        20        75
4        45        55
5        65        65

Updated, as per the additional details, and the output matches the expected result.

import pandas as pd

# sample
df = pd.DataFrame({'colA': [10, 10, 20, 20, 30, 30, 40, 40, 40], 'colB': [62, 72, 75, 10, 35, 45, 55, 20, 30]})

# display(df)
   colA  colB
0    10    62
1    10    72
2    20    75
3    20    10
4    30    35
5    30    45
6    40    55
7    40    20
8    40    30

df[~(df['colB'].isin(df['colA']) & (df['colB'] != df['colA']))]

[out]:
   colA  colB
0    10    62
1    10    72
2    20    75
4    30    35
5    30    45
6    40    55

Collectives™ on Stack Overflow

Dropping rows from pandas dataframe based on value in column(s)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related