基于指定CSV数据，计算DataFrame连续日期统计值百分比差异并按条件输出

Got it, let's work through this problem step by step. You want to calculate percentage differences between consecutive date-based stats in your CSV data and output results based on specific conditions. Here's a practical, pandas-based solution tailored to your data structure:

Step 1: Load and Preprocess the Data #

First, we'll load the CSV into a pandas DataFrame, parse the timezone-aware date column, and clean up missing values (since your sample has rows with empty metrics):

import pandas as pd

# Replace 'your_data.csv' with your actual file path
df = pd.read_csv('your_data.csv')

# Parse the date column with UTC timezone
df['date'] = pd.to_datetime(df['date'], utc=True)

# Extract just the date component (to group metrics by day)
df['date_only'] = df['date'].dt.date

# Drop rows where all key metrics are missing (adjust subset to match your priority columns)
df_clean = df.dropna(subset=['cpu', 'mem', 'load', 'drops'], how='all')

Step 2: Aggregate Daily Statistics #

Your raw data has multiple entries per day, so we need to first calculate daily summary stats (like mean, sum, or max) for each metric. Adjust the aggregation function to match what you need:

# Aggregate metrics by date - swap 'mean'/'sum' for other stats as needed
daily_stats = df_clean.groupby('date_only').agg({
    'cpu': 'mean',
    'mem': 'mean',
    'load': 'mean',
    'drops': 'sum',
    'latency': 'mean',
    'upload': 'mean',
    'download': 'mean'
}).reset_index()

# Make sure the dates are in chronological order
daily_stats = daily_stats.sort_values('date_only')

Step 3: Calculate Consecutive Percentage Differences #

Use pandas' built-in pct_change() function to compute the percentage difference between each day's stats and the previous day's. Multiply by 100 to get percentage values:

# Calculate percentage change for each metric
daily_stats['cpu_pct_change'] = daily_stats['cpu'].pct_change() * 100
daily_stats['mem_pct_change'] = daily_stats['mem'].pct_change() * 100
daily_stats['load_pct_change'] = daily_stats['load'].pct_change() * 100
daily_stats['drops_pct_change'] = daily_stats['drops'].pct_change() * 100
daily_stats['latency_pct_change'] = daily_stats['latency'].pct_change() * 100

Step 4: Filter Results Based on Conditions #

Now you can filter the DataFrame to show only rows that meet your criteria. For example, let's say you want to see days where load changed by more than 20% OR drops changed by more than 50%:

# Define your custom condition (adjust thresholds and metrics as needed)
filter_condition = (daily_stats['load_pct_change'].abs() > 20) | (daily_stats['drops_pct_change'].abs() > 50)

# Get the filtered results
filtered_results = daily_stats[filter_condition]

# Print or save the output
print("Filtered percentage change results:")
print(filtered_results)
# filtered_results.to_csv('stats_pct_change_results.csv', index=False)

Optional: Handle Missing Dates #

If your dataset has gaps (days with no data) and you want to include those in your consecutive calculations, use resample to fill in missing dates:

# Convert date_only to datetime index
daily_stats = daily_stats.set_index('date_only')

# Resample to daily frequency (fills missing dates with NaN)
daily_stats_full = daily_stats.resample('D').asfreq().reset_index()

# Calculate percentage changes on the full date range
daily_stats_full['load_pct_change'] = daily_stats_full['load'].pct_change() * 100
# Repeat for other metrics as needed

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