如何计算连续日期间数值的百分比差异并跳过非连续日期？

Got it, let's tackle this problem step by step. You have a CSV with datetime and memory values, and you want to compute the percentage difference of mem between consecutive dates—skipping any pairs where dates aren't back-to-back.

First, let's format your sample data as a proper CSV (using commas as delimiters for clarity):

date,mem
2018-03-09 13:27:05,23
2018-03-09 13:27:13,22
2018-03-09 13:54:34,21
2018-03-10 13:54:42,12
2018-03-10 16:18:34,34
2018-03-10 16:18:41,45
2018-03-12 22:40:36,45
2018-03-12 22:40:36,12
2018-03-14 22:40:44,35
2018-03-14 22:40:44,25
2018-03-15 23:12:36,26
2018-03-15 23:12:44,28
2018-03-15 23:22:34,12
2018-03-15 13:27:05,14
2018-03-16 13:27:13,54
2018-03-16 13:54:34,12
2018-03-16 13:54:42,56
2018-03-17 16:18:34,45
2018-03-18 16:18:41,76

Approach Using Python & Pandas #

Pandas is ideal for this kind of time-series data manipulation. Here's a practical, step-by-step solution:

Step 1: Load and Prepare the Data #

First, read the CSV, parse the datetime column, and extract just the date part (since we care about consecutive days, not exact timestamps):

import pandas as pd

# Read the CSV, parse datetime column, and handle whitespace delimiter if needed
df = pd.read_csv('your_data.csv', parse_dates=['date'], header=0)
# If your raw data uses spaces instead of commas, use: sep='\s+'

# Extract date (without time) to group entries by day
df['day'] = df['date'].dt.date

Step 2: Aggregate Daily Mem Values #

Since each day has multiple mem entries, we'll first compute a daily representative value (I'll use the average here—swap in sum() or max() if you need a different metric):

# Group by day and calculate daily average mem
daily_stats = df.groupby('day')['mem'].mean().reset_index()
daily_stats.columns = ['date', 'avg_mem']

Step 3: Identify Consecutive Dates & Calculate Differences #

Next, we'll flag consecutive date pairs and compute the percentage difference:

# Add columns for the previous day's date and mem value
daily_stats['prev_date'] = daily_stats['date'].shift(1)
daily_stats['prev_avg_mem'] = daily_stats['avg_mem'].shift(1)

# Check if current date is exactly one day after the previous date
daily_stats['is_consecutive'] = (daily_stats['date'] - daily_stats['prev_date']).dt.days == 1

# Filter to keep only consecutive date pairs
consecutive_pairs = daily_stats[daily_stats['is_consecutive']].copy()

# Calculate percentage difference: ((current value - previous value)/previous value)*100
consecutive_pairs['pct_diff'] = ((consecutive_pairs['avg_mem'] - consecutive_pairs['prev_avg_mem']) / consecutive_pairs['prev_avg_mem']) * 100

# Clean up the result to show only relevant columns
final_result = consecutive_pairs[['prev_date', 'date', 'prev_avg_mem', 'avg_mem', 'pct_diff']]
print(final_result)

Example Output #

Using your sample data, the result will look like this (rounded for readability):

prev_date        date  prev_avg_mem   avg_mem   pct_diff
1  2018-03-09  2018-03-10          22.0  30.33333  37.878788
10 2018-03-15  2018-03-16          20.0  40.66667  103.333333
11 2018-03-16  2018-03-17          40.66667 45.00000  10.655738
12 2018-03-17  2018-03-18          45.00000 76.00000  68.888889

Quick Notes #

• If you want to compare individual entries across consecutive days (instead of daily aggregates), adjust the logic to check if each entry's date is consecutive to the previous entry's date, rather than grouping by day.
• Swap the numerator in the percentage difference formula if you want to calculate the change relative to the current day instead of the previous one.

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