筛选满足小时完整性的DataFrame时间区间数据并按天求和mat.3

Got it, let's work through this problem together. The core issue here is we need to only keep days where every hour between 11 AM and 4 PM (11:00 to 16:00) has at least one data point, then sum the mat.3 values for those valid days in that time window. Here's a step-by-step implementation using pandas:

Step 1: Prepare Time Columns #

First, make sure your timestamp column is properly parsed as a datetime, then extract the date and hour components we'll need for grouping and validation:

import pandas as pd

# Convert timestamp column to datetime (replace 'timestamp' with your actual column name)
df['timestamp'] = pd.to_datetime(df['timestamp'])

# Extract date (for daily grouping) and hour (to check hourly completeness)
df['date'] = df['timestamp'].dt.date
df['hour'] = df['timestamp'].dt.hour

Step 2: Narrow Down to Target Time Window #

Filter the data to only include rows between 11 AM and 4 PM—this reduces the data we need to process:

# Keep only rows where hour is between 11 and 16 (inclusive)
time_window_df = df[(df['hour'] >= 11) & (df['hour'] <= 16)]

Step 3: Identify Valid Days (Full Hourly Coverage) #

We need to find days where all 6 hours (11,12,13,14,15,16) have at least one entry. A quick, efficient way to do this is checking if the number of unique hours per day equals 6:

# Group by date, count unique hours per day, then filter days with exactly 6 unique hours
valid_days_mask = time_window_df.groupby('date')['hour'].nunique() == 6
valid_dates = valid_days_mask[valid_days_mask].index

Step 4: Filter Valid Data and Calculate Daily Sums #

Now we only keep rows from valid days, then sum the mat.3 values per day:

# Filter to only include valid days
valid_data = time_window_df[time_window_df['date'].isin(valid_dates)]

# Calculate daily sum of mat.3
daily_mat3_sum = valid_data.groupby('date')['mat.3'].sum().reset_index()

Key Notes: #

• This approach strictly excludes days where even one hour in the 11AM-4PM window is missing data—exactly what you need to avoid partial days.
• If you have multiple data points in a single hour, that's fine; we just need at least one per hour to count the day as valid.
• Using nunique() is more performant than checking full set membership, especially for large datasets.

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