如何在Pandas DataFrame中匹配或查找指定时间±120秒内的最近时间

Hey there! Let's work through this problem together—here's a straightforward, reliable way to implement the logic you need, and I'll explain why str.contains isn't the right tool for this job.

Step 1: Convert Time Data to Datetime Type #

First, we need to convert both your DataFrame's time column and the input user_time to pandas datetime objects. This lets us do actual time calculations (like finding differences) instead of fumbling with error-prone string matching.

import pandas as pd

# Convert the DataFrame's time column to datetime
df['time'] = pd.to_datetime(df['time'], format='%Y-%m-%d %H:%M:%S')
# Convert input user_time to datetime
user_time_dt = pd.to_datetime(user_time, format='%Y-%m-%d %H:%M:%S')

Step 2: Check for Exact Match #

Start with the simplest case: if the input time exists exactly in the time column, return it immediately.

if user_time_dt in df['time'].values:
    return user_time_dt.strftime('%Y-%m-%d %H:%M:%S')

Step 3: Find the Closest Valid Time #

If there's no exact match, we need to:

1. Calculate the absolute time difference (in seconds) between each row's time and the input time.
2. Filter out any times that are more than 120 seconds away (since we can't consider those).
3. Find the closest remaining time, then check if it's within ±1 second of the input.

Here's the code for this part:

# Calculate absolute time differences in seconds
df['time_diff'] = abs(df['time'] - user_time_dt).dt.total_seconds()

# Keep only times within ±120 seconds of the input
valid_candidates = df[df['time_diff'] <= 120]

# If no valid candidates exist, return "不存在"
if valid_candidates.empty:
    return "不存在"

# Find the row with the smallest time difference
closest_row = valid_candidates.loc[valid_candidates['time_diff'].idxmin()]
closest_time_dt = closest_row['time']
min_time_diff = closest_row['time_diff']

# Check if the closest time is within ±1 second
if min_time_diff <= 1:
    return closest_time_dt.strftime('%Y-%m-%d %H:%M:%S')
else:
    return "不存在"

Why Not Use df.loc[df['time'].str.contains(closest_time)]? #

String matching with str.contains is a bad fit here because:

• It works on raw string values, not actual time values. Even subtle formatting differences (like trailing spaces, though you said the format is fixed) can break the match.
• You can't calculate meaningful time differences with strings—datetime objects are designed specifically for this kind of temporal comparison.

Full Function Example #

Putting it all together into a reusable function:

import pandas as pd

def get_matching_time(df, user_time):
    # Ensure time column is in datetime format
    df['time'] = pd.to_datetime(df['time'], format='%Y-%m-%d %H:%M:%S')
    user_time_dt = pd.to_datetime(user_time, format='%Y-%m-%d %H:%M:%S')
    
    # Check for exact match first
    if user_time_dt in df['time'].values:
        return user_time_dt.strftime('%Y-%m-%d %H:%M:%S')
    
    # Calculate time differences in seconds
    df['time_diff'] = abs(df['time'] - user_time_dt).dt.total_seconds()
    
    # Filter candidates to those within ±120 seconds
    valid_candidates = df[df['time_diff'] <= 120]
    if valid_candidates.empty:
        return "不存在"
    
    # Find the closest valid time and check if it's within ±1 second
    closest_row = valid_candidates.loc[valid_candidates['time_diff'].idxmin()]
    if closest_row['time_diff'] <= 1:
        return closest_row['time'].strftime('%Y-%m-%d %H:%M:%S')
    else:
        return "不存在"

You can call this function with your example input like so:

example_user_time = '2018-04-10 13:00:03'
result = get_matching_time(your_dataframe, example_user_time)
print(result)

内容的提问来源于stack exchange，提问作者user9238790