How To Obtain The Unique Record Of A Cross Joined Table Based On The Dates Of Two Different Columns?

I have quite a complex logic to create. I have some client clinic encounter data which has historical testing results, R_DATE_TESTED, R_RESULT mapped to each client (P_CLIENT_ID) f

Solution 1:

Yuo can use this code:

import numpy as np

# df - your DataFrame

group = df.groupby(['P_CLIENT_ID', 'P_DATE_ENCOUNTER'])

def foo(df):
    result = df.loc[df.P_DATE_ENCOUNTER>df.R_DATE_TESTED, ['R_DATE_TESTED', 'R_RESULT']].tail(1).reset_index()
    if not result.empty:
        return result
    else:
        return pd.DataFrame([[np.nan, np.nan, np.nan]], columns=['RECORD_ID','R_DATE_TESTED', 'R_RESULT'])


group.apply(foo)