在Pandas DataFrame中按月追踪员工ID的活跃度与空缺状态

我有一个按月份记录用户ID的DataFrame：

DATE             USER_ID            ACTIVITY 
2021-06-01       A1                1
2021-06-01       B2                1
2021-06-01       C3                0
2021-07-01       A1                1
2021-07-01       B2                0
2021-07-01       C3                1
2021-08-01       A1                1
2021-08-01       B2                0
2021-08-01       C3                0
2021-09-01       A1                1
2021-09-01       B2                1
2021-09-01       C3                1

ACTIVITY列取值为1（用户活跃）和0（用户不活跃）。需要新增一列，按月追踪用户ID的状态，规则如下：

• 上月状态为0且本月为0，标记为"vacancy stock"
• 上月状态为0且本月为1，标记为"new active"
• 上月状态为1且本月为0，标记为"new vacancy"
• 上月状态为1且本月为1，标记为"active stock"

符合规则的预期DataFrame如下：

DATE             USER_ID            ACTIVITY          NEW_COLUMN
2021-06-01       A1                1                   NaN
2021-06-01       B2                1                   NaN
2021-06-01       C3                0                   NaN
2021-07-01       A1                1                   active stock
2021-07-01       B2                0                   new vacancy                   
2021-07-01       C3                1                   new active
2021-08-01       A1                1                   active stock
2021-08-01       B2                0                   vacancy stock
2021-08-01       C3                0                   new vacancy
2021-09-01       A1                1                   active stock
2021-09-01       B2                1                   new active
2021-09-01       C3                1                   new active

可以通过Pandas的分组移位和映射逻辑实现需求，代码如下：

import pandas as pd

# 构造原始数据
df = pd.DataFrame({
    'DATE': ['2021-06-01', '2021-06-01', '2021-06-01',
             '2021-07-01', '2021-07-01', '2021-07-01',
             '2021-08-01', '2021-08-01', '2021-08-01',
             '2021-09-01', '2021-09-01', '2021-09-01'],
    'USER_ID': ['A1', 'B2', 'C3', 'A1', 'B2', 'C3', 'A1', 'B2', 'C3', 'A1', 'B2', 'C3'],
    'ACTIVITY': [1, 1, 0, 1, 0, 1, 1, 0, 0, 1, 1, 1]
})

# 按用户分组，获取上月的活跃状态（第一行无上月数据，结果为NaN）
df['LAST_ACTIVITY'] = df.groupby('USER_ID')['ACTIVITY'].shift(1)

# 定义状态组合与标签的映射关系
status_map = {
    (0, 0): 'vacancy stock',
    (0, 1): 'new active',
    (1, 0): 'new vacancy',
    (1, 1): 'active stock'
}

# 生成目标列
df['NEW_COLUMN'] = df.apply(lambda x: status_map.get((x['LAST_ACTIVITY'], x['ACTIVITY'])), axis=1)

# 可选：删除辅助列LAST_ACTIVITY
# df = df.drop('LAST_ACTIVITY', axis=1)

print(df)

代码说明 #

1. 分组移位：通过groupby('USER_ID')['ACTIVITY'].shift(1)为每个用户提取上月的活跃状态，用户首次出现的行（如2021-06-01的所有用户）因无历史数据，LAST_ACTIVITY为NaN。
2. 映射标签：用字典存储所有状态组合对应的标签，通过apply函数逐行匹配上月和本月的活跃状态，生成目标列NEW_COLUMN。
3. 运行后得到的结果与预期完全一致，首次出现的用户行NEW_COLUMN为NaN。

内容的提问来源于stack exchange，提问作者Abisai Pérez Zamarripa