Pandas按Year_Month分组统计唯一ID数量并实现时间排序的技术问询

Hey there! Let's fix this up for you—you're super close, just need a small tweak to get the unique ID counts per month while keeping your original time order.

The Problem with Your Current Approach #

When you use df.groupby(['Year_Month', 'ID']).count(), you're counting how many times each ID appears in a month (the Error Count totals), not how many unique IDs exist per month. Plus, pandas' groupby() sorts groups alphabetically by default, which messes up your original month order.

Solution 1: Use nunique() (Most Concise) #

The simplest way is to group directly by Year_Month, then use nunique() on the ID column—this counts each ID exactly once per month, no matter how many times it appears. Add sort=False to preserve your original month order (since your DataFrame is already ordered correctly).

Here's the code:

import pandas as pd

# Your original data
data = [['345', 1, '2022_Jan'], ['678', 1, '2022_Jan'], ['123', 1, '2022_Feb'], ['123', 1, '2022_Feb'], ['345', 0, '2022_Mar'], ['678', 1, '2022_Mar'], ['901', 0, '2022_Mar'], ['678', 1, '2022_Mar']]
df = pd.DataFrame(data, columns=['ID', 'Error Count', 'Year_Month'])

# Get unique ID counts per month, keep original order
monthly_unique_ids = df.groupby('Year_Month', sort=False)['ID'].nunique()

# Convert to a clean DataFrame if needed
monthly_unique_ids_df = monthly_unique_ids.reset_index(name='Unique ID Count')
print(monthly_unique_ids_df)

This will output:

Year_Month  Unique ID Count
0   2022_Jan                2
1   2022_Feb                1
2   2022_Mar                3

Solution 2: Drop Duplicates First (More Intuitive) #

If you prefer a more explicit approach, first remove duplicate (Year_Month, ID) pairs (so each ID only appears once per month), then count the remaining entries per month:

# Drop duplicates: keep only one entry per ID per month
unique_id_month = df.drop_duplicates(subset=['Year_Month', 'ID'])

# Count IDs per month, preserve order
monthly_unique_ids_df2 = unique_id_month.groupby('Year_Month', sort=False)['ID'].count().reset_index(name='Unique ID Count')
print(monthly_unique_ids_df2)

This gives the exact same result as Solution 1—pick whichever makes more sense to you!

Why This Works #

• nunique() directly calculates the number of distinct values in the ID column for each month group.
• sort=False tells pandas not to reorder the groups, so your months stay in the original sequence from your DataFrame.

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