For every ID , look if there is a row that matches conditions and create a new column

user_13737161 0
Viewed 75

Here is my dataframe:


       ID  Tell  Number 
0       1   Yes     3   
1       1   Yes     6
2       1   No      9
3       2   Yes     4
4       2   Yes     7
5       2   No      8
6       3   Yes     15
7       3   Yes     8
8       3   Yes     6
9       3   Yes     13 

# Creating the dictionary
dic = {'ID': [1,1,1,2,2,2,3,3,3,3], 'Tell': ['Yes', 'Yes', 'No', 'Yes','Yes', 'No','Yes', 'Yes','No', 'Yes'], 'Number': [3,6,9,4,7,8,15,8,6,13]}

# Creating the dataframe
df = pd.DataFrame(dic)

I want to add a 6th column that checks for each ID if there is a row that has 'No' in the Tell column and checks if that row the number is not equal to 8. If the conditions matches then the 6th column is 0. Else its 1.

So, my resultant df should look like this:

       ID  Tell  Number  col6
0       1   Yes     3     0
1       1   Yes     6     0
2       1   No      9     0
3       2   Yes     4     1
4       2   Yes     7     1
5       2   No      8     1
6       3   Yes     15    1
7       3   Yes     8     1
8       3   Yes     6     1
9       3   Yes     13    1

Here is what I started with:

I know I would have to use merge, and df.loc for my conditions but I'm not sure how to implement this.

Please help

pandas merge python
1 Answers

Do with two groupby transform + any 

s1=df.Tell.eq('No')
s2=df.Number.eq(8)
s=s1.groupby(df.ID).transform('any') & s2.groupby(df.ID).transform('any') 
df['New']=s.astype(int)
df
   ID Tell  Number  New
0   1  Yes       3    0
1   1  Yes       6    0
2   1   No       9    0
3   2  Yes       4    1
4   2  Yes       7    1
5   2   No       8    1
6   3  Yes      15    1
7   3  Yes       8    1
8   3   No       6    1
9   3  Yes      13    1
user_79645270
Related