如何用Pandas按日期、用户和部门统计频次生成DataFrame

Hey there! Let's work through this problem together. You want to count how often each user shows up per date and department, add that count as a new column, and keep the original columns intact so you can use the result directly with matplotlib. Here's how to do it:

第一步：重现原始数据 #

First, let's define your original DataFrame so anyone can follow along:

import pandas as pd

df = pd.DataFrame({
    'Date': ['20191101','20191101','20191101','20191101','20191102','20191102','20191102','20191102' ,'20191103','20191103','20191103','20191103'],
    'User': ['James','Kevin','Kevin','Corrado','James','Kevin','Corrado','Corrado','James','Kevin','Corrado','Corrado'],
    'Department': ['A','B','B','C','A','B','C','C','A','B','C','C']
})

第二步：计算频次并生成目标DataFrame #

To get your df2, we'll use pandas' groupby() combined with transform(). This method lets us calculate the count for each (Date, Department, User) group and then map that count back to every row in the original DataFrame—perfect for plotting since you keep all the context you need:

# Add the Count column to the original DataFrame
df['Count'] = df.groupby(['Date', 'Department', 'User'])['User'].transform('count')

# If you want a deduplicated version (one row per unique group, great for bar charts), use drop_duplicates()
df2 = df.drop_duplicates(subset=['Date', 'Department', 'User'])

结果展示 #

After running the code, your full df will look like this (every row has its corresponding count):

And the deduplicated df2 (ideal for most matplotlib plots) will be:

快速绘图示例 #

Here's a quick matplotlib example using df2 to visualize the counts:

import matplotlib.pyplot as plt

plt.figure(figsize=(10, 6))
for date in df2['Date'].unique():
    subset = df2[df2['Date'] == date]
    plt.bar(subset['Department'] + '-' + subset['User'], subset['Count'], label=date)

plt.xlabel('Department-User')
plt.ylabel('Appearance Count')
plt.title('User Frequency by Date & Department')
plt.legend()
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

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