df1 = pandas.DataFrame( {
"ID" : ["11", "11", "11", "11" , "55"] ,
"updated_date" : ["2019/04/03", "2019/05/02", "2019/05/20", "2019/07/01", "2019/03/03"] } )
df2 = pandas.DataFrame( {
"ID" : ["11", "11", "11", "11" , "55"] ,
"other_date" : ["2019/04/09", "2019/05/14", "2019/06/05", "2019/06/05", "2019/03/03"] } )
There are two dataframes above. I would like to combine them so that the new dataframe has the ID, the updated_date and the minimum other_date that is larger than the updated date for that ID number.
For ID# = 11 and updated_date = 2019-04-03 there are 4 choices for other date (2019-04-09, 2019-05-14, 2019-06-05, 2019-06-05). The minimum date that is larger than the updated_date is 2019-04-09.
For ID# = 11 and updated_date = 2019-05-02 there are now 3 choices for other date (2019-05-14, 2019-06-05, 2019-06-05). The minimum date that is larger than the updated_date is 2019-05-14 and so on...
other_dates cannot be reused.
'NONE' if conditions are not satisfied.
Thank you!
Desired output is this
ID | updated_date | other_date
0 11 | 2019-04-03 | 2019-04-09
1 11 | 2019-05-02 | 2019-05-14
2 11 | 2019-05-20 | 2019-06-05
3 11 | 2019-07-01 | NONE (because conditions are not met)
4 55 | 2019-03-03 | 2019-03-03