如何对Pandas DataFrame按日/月聚合total_value并绘图？

First, let's break down why your original code wasn't working and fix it step by step:

• Your payout_date column is probably stored as a string instead of a datetime type, so pandas couldn't recognize it as a time series for grouping.
• Grouping by the full timestamp (including hours/minutes) created a separate group for every single entry, which is why you saw bars for each individual total_value instead of aggregated sums.
• The TypeError likely happened because your filtered date range (2018-02) didn't match the sample data you provided (which uses 2017-02), leaving an empty DataFrame with no numeric values to plot.

Step 1: Convert payout_date to Datetime Type #

First, make sure pandas understands your date column as a datetime object:

import pandas as pd
import matplotlib.pyplot as plt

# Convert the string date column to datetime
df['payout_date'] = pd.to_datetime(df['payout_date'])

Step 2: Daily Aggregation & Plotting #

To group by day (ignoring the time component) and plot the summed values:

# Set datetime as index and filter your desired date range (adjust to match your data)
daily_data = df.set_index('payout_date').loc['2017-02-01':'2017-02-14']

# Group by day and calculate total_value sum
daily_aggregated = daily_data.groupby(pd.Grouper(freq='D'))['total_value'].sum().reset_index()

# Create the bar plot
daily_aggregated.plot(x='payout_date', y='total_value', kind='bar', title='Daily Total Value')
plt.xlabel('Date')
plt.ylabel('Total Value')
plt.show()

Step 3: Monthly Aggregation & Plotting #

For monthly sums, just adjust the grouping frequency to 'M':

# Group by month and sum total_value
monthly_aggregated = df.set_index('payout_date').groupby(pd.Grouper(freq='M'))['total_value'].sum().reset_index()

# Optional: Format month labels for readability
monthly_aggregated['payout_date'] = monthly_aggregated['payout_date'].dt.strftime('%Y-%m')

# Plot the monthly totals
monthly_aggregated.plot(x='payout_date', y='total_value', kind='bar', title='Monthly Total Value')
plt.xlabel('Month')
plt.ylabel('Total Value')
plt.show()

Key Tips: #

• Use pd.Grouper(freq='D') for daily groups, 'M' for monthly, 'Y' for yearly, etc.—pandas supports a wide range of time frequencies.
• If you still get an empty DataFrame error, double-check that your date filter matches the actual dates in your dataset (your sample uses 2017, but your original code filtered 2018).
• reset_index() converts the grouped datetime index back to a column, making it straightforward to use with pandas' plotting functions.

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