Pandas DataFrame筛选问题：获取与指定Datetime最接近或相等的所有记录

Let's work through this step by step. The TypeError you're hitting comes from a simple oversight in how you're setting the DataFrame index, plus a small gap in handling multiple rows with the same datetime. Here's the breakdown and solution:

What's Causing the Error? #

When you run holdings_df.set_index("datetime"), this returns a new DataFrame with datetime as the index—but you didn't assign this new DataFrame back to holdings_df. Your original DataFrame still uses the default integer index, so when you try holdings_df.index.get_loc(...), you're trying to match a Timestamp value against integer indices. That's why you get the error about comparing Timestamp and int.

Solution 1: Straightforward Filtering (Easy to Read) #

This method is perfect for your use case, especially since you have multiple rows sharing the same datetime (like all the 2021-07-30 entries):

import pandas as pd

def get_closest_holdings(holdings_df, target_datetime):
    # Ensure datetime column is in Timestamp format (safety check)
    holdings_df['datetime'] = pd.to_datetime(holdings_df['datetime'])
    
    # Filter all rows where datetime is <= target_datetime
    valid_rows = holdings_df[holdings_df['datetime'] <= target_datetime]
    
    # Handle edge case: target is earlier than all records
    if valid_rows.empty:
        return pd.DataFrame(columns=['instrument', 'quantity'])
    
    # Find the most recent (closest) datetime in valid rows
    closest_dt = valid_rows['datetime'].max()
    
    # Return only instrument and quantity for that datetime
    return valid_rows[valid_rows['datetime'] == closest_dt][['instrument', 'quantity']]

Solution 2: Index-Based Lookup (Faster for Large Data) #

If you're working with a big dataset, using the index directly is more efficient. Just make sure to properly set and sort the index first:

import pandas as pd

def get_closest_holdings(holdings_df, target_datetime):
    # Create a copy to avoid modifying the original DataFrame
    df = holdings_df.copy()
    df['datetime'] = pd.to_datetime(df['datetime'])
    
    # Set datetime as index and sort it (critical for index lookup)
    df = df.set_index('datetime').sort_index()
    
    # Find the position of the last datetime <= target_datetime
    # method='pad' fetches the closest smaller/equal value
    idx = df.index.get_indexer([pd.Timestamp(target_datetime)], method='pad')[0]
    
    # Handle edge case: target is earlier than all records
    if idx == -1:
        return pd.DataFrame(columns=['instrument', 'quantity'])
    
    # Get all rows for the closest datetime (handles multiple entries)
    closest_dt = df.index[idx]
    return df.loc[closest_dt][['instrument', 'quantity']]

Testing the Solutions #

Let's verify with your examples:

• When target_datetime = '2021-07-20': Both functions return the row with $USD and 2000.000000.
• When target_datetime = '2021-07-30': Both functions return all 9 rows from that date (including $USD -1841.278610 and all instrument codes).

Key Fixes from Your Original Code #

1. Assign the indexed DataFrame: Always save the result of set_index (either with inplace=True or reassign to a variable).
2. Handle multiple rows per datetime: Your original code tried to fetch a single row with iloc, but you need to return all rows matching the closest datetime.
3. Explicit Timestamp conversion: Ensure your target datetime is converted to a Timestamp when using index-based methods.

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