如何优化Pandas中嵌套循环与条件判断的用户类型按天统计代码？

我有一个包含16列的DataFrame，需要基于其中Type（包含Subscriber和One-time user两种类型）和Day（星期几）两列生成统计表格。实际数据有16000+行，示例数据如下：

Type,Trip_in_min,Day
One-time user,25,Sunday
Subscriber,11,Sunday
Subscriber,4,Sunday
One-time user,11,Sunday
Subscriber,9,Sunday
One-time user,4,Sunday
Subscriber,2,Sunday
One-time user,3,Sunday
Subscriber,2,Sunday
Subscriber,8,Sunday
One-time user,12,Monday
Subscriber,11,Monday
One-time user,2,Monday
Subscriber,2,Monday
One-time user,5,Tuesday
Subscriber,9,Tuesday
One-time user,6,Tuesday
Subscriber,16,Tuesday

期望生成的统计结果格式如下：

Subscribers  One Time Users
Sunday               6               4
Monday               2               2
Tuesday              2               2
Wednesday            0               0
Thursday             0               0
Friday               0               0
Saturday             0               0

当前使用的嵌套循环+大量条件判断的代码效率极低，代码如下：

import pandas as pd
file = pd.read_csv(r"D:\OneDrive\DataAnalysis\Test2.txt")
df = pd.DataFrame(file)
user_type = 0
regular = 0
regular1 = 0
regular2 = 0
regular3 = 0
regular4 = 0
regular5 = 0
regular6 = 0

one_time_user = 0
one_time_user1 = 0
one_time_user2 = 0
one_time_user3 = 0
one_time_user4 = 0
one_time_user5 = 0
one_time_user6 = 0
day_idx = 0
new_list = pd.DataFrame({'Subscribers': [0, 0, 0, 0, 0, 0, 0], 'One Time Users': [0, 0, 0, 0, 0, 0, 0]}, index=['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'])
for index1, row in df.iterrows():
    for i in range(0, len(df)):
        user_type = df['Type'].iloc[i]
        day = df['Day'].iloc[i]
        if day == 'Sunday':
            if user_type == 'Subscriber':
                regular += 1
            else:
                one_time_user += 1
        elif day == 'Monday':
            if user_type == 'Subscriber':
                regular1 += 1
            else:
                one_time_user1 += 1
        elif day == 'Tuesday':
            if user_type == 'Subscriber':
                regular2 += 1
            else:
                one_time_user2 += 1
        elif day == 'Wednesday':
            if user_type == 'Subscriber':
                regular3 += 1
            else:
                one_time_user3 += 1
        elif day == 'Thursday':
            if user_type == 'Subscriber':
                regular4 += 1
            else:
                one_time_user4 += 1
        elif day == 'Friday':
            if user_type == 'Subscriber':
                regular5 += 1
            else:
                one_time_user5 += 1
        elif day == 'Saturday':
            if user_type == 'Subscriber':
                regular6 += 1
            else:
                one_time_user6 += 1
    break
#print('Regular:', regular)
#print('One Tine User:', one_time_user)
new_list.at["Sunday", "Subscribers"] = regular
new_list.at['Sunday', "One Time Users"] = one_time_user
new_list.at["Monday", "Subscribers"] = regular1
new_list.at['Monday', "One Time Users"] = one_time_user1
new_list.at["Tuesday", "Subscribers"] = regular2
new_list.at['Tuesday', "One Time Users"] = one_time_user2
new_list.at["Wednesday", "Subscribers"] = regular3
new_list.at['Wednesday', "One Time Users"] = one_time_user3
new_list.at["Thursday", "Subscribers"] = regular4
new_list.at['Thursday', "One Time Users"] = one_time_user4
new_list.at["Friday", "Subscribers"] = regular5
new_list.at['Friday', "One Time Users"] = one_time_user5
new_list.at["Saturday", "Subscribers"] = regular6
new_list.at['Saturday', "One Time Users"] = one_time_user6
print(new_list)

需要优化代码，提升处理效率。

方案1：使用pd.crosstab（最简洁高效） #

Pandas的crosstab函数专门用于生成交叉统计表格，底层是向量化操作，比循环快几个数量级，代码量极少：

import pandas as pd

# 读取数据
df = pd.read_csv(r"D:\OneDrive\DataAnalysis\Test2.txt")

# 定义星期顺序，确保结果按指定顺序排列
day_order = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']

# 生成交叉表：行是Day，列是Type，统计数量
result = pd.crosstab(df['Day'], df['Type'])

# 重命名列名，匹配期望格式
result = result.rename(columns={
    'Subscriber': 'Subscribers',
    'One-time user': 'One Time Users'
})

# 重新索引，补全所有星期几，缺失的填充0，并按指定顺序排列
result = result.reindex(day_order, fill_value=0)

print(result)

方案2：使用groupby + unstack #

如果需要更灵活的分组逻辑，可以用groupby统计后再展开：

import pandas as pd

df = pd.read_csv(r"D:\OneDrive\DataAnalysis\Test2.txt")
day_order = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']

# 按Day和Type分组，统计每组的行数
grouped = df.groupby(['Day', 'Type']).size().unstack(fill_value=0)

# 重命名列并重新索引
grouped = grouped.rename(columns={
    'Subscriber': 'Subscribers',
    'One-time user': 'One Time Users'
}).reindex(day_order, fill_value=0)

print(grouped)

原代码低效原因 #

• 用了双重循环，外层iterrows遍历每行，内层又循环整个DataFrame，实际把数据遍历了N²次（N是行数），16000行的话就是2.56亿次操作，完全没必要。
• 手动定义大量变量和条件判断，代码冗余且容易出错。
• 没有利用Pandas的向量化操作优势，显式循环是Pandas性能的大忌。

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