How to get the last/maximum date that is on/earlier than another baseline date by user?

user_19321677 0
Viewed 66

I have a df where I am trying to create the Last Login Date column, as shown in the image.

I am not sure how to get the maximum login date that was on/prior the email notification date for that current row. I added explanations on how I expect the data to look. Any help is appreciated in either sql or pandas.

enter image description here

amazon-redshift pandas sql
3 Answers

Try this:


def foo():
    '''
    Making df
    df = pd.DataFrame({
        'email_notification_date' : ['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-04', '2020-01-05', '2020-01-06', '2020-01-07', '2020-01-18'],
        'login_date' : ['2020-01-04', np.nan, '2020-01-06', np.nan, np.nan, '2020-01-06', '2020-01-10', np.nan]
    })
    '''

    # Converting into Datetime
    df['email_notification_date'] = pd.to_datetime(df['email_notification_date'])
    df['login_date'] = pd.to_datetime(df['login_date'])

    last_login_date = []
    for i in range(len((df))):

        # Find all login_dates before each email_notification_date.
        login_date_list = np.where(df['login_date'] <= df.loc[i, 'email_notification_date'])
        print(login_date_list)
        
        # Extract the maximum(latest) day from the dates_list
        last_login_date_tmp = np.nan if login_date_list[0].size == 0 else df['login_date'][login_date_list[0][-1]]
        print(last_login_date_tmp)

        last_login_date.append(last_login_date_tmp)    
        
    df['last_login_date'] = last_login_date

    print(df)

Output :

(array([], dtype=int64),)
nan
(array([], dtype=int64),)
nan
(array([], dtype=int64),)
nan
(array([0], dtype=int64),)
2020-01-04 00:00:00
(array([0], dtype=int64),)
2020-01-04 00:00:00
(array([0, 2, 5], dtype=int64),)
2020-01-06 00:00:00
(array([0, 2, 5], dtype=int64),)
2020-01-06 00:00:00
(array([0, 2, 5, 6], dtype=int64),)
2020-01-10 00:00:00


  email_notification_date login_date last_login_date
0              2020-01-01 2020-01-04             NaT
1              2020-01-02        NaT             NaT
2              2020-01-03 2020-01-06             NaT
3              2020-01-04        NaT      2020-01-04
4              2020-01-05        NaT      2020-01-04
5              2020-01-06 2020-01-06      2020-01-06
6              2020-01-07 2020-01-10      2020-01-06
7              2020-01-18        NaT      2020-01-10
user_182731290

Use:

#Making sample data
s = pd.date_range('2020-01-01', '2020-01-08')
t = pd.to_datetime(('2020-01-04', np.nan, '2020-01-06', np.nan, np.nan, '2020-01-06', '2020-01-10', np.nan), errors='ignore')
df = pd.DataFrame({'email_notification_date': s, 'login_date': t})
#Doing main job
df['last_login_date'] = df['email_notification_date'].apply(lambda x: df['login_date'][x>=df['login_date']]).max(axis=1)

Output:

enter image description here

user_72816750

Use pandas.merge_asof:

out pd.merge_asof(df.assign(date=pd.to_datetime(df['email_notification_date']).sort_values()),
              pd.to_datetime(df['login_date']).dropna().sort_values().rename('last_login_date'),
              left_on='date', right_on='last_login_date'
             )

output:

  email_notification_date  login_date       date last_login_date
0              2020-01-01  2020-01-04 2020-01-01             NaT
1              2020-01-02         NaN 2020-01-02             NaT
2              2020-01-03  2020-01-06 2020-01-03             NaT
3              2020-01-04         NaN 2020-01-04      2020-01-04
4              2020-01-05         NaN 2020-01-05      2020-01-04
5              2020-01-06  2020-01-06 2020-01-06      2020-01-06
6              2020-01-07  2020-01-10 2020-01-07      2020-01-06
7              2020-01-18         NaN 2020-01-18      2020-01-10
user_163434640
Related