使用Matplotlib在同图绘制价格时间序列与小时观测数柱状图

Got it, let's walk through how to create this combined plot efficiently—even with 100k+ rows of data. Here's a step-by-step guide using matplotlib (the most flexible tool for dual-axis plots) and your existing DataFrames:

1. Prep Your Data First #

First, double-check that your time columns are datetime objects (not strings) — this is critical for proper axis alignment and performance:

# For your price time series DataFrame (let's call it df_price)
df_price['time'] = pd.to_datetime(df_price['time'])

# For your hourly count DataFrame (let's call it df_hourly_count)
# Make sure the time column here represents the end/start of each hour (e.g., 01:00:00 for 00:00-01:00)
df_hourly_count['time'] = pd.to_datetime(df_hourly_count['time'])

2. Create the Dual-Axis Plot #

We'll use one axis for the price time series (line plot) and a twin axis for the hourly count (bar chart). This keeps both metrics visible without cluttering the plot.

import matplotlib.pyplot as plt

# Set up the base figure and first axis (price series)
fig, ax1 = plt.subplots(figsize=(14, 7))  # Larger size for readability with big data

# Plot the price time series
# Use a thin line to avoid overcrowding with 100k+ points
line_plot = ax1.plot(df_price['time'], df_price['price'], 
                     color='tab:blue', linewidth=0.7, label='Price')
# Customize ax1
ax1.set_xlabel('Time', fontsize=12)
ax1.set_ylabel('Price', color='tab:blue', fontsize=12)
ax1.tick_params(axis='y', labelcolor='tab:blue')
ax1.grid(alpha=0.3, linestyle='--')

# Create a twin axis for the hourly count bar chart
ax2 = ax1.twinx()

# Plot the hourly count bars
# Set width to 3600 seconds (1 hour) to match datetime axis units
bar_plot = ax2.bar(df_hourly_count['time'], df_hourly_count['count'],
                   color='tab:orange', alpha=0.6, width=3600, label='Hourly Observations')
# Customize ax2
ax2.set_ylabel('Hourly Observation Count', color='tab:orange', fontsize=12)
ax2.tick_params(axis='y', labelcolor='tab:orange')

# Add a combined legend
lines1, labels1 = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax1.legend(lines1 + lines2, labels1 + labels2, loc='upper left')

# Clean up the x-axis (rotate labels to avoid overlap)
plt.xticks(rotation=45, ha='right')

# Adjust layout to prevent label cutoff
fig.tight_layout()

# Show or save the plot
plt.show()
# plt.savefig('price_vs_hourly_count.png', dpi=300, bbox_inches='tight')

3. Key Tips for Large Datasets (100k+ Rows) #

• Line Plot Performance: Using a thin line (linewidth=0.5-0.8) and no markers reduces rendering time. Matplotlib handles large line plots well, but if you still see lag, you can downsample the price data slightly (e.g., resample to 1-minute intervals) without losing meaningful trends.
• Bar Chart Alignment: The width=3600 parameter ensures each bar exactly spans one hour (since datetime axes use seconds as units). If your hourly count DataFrame uses start times (e.g., 00:00:00 for 00:00-01:00), the bars will align perfectly with the time series.
• Color Accessibility: If you need colorblind-friendly palettes, swap tab:blue and tab:orange for #1f77b4 and #ff7f0e (matplotlib's colorblind-safe defaults).

4. Troubleshooting #

• If the x-axis looks messy: Use ax1.xaxis.set_major_locator(plt.MaxNLocator(10)) to limit the number of visible ticks.
• If bars overlap with the line: Lower the alpha value of the bars (e.g., alpha=0.4) to make the line more visible.

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