如何基于另一DataFrame时间戳列给目标DataFrame添加产品列并解决类型错误

Hey Jozsef, let's work through this problem step by step—since you mentioned you're new to pandas, we'll focus on a clean, efficient solution that fixes your data type issue and avoids those slow nested loops.

Your error happens because openpyxl sometimes loads Excel dates as floats (Excel stores dates as days since 1899-12-30 under the hood), leading to a type mismatch when comparing datetime values to float values. Plus, nested loops are really inefficient for large datasets—pandas was built specifically for this kind of time-based matching task.

First, let's ditch manual openpyxl cell access and use pandas to load both files, ensuring all time columns are parsed as proper datetime types:

import pandas as pd

# Load sensor data, tell pandas to parse the 'Time' column as datetime
sensor_df = pd.read_excel('Szárítás összes januar_P.xlsx', parse_dates=['Time'])

# Load product interval data, parse 'From' and 'To' as datetime
product_df = pd.read_excel('Gyártások teszt_P.xlsx', parse_dates=['From', 'To'])

This eliminates the float/datatype mismatch entirely, since pandas handles date parsing automatically.

For your use case (non-overlapping, ordered time intervals), pandas' merge_asof is perfect—it's way faster than looping through every row. Here's how to use it:

# First, make sure the product interval data is sorted by the 'From' column
product_df = product_df.sort_values('From')

# Use merge_asof to match each sensor timestamp to the correct product interval
result_df = pd.merge_asof(
    sensor_df.sort_values('Time'),  # Sensor data must also be sorted by time
    product_df,
    left_on='Time',
    right_on='From',
    direction='backward'  # Find the latest 'From' time that's <= the sensor time
)

# Filter out any rows where the sensor time is beyond the interval's 'To' time
result_df = result_df[result_df['Time'] < result_df['To']]

# Clean up the columns to match your desired output
result_df = result_df[['Time', 'S1', 'S2', 'S3', 'product']]

If you ever have overlapping intervals (your example doesn't), you can use a slightly slower but more flexible apply method instead:

def find_product(time):
    # Find the row where the sensor time falls between 'From' and 'To'
    match = product_df[(product_df['From'] <= time) & (product_df['To'] > time)]
    # Return the product ID if a match exists, else None
    return match['product'].iloc[0] if not match.empty else None

# Apply the function to every timestamp in the sensor data
sensor_df['product'] = sensor_df['Time'].apply(find_product)
result_df = sensor_df[['Time', 'S1', 'S2', 'S3', 'product']]

Finally, export the matched data back to Excel:

result_df.to_excel('匹配后的传感器数据.xlsx', index=False)

The index=False argument prevents pandas from adding an extra row number column to your output.

If you really need to stick with openpyxl for some reason, you can convert those float dates back to datetime values before comparing:

from datetime import datetime, timedelta

def excel_float_to_datetime(float_date):
    # Convert Excel's float date (days since 1899-12-30) to a datetime object
    return datetime(1899, 12, 30) + timedelta(days=float_date)

# In your loop, add this conversion before comparing:
gy_from_val = sheet_gy.cell(row=s_gy, column=6).value
if isinstance(gy_from_val, float):
    gy_from_val = excel_float_to_datetime(gy_from_val)

gy_to_val = sheet_gy.cell(row=s_gy, column=7).value
if isinstance(gy_to_val, float):
    gy_to_val = excel_float_to_datetime(gy_to_val)

# Now your comparison will work:
if gy_from_val < sheet_1.cell(row=s, column=2).value < gy_to_val:
    sheet_1.cell(row=s, column=16).value = sheet_gy.cell(row=s_gy, column=9).value

But again, pandas is way better for this kind of task—especially if your datasets are large.

When you open the saved Excel file, you'll see the product column populated exactly like your desired example:

Time S1 S2 S3 product
2019-01-04 05:00:20 -0,068576396 -0,081597209 0,328993082 T2887_001
2019-01-04 05:00:50 -0,071180522 -0,079861104 0,353298664 T2887_001
...

内容的提问来源于stack exchange，提问作者József Peitli