CODEWITHSUNDEEP
pythonpandasnumpy
Debadri Dutta
Debadri Dutta

Reputation: 1193

Merging two Dataframes in Pandas based on time-range difference

I have these two dataframes, df1,df2.

df1:

dateTime                 userId  session

2018-08-30 02:20:19      2233      1
2018-08-30 05:32:10      1933      1
2018-08-30 09:10:39      2233      2
2018-08-30 10:26:59      2233      3
2018-08-30 11:56:25      4459      1
2018-08-30 12:30:55      4459      1

df2:

clickTime                 userId  session  clickId

2018-08-30 02:21:09      2233               1987
2018-08-30 02:23:19      2233               1988
2018-08-30 02:24:00      2233               1989
2018-08-30 02:32:09      2233               1990
2018-08-30 05:33:10      1933               2009
2018-08-30 05:35:19      1933               2010
2018-08-30 05:36:59      1933               2011
2018-08-30 11:57:25      4459               3012
2018-08-30 11:58:55      4459               3013

I want to merge the two dataframes on userId and also the time-range columns, which falls within say a range of 10 mins. of df1.

So my dataframe that I want is something like this:

  dateTime               userId  session   clickTime             clickId

2018-08-30 02:20:19      2233      1       2018-08-30 02:21:09    1987
2018-08-30 02:20:19      2233      1       2018-08-30 02:23:19    1988
2018-08-30 02:20:19      2233      1       2018-08-30 02:21:09    1989
2018-08-30 02:20:19      2233      1       2018-08-30 02:21:09    1990

So I want them for each user, dataframe should look like this, for each userId I want this dataframe. Is it possible?

So its like I want to merge df1 and df2 on userId as well as clickTime of df2 should lie with a time range of say 10-15mins of dateTime column of df1.

Upvotes: 0

Views: 113

Answers (1)

piRSquared
piRSquared

Reputation: 294488

IIUC: Use pandas.merge_asof

pd.merge_asof(
    df1, df2,
    left_on='dateTime',
    right_on='clickTime',
    by='userId',
    direction='nearest'
)

             dateTime  userId  session           clickTime  clickId
0 2018-08-30 02:20:19    2233        1 2018-08-30 02:21:09     1987
1 2018-08-30 05:32:10    1933        1 2018-08-30 05:33:10     2009
2 2018-08-30 09:10:39    2233        2 2018-08-30 02:32:09     1990
3 2018-08-30 10:26:59    2233        3 2018-08-30 02:32:09     1990
4 2018-08-30 11:56:25    4459        1 2018-08-30 11:57:25     3012
5 2018-08-30 12:30:55    4459        1 2018-08-30 11:58:55     3013

You can specify a tolerance on how far away to look

pd.merge_asof(
    df1, df2,
    left_on='dateTime',
    right_on='clickTime',
    by='userId',
    direction='nearest',
    tolerance=pd.Timedelta(15, unit='m')
)

             dateTime  userId  session           clickTime  clickId
0 2018-08-30 02:20:19    2233        1 2018-08-30 02:21:09   1987.0
1 2018-08-30 05:32:10    1933        1 2018-08-30 05:33:10   2009.0
2 2018-08-30 09:10:39    2233        2                 NaT      NaN
3 2018-08-30 10:26:59    2233        3                 NaT      NaN
4 2018-08-30 11:56:25    4459        1 2018-08-30 11:57:25   3012.0
5 2018-08-30 12:30:55    4459        1                 NaT      NaN

Upvotes: 1

Related Questions