如何用Pandas按30分钟时间桶统计全年数据行数（含空桶）

Got it, let's tackle this problem head-on. You need to count rows in 30-minute intervals across a full year, and crucially, include every single 30-minute bucket even if there's no data for it. Below are two practical approaches depending on whether you're working with SQL (data warehouse/database) or Python (Pandas for data analysis):

Approach 1: SQL (Works for PostgreSQL, MySQL, SQL Server) #

The core idea here is to first generate a complete list of all 30-minute buckets for your full year time range, then left join your actual data to this list and count rows. This ensures empty buckets show up with a count of 0.

Example for PostgreSQL #

PostgreSQL has a handy generate_series function to create the time buckets:

-- Step 1: Define your full year time range
WITH time_buckets AS (
  SELECT
    generate_series(
      '2023-01-01 00:00:00'::timestamp, -- Start of your year
      '2023-12-31 23:30:00'::timestamp, -- End of your year (last 30min bucket)
      '30 minutes'::interval
    ) AS bucket_start
)
-- Step 2: Left join with your data and count rows
SELECT
  tb.bucket_start,
  COUNT(t.your_table_id) AS row_count -- Replace with a non-null column from your table
FROM time_buckets tb
LEFT JOIN your_table t
  ON t.Timestamp >= tb.bucket_start
  AND t.Timestamp < tb.bucket_start + '30 minutes'::interval
GROUP BY tb.bucket_start
ORDER BY tb.bucket_start;

Example for MySQL/SQL Server (Using Recursive CTE) #

If your database doesn't have generate_series, use a recursive CTE to build the buckets:

-- MySQL/SQL Server Recursive CTE to create 30min buckets
WITH time_buckets AS (
  SELECT CAST('2023-01-01 00:00:00' AS DATETIME) AS bucket_start
  UNION ALL
  SELECT DATEADD(MINUTE, 30, bucket_start)
  FROM time_buckets
  WHERE bucket_start < CAST('2023-12-31 23:30:00' AS DATETIME)
)
SELECT
  tb.bucket_start,
  COUNT(t.Timestamp) AS row_count
FROM time_buckets tb
LEFT JOIN your_table t
  ON t.Timestamp >= tb.bucket_start
  AND t.Timestamp < DATEADD(MINUTE, 30, tb.bucket_start)
GROUP BY tb.bucket_start
ORDER BY tb.bucket_start
OPTION (MAXRECURSION 0); -- Required for SQL Server to avoid recursion limits

Approach 2: Python with Pandas #

For data analysis workflows, Pandas makes this straightforward by resampling against a complete time index.

import pandas as pd

# Load your data (replace with your data loading logic)
df = pd.read_csv('your_data.csv')

# Convert Timestamp column to datetime type
df['Timestamp'] = pd.to_datetime(df['Timestamp'])

# Define the full year time range with 30min frequency
full_time_range = pd.date_range(
  start='2023-01-01 00:00:00',
  end='2023-12-31 23:30:00',
  freq='30T'
)

# Resample your data to 30min buckets, then reindex to include all buckets
bucketed_counts = df.resample('30T', on='Timestamp').size()
bucketed_counts = bucketed_counts.reindex(full_time_range, fill_value=0)

# Optional: Rename the series for clarity
bucketed_counts = bucketed_counts.rename('row_count')

# View the result
print(bucketed_counts.head())

Key notes here:

• freq='30T' tells Pandas to use 30-minute intervals
• reindex(full_time_range, fill_value=0) ensures every bucket in the full year is included, even if empty

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