How to check n amount of positive values in a group in pandas

user_12559323 0
Viewed 51

I have a dataframe which looks like this

pd.DataFrame({'a': ['cust1', 'cust1', 'cust1', 'cust1', 'cust2', 'cust2', 'cust2', 'cust2', 'cust3', 'cust3', 'cust3', 'cust3'],
                   'year': [2017, 2018, 2019, 2020, 2017, 2018, 2019, 2020, 2017, 2018, 2019, 2020],
                   'amt': [2, 0, 4, 'NaN', 2, 2, 3, 3, 3, 2, 'NaN', 5]})

        a  year  amt
0   cust1  2017    2
1   cust1  2018    0
2   cust1  2019    4
3   cust1  2020  NaN
4   cust2  2017    2
5   cust2  2018    2
6   cust2  2019    3
7   cust2  2020    3
8   cust3  2017    3
9   cust3  2018    2
10  cust3  2019  NaN
11  cust3  2020    5

I need to check whether there were minimum of 3 positive values in column 'amt' per group in column 'a'. Resulting dataframe should look like this

        a  year  amt   cond
0   cust1  2017    2  False
1   cust1  2018    0  False
2   cust1  2019    4  False
3   cust1  2020  NaN  False
4   cust2  2017    2   True
5   cust2  2018    2   True
6   cust2  2019    3   True
7   cust2  2020    3   True
8   cust3  2017    3   True
9   cust3  2018    2   True
10  cust3  2019  NaN   True
11  cust3  2020    5   True

following logic applies:

cust1 = False as only 2 positive values (2017, 2019)

cust2 = True as 4 positive values

cust3 = True as 3 positive values

pandas-groupby pandas python
2 Answers

Let us try transform with sum

df = df.replace('NaN',np.nan)
df['cond'] = df.amt.gt(0).groupby(df['a']).transform('sum')>2
df
Out[62]: 
        a  year  amt   cond
0   cust1  2017  2.0  False
1   cust1  2018  0.0  False
2   cust1  2019  4.0  False
3   cust1  2020  NaN  False
4   cust2  2017  2.0   True
5   cust2  2018  2.0   True
6   cust2  2019  3.0   True
7   cust2  2020  3.0   True
8   cust3  2017  3.0   True
9   cust3  2018  2.0   True
10  cust3  2019  NaN   True
11  cust3  2020  5.0   True
user_79645270

I recommend that you have to use a for loop. And then, you have to modified the dataset or create another.

for i in range(df.shape[0])
  ### Your algoritm goes here (Your only need to select the file an the operation you want to do)
Admin1
Related