如何创建列值为多日平均值的透视表？（含df1按3天求B列均值的需求示例）

Got it, let's break down how to solve your problem and cover the general approach for creating pivot tables with N-day averages.

Step-by-Step Solution for Your Specific Case #

You mentioned needing to group dates into 24/08/2021, 25-27/08/2021, 28-30/08/2021 and calculate the average of column B for each group. Here's exactly how to do this in pandas:

1. Convert Date Column to Datetime Type #

First, make sure your date column is in a datetime format (critical for proper grouping):

import pandas as pd

# Replace 'date' with your actual date column name
df1['date'] = pd.to_datetime(df1['date'], format='%d/%m/%Y')

2. Define Custom Date Groups #

Since your first group is a single day followed by 3-day blocks, we'll use pd.cut() to manually set group boundaries:

# Set the starting point of your groups
start_date = pd.to_datetime('24/08/2021', format='%d/%m/%Y')

# Create bins for grouping: [24th, 25th), [25th, 28th), [28th, 31st)
# This ensures 24th is alone, 25-27 are grouped, 28-30 are grouped
date_bins = [
    start_date,
    start_date + pd.Timedelta(days=1),
    start_date + pd.Timedelta(days=4),
    start_date + pd.Timedelta(days=7)
]

# Assign group labels that match your df2 expected output
group_labels = ['24/08/2021', '25-27/08/2021', '28-30/08/2021']

# Add a 'group' column to df1
df1['group'] = pd.cut(df1['date'], bins=date_bins, labels=group_labels)

3. Generate the Pivot Table #

Now use pivot_table() to calculate the average of column B per group:

# Create pivot table with group as index and mean of B as value
pivot_result = df1.pivot_table(
    index='group',
    values='B',
    aggfunc='mean'
).reset_index()

# Optional: Add group numbering like your df2 (1, 2, 3)
pivot_result['group_number'] = pivot_result.index + 1

General Approach: Creating Pivot Tables with N-Day Averages #

Depending on whether you need fixed-interval groups or custom groups, here are the two main methods:

Method 1: Fixed N-Day Intervals (e.g., every 3 days starting from the earliest date) #

If you want consistent N-day blocks without custom exceptions, use resample():

# Set date column as index
df1.set_index('date', inplace=True)

# Resample every 3 days and calculate mean of B
resampled_data = df1['B'].resample('3D').mean().reset_index()

# Convert to pivot table format (if needed)
pivot_fixed = resampled_data.pivot_table(
    index=resampled_data['date'].dt.strftime('%d-%m/%Y'),
    values='B',
    aggfunc='mean'
)

• Note: resample('3D') will start grouping from the earliest date in your dataset. If you need to start from a specific date, use resample('3D', origin=start_date).

Method 2: Custom N-Day Groups #

For cases where groups don't follow a strict fixed interval (like your single-day first group), use pd.cut() as shown earlier. Adjust the date_bins and group_labels to match your desired grouping logic.

Key Notes #

• Always validate your date column is datetime type first—string dates will break grouping logic.
• If you have missing dates in your dataset, resample() will insert NaN for those periods; use fillna() if you need to handle gaps.
• When using pd.cut(), double-check your bin boundaries to avoid missing dates or overlapping groups.

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