如何用Pandas统计各ID的状态变化次数（升降/不变）

给定状态优先级为 C > B > A，现有数据集如下：

需要按ID分组统计每个ID的状态上升、不变、下降次数，期望结果如下：

SQL 实现方案 #

核心思路是先将状态映射为对应优先级的数值，再通过窗口函数获取每个ID的上一条状态值，最后对比统计变化类型。

完整SQL语句 #

SELECT 
    ID,
    SUM(CASE WHEN status_value > prev_status THEN 1 ELSE 0 END) AS Increase,
    SUM(CASE WHEN status_value = prev_status THEN 1 ELSE 0 END) AS Same,
    SUM(CASE WHEN status_value < prev_status THEN 1 ELSE 0 END) AS Decrease
FROM (
    SELECT 
        ID,
        CASE Start 
            WHEN 'C' THEN 3
            WHEN 'B' THEN 2
            WHEN 'A' THEN 1
        END AS status_value,
        LAG(CASE Start 
            WHEN 'C' THEN 3
            WHEN 'B' THEN 2
            WHEN 'A' THEN 1
        END) OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS prev_status
    FROM your_table
) t
WHERE prev_status IS NOT NULL
GROUP BY ID
ORDER BY ID;

说明 #

1. CASE语句将状态转换为数值：C=3，B=2，A=1，方便数值对比
2. LAG()窗口函数按ID分组，获取当前行的上一条状态数值（ORDER BY (SELECT NULL)默认使用原表顺序，若有时间字段建议替换为时间字段排序）
3. 过滤掉无前置状态的第一条记录，分组统计各变化类型的次数

Python（Pandas）实现方案 #

利用Pandas的分组、移位函数实现状态对比与统计。

完整代码 #

import pandas as pd

# 构造数据集（实际使用时可替换为读取文件）
data = {
    'ID': [1007, 1007, 1007, 1008, 1008, 1008, 1008, 1009, 1009],
    'Start': ['A', 'A', 'B', 'B', 'A', 'C', 'B', 'A', 'A']
}
df = pd.DataFrame(data)

# 状态映射为数值
status_map = {'A': 1, 'B': 2, 'C': 3}
df['status_value'] = df['Start'].map(status_map)

# 分组获取上一条状态值
df['prev_status'] = df.groupby('ID')['status_value'].shift(1)

# 过滤无前置状态的行
df = df.dropna(subset=['prev_status'])

# 标记各变化类型
df['Increase'] = (df['status_value'] > df['prev_status']).astype(int)
df['Same'] = (df['status_value'] == df['prev_status']).astype(int)
df['Decrease'] = (df['status_value'] < df['prev_status']).astype(int)

# 分组统计结果
result = df.groupby('ID')[['Increase', 'Same', 'Decrease']].sum().reset_index()
print(result)

输出结果 #

ID  Increase  Same  Decrease
0  1007         1     1         0
1  1008         1     0         2
2  1009         0     1         0

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