如何按年/月统计并可视化“Rood”订单的出现频次？

Hey there! Let's walk through exactly how to group your data by year-month, count "Rood" orders, and visualize the results. I'll use pandas and matplotlib/seaborn since they're the go-to tools for this kind of task.

Step 1: Prep Your Date Column #

First, make sure your date column is formatted as a datetime object—this is critical for grouping by time periods correctly. If it's currently stored as a string, convert it with:

import pandas as pd

# Replace 'date_column' with your actual date column name
df['date_column'] = pd.to_datetime(df['date_column'])

Step 2: Filter for "Rood" Orders #

Narrow down your dataset to only include orders labeled "Rood" (adjust the column name if yours is different, like order_category):

# Replace 'order_type' with your column that contains the "Rood" label
rood_orders = df[df['order_type'] == 'Rood'].copy()

Step 3: Group by Year & Month to Count Frequencies #

You have two clean, straightforward ways to do this:

Option 1: Use dt.to_period('M') for Combined Year-Month Labels #

This creates a single column with values like 2022-03 (March 2022), which is perfect for plotting without extra steps:

# Group by year-month and count total "Rood" orders
monthly_counts = rood_orders.groupby(rood_orders['date_column'].dt.to_period('M')).size().reset_index(name='rood_count')

# Optional: Convert period back to datetime for smoother axis formatting
monthly_counts['date_column'] = monthly_counts['date_column'].dt.to_timestamp()

Option 2: Group by Separate Year & Month Columns #

If you want to keep year and month as distinct columns (useful for further analysis):

# Extract year and month into new columns
rood_orders['year'] = rood_orders['date_column'].dt.year
rood_orders['month'] = rood_orders['date_column'].dt.month

# Group and count orders per year-month pair
monthly_counts = rood_orders.groupby(['year', 'month'])['order_type'].count().reset_index(name='rood_count')

Step 4: Visualize the Results #

Let's turn those counts into a clear, easy-to-read plot. I'll show both line and bar chart options depending on your preference.

For Option 1 (Combined Year-Month Date): #

import matplotlib.pyplot as plt

plt.figure(figsize=(12, 6))
plt.plot(monthly_counts['date_column'], monthly_counts['rood_count'], marker='o', linewidth=2, color='#3498db')

# Add labels and title for clarity
plt.title('Monthly "Rood" Order Frequency by Year', fontsize=14)
plt.xlabel('Date', fontsize=12)
plt.ylabel('Number of "Rood" Orders', fontsize=12)
plt.xticks(rotation=45)
plt.tight_layout()  # Fixes overlapping labels
plt.show()

For Option 2 (Separate Year & Month): #

Combine the year and month columns into a readable string for the x-axis first:

# Create a "YYYY-MM" string for plotting
monthly_counts['year_month'] = monthly_counts['year'].astype(str) + '-' + monthly_counts['month'].astype(str).str.zfill(2)

plt.figure(figsize=(12, 6))
plt.bar(monthly_counts['year_month'], monthly_counts['rood_count'], color='#2ecc71')

plt.title('Monthly "Rood" Order Frequency by Year', fontsize=14)
plt.xlabel('Year-Month', fontsize=12)
plt.ylabel('Number of "Rood" Orders', fontsize=12)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

Pro Tips #

• If you have months with zero "Rood" orders that aren't showing up in your counts, use resample('M') to fill those gaps with 0s (works best with Option 1's datetime index):

  monthly_counts = rood_orders.set_index('date_column')['order_type'].resample('M').count().reset_index(name='rood_count')
  

• For fancier, statistical-style plots, swap matplotlib for seaborn—just use sns.lineplot(x='date_column', y='rood_count', data=monthly_counts) instead of the matplotlib plotting code!

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