[Python] Merge two dataframes on date, taking row based on most recent date

I have two linked dataframes which contain data on the status of something but both the data sources are poor. I have the status (e.g. 1=lost, 2=found, 3=unknown) and the date if found.

For instance, say dfA is so:

ID, status, dateFound, registerDate
1, 1, NaN , 5/3/24
2, 2, 1/1/24, 1/1/24
3, 2, 2/1/24, 3/1/24


and 'dfB' is so:

ID, thing_status, dateFound, date_registered
1, 2, 6/4/24, 23/4/24
2, 1, NaN, 24/12/23
3, 2, 4/1/24, 5/1/24


I wish to create a new dataframe taking the last row from either dfA or dfB based on the latest registerDate/date_registered.

i.e. the ideal output would be:

ID, status, dateFound, registerDate
1, 2, 6/4/24, 23/4/24
2, 2, 1/1/24, 1/1/24
3, 2, 4/1/24, 5/1/24


I am currently doing this using a for-loop but suspect there is a simpler way.

Continue reading...