如何使用Python Pandas统计CSV中各页面每月新增点赞用户数

实现逻辑 #

• 对每个页面+用户分组，提取用户给当前页面首次点赞的月份，过滤掉后续重复的点赞记录
• 基于首次点赞记录，按页面+月份分组统计独立用户数，即为当月新增点赞用户数
• 生成全量的页面+月份组合，补全没有新增用户的月份数据为0
• 按要求格式化输出结果

完整代码 #

import pandas as pd

# 实际使用时替换为读取你的CSV文件
# df = pd.read_csv("你的文件路径.csv")

# 以下为示例数据构造，可替换为实际读取逻辑
data = [
    ["usera","sample1","2021-05-30"],
    ["userb","sample1","2021-05-20"],
    ["usera","sample1","2021-05-12"],
    ["usera","sample1","2021-07-24"],
    ["userx","sample1","2021-07-25"],
    ["usera","sample2","2021-05-06"],
    ["usera","sample2","2021-05-07"],
    ["usera","sample2","2021-05-08"],
    ["usera","sample2","2021-05-09"],
    ["usera","sample2","2021-05-09"],
    ["usera","sample2","2021-06-09"],
    ["userx","sample2","2021-06-01"],
    ["usera","sample2","2021-07-10"],
    ["userx","sample2","2021-07-11"],
    ["userz","sample2","2021-07-12"],
]
df = pd.DataFrame(data, columns=['username','page','date'])

# 日期格式转换，提取月份
df['date'] = pd.to_datetime(df['date'])
# 如果涉及跨年数据，建议用下面的写法保留年份：df['month'] = df['date'].dt.strftime('%Y-%m')
df['month'] = df['date'].dt.strftime('%m')

# 取每个用户给对应页面首次点赞的记录
first_like_record = df.groupby(['page','username'])['month'].min().reset_index()

# 统计每个页面每月新增用户数
monthly_new_count = first_like_record.groupby(['page','month'])['username'].nunique().reset_index(name='count')

# 补全无新增的月份为0
all_pages = df['page'].unique()
all_months = sorted(df['month'].unique())
full_index = pd.MultiIndex.from_product([all_pages, all_months], names=['page','month'])
result = monthly_new_count.set_index(['page','month']).reindex(full_index, fill_value=0).reset_index()

# 格式化输出
for _, row in result.iterrows():
    print(f"{row['page']} {row['count']} new users liked in {row['month']} month")

输出结果 #

sample1 2 new users liked in 05 month
sample1 0 new users liked in 06 month
sample1 1 new users liked in 07 month
sample2 1 new users liked in 05 month
sample2 1 new users liked in 06 month
sample2 1 new users liked in 07 month

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