1

Create the output dataframe from input, how to filter for rows when target == 1 for the first time for each id, or in order words removing consecutive occurrence for each ids where target is 1 however keep all 0s in target before target = 1.

Input

ID   date         target
a1   2019-11-01   0
a1   2019-12-01   0
a1   2020-01-01   1
a1   2020-02-01   1
a1   2020-03-01   0
a2   2019-11-01   0
a2   2019-12-01   1
a2   2020-03-01   0
a2   2020-04-01   1

Output

ID   date         target
a1   2019-11-01   0
a1   2019-12-01   0
a1   2020-01-01   1
a2   2019-11-01   0
a2   2019-12-01   1
Priyank
  • 144
  • 10
  • groupby ID, cumsum target, test the result<=1, which generates the mask Series to filter your original df – Bing Wang Mar 01 '21 at 01:27

2 Answers2

2

You could keep only the rows in the groupby where the cumsum of target is <= 1, then group again and make sure that a zero after a one is dropped using .ne

import pandas as pd
df = pd.DataFrame({'ID': ['a1', 'a1', 'a1', 'a1', 'a1', 'a2', 'a2', 'a2', 'a2'],
 'date': ['2019-11-01',
  '2019-12-01',
  '2020-01-01',
  '2020-02-01',
  '2020-03-01',
  '2019-11-01',
  '2019-12-01',
  '2020-03-01',
  '2020-04-01'],
 'target': [0, 0, 1, 1, 0, 0, 1, 0, 1]})


df = df.loc[df.groupby('ID')['target'].cumsum()<=1]
df = df.loc[df.groupby('ID')['target'].shift(1).ne(1)]

Output

    ID  date    target
0   a1  2019-11-01  0
1   a1  2019-12-01  0
2   a1  2020-01-01  1
5   a2  2019-11-01  0
6   a2  2019-12-01  1
Chris
  • 15,819
  • 3
  • 24
  • 37
  • Editing the question after a valid answer is posted is usually frowned upon, especially as you've shown no effort to attempt to solve the problem. A better approach would be to accept this as the correct answer, then post a new question with the revised data. – Chris Mar 01 '21 at 01:46
2


from io import stringIO

data = StringIO("""
uid,  date,         target
a1,   2019-11-01,   0
a1,   2019-12-01,   0
a1,   2020-01-01,   1
a1,  2020-02-01,   1
a1,   2020-03-01,   0
a2,   2019-11-01,   0
a2,   2019-12-01,   1
a2,   2020-03-01,   0
a2,   2020-04-01,   1
"""
)

df = pd.read_csv(data).rename(columns=lambda x: x.strip())

def filter_in_group(df: pd.DataFrame):
  ind = np.argmax(df.target)
  return df.loc[:, ['date', 'target']].iloc[:ind+1]

df_filtered = (
df
.groupby('uid')
.apply(lambda x: filter_in_group(x))
.reset_index()
.drop('level_1', axis=1)
)
Alex YIN
  • 46
  • 2
  • Reference to understand argmax [https://stackoverflow.com/questions/36300334/understanding-argmax/36300419] – Priyank Mar 01 '21 at 03:57