CODEWITHSUNDEEP
pythonpandasdataframe
worldCurrencies
worldCurrencies

Reputation: 467

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

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

Upvotes: 0

Views: 82

Answers (6)

Kureteiyu
Kureteiyu

Reputation: 639

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]

Upvotes: 0

BENY
BENY

Reputation: 323386

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

Upvotes: 2

user7864386
user7864386

Reputation:

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

Upvotes: 2

user17242583
user17242583

Reputation:

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

Upvotes: 3

Scott Boston
Scott Boston

Reputation: 153550

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

Upvotes: 0

Corralien
Corralien

Reputation: 120559

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

Upvotes: 1

Related Questions