Pandas Python how to delete rows based on two column conditions?

Question

I am unsure how to go about this... I have 1 column, 'Status' and another column, 'MultiID'. I am trying to delete all the rows with the Same MultiID, but only if it includes status 'B'.

import pandas as pd

# initialise data of lists.
data = {'Status':['A', 'B', 'A', 'C', 'C', 'A', 'B', 'A'], 
'MultiID': [1, 1, 2, 2, 2, 3, 3, 3]}

# Create DataFrame
df = pd.DataFrame(data)

# Print the output.
print(df)

Since There's a 'B' in MultiID's 1 and 3, the output should be:

0      A        2
1      C        2
2      C        2

user17242583 · Accepted Answer · 2022-01-28 20:33:47Z

3

Here's a short solution:

new_df = df.groupby('MultiID').filter(lambda g: ~g['Status'].eq('B').any())

Output:

>>> new_df
  Status  MultiID
2      A        2
3      C        2
4      C        2

answered Jan 28, 2022 at 20:33

user17242583

Sign up to request clarification or add additional context in comments.

Comments

user7864386 · Accepted Answer · 2022-01-28 20:35:09Z

2

Here's one way without groupby. Get the "MultiID"s of rows where Status=="B", then filter the rows without those MultiIDs:

MultiIDs_w_Bs = df.loc[df['Status'].eq('B'), 'MultiID']
out = df[~df['MultiID'].isin(MultiIDs_w_Bs)]

Output:

  Status  MultiID
2      A        2
3      C        2
4      C        2

answered Jan 28, 2022 at 20:35

user7864386

Comments

BENY · Accepted Answer · 2022-01-28 20:35:38Z

2

No need groupby

out = df.loc[~df.MultiID.isin(df.loc[df.Status.eq('B'),'MultiID'])]
Out[169]: 
  Status  MultiID
2      A        2
3      C        2
4      C        2

answered Jan 28, 2022 at 20:35

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

Corralien · Accepted Answer · 2022-01-28 20:29:59Z

1

Use groupby before filter out your dataframe according your condition. Here transform allows to broadcast boolean result to each member of the group.

out = df[df.groupby('MultiID')['Status'].transform(lambda x: all(x != 'B'))]
print(out)

# Output
  Status  MultiID
2      A        2
3      C        2
4      C        2

answered Jan 28, 2022 at 20:29

Corralien

121k8 gold badges43 silver badges68 bronze badges

Comments

Scott Boston · Accepted Answer · 2022-01-28 20:33:08Z

0

Try:

df[~df['Status'].eq('B').groupby(df['MultiID']).transform('any')]

Output:

  Status  MultiID
2      A        2
3      C        2
4      C        2

Details:

Create a boolean series where status equals to B.
Group that series by MultiID
Using transform if any record in that group is True, the make the whole group True
Invert True and False and boolean filter the original dataframe

answered Jan 28, 2022 at 20:33

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Comments

Kureteiyu · Accepted Answer · 2022-01-28 20:36:48Z

0

s = ['A', 'B', 'A', 'C', 'C', 'A', 'B', 'A']
m = [1, 1, 2, 2, 2, 3, 3, 3]

# Loop through lists and remove the element if its status is B
for i in range(len(m)-1):
    if s[i] == 'B':
        del m[i]

# Remove all B's from s
while 'B' in s:
    s.remove('B')

print(s)
print(m)

gives

['A', 'A', 'C', 'C', 'A', 'A']
[1, 2, 2, 2, 3, 3]

answered Jan 28, 2022 at 20:36

Kureteiyu

6391 gold badge5 silver badges13 bronze badges

Collectives™ on Stack Overflow

Pandas Python how to delete rows based on two column conditions?

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related