How to drop rows from pandas based on several numerical value conditions within subsets of each dataframe?

user_10145261 0
Viewed 53

I have a df that looks like this:

Account 1   Pre       9
Account 1   Pre       9
Account 1   During    5
Account 1   Post      5
Account 1   Post      5
Account 2   Pre       11
Account 2   During    9
Account 2   Post      7
Account 2   Post      7
Account 2   Post      7
Account 2   Post      7
Account 3   Pre       1
Account 3   During    2
Account 3   During    2
Account 3   Post      3

I am trying to drop all rows for each account if Pre, During, and Post are all less than 10. So in the example above we would lose all of the Account 1 rows and all of the Account 3 rows but keep all Account 2 rows because there in a single row that has 11.

I'm relatively new to pandas and python but I'm thinking something following the logic below might work:

for each Account in Account:
    if 'Pre' > 10 AND 'During' > 10 AND 'Post' > 10
    return (df_updated)

This df_updated should be composed of only the Account 2 I believe. I don't think I can just take the results of this for loop though and return a new df directly though so I am not quite sure how to do this.

Thank you for any help you can provide!

pandas python
4 Answers

Data

print(df)

 Account  Status  Count
0   Account1     Pre      9
1   Account1     Pre      9
2   Account1  During      5
3   Account1    Post      5
4   Account1    Post      5
5   Account2     Pre     11
6   Account2  During      9
7   Account2    Post      7
8   Account2    Post      7
9   Account2    Post      7
10  Account2    Post      7
11  Account3     Pre      1
12  Account3  During      2
13  Account3  During      2
14  Account3    Post      3



df[df.groupby('Account')['Count'].transform(lambda x: x.gt(10).any())]



 Account  Status  Count
5   Account2     Pre     11
6   Account2  During      9
7   Account2    Post      7
8   Account2    Post      7
9   Account2    Post      7
10  Account2    Post      7
user_89869750

Let say your df has 3 columns:

Accountname type      value
Account 1   Pre       9
Account 1   Pre       9
Account 1   During    5
Account 1   Post      5
Account 1   Post      5
Account 2   Pre       11
Account 2   During    9
Account 2   Post      7
Account 2   Post      7
Account 2   Post      7
Account 2   Post      7
Account 3   Pre       1
Account 3   During    2
Account 3   During    2
Account 3   Post      3

You dont need such complicated scripts, you can easily filter it with :

df= df[lambda x: x['accountname'].isin(df[df['value']>10].accountname)]

output:

Account 2   Pre       11
Account 2   During    9
Account 2   Post      7
Account 2   Post      7
Account 2   Post      7
Account 2   Post      7
user_39588780

It looks like the second column only have Pre, During, and Post. That means you only need to check for each account if there is a single row with the third column value > 10. The second columns doesn't play any roles in your problem:

df.loc[df['col3'].gt(10).groupby(df['col1']).transform('any')]

Output:

     account    step  value
4  Account 2     Pre     11
5  Account 2  During      9
6  Account 2    Post      7
7  Account 2    Post      7
8  Account 2    Post      7
9  Account 2    Post      7
user_42384080

You can groupby.filter accounts with at least one value greater than 10

df.groupby('col1').filter(lambda x: x.col3.gt(10).any())

Out:

         col1    col2  col3
5   Account 2     Pre    11
6   Account 2  During     9
7   Account 2    Post     7
8   Account 2    Post     7
9   Account 2    Post     7
10  Account 2    Post     7

Setup the dataframe

import pandas as pd
import io

t = '''
Account 1   Pre       9
Account 1   Pre       9
Account 1   During    5
Account 1   Post      5
Account 1   Post      5
Account 2   Pre       11
Account 2   During    9
Account 2   Post      7
Account 2   Post      7
Account 2   Post      7
Account 2   Post      7
Account 3   Pre       1
Account 3   During    2
Account 3   During    2
Account 3   Post      3'''

df = pd.read_csv(io.StringIO(t), sep='\s\s+', engine='python', header=None, names=list('123')).add_prefix('col')
df

Out:

         col1    col2  col3
0   Account 1     Pre     9
1   Account 1     Pre     9
2   Account 1  During     5
3   Account 1    Post     5
4   Account 1    Post     5
5   Account 2     Pre    11
6   Account 2  During     9
7   Account 2    Post     7
8   Account 2    Post     7
9   Account 2    Post     7
10  Account 2    Post     7
11  Account 3     Pre     1
12  Account 3  During     2
13  Account 3  During     2
14  Account 3    Post     3
user_142777220
Related