如何将日期索引的Dataframe按月重采样统计计数并绘制无重叠轴图表？

Got it, let's walk through how to solve this problem step by step—first resampling your DataFrame to get monthly instance counts, then creating a clean, readable bar chart without overlapping x-axis labels.

First, we need to make sure your date column is properly formatted as datetime, then use pandas' resampling functionality to tally up instances per month.

Here's the code to do that:

import pandas as pd

# Load your dataset (replace with your actual file path or data source)
df = pd.read_csv("your_data.csv")

# Convert the 'Date' column to datetime format and set it as the DataFrame index
df["Date"] = pd.to_datetime(df["Date"])
df.set_index("Date", inplace=True)

# Resample by month ('M' denotes month-end frequency), count entries, and rename the result column
monthly_counts = df.resample("M").size().reset_index(name="Count")

What this does: #

• pd.to_datetime(df["Date"]) ensures your dates are parsed correctly as datetime objects.
• resample("M") groups the data by each month (using the last day of the month as the index, which matches your desired output).
• size() counts the number of rows (instances) in each monthly group.
• reset_index(name="Count") converts the resampled index back to a column and names the count column appropriately.

The resulting monthly_counts DataFrame will look exactly like your desired output:

Date  Count
0 2001-05-31      2
1 2001-06-30      3
2 2001-07-31      4
...

Since your data spans 2001-2017 (17 years = 204 months), we need to adjust the x-axis labels to avoid clutter. We'll use matplotlib for this—here's how:

import matplotlib.pyplot as plt

# Set up the plot with a wider figure to accommodate all labels
fig, ax = plt.subplots(figsize=(14, 7))

# Plot the bar chart
ax.bar(monthly_counts["Date"], monthly_counts["Count"], color="#1f77b4")

# Add labels and title
ax.set_xlabel("Month", fontsize=12)
ax.set_ylabel("Number of Instances", fontsize=12)
ax.set_title("Monthly Instance Counts (2001-2017)", fontsize=14, pad=20)

# Rotate x-axis labels and align them to prevent overlap
plt.xticks(rotation=45, ha="right", fontsize=10)

# Adjust the layout so labels don't get cut off
plt.tight_layout()

# Display the plot
plt.show()

Key adjustments for readability: #

• figsize=(14,7) makes the chart wide enough to fit all month labels without cramming.
• rotation=45 and ha="right" rotate labels 45 degrees and align them to the right, so they don't overlap.
• tight_layout() automatically adjusts the plot margins to ensure all labels and titles are visible.

If you prefer using seaborn for a more polished look, here's an alternative snippet:

import seaborn as sns

plt.figure(figsize=(14,7))
sns.barplot(x="Date", y="Count", data=monthly_counts, color="#1f77b4")
plt.xticks(rotation=45, ha="right")
plt.xlabel("Month")
plt.ylabel("Number of Instances")
plt.title("Monthly Instance Counts (2001-2017)")
plt.tight_layout()
plt.show()

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