如何让pandas cut函数保留无数据区间并在交叉表显示0值

需求：对报表执行次数按指定时间区间分组统计，指定区间为0-10sec、10-30sec、30-60sec、1-2min、>2min。

输入数据：

REPORT,TIME_SEC
Report1,1
Report1,5
Report3,4
Report2,158
Report2,20
Report3,131

现有代码：

import pandas as pd

infile = "/Users/user1/data1.csv"
time_column = "TIME_SEC"
range_column = "TIME_RANGE"
groupby_column = "REPORT"
df = pd.read_csv(infile)
bin1 = [0,10,30,60,120,7200]
label1 = ['0-10sec','10-30sec','30-60sec','1-2min','>2min']
# print(f"df=\n{df}")
df[range_column] = pd.cut(df[time_column], bins=bin1, labels=label1, include_lowest=True)
print(f"df=\n{df}")

df_final = pd.crosstab(df[groupby_column], df[range_column])
df_final.columns = df_final.columns.astype(str)
df_final.reset_index(inplace=True)
print(f"df_final=\n{df_final}")

当前问题：pd.cut生成的结果中不包含无数据的区间，导致pd.crosstab输出缺失30-60sec、1-2min区间。期望最终统计结果包含所有指定区间，无数据的区间对应值为0，如下：

TIME_RANGE   REPORT  0-10sec  10-30sec  30-60sec  1-2min  >2min
0           Report1        2         0         0       0      0
1           Report2        0         1         0       0      1
2           Report3        1         0         0       0      1

有两种可靠的方式实现需求：

方法一：将时间区间列转为有序分类类型 #

通过把TIME_RANGE列设置为指定了所有目标类别的有序分类（Categorical），确保无数据的区间也会被保留。修改代码中生成TIME_RANGE的部分：

import pandas as pd

infile = "/Users/user1/data1.csv"
time_column = "TIME_SEC"
range_column = "TIME_RANGE"
groupby_column = "REPORT"
df = pd.read_csv(infile)
bin1 = [0,10,30,60,120,7200]
label1 = ['0-10sec','10-30sec','30-60sec','1-2min','>2min']

# 生成时间区间并转为指定类别的有序分类
df[range_column] = pd.Categorical(
    pd.cut(df[time_column], bins=bin1, labels=label1, include_lowest=True),
    categories=label1,
    ordered=True
)

# 生成交叉表，此时会包含所有指定区间
df_final = pd.crosstab(df[groupby_column], df[range_column])
df_final.reset_index(inplace=True)
print(f"df_final=\n{df_final}")

原理 #

pd.Categorical强制指定了所有需要保留的类别，即使这些类别在当前数据中没有对应行，后续的pd.crosstab也会将它们纳入统计，无数据的区间自动填充0。

方法二：对交叉表结果补全列 #

如果不想修改原始列的类型，可以在生成交叉表后，用reindex方法补全缺失的区间列并填充0：

import pandas as pd

infile = "/Users/user1/data1.csv"
time_column = "TIME_SEC"
range_column = "TIME_RANGE"
groupby_column = "REPORT"
df = pd.read_csv(infile)
bin1 = [0,10,30,60,120,7200]
label1 = ['0-10sec','10-30sec','30-60sec','1-2min','>2min']

df[range_column] = pd.cut(df[time_column], bins=bin1, labels=label1, include_lowest=True)

df_final = pd.crosstab(df[groupby_column], df[range_column])
# 补全所有指定区间列，无数据的填充0
df_final = df_final.reindex(columns=label1, fill_value=0)
df_final.reset_index(inplace=True)
print(f"df_final=\n{df_final}")

原理 #

reindex会按照指定的columns列表重新排列交叉表的列，缺失的列会被添加，并用fill_value指定的值填充空值。

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