Find nearest rows between data frames with positive timedelta

Question

I have two dataframes, each of which has a column datetime:

df_long=
     mytime_long
0    00:00:01 1/10/2013
1    00:00:05 1/10/2013
2    00:00:55 1/10/2013

df_short=
     mytime_short
0    00:00:02 1/10/2013
1    00:00:03 1/10/2013
2    00:00:06 1/10/2013

Timestamps are unique and can be sorted in each of two data blocks.

I would like to create a new framework containing the nearest (index, mytime_long) after or at the same time value in mytime_short(hence with non-negative timedelta)

ex.
0  (0, 00:00:02 1/10/2013)
1  (2, 00:00:06 1/10/2013)
2  (np.nan,np.nat)

Answer 1

write a function to get the closest index and timestamp in df_short given by the timestamp

def get_closest(n):
    mask = df_short.mytime_short >= n
    ids = np.where(mask)[0]
    if ids.size > 0:
        return ids[0], df_short.mytime_short[ids[0]]
    else:
        return np.nan, np.nan

apply this function through df_long.mytime_longto get a new data frame with indices and timestamp values ​​in a tuple

df = df_long.mytime_long.apply(get_closest)
df
# output:
0    (0, 2013-01-10 00:00:02)
1    (2, 2013-01-10 00:00:06)
2                  (nan, nan)

---

ilia timofeev answer pandas.merge_asof,

df = pd.merge_asof(df_long, 
              df_short.reset_index(), 
              left_on='mytime_long', 
              right_on='mytime_short', 
              direction='forward')[['index', 'mytime_short']]
df
# output:
   index        mytime_short
0    0.0 2013-01-10 00:00:02
1    2.0 2013-01-10 00:00:06
2    NaN                 NaT

Answer 2

, . , timestamp "" "long", .

#recreate data 
df_long = pd.DataFrame(
    pd.to_datetime( ['00:00:01 1/10/2013','00:00:05 1/10/2013','00:00:55 1/10/2013']),
    index = [0,1,2],columns = ['mytime_long'])

df_short = pd.DataFrame(
    pd.to_datetime( ['00:00:02 1/10/2013','00:00:03 1/10/2013','00:00:06 1/10/2013']),
    index = [0,1,2],columns = ['mytime_short'])

#join by time, preserving ids
df_all = df_short.assign(inx_s=df_short.index).set_index('mytime_short').join( 
    df_long.assign(inx_l=df_long.index).set_index('mytime_long'),how='outer')
#mark all "short" rows with nearest "long" id
df_all['inx_l'] = df_all.inx_l.ffill().fillna(-1)

#select "short" rows 
df_short_candidate = df_all[~df_all.inx_s.isnull()].astype(int)
df_short_candidate['mytime_short'] = df_short_candidate.index

#select get minimal "short" time in "long" group,
#join back with long to recover empty intersection
df_res = df_long.join(df_short_candidate.groupby('inx_l').first())

print (df_res)

:

          mytime_long  inx_s        mytime_short
0 2013-01-10 00:00:01    0.0 2013-01-10 00:00:02
1 2013-01-10 00:00:05    2.0 2013-01-10 00:00:06
2 2013-01-10 00:00:55    NaN                 NaT

---

100000 :

• 186 .
• 1min 3s df_long.mytime_long.apply(get_closest)

---

UPD:, @Haleemur Ali pd.merge_asof 10