如何在Pandas中对CSV格式的Tick数据应用条件筛选？

Hey there! Let's break down how to handle conditional data processing for your CSV tick data using Pandas. I'll walk you through common scenarios with examples tailored to your data structure.

First, let's make sure we load the data correctly—your timestamp format (YYYY.MM.DD HH:MM:SS.sss) needs a custom parser to be recognized as datetime:

import pandas as pd

# Custom parser to match your timestamp format
def parse_tick_timestamp(timestamp_str):
    return pd.to_datetime(timestamp_str, format="%Y.%m.%d %H:%M:%S.%f")

# Load the CSV and parse the time column
df = pd.read_csv("your_tick_data.csv", parse_dates=["Time (UTC)"], date_parser=parse_tick_timestamp)
# Optional: Set time as index for easier time-based operations
df = df.set_index("Time (UTC)")

1. Filter Rows Based on Conditions #

This is the most common task—extracting rows that meet specific criteria.

Example 1: Single Condition #

Filter rows where the Bid price is greater than 1.2009:

bid_filtered = df[df["Bid"] > 1.2009]

Example 2: Multiple Conditions #

Combine conditions (use & for "AND", | for "OR", and wrap each condition in parentheses):

# Get rows where Ask equals 1.20104 AND BidVolume is over 1.5
multi_filtered = df[(df["Ask"] == 1.20104) & (df["BidVolume"] > 1.5)]

Example 3: Time-Based Filtering #

Since we set the time as index, we can easily slice by time range:

# Get data between 00:00:00 and 00:00:10 on 2015-01-14
time_slice = df.loc["2015-01-14 00:00:00":"2015-01-14 00:00:10"]

2. Modify Values Based on Conditions #

You can update existing columns or create new ones using conditional logic.

Example 1: Create a Volume Status Column #

Tag rows based on AskVolume levels:

# Using np.where for simple binary conditions
df["Volume_Status"] = pd.np.where(df["AskVolume"] < 1.3, "Low Volume", "Normal Volume")

# For multi-level conditions, use apply() for more flexibility
def classify_volume(volume):
    if volume < 1.3:
        return "Low Volume"
    elif volume > 2.0:
        return "High Volume"
    else:
        return "Normal Volume"

df["Volume_Status"] = df["AskVolume"].apply(classify_volume)

Example 2: Track Price Changes #

Calculate and filter for Bid price movements:

# Compute the difference between current and previous Bid price
df["Bid_Change"] = df["Bid"].diff()

# Keep only rows where Bid price changed (exclude unchanged ticks)
price_changes = df[df["Bid_Change"] != 0]

3. Conditional Grouping & Aggregation #

Aggregate data based on groups defined by conditions.

Example 1: Group by Bid Price #

Calculate average AskVolume and total BidVolume for each unique Bid price:

bid_groups = df.groupby("Bid").agg({
    "AskVolume": "mean",
    "BidVolume": "sum"
})

Example 2: Time-Based Aggregation (Resampling) #

Aggregate tick data into larger time intervals (e.g., 1-minute bars) with conditional metrics:

# Resample to 1-minute intervals, keeping last price and summing volumes
minute_bars = df.resample("1min").agg({
    "Ask": "last",
    "Bid": "last",
    "AskVolume": "sum",
    "BidVolume": "sum"
})

4. Handle Missing Data #

Your sample data has a truncated row (2....), so let's clean that up:

# Drop any rows with missing values
clean_df = df.dropna()

# Forward-fill missing values with the previous row's data (good for tick data)
filled_df = df.fillna(method="ffill")

These are the core conditional processing tasks for tick data. If you have a specific condition or analysis goal in mind, feel free to share more details and we can refine this further!

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