How to mark first entry per group satisfying some criterion?-CodePudding

Let's say I have some dataframe where one column has some values occuring multiple times forming groups (column A in the snippet). Now I'd like to create a new column that with e.g. a 1 for the first x (column C) entries per group, and 0 in the other ones. I managed to do the first part, but I did not find a good way to include the condition on the xes, is there a good way of doing that?

import pandas as pd
df = pd.DataFrame(
    {
        "A": ["0", "0", "1", "2", "2", "2"],  # data to group by
        "B": ["a", "b", "c", "d", "e", "f"],  # some other irrelevant data to be preserved
        "C": ["y", "x", "y", "x", "y", "x"],  # only consider the 'x'
    }
)
target = pd.DataFrame(
    {
        "A": ["0", "0", "1", "2", "2", "2"],  
        "B": ["a", "b", "c", "d", "e", "f"], 
        "C": ["y", "x", "y", "x", "y", "x"],
        "D": [  0,   1,   0,   1,   0,   0]  # first entry per group of 'A' that has an 'C' == 'x'
    }
)
# following partial solution doesn't account for filtering by 'x' in 'C'
df['D'] = df.groupby('A')['C'].transform(lambda x: [1 if i == 0 else 0 for i in range(len(x))])

CodePudding user response：

In your case do slice then drop_duplicates and assign back

df['D'] = df.loc[df.C=='x'].drop_duplicates('A').assign(D=1)['D']
df['D'].fillna(0,inplace=True)
df
Out[149]: 
   A  B  C    D
0  0  a  y  0.0
1  0  b  x  1.0
2  1  c  y  0.0
3  2  d  x  1.0
4  2  e  y  0.0
5  2  f  x  0.0