Group by unique Name and Status with the last Date
One way to do it would be to GroupBy
the Name
, and aggregate on IsItNew
with two functions. A custom one using any
to check if there are any False
values, and idxmin
, to find the index of the first False
, which you can later on use to index the dataframe on ControlDate
:
df_ = df.groupby('Name').agg({'IsItNew':
{'IsItNew':lambda x: ~(~x).any(),
'ControlDate':'idxmin'}})
.droplevel(0, axis=1)
.reset_index()
df_['ControlDate'] = df.loc[df_['ControlDate'].values, 'ControlDate'].reset_index(drop=True)
xName IsItNew ControlDate
0 Car1 False 15/03/2018
1 Car2 False 25/05/2018
2 Car3 True 30/04/2018