CODEWITHSUNDEEP
pythonpandasdatetimemergepandas-groupby
clog14
clog14

Reputation: 1641

Merging dataframes by closest earlier date by group

I have two dataframes generated by the following code:

import datetime 


def random_date(start, minutesList):
    current = start
    l = len(minutesList)
    out_ = []
    for min_ in minutesList:
        curr = current + datetime.timedelta(minutes=min_)
        out_.append(curr.strftime("%d/%m/%y %H:%M") )

    return(out_)



startDate = datetime.datetime(2013, 9, 20,13,00)

minutesListUsages = [2, 5, 6, 35, 38, 45, 57]                                            
minutesListLogins = [0, 1, 1.5, 3, 5.5, 24, 37, 37.5, 39.5, 45, 48, 53, 59, 60]

df_logins1 = pd.DataFrame([random_date(startDate,minutesListLogins),
                          [1] * len(random_date(startDate,minutesListLogins))]).transpose()
df_logins1.columns = ['date', 'id']
df_logins1

df_logins2 = pd.DataFrame([random_date(startDate,minutesListLogins),
                          [2] * len(random_date(startDate,minutesListLogins))]).transpose()
df_logins2.columns = ['date', 'id']
df_logins2

df_logins = df_logins1.append(df_logins2)

# Usages
df_usages1 = pd.DataFrame([random_date(startDate,minutesListUsages),
                          [1] * len(random_date(startDate,minutesListUsages))]).transpose()
df_usages1.columns = ['date', 'id']
df_usages1

df_usages2 = pd.DataFrame([random_date(startDate,minutesListUsages),
                          [2] * len(random_date(startDate,minutesListUsages))]).transpose()
df_usages2.columns = ['date', 'id']
df_usages2

df_usages = df_usages1.append(df_usages2)

I would like to indicate in df_logins which login was associated with a usage from df_usage. I would like to do this by id. I say a login is associated with a usage if it is the closest, but predating login relative to a given usage.

Based on this definition, how can I identify logins that led to a usage by id.

Thanks

Upvotes: 4

Views: 397

Answers (1)

BENY
BENY

Reputation: 323226

You can using merge_asof with by and on parameter 

df_usages.date=pd.to_datetime(df_usages.date)
df_logins.date=pd.to_datetime(df_logins.date)
df_usages,df_logins=df_usages.sort_values('date').rename(columns={'date':'use_date'}),df_logins.sort_values('date').rename(columns={'date':'log_date'})
pd.merge_asof(df_usages,df_logins,left_on='use_date',right_on='log_date',by='id',direction = 'nearest')
Out[168]: 
              use_date id            log_date
0  2013-09-20 13:02:00  1 2013-09-20 13:01:00
1  2013-09-20 13:02:00  2 2013-09-20 13:01:00
2  2013-09-20 13:05:00  1 2013-09-20 13:05:00
3  2013-09-20 13:05:00  2 2013-09-20 13:05:00
4  2013-09-20 13:06:00  1 2013-09-20 13:05:00
5  2013-09-20 13:06:00  2 2013-09-20 13:05:00
6  2013-09-20 13:35:00  1 2013-09-20 13:37:00
7  2013-09-20 13:35:00  2 2013-09-20 13:37:00
8  2013-09-20 13:38:00  1 2013-09-20 13:37:00
9  2013-09-20 13:38:00  2 2013-09-20 13:37:00
10 2013-09-20 13:45:00  1 2013-09-20 13:45:00
11 2013-09-20 13:45:00  2 2013-09-20 13:45:00
12 2013-09-20 13:57:00  1 2013-09-20 13:59:00
13 2013-09-20 13:57:00  2 2013-09-20 13:59:00

Upvotes: 1

Related Questions