[FIXED] Fill a column based on levels of first column and condition on second column

Issue

We have a data frame, df, with two columns as given below. Variable A has two levels, 1 and 2. Variable B has three levels YES, NO, and OTHER. We want to derive another data frame, df2, with variable C, which takes a value of "1" if there exists at least one YES for any level in variable A , otherwise "0".

df

A   B
1   YES
1   YES
1   OTHER
1   NO
1   YES
1   NO
2   YES
2   YES
2   YES
2   NO
2   YES
3   OTHER
3   NO
3   NO
3   NO

df2

A   C
1   1
2   1
3   0

Solution

Use groupby:

>>> df['B'].eq('YES').groupby(df['A']).any().astype(int).reset_index(name='C')
   A  C
0  1  1
1  2  1
2  3  0

Answered By - richardec