基于Pandas按日期分组计算芝加哥出租车每小时营收中位数的技术问询

Hey there! Let's break down how to calculate median fares for standard taxis across hourly, daily, and weekly time windows, plus visualize and analyze the revenue patterns in your Chicago taxi dataset. I'll assume you've already loaded your data into a pandas DataFrame (let's call it taxi_df) with Timestamp as a datetime column and Fare as the revenue field.

1. Preprocess the Data #

First, confirm your Timestamp is properly formatted (you mentioned it's already pandas datetime, but just to double-check) and filter for standard taxis if your dataset includes a taxi type field (adjust the column name if yours differs):

# Ensure Timestamp is datetime (skip if already done)
taxi_df['Timestamp'] = pd.to_datetime(taxi_df['Timestamp'])

# Filter for standard taxis (replace 'Taxi_Type' with your actual column name)
standard_taxis = taxi_df[taxi_df['Taxi_Type'] == 'Standard']

Note: If your dataset doesn't explicitly label taxi types, you might need to infer from Taxi ID patterns or skip this step if all entries are standard taxis.

2. Calculate Median Fare by Time Dimensions #

We'll use pandas' groupby with time-based extraction/resampling to compute median fares for each time window.

Hourly Median Fare #

Extract the hour of day from Timestamp and group by it:

# Add hour column (0 = midnight, 23 = 11 PM)
standard_taxis['Hour'] = standard_taxis['Timestamp'].dt.hour

# Compute hourly median fare
hourly_median = standard_taxis.groupby('Hour')['Fare'].median().reset_index()

Daily Median Fare #

Group by the full date component of Timestamp:

# Add date column (YYYY-MM-DD)
standard_taxis['Date'] = standard_taxis['Timestamp'].dt.date

# Compute daily median fare
daily_median = standard_taxis.groupby('Date')['Fare'].median().reset_index()

Weekly Median Fare #

Use dt.isocalendar() to get year-week pairs, then group by those:

# Add year-week column (e.g., "2023-W45")
standard_taxis['Year_Week'] = standard_taxis['Timestamp'].dt.isocalendar().apply(lambda x: f"{x.year}-W{x.week}", axis=1)

# Compute weekly median fare
weekly_median = standard_taxis.groupby('Year_Week')['Fare'].median().reset_index()

3. Visualize the Trends #

Seaborn and Matplotlib work perfectly for plotting these time-based patterns. Here are examples for each dimension:

Hourly Trend Plot #

import seaborn as sns
import matplotlib.pyplot as plt

plt.figure(figsize=(12, 6))
sns.lineplot(data=hourly_median, x='Hour', y='Fare', marker='o', color='#2ecc71')
plt.title('Hourly Median Fare for Standard Chicago Taxis')
plt.xlabel('Hour of Day')
plt.ylabel('Median Fare ($)')
plt.grid(True, alpha=0.3)
plt.show()

Expected insight: You’ll likely see peaks during morning (7-9 AM) and evening (5-7 PM) rush hours, plus late-night windows (10 PM-2 AM) where fares rise due to lower availability or surge pricing.

Daily Trend Plot #

plt.figure(figsize=(15, 6))
sns.lineplot(data=daily_median, x='Date', y='Fare', color='#3498db')
plt.title('Daily Median Fare for Standard Chicago Taxis')
plt.xlabel('Date')
plt.ylabel('Median Fare ($)')
plt.xticks(rotation=45, ha='right')
plt.grid(True, alpha=0.3)
plt.show()

Expected insight: Look for weekly cycles—weekends often have higher median fares from leisure trips (nightlife, tourism) while weekdays lean toward shorter commutes.

Weekly Trend Plot #

plt.figure(figsize=(15, 6))
sns.lineplot(data=weekly_median, x='Year_Week', y='Fare', color='#e74c3c')
plt.title('Weekly Median Fare for Standard Chicago Taxis')
plt.xlabel('Year-Week')
plt.ylabel('Median Fare ($)')
plt.xticks(rotation=45, ha='right')
plt.grid(True, alpha=0.3)
plt.show()

Expected insight: This highlights longer-term shifts—like gradual fare increases over time, or dips during major events (e.g., COVID lockdowns, holiday slowdowns).

4. Key Revenue Feature Analysis #

• Hourly: Rush hours and late nights drive higher median fares, tied to increased demand and potentially longer trip distances or surge pricing.
• Daily: Weekends outperform weekdays in median fares due to leisure-focused travel, while weekday fares are anchored by short commutes.
• Weekly: Seasonal trends stand out—summer tourism and winter weather delays often push fares higher, while holiday weeks might see dips as travel slows.
• Outliers: Flag days/weeks with extreme median fares—these could link to special events (Chicago Marathon, Lollapalooza), holidays, or data anomalies to investigate further.

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