如何用Pandas从月度tick数据CSV中筛选Ask价格日跌幅超5%的日期

Hey there! I get it—you've got monthly tick data and need to zoom into daily-level stats instead of the whole month. Let's walk through how to adjust your Pandas code to make this work perfectly.

Step 1: Load and Parse the Time Column First #

First, we need to make sure the Time(UTC) column is recognized as a datetime object in Pandas. This is key for grouping by date later.

import pandas as pd

# Load your CSV and auto-parse the UTC time column
df = pd.read_csv('your_tick_data.csv', parse_dates=['Time(UTC)'])

Step 2: Extract Date from UTC Time #

Since we want to group by calendar date (ignoring hours/minutes/seconds), add a new Date column that just keeps the date part of the UTC timestamp:

# Extract the date component (e.g., 2024-05-01 from 2024-05-01 14:30:00 UTC)
df['Date'] = df['Time(UTC)'].dt.date

Step 3: Group by Date and Calculate Daily Stats #

Now we can group the data by the new Date column, then compute the max and min Ask prices for each day. After that, we'll calculate your desired fluctuation ratio: (max_ask - min_ask)/max_ask.

# Group by date, get max and min Ask for each day
daily_ask_stats = df.groupby('Date')['Ask'].agg(['max', 'min'])

# Calculate the fluctuation ratio
daily_ask_stats['fluctuation_ratio'] = (daily_ask_stats['max'] - daily_ask_stats['min']) / daily_ask_stats['max']

Step 4: Filter Dates with Ratio > 0.05 #

Finally, we'll filter out any dates where the fluctuation ratio exceeds your threshold of 0.05. We'll also add a safety check to avoid division by zero or days with no price movement (where max = min):

# Filter out invalid cases (max is 0 or no price movement) first
valid_daily_stats = daily_ask_stats[(daily_ask_stats['max'] != 0) & (daily_ask_stats['max'] != daily_ask_stats['min'])]

# Get dates where fluctuation ratio is greater than 0.05
target_dates = valid_daily_stats[valid_daily_stats['fluctuation_ratio'] > 0.05].index.tolist()

# Print the result
print("Dates with Ask price fluctuation > 5%:")
for date in target_dates:
    print(date)

Bonus: One-Liner (Optional) #

If you prefer more concise code, you can combine some steps, but the above breakdown is easier to debug and understand:

import pandas as pd

df = pd.read_csv('your_tick_data.csv', parse_dates=['Time(UTC)'])
target_dates = (
    df.assign(Date=df['Time(UTC)'].dt.date)
      .groupby('Date')['Ask']
      .agg(lambda x: (x.max() - x.min())/x.max() if x.max() !=0 and x.max() !=x.min() else 0)
      .loc[lambda x: x > 0.05]
      .index.tolist()
)

That should do it! You'll now get exactly the list of dates where the daily Ask price had a fluctuation over 5% as defined.

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