如何用Pandas同时按日期与用户ID分组，聚合日均消费均值？

Hey there! I see you're trying to calculate the daily average consumption for each individual customer, but hitting a roadblock where df.resample('D').mean() combines all users' data instead of keeping them separate. No worries—this is a common scenario, and we can solve it by combining groupby() with resample().

Step 1: Load and Prepare Your Data #

First, make sure your datetime column is parsed as a datetime type (this is crucial for resampling to work correctly). Here's how to load your sample data:

import pandas as pd
from io import StringIO

# Your sample CSV data
data = """customer consumption datetime
1 0.970 2013-06-29 19:00:00
1 0.625 2013-06-29 19:30:00
1 0.153 2013-06-29 20:00:00
1 0.484 2013-06-29 20:30:00
1 0.489 2013-06-29 21:00:00
1 0.970 2013-06-30 19:00:00
1 0.625 2013-06-30 19:30:00
1 0.153 2013-06-30 20:00:00
1 0.484 2013-06-30 20:30:00
1 0.489 2013-06-30 21:00:00
2 0.461 2013-06-29 19:00:00
2 0.894 2013-06-29 19:30:00
2 0.848 2013-06-29 20:00:00
2 0.977 2013-06-29 20:30:00
2 0.189 2013-06-29 21:00:00
2 0.461 2013-06-30 19:00:00
2 0.894 2013-06-30 19:30:00
2 0.848 2013-06-30 20:00:00
2 0.977 2013-06-30 20:30:00
2 0.189 2013-06-30 21:00:00"""

# Load the data into a DataFrame, parsing the datetime column
df = pd.read_csv(StringIO(data), sep=' ', parse_dates=['datetime'])

If you're loading from an actual CSV file, use this instead:

df = pd.read_csv('your_file.csv', parse_dates=['datetime'])

Step 2: Calculate Daily Mean per Customer #

The key here is to group by customer first, then resample each group by day and compute the mean. You have two options for this:

Option 1: Set datetime as index first

# Set datetime as the index
df = df.set_index('datetime')

# Group by customer, resample daily, and calculate mean consumption
daily_avg = df.groupby('customer').resample('D')['consumption'].mean()

Option 2: Use the on parameter (no need to set index)

If you prefer to keep datetime as a regular column, use the on argument in resample():

daily_avg = df.groupby('customer').resample('D', on='datetime')['consumption'].mean()

Step 3: View the Result #

Running either option will give you a multi-index Series where the first level is the customer ID, and the second is the date:

customer  datetime  
1         2013-06-29    0.5442
          2013-06-30    0.5442
2         2013-06-29    0.6738
          2013-06-30    0.6738
Name: consumption, dtype: float64

If you want a cleaner "wide" format (dates as rows, customers as columns), use unstack():

daily_avg_wide = daily_avg.unstack('customer')
print(daily_avg_wide)

Output:

customer       1      2
datetime               
2013-06-29  0.5442  0.6738
2013-06-30  0.5442  0.6738

Why This Works #

By grouping first with groupby('customer'), we split the DataFrame into separate subsets for each user. Then, resample('D') works on each subset independently, calculating the daily average for just that customer's data—no more mixing users together!

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