在Pandas中按连续序列分组统计数量（模拟SQL DENSE_RANK）

原始DataFrame：

ColA ColB ColC ColD ColF 
 1.    2.   3.   1.   T
 2.    2.   3.   1.   T
 3.    2.   3.   1.   F
 4.    2.   3.   1.   F
 5.    2.   3.   1.   T
 6.    2.   3.   2.   T
 7.    2.   3.   2.   T
 8.    2.   3.   2.   T
 9.    2.   3.   2.   F
 10.   2.   3.   2.   F
 11.   2.   3.   2.   F
 12.   2.   3.   2.   T
 13.   2.   3.   2.   T

期望输出：

ColB ColC ColD ColF grp grpcount

 2.   3.   1.   T.    1.   2

 2.   3.   1.   F.    2.   2

 2.   3.   1.   T.    3.   1

 2.   3.   2.   T.    1.   3

 2.   3.   2.   F.    2.   3

 2.   3.   2.   T.    3.   2

用户尝试了以下代码，但只能将ColF的T和F合并统计，无法保留连续相同ColF值的序列分组：

df_2 = df.sort_values(['ColA'],ascending=True).groupby(['ColB','ColC','ColD','ColF'])['ColA'].count().reset_index(name='grpcount')

数据重建代码：

Data = {
        'ColA':['1','2','3','4','5','6','7','8','9','10','11','12','13'],
        'ColB':['2','2','2','2','2','2','2','2','2','2','2','2','2'],
        'ColC':['3','3','3','3','3','3','3','3','3','3','3','3','3'],
        'ColD':['1','1','1','1','1','2','2','2','2','2','2','2','2'],
        'ColF':['T','T','F','F','T','T','T','T','F','F','F','T','T']}

df = pd.DataFrame(Data,columns=['ColA','ColB','ColC','ColD','ColF'])
print(df)

要实现按ColB、ColC、ColD分组后，对连续的ColF序列分组并统计数量，同时保留原顺序，可通过以下步骤实现：

1. 按ColB、ColC、ColD分组，标记每组内ColF值的连续变化点
2. 基于变化点生成每组内的组号grp
3. 按组合字段分组统计每组数量

具体代码如下：

import pandas as pd

# 数据重建
Data = {
        'ColA':['1','2','3','4','5','6','7','8','9','10','11','12','13'],
        'ColB':['2','2','2','2','2','2','2','2','2','2','2','2','2'],
        'ColC':['3','3','3','3','3','3','3','3','3','3','3','3','3'],
        'ColD':['1','1','1','1','1','2','2','2','2','2','2','2','2'],
        'ColF':['T','T','F','F','T','T','T','T','F','F','F','T','T']}

df = pd.DataFrame(Data,columns=['ColA','ColB','ColC','ColD','ColF'])

# 标记连续序列的变化点：当ColF与上一行不同时，计为新组起点
df['change'] = df.groupby(['ColB','ColC','ColD'])['ColF'].diff().ne(0).cumsum()

# 生成每个分组内的组号grp
df['grp'] = df.groupby(['ColB','ColC','ColD'])['change'].rank(method='dense').astype(int)

# 分组统计grpcount，整理输出结果
result = df.groupby(['ColB','ColC','ColD','ColF','grp'], as_index=False)['ColA'].count().rename(columns={'ColA':'grpcount'})

print(result)

运行后输出结果：

ColB ColC ColD ColF  grp  grpcount
0    2    3    1    T    1         2
1    2    3    1    F    2         2
2    2    3    1    T    3         1
3    2    3    2    T    1         3
4    2    3    2    F    2         3
5    2    3    2    T    3         2

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