如何用Python统计PostgreSQL导出列表中用户名的出现频率

问题背景 #

我在PostgreSQL数据库中有一张表，通过以下SQL语句提取数据：

sql_statement = """ select a.slno, a.clientid, a.filename, a.user1_id, b.username, a.user2_id, c.username as username2, a.uploaded_ts, a.status_id
                from masterdb.xmlform_joblist a
                left outer join masterdb.auth_user b
                on a.user1_id = b.id 
                left outer join masterdb.auth_user c
                on a.user2_id = c.id
                """
cursor.execute(sql_statement)
result = cursor.fetchall()

提取数据后，将指定字段存入列表：

date = []
username1 = []
username2 = []
user1_id = []
user2_id = []
status_id = []
cient_id = []
filename = []

#extracting specific data from specified fields in the database
for row in result:
    date.append(row[7])
    username1.append(row[4])
    username2.append(row[6])
    status_id.append(row[8])
    cient_id.append(row[1])
    filename.append(row[2])
    #creating log file for the extracted fields 
    logger.info("Date | {} , username1 | {} , username2 | {} , status_id | {} , client_id | {} , filename | {} ".format(row[7], row[4], row[6], row[8], row[1], row[2]))

现在需要统计username1列表中各用户名的出现次数，预期结果类似：

Dean = 10
Sarah = 6
Alan = 2

曾尝试用Pandas但未成功，询问如何用Python实现。

实现方法 #

方法1：使用Python标准库collections.Counter #

无需额外安装依赖，直接用标准库即可完成统计：

from collections import Counter

# 统计username1的出现频率
username_counts = Counter(username1)

# 按预期格式输出结果
for name, count in username_counts.items():
    print(f"{name} = {count}")

如果需要按出现次数从高到低排序，可使用most_common()方法：

# 按次数降序输出
for name, count in username_counts.most_common():
    print(f"{name} = {count}")

方法2：用Pandas实现（修正之前的错误） #

如果想用Pandas，只需将列表转为Series对象后调用统计方法即可：

import pandas as pd

# 将列表转为Pandas Series
username_series = pd.Series(username1)

# 统计频率并按预期格式输出
counts = username_series.value_counts()
for name, count in counts.items():
    print(f"{name} = {count}")

之前用Pandas失败大概率是没有正确将列表转为可统计的Pandas对象，上述代码可解决该问题。

方法3：直接在SQL中统计（更高效） #

针对PostgreSQL数据库，直接在SQL层面统计能减少内存占用，尤其适合数据量较大的场景：

select b.username, count(*) as occurrence_count
from masterdb.xmlform_joblist a
left outer join masterdb.auth_user b on a.user1_id = b.id
group by b.username
order by occurrence_count desc;

执行这条SQL后可直接获取统计结果，无需在Python中二次处理，效率更高。

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