As @MhDG7 said, you can only merge on values that match exactly. So your first task is for each timestamp in gps_data find the closest match among timestamps in tag_data. The key line in the below is this one

tag_secs_for_gps_secs = [min(tag_data['Secs'], key = lambda tag_secs: abs(tag_secs - gps_secs))  for gps_secs in gps_data['Secs'] ]

that does exactly that. Here, for each gps time expressed in seconds (see below) we find an element among tag_data timestamps (also expressed in seconds) that minimizes the absolute distance between the two

Here is the full script First we create toy data

import sys
import pandas as pd
from io import StringIO
from datetime import datetime

gps_data_raw = StringIO(
    """
Latitude,Longitude,Time
N43°03.6162',W085°57.5477',10:33:03
N43°03.6163',W085°57.5472',10:33:06
N43°03.6168',W085°57.5477',10:33:09
N43°03.6167',W085°57.5477',10:33:11
N43°03.6163',W085°57.5486',10:33:14
    """)
gps_data = pd.read_csv(gps_data_raw)

tag_data_raw = StringIO(
    """
Time,TagID
10:33:01,C10002AABB00112233445566
10:33:05,013193AABB00112233445566
10:33:12,017072AABB00112233445566
10:33:22,023764AABB00112233445566
10:33:42,A15800AABB00112233445566
10:33:49,E280116060000207A6336CC6
10:33:51,E280116060000207A6344236
    """)
tag_data = pd.read_csv(tag_data_raw)

Then we convert timestamp to datetime object and eventually to (integer) seconds from the base_date

base_date = datetime(1900, 1, 1, 0, 0, 0)
gps_data['Time'] = pd.to_datetime(gps_data['Time'], format='%H:%M:%S')
tag_data['Time'] = pd.to_datetime(tag_data['Time'], format='%H:%M:%S')
gps_data['Secs'] = gps_data['Time'].apply(lambda t: int((t-base_date).total_seconds()))
tag_data['Secs'] = tag_data['Time'].apply(lambda t: int((t-base_date).total_seconds()))

Then we match timestamps, stick them into the dataframe, and merge on that column

tag_secs_for_gps_secs = [min(tag_data['Secs'], key = lambda tag_secs: abs(tag_secs - gps_secs))  for gps_secs in gps_data['Secs'] ]

gps_data['Nearest_tag_secs'] = tag_secs_for_gps_secs
merged_data = gps_data.merge(tag_data, left_on = 'Nearest_tag_secs', right_on = 'Secs')

The resultimg dataframe merged_data can use a bit of cleanup but reproduced here in full so you can see what happened

|    | Latitude     | Longitude     | Time_x              |   Secs_x |   Nearest_tag_secs | Time_y              | TagID                    |   Secs_y |
|---:|:-------------|:--------------|:--------------------|---------:|-------------------:|:--------------------|:-------------------------|---------:|
|  0 | N43°03.6162' | W085°57.5477' | 1900-01-01 10:33:03 |    37983 |              37981 | 1900-01-01 10:33:01 | C10002AABB00112233445566 |    37981 |
|  1 | N43°03.6163' | W085°57.5472' | 1900-01-01 10:33:06 |    37986 |              37985 | 1900-01-01 10:33:05 | 013193AABB00112233445566 |    37985 |
|  2 | N43°03.6168' | W085°57.5477' | 1900-01-01 10:33:09 |    37989 |              37992 | 1900-01-01 10:33:12 | 017072AABB00112233445566 |    37992 |
|  3 | N43°03.6167' | W085°57.5477' | 1900-01-01 10:33:11 |    37991 |              37992 | 1900-01-01 10:33:12 | 017072AABB00112233445566 |    37992 |
|  4 | N43°03.6163' | W085°57.5486' | 1900-01-01 10:33:14 |    37994 |              37992 | 1900-01-01 10:33:12 | 017072AABB00112233445566 |    37992 |

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top