Pandas identify first row with column value in a group

user_7054084 0
Viewed 486

I have a dataframe that has three columns :

    ID       Date    Status
0    1   1/1/2000  Complete
1    1   1/4/2000  ReOpened
2    1  1/10/2000  ReOpened
3    1  1/11/2000    Closed
4    1  1/15/2000  ReOpened
5    2   1/2/2000  ReOpened
6    2   1/4/2000  ReOpened
7    2  1/10/2000    Closed
8    3  1/20/2000    Closed
9    3  1/22/2000    Closed
10   4  1/25/2000  ReOpened

For each ID, if there is a 'ReOpened' status, I need to get the row that shows the first time it has been 'ReOpened' based on the date. So my output would look like:

   ID ProductionDate    Status
0   1       1/4/2000  ReOpened
1   2       1/2/2000  ReOpened
2   4      1/25/2000  ReOpened

I have tried: df = pd.np.where(df.Status.str.contains("ReOpened"), df.groupby(['ID']).first(),0) but this doesn't work.

pandas python
2 Answers

Do this with groupby and cumsum on a mask:

df[df['Status'].eq('ReOpened').groupby(df['ID']).cumsum() == 1] 

    ID       Date    Status
1    1   1/4/2000  ReOpened
5    2   1/2/2000  ReOpened
10   4  1/25/2000  ReOpened

You can also use groupby and first after filtering to get the first row only:

df[df['Status'].eq('ReOpened')].groupby('ID', as_index=False).first()  

   ID       Date    Status
0   1   1/4/2000  ReOpened
1   2   1/2/2000  ReOpened
2   4  1/25/2000  ReOpened

If performance matters, you can reduce the above to a single boolean indexing operation with eq and duplicated:

df[df['Status'].eq('ReOpened') & ~df.duplicated(['ID', 'Status'])] 

    ID       Date    Status
1    1   1/4/2000  ReOpened
5    2   1/2/2000  ReOpened
10   4  1/25/2000  ReOpened
user_49090870

drop_duplicates should be enough.

df[df.Status.eq('ReOpened')].drop_duplicates(['ID'])                                                                       
#    ID       Date    Status
#1    1   1/4/2000  ReOpened
#5    2   1/2/2000  ReOpened
#10   4  1/25/2000  ReOpened
user_95107290
Related