遍历SQLite3时间戳列表匹配Pandas列并获取行索引报错排查

It looks like you're running into two common pitfalls when matching timestamps between SQLite and Pandas—either your data types aren't aligned, or there's a precision mismatch breaking the matches. Let's walk through how to fix this step by step.

First, Diagnose the Root Causes #

Let's unpack why you're seeing those errors:

• Empty Series (Series([], Name: date_index, dtype: int32): This means the timestamp from sql_min doesn't exist in df['Timestamp'] at all.
• ValueError: Lengths must match to compare: This almost always happens when comparing incompatible data types (e.g., a string from SQLite vs. a datetime64 object in Pandas).

Step-by-Step Solutions #

1. Align Your Timestamp Data Types #

SQLite doesn't have a native datetime type, so it often returns timestamps as strings or raw Python datetime objects. Pandas uses its own datetime64 format by default—let's make sure both sides speak the same language:

import pandas as pd

# Convert your SQLite timestamp list to Pandas datetime objects
sql_min = pd.to_datetime(sql_min)

# Ensure your DataFrame's Timestamp column is also in datetime format
df['Timestamp'] = pd.to_datetime(df['Timestamp'])

2. Fix Precision Mismatches #

A common hidden issue: SQLite might truncate microseconds, while Pandas retains them. Even if the date/time is identical down to the second, those tiny microsecond differences will break the match. Fix this by rounding your Pandas timestamps to match SQLite's precision:

# Floor timestamps to seconds (adjust to 'ms' for milliseconds if needed)
df['Timestamp'] = df['Timestamp'].dt.floor('s')

3. Get Integer Row Numbers Efficiently #

Looping through each timestamp isn't the most Pandas-idiomatic approach. Instead, use vectorized operations to grab all matching indices at once:

# Get all matching date_index values as a list of integers
matching_indices = df.loc[df['Timestamp'].isin(sql_min), 'date_index'].tolist()
print(matching_indices)

If you still need to loop (for per-item custom logic), add a check to avoid empty Series errors:

for item in sql_min:
    matches = df[df['Timestamp'] == item]
    if not matches.empty:
        # Grab the first matching date_index as an integer
        index = matches['date_index'].iloc[0]
        print(index)
    else:
        print(f"No match found for timestamp: {item}")

Why Your Original Code Failed #

• When no match exists, df.loc[df['Timestamp'] == item, 'date_index'] returns an empty Series instead of a single integer.
• If data types didn't align, comparing them threw the ValueError because Pandas couldn't reconcile the incompatible types.

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